Combine multiple datasets into one

[barrymac20]

Hi All.
I hope i'm in the correct sub.....

I'm developing a product configurator in excel and I'm at a point now where I can generate multiple bills of materials for separate parts of my product. These BOMs consist of part numbers and quantities, so two columns of data.

I will have 20 sets of this data and I want to be able to combine them into one set of data with any duplicate part number and their quantities being added together.
I have been trying to use the consolidate function but its a bit clunky, or maybe i' doing it wrong..... My lists can be dynamic so I want the combined list to auto update also. Not sure if "consolidate" can do this easily.

Any help would be greatly appreciated.

P.S. I also need the consolidated list to automatically update if I make a change to a configuration.

Barry

[noboffinme]

Hi,
Can you post a file or example?

[barrymac20]

I have attached a sample with two sets of data. There will be up to 20 sets in practice.
The Data in columns D,E,N,O is derived from formulae. (Formulas are removed in the sample attached)
The Data in columns B,C,L,M are copys of the data in D,E,N,O.
I've gone this route as I couldn't get the consolidate to work with the columns with formulae. I've had a bit more joy with the copied data.

[barrymac20]

Sorry Guys. For some reason I can't upload the file!!

[noboffinme]

Hi Barry,
As this is a bit complex, it would be good to see either the file or if it's a very large file, some dummy data that replicates the original.
What problems are you having posting the file?

[alansidman]

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[barrymac20]

File is now attached

Hi All.
I hope i'm in the correct sub.....

I'm developing a product configurator in excel and I'm at a point now where I can generate multiple bills of materials for separate parts of my product. These BOMs consist of part numbers and quantities, so two columns of data.

I will have 20 sets of this data and I want to be able to combine them into one set of data with any duplicate part number and their quantities being added together.
I have been trying to use the consolidate function but its a bit clunky, or maybe i' doing it wrong..... My lists can be dynamic so I want the combined list to auto update also. Not sure if "consolidate" can do this easily.

Any help would be greatly appreciated. I'm open to a VBA option if thats better....

P.S. I also need the consolidated list to automatically update if I make a change to a configuration.

Barry

[noboffinme]

Hi barrymac20,
Thanks for posting the data, my first piece of feedback is that the format of how you keep your data needs to change.
You will be able to do much more with your data if you format it n the same way as the attachment.
Cheers

[barrymac20]

Thanks for the feedback.

Can I ask why you think its better that way? Is it because its easier to consolidate as the data is in the same column?

Note that the items in column F and G (in your example) are dynamic. These will change depending on whats selected elsewhere in the spreadsheet.

[noboffinme]

You have split your data into separate tables, therefore, to get it back together and report from it, you need to either change to what I've suggested or consolidate the data in another way (Pivot Tables with consolidated ranges etc...)
Example if you tried to summarise your data in a Pivot Table, you have 2 Columns called 'Part Number', 'Qty' etc so you will one get an error about duplicate names, let's say you change the names to 'Part Number1', 'Qty1'. 'Part Number2', 'Qty2' to overcome this, which Column do you use in the Pivot?
Btw, It won't matter if Columns 'F' & 'G' are dynamic.
It would be the same even without a Pivot Table.
Hope this makes sense.