Hey
I have a spreadsheet with two columns one has months (unformatted) from Jan - July
The other column has 250 individual names and over 30,000 different entries
I need a table that shows how many instances there are of each name next to each month
I have attached an example…
Any help would be greatly appreciated!!
Adele x
Try a simple Pivot Table
http://peltiertech.com/Excel/Pivots/pivotstart.htm
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Put this in F3 and copy down
=SUM(INDEX((E3=$C$3:$C$13)*1,))
O see Pivot Table help
Люди, питающие благие намерения, как раз и становятся чудовищами.
Regards, ?Born in USSR?
Vusal M Dadashev
Baku, Azerbaijan
really sorry i have changed the example and reattached it needs to show how many unique names there are
for example in March there is only Dave and Jack as instances of names
sorry!!
Long one, but does what you need. In F3 and copy down
=SUM(INDEX((MATCH(OFFSET($C$2,MATCH(E3,$C$3:$C$13,0),-1,COUNTIF($C$3:$C$13,E3)),OFFSET($C$2,MATCH(E3,$C$3:$C$13,0),-1,COUNTIF($C$3:$C$13,E3)),0)=ROW(INDIRECT("1:"&ROWS(OFFSET($C$2,MATCH(E3,$C$3:$C$13,0),-1,COUNTIF($C$3:$C$13,E3))))))*1,0))
Create a unique list of months by using Advance Filter.
Select C2:C13 and go to Data|Advanced Filter and select Unique Records only.
Select Copy to another Location and enter H2 in the Copy to field.
Click Ok.
Then in I3 enter formula:
=COUNT(1/FREQUENCY(IF($C$3:$C$13=H3,IF($B$3:$B$13<>"",MATCH($B$3:$B$13,$B$3:$B$13,0))),ROW($B$3:$B$13)-ROW($B$3)+1))
confirm with CTRL+SHIFT+ENTER not just ENTER and copy down.
Adjust ranges to suit and reconfirm with CSE keys, before copying down.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks