LinkBack URL
About LinkBacks
Respected Experts,
I have attached a sample workbook where I want to count unique same values in column B against each unique item in column C. I have given expected answer in the same sheet for proper understanding.
Respected Experts,
I have attached a sample workbook where I want to count unique same values in column B against each unique item in column C. I have given expected answer in the same sheet for proper understanding.
Firstly I cut down your sample to 10,000 rows to make the file a bit smaller so I could re-attach it.
I then created a helper column in your source data that picks out the first unique entry in the V Column and gives it a "1" result, any subsequent matches will give "0" result.
Formula:
Please Login or Register to view this content.
I then created a pivot table that sums the helper column to give the discrete count of V.
Apply the array formulaFormula:Please Login or Register to view this content.
ARRAY Formulas
For M in ROW 3 then drag down.
For V in ROW 3 then drag down.
Please Login or Register to view this content.
Please Login or Register to view this content.
Pl note
Array formula should be confirmed with Ctrl+Shift+Enter keys together.
If answere is satisfactory press * to add reputation.
The previous functions given takes more time. Here is faster solution
In G3
=MIN('sample data'!$C$3:$C$71111)
In G4
=INDEX('sample data'!$C$3:$C$71111,MATCH(G3,'sample data'!$C$3:$C$71111,1)+1)
In H3 . Even though this formula is bit long It is faster.
Drag Formulas in G4 and H3 down.Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks