Forum Discussion
Consolidating SKU quantities
I'm a relative newbie using Excel to manage warehouse inventory. I have an Excel file of individual SKUs showing location, bin #, quantity and so on. Sometimes the same SKU is found in multiple locations so there are individual entries for each instance. So you may have the same SKU listed 5 times, for example.
I now want to generate a file that has a single entry for each unique SKU. In the case of multiple locations, I want the quantities for each location added together so the file shows a single SKU + the consolidated quantity from each location. If the original file has 5 SKU locations with 10 items per location, then the new report should show 1 SKU with 50 items. Note the location details wouldn't be included in the new file.
I really am new at this. Can someone help?
- I have used Excel and DBase since the 90s. In my Air Force Career, I had to used excel for many various inventors, parts and storage.
You can add another column and use Alpha-Numeric Designators for each SKU; use the File DATA and SORT functions to align all of your SKU in the same location. This lets you see everything in the workbook, worksheet.
Let me know if this helps. There are probably quite a few here who could help you. But you could help us help you by posting a copy of the Excel file you have that is in need of that consolidation. Without knowing how the data are arranged, we could give ideas but that would be risky given your self-description as an Excel newbie. The solutions aren't probably all that difficult, but it would be asking a lot of a newbie to follow suggestions that themselves are done "blind."
Post a copy of the spreadsheet on OneDrive or GoogleDrive, and paste a link here that grants us edit access to it. Make sure that the spreadsheet illustrates the problem, but does not include any proprietary or confidential information.
- Thanks for your suggestions, Mathetes. I've been fortunate to stumble across someone here who seems to know what to do so am working the problem that way.
- I have this exact same problem ... I have multiple entries for the same item number, each entry with its own quantity but the same unit price and description. I want to consolidate them by item number, sum the counts of each identical item number, consolidate the unit prices without summing them and consolidate the descriptions so I have The item number, the combined quantity, the unit price and the description. How did you get yours solved?
