Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Consolidating Data from Multiple lines
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Consolidating Data from Multiple lines
Have a look in the help index for SUMIF
-- Don Guillett Microsoft MVP Excel SalesAid Software "Bob" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Consolidating Data from Multiple lines
On May 27, 4:29*pm, 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. 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Consolidating Data from Multiple lines
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Consolidating data from multiple spreadsheets... | Excel Discussion (Misc queries) | |||
Merging or Consolidating Data from Multiple Workbooks | Excel Discussion (Misc queries) | |||
Consolidating data from multiple sheets. | Excel Discussion (Misc queries) | |||
need help in consolidating multiple data | Excel Discussion (Misc queries) | |||
Need advice : consolidating data from multiple CSV files in Excel - External data handling | Excel Programming |