What I would like to do is take the follwoing data:

Inventory Tag Item Location Qty
22 H00016 770 20
100 H00016 770 10
76 H00016 ATO01 5
99 H00047 770 50
300 H00101 BOH01 22

and end up with with the following in a new worksheet:

Thanks,
Bob

Item Location Qty
H00016 770 30
H00016 ATO01 5
H00047 770 50
H00101 BOH01 22

Basically I now have one line per location with the quantities summed up
into a new worksheet or workbook. Now I can upload the data to the inventory
system.

On May 27, 4:29*pm, Bob <B...@discussions.microsoft.com> wrote:
> What I would like to do is take the follwoing data:
>
> Inventory Tag * Item * *Location * * * *Qty
> 22 * * * * * * * * * * *H00016 *770 * * * * * 20
> 100 * * * * * * * * * * H00016 *770 * * 10
> 76 * * * * * * * * * * *H00016 *ATO01 * 5
> 99 * * * * * * * * * * *H00047 *770 * * 50
> 300 * * * * * * * * * * H00101 *BOH01 * 22
>
> and end up with with the following in a new worksheet:
>
> Thanks,
> Bob
>
> Item * * * * Location * *Qty
> H00016 *770 * * 30
> H00016 *ATO01 * 5
> H00047 *770 * * 50
> H00101 *BOH01 * 22
>
> Basically I now have one line per location with the quantities summed up
> into a new worksheet or workbook. Now I can upload the data to the inventory
> system.

Depending on how often this is done, it may be easist to just do an
Advanced Filter to get the unique item/location combos and then do a
SUMIF formula for the quantity.

A pivot table would be ideal for what you are asking. Put your cursor in the
middle of your data somewhere and Select Data -> Pivot Table -> Follow the
Wizard that pops up (or just select finish as it normally makes the correct
decisions for you). Place your item code and location in the left column. Put
your quantities in the center data section and you are done...
--
HTH...

Jim Thomlinson


"Bob" wrote:

> What I would like to do is take the follwoing data:
>
> Inventory Tag Item Location Qty
> 22 H00016 770 20
> 100 H00016 770 10
> 76 H00016 ATO01 5
> 99 H00047 770 50
> 300 H00101 BOH01 22
>
> and end up with with the following in a new worksheet:
>
> Thanks,
> Bob
>
> Item Location Qty
> H00016 770 30
> H00016 ATO01 5
> H00047 770 50
> H00101 BOH01 22
>
> Basically I now have one line per location with the quantities summed up
> into a new worksheet or workbook. Now I can upload the data to the inventory
> system.