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:
=IF(SUMPRODUCT(($B$3:$B3=B3)*($C$3:$C3=C3))>1,0,1)
I then created a pivot table that sums the helper column to give the discrete count of V.
Apply the array formulaFormula:=COUNT(1/FREQUENCY(IF('sample data'!C$3:C$71111=B3,'sample data'!B$3:B$71111);IF('sample data'!C$3:C$71111=B3,'sample data'!B$3:B$71111)))
ARRAY Formulas
For M in ROW 3 then drag down.
For V in ROW 3 then drag down.
=INDEX('sample data'!$C$3:$C$71111,SMALL(IF(ISERROR(MATCH('sample data'!$C$3:$C$71111,$E$2:$E2,0)),ROW($C$3:$C$71111),""),1)-ROW($C$3)+1)
=SUM(1/COUNTIF(INDEX('sample data'!$B$3:$B$71111,MATCH($E3,'sample data'!$C$3:$C$71111,0)):INDEX('sample data'!$B$3:$B$71111,MATCH($E3,'sample data'!$C$3:$C$71111,1)),INDEX('sample data'!$B$3:$B$71111,MATCH($E3,'sample data'!$C$3:$C$71111,0)):INDEX('sample data'!$B$3:$B$71111,MATCH($E3,'sample data'!$C$3:$C$71111,1))))
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.=SUM(1/COUNTIF(INDEX('sample data'!$B$3:$B$71111,MATCH($G3,'sample data'!$C$3:$C$71111,0)):INDEX('sample data'!$B$3:$B$71111,MATCH($G3,'sample data'!$C$3:$C$71111,1)),INDEX('sample data'!$B$3:$B$71111,MATCH($G3,'sample data'!$C$3:$C$71111,0)):INDEX('sample data'!$B$3:$B$71111,MATCH($G3,'sample data'!$C$3:$C$71111,1))))
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks