Finding Number of Unique Values for a specified reference value

[BenDoc08]

Hi Guys

Apologies if I haven't worded this post and title very well, but I'll do my best to explain.

In Column A I have a list of 180 unique ID numbers that repeat often (I have 9000 rows). In Column M I have an alpha numeric code. This alpha numeric code can be different for the same ID Number. (So ID Number 255 can have numerous alpha numeric codes associated with it). What I want to be able to do is count how many different/unique Alpha Numeric codes exist for a particular ID Number.

Is there a way to do this without using VBA? And if so, is there a formulaic way of identifying the most common Alpha Numeric code for a particular ID Number?

Thanks in advance,
Ben

 

[p45cal]

Pivot table, it'll take you a minute or so.

 

[BenDoc08]

Ah, of course!

Cheers mate

 

[BenDoc08]

Pivot Table Data (Sheet B) has worked so far but now I'm stuck trying to do a lookup:

I have my Unique ID as a row label, then I have my alpha numeric value also as a row label, so it's basically a sub-category if you know what I mean. I have also used an Alpha Numeric value count so that the alpha numeric value immediately below my unique ID is the most common value (for that Unique ID).

What I now want to do is basically use a formula (in Sheet A) that says find my Unique ID on Sheet B and take the cell value directly beneath that. I also want to make it automatically take the count value in an adjacent cell.

It's kind of like trying to do a HLOOKUP on a VLOOKUP reference value, but I don't know how to do that. Anyone able to shed some light?

Cheers again

 

[xenou]

It's generally better to keep your data intact and separated from the reporting of the data. Then a pivot table is light work:

<img alt="image of pivot table" src="http://northernocean.net/etc/mrexcel/20110915.png" />

The PT quickly shows that we have in this data set (counting a third column which is arbitrary):
1A - 3
1B - 1
2A - 2
3C - 1
3D - 1
5C - 1

It's a little different question if you've now set your data up with staggered totals and need to use an index to find the cells of interest rather than getting the counts of unique ID-Code combinations. Too late at night for me right now to tackle that. That sounds like an Index(Match()) question to me (explained here).