Algorithm to combine identical items in a list

[William]

I have a rather large worksheet that contains a list of all items in a
warehouse inventory. I would like to be able to combine duplicate
items as new inventory is added.

Each item has (among other fields) an item ID, a color and a
condition. I would like to combine only the lines where all of these
fields are identical, summing the quantities in each record, and
recalculating the cost based on the cost field in each record.
Currently, I do this by starting at the top of the list and comparing
each record to all of the records below it to the end of the list.
This is rather time-consuming as the macro must do thousands of
comparisons for each of the thousands of records in the list. Is there
a faster way to accomplish this without changing the sort order of the
list?

Thanks for your help,
William

 

[Ron Rosenfeld]

I have a rather large worksheet that contains a list of all items in a
warehouse inventory. I would like to be able to combine duplicate
items as new inventory is added.

Each item has (among other fields) an item ID, a color and a
condition. I would like to combine only the lines where all of these
fields are identical, summing the quantities in each record, and
recalculating the cost based on the cost field in each record.
Currently, I do this by starting at the top of the list and comparing
each record to all of the records below it to the end of the list.
This is rather time-consuming as the macro must do thousands of
comparisons for each of the thousands of records in the list. Is there
a faster way to accomplish this without changing the sort order of the
list?

Thanks for your help,
William

It's hard for me to be specific without some good examples.

Some methods might include the Data/Consolidation , Pivot tables, or possibly
constructing an array of ID&color&condition; then sorting on that array and
using then Subtotaling and collapsing the display.
--ron

 

[joel]

Can you post a few rows of the worksheet so we know which columns t
compare and which column contains the quantities