Help Merging Identical rows within a large set of data

[shadestreet]

I have about 4500 different SKU's listed in rows in a file. (An "SKU"
stands for Stock Keeping Unit, and is basically a numeric code to
identify a product-FYI).

Each one of these SKUs has several characteristics, such as Cases per
Pallet, weight per case, dollars per unit, etc that are listed in
columns.

It turns out that some of these SKU numbers are identical, so I wanted
to take any that were identical and merge them together. Basically I
need to delete the duplicate row except for the value in Column G which
is the amount of units sold. That value I will need to add to column G
of the original row.

The only solution I can think of is to use Subtotals and set it so at
each change in SKU it adds the units sold. Then I could find any
Subtotal that consists of more than one SKU, manually fix the value for
column G, and delete the duplicate row. After I do this for all of
them, i could resort to remove subtotals. This solution is tedious,
can anyone think of a faster and mroe efficient solution?

Thanks

 

[Bob Flanagan]

One approach would be to

make a copy of the worksheet
Delete all duplicates*
On the original worksheet, run a pivot table of SKU and amount sold.
Then, on the new worksheet, use a vlookup to lookup the sku in the pivot
table and return the amount sold.
Lastly convert the vlookup formulas to values by doing a copy, paste special
values onto the original range.

The vlookup should cover the range of the sku and the amount sold columns.
The last argument should be "false" for a perfect match. The sum of the
result across all rows should equal the original data.

To get rid of the duplicates, you can search for code in the forum that will
help yuu. Or you can purchase a duplicate finder, which I just happen to
sell <grin>.

Bob Flanagan
Macro Systems
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel