Consoildation of data for upload
 

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

INPUT TABLE
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:

OUTPUT TABLE
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.

One suggestion was to use an advanced filter to get the unique
Item/Locations and the do a sumif to get the numbers put together. I have not
been able to get a sum if to do this. How do I do it? In order to get the
unique Item/Location combinations, I had to comebine them into a string. I
presume I would continue using this string for the SUMIF.

Thanks
Bob

Consoildation of data for upload
 

have you tried Pivot Table?
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"Bob" escreveu:

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

INPUT TABLE
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:

OUTPUT TABLE
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.

One suggestion was to use an advanced filter to get the unique
Item/Locations and the do a sumif to get the numbers put together. I have not
been able to get a sum if to do this. How do I do it? In order to get the
unique Item/Location combinations, I had to comebine them into a string. I
presume I would continue using this string for the SUMIF.

Thanks
Bob

Consoildation of data for upload
 

Yes, a pivot table works if I did not have to upload the data. The data needs
to be EXACTLY as in the output table. The problem with the pivot table is
that it has totals in it and other stuff around it which I need to strip to
be able to upload into our main system.

That's where I'm stuck with the pivot table concept.


"Marcelo" wrote:

have you tried Pivot Table?
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"Bob" escreveu:

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

INPUT TABLE
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:

OUTPUT TABLE
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.

One suggestion was to use an advanced filter to get the unique
Item/Locations and the do a sumif to get the numbers put together. I have not
been able to get a sum if to do this. How do I do it? In order to get the
unique Item/Location combinations, I had to comebine them into a string. I
presume I would continue using this string for the SUMIF.

Thanks
Bob