Consolidating Data from Multiple lines

  • Thread starter Thread starter Bob
  • Start date Start date
B

Bob

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.
 
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.
Click to expand...

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...
 
Back
Top