Q. Consolidating a long list, in single workbook.

[George]

Hi,

I have an Excel 2000 speadsheet, which consists of a long list.
This long list has 3 columns.

2 of the colums are alpha, and the one is numeric.

The alpha column may have many duplicates, while one of the remaining
fields (the numeric one) may change for each duplicate.

So we have:

COLUMN A COLUMN B COLUMN C
1010111 ddllol df


Please note that what's in column c doesn't matter to me. It's just
there.

OK, so Column B can have duplicate entries as mentioned.

But, if Column B has any duplicates, you can be sure that the numeric
value in Column A is different.

What I need is to be able to count up how many different 'versions' or
values that any Given value in Column B has.

I know it sounds like an MS Access thing, but I'm sure one of you
bright Excel people can help with this.

Thanks in advance,
George

 

[Bernie Deitrick]

George,

Select your table, then use Data | Pivot Table.

Select the button with the heading for your second column to the row area,
and then the data area, and you will get a table of counts for each unique
value in your second column.

HTH,
Bernie
MS Excel MVP

 

[George]

Bernie,

No. What you suggested just gives me a total count.

Jim

 

[George]

Bernie,

I think my problem, as initially described may require some coding.
But, a simpler way to get where I'm going is to sort this list, by
one of the columns.

Then, as that column will contain duplicates, count how many times
those duplicates occur,

How would I do that?

Thanks,
George

 

[Bernie Deitrick]

Jim,

What I suggested gives a table of counts of each unique item. But to do it,
you need to drop the button on BOTH the row and data areas You get the
total count if you only drop the button on the data area, and not in the row
area.

HTH,
Bernie
MS Excel MVP

 

[George]

Bernie,

I may have been unclear.

What I'm asking about would be akin to having a huge list of
zipcodes.

OK, so being that US cities, can span more than one zipcode in a
state, I want to be able to:

Look at it like this:
I want in my long inventory list to find out how many different cities
per state (based on how many zipcodes that city spans).

If I had an example like that to study, I could apply it to my
inventory task.

Thanks,
George

 

[Bernie Deitrick]

George,

My apologies - to continue your analogy, I thought you wanted the number of
unique ZIP codes per city, not the number of unique cities per state.

To do that, you will need to use a helper column.

Let's say that "State" is in column A, "City" is in column B, and "ZIP
codes" are in column C, and your data is in rows 2 to 200 (with headers in
row 1). We'll make a new column, D, which will be "Count".

In cell D2, array enter the formula (enter with Ctrl-Shift-Enter rather than
just Enter):

=1/SUM(($A$1:$A$200=A2)*($B$1:$B$200=B2))

and copy down to cells D3200.

Then make your pivot table, drag "State" to the row area, and your new
column "Count" to the data area.

HTH,
Bernie
MS Excel MVP