I would sort the data by Originator then by PRN, then use a formula like:
=IF(AND(B9=B8,C9=C8),0,1)
You can then use SUMIF formulas or a pivot table to create a simple summary of unique counts.
Hi all,
Does anyone have an idea how I count the number of unique reference number for each name in an xls? So for example, as attached, Bill has two unique reference numbers (need to be done on much bigger set of data or I would just count!)
Any suggestions would be much appreciated!
Dave
I would sort the data by Originator then by PRN, then use a formula like:
=IF(AND(B9=B8,C9=C8),0,1)
You can then use SUMIF formulas or a pivot table to create a simple summary of unique counts.
This website wants to know your momentum - | Deny | | Allow |
Eventually a Pivot Table ?
Hi DMJC and welcome to the forum
You can also use the Advanced Filter that allows you to replace your full list with a list of unique entries, or you can place the output in a different location. I think its available in the older versions of Excel, but the menu structure could be different from 2010, which I use mostly.
Sorry - I got the wrong end of the stick, having looked at this again. I agree with Pecoflyer - use a Pivot Table.
Or maybe a formula approach, see the attach file. The formula in column E is an array formula, "CSE" to commit the formula and copy down.
Bookmarks