consolidating data in a single worksheet

November 2, 2010 at 08:52:45
Specs: Windows Vista
I have an excel worksheet with 50,000 different book isbn, one isbn per cell. Out of the 50,000 there are about 20,000 or 30,000 duplicates with varies qtys. Is there a way to consolidate the duplicate isbn and qty to a single cell without having to do it manually. In column A I have all the isbn and Column B the qty

See More: consolidating data in a single worksheet

November 2, 2010 at 09:11:11
There's lots of ways depending on what you are looking for as the output.

One simple way is to create a Filtered Lst of Unique values someplace else in your workbook and then use SUMIF.

Select Column A
Data...Filter...Advanced Filter
Copy to another location (e.g. G1)
Unique values

This should creat a list containing a single instance of each ISBN.

In H2 enter this and drag it down:

=SUMIF(A:A, G2, B:B)

This will SUM the cells in Column B which correspond to the value in the criteria argument whenever it's found in Column A.

Report •

November 2, 2010 at 09:50:47
Thank you so much. I did a test run and from what I can see it is working properly. I know like very very basic excel. What do you recommend reading to brush up a little on skills.

Report •

November 2, 2010 at 10:13:44
I don't have any recommendations mainly because I never read any books related to Excel.

I just lurked in a few Excel related forums, used Google a lot (still do) and try to find answers to questions by trial and error.

It's amazing how smart you can sound if you can find something close in other forums via Google and then modify it to fit the specific question asked. ;-) Most times I'll give credit where credit is due or even point the OP to the website where I found the answer.

For example, instead of asking here how to deal with the #N/A errors, you could have just Googled something like eliminate isna and gotten lots of hits. Reviewing some of those hits would not only have provided the answer, but you might have run across some other pearl of wisdom that you can put away for another time.

In other words, I don't always know the answers to the questions in this forum, but most times I know how to find/tweak/figure out the answers.

The journey is half the fun.

Report •

Related Solutions

November 2, 2010 at 10:25:40
I got another question related to the consolidation of the isbn. In a different column I have a price for the isbn, but I had it removed earlier as I was trying to eliminate some fields in an attempt to consolidate. How would you reflect the specific price for the isbn in the consolidated format?

I really appreciate your assistance

Report •

Ask Question