Unique Records in a Column with a twist
Hi All,
I would like to place a formula in cells A2 down that does a running count of unique combinations of columns B and C.
For example, the first record is a white dog, so at that point there is 1 category of dog, next is a brown dog, so there there is now 2 categories etc.
I initially tried to adapt an array formula 1/COUNTIF($B$2:$B8,$B$2:$B8) but this wont quite do the trick.
Please see file attached.
Any ideas from anyone would be most appreciated.
Thanks
Ciaran Mc
In A2 enter:
=COUNTIF($B$2:B2,B2)
and copy down
Gary's Student
THis will give you a count of unique B&C combinations, but based on your sample, that isnt what you want...
=SUMPRODUCT(($B$2:$B$9=B2)*($C$2:$C$9=C2))
this will give tyou a running count of unique items in B, which is exactly what your sample shows, but it completely ignores column C...
=COUNTIF($B$2:B2,B2)
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Hi Jakobshavn,
I should have made my example a little clearer but this is not what Im looking for.
Column A should show a running count of animal and colour. In this attachment, you can see that the first 2 records are white dogs. So cell A3 should be 1 as there is only 1 unique combinations of dog and colour so far ! Cell A8 is 2 with the introduction of another colour, if you get me
Hope you can help
Thanks
Ciaran
I dont understand your logic...
you have 2 for cat-orange, but there is only 1 cat-orange
Please Login or Register to view this content.
yet you have dog-white twice but only count it once??
Please explain exactly how you arrive at the answers you have, because it is not clear to me what you want
Hi Dibbins, sorry this is a tricky one to explain !
You are correct, there is only 1 orange cat, however oranage is the second cat colour used thus far. The last row is 3 because at that particular row there are 3 colours used for cat with the addition of pink also. So col A is counting the number of animal colurs used thus far !
Hope this makes sense
Thanks
Ciaran
If you can sort the data, you could use this, copied down...
=IF(H2=H1,IF(I2=I1,K1,K1+1),1)
Or perhaps try using a Pivot table, with...
column lable = color
row label = animal
values = count of animals
hi Ciaran. try this array in A2:
=SUM(IF(FREQUENCY(IF(B$2:B2=B2,MATCH(C$2:C2,C$2:C2,0)),ROW(B$2:B2)-ROW($B$2)+1)>0,1))
you have to paste the formula inside the formula bar & press CTRL + SHIFT + ENTER
Thanks, if you have clicked on the * and added our rep.
If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".
"Contentment is not the fulfillment of what you want, but the realization of what you already have."
Tips & Tutorials I Compiled | How to Get Quick & Good Answers
A2, control+shift+enter, not just enter, and copy down:Originally Posted by ciaran01
Hi Jakobshavn,
I should have made my example a little clearer but this is not what Im looking for.
Column A should show a running count of animal and colour. In this attachment, you can see that the first 2 records are white dogs. So cell A3 should be 1 as there is only 1 unique combinations of dog and colour so far ! Cell A8 is 2 with the introduction of another colour, if you get me
Hope you can help
Thanks
Ciaran
Please Login or Register to view this content.
Great solution thanks a lot guys
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks