Combine similar data from multiple sheets

**maacmaac** · 02-07-2010, 03:50 PM

I have a workbook with 4 sheets (call them Q1, Q2, Q3, Q4). Each sheet contains ~38,000 rows of data.

I need to combine all 4 sheets into one single sheet, but in the process I need to combine similar data that occurs over all 4 sheets. Similar data would be all of the following being equal:
1. Sales Rep Number
2. Account Number
3. Invoice Number

For example (example 1)
............Sales Rep............Account Number..............Invoice Number..........Sales Amount
Q1.......Mike....................12345...............................TX-EX34.....................10,000
Q3.......Mike....................12345...............................TX-EX34.....................20,000
Q4.......Mike....................12345...............................TX-EX34.....................50,000

After combining data, I would have one single line as follows:
............Sales Rep............Account Number..............Invoice Number..........Sales Amount
............Mike....................12345...............................TX-EX34.....................80,000

For example (example 2)
............Sales Rep............Account Number..............Invoice Number..........Sales Amount
Q3.......Jim.......................45774...............................PRS-ET99...................45,000
Q4.......Jim.......................45774...............................PRS-ET99...................15,000

After combining data, I would have one single line as follows:
............Sales Rep............Account Number..............Invoice Number..........Sales Amount
............Jim.......................45774...............................PRS-ET99...................60,000

I have searched all over the forums, but haven’t found anything that is similar to what I am looking for. I have attached a spreadsheet with sample data, which includes the desired output. Any assistance is appreciated. Thanks.

**Richard Buttrey** · 02-07-2010, 04:04 PM

Hi,

Do you have access to Excel 2007?
I ask since the easiest way would be for a macro to first combine all four sheets into one, then extract a unique list of Invoice Numbers (which appears to be the end requirement) and finally summarise by Invoice number along with noting the client name account number and rep number. Excel 2007 would be needed for this approach because of the combined number of rows.

Regards

**maacmaac** · 02-07-2010, 06:46 PM

I do have 2007, but only at home. The company I work for only has 2003. Also, other users would need to use code who also only have 2003.

Also, I looked at your comments. The Invoice Number isn't unique. Sorry I wasn't clear in my initial description. More than one sales rep can be one the same invoice number. See other example below. To combine multiple rows into one row, the "Sales Rep", "Account Number" and "Invoice Number" must all be equal. Thanks.

............Sales Rep............Account Number..............Invoice Number..........Sales Amount
Q1.......Mike....................12345...............................TX-EX34.....................10,000
Q2.......Mike....................12345...............................TX-EX34.....................20,000
Q3.......Mike....................12345...............................TX-EX34.....................20,000
Q3.......Jim......................34625...............................TX-EX34.....................20,000
Q4.......Mike....................12345...............................TX-EX34.....................50,000
Q4.......Jim......................34625...............................TX-EX34.....................50,000

After combining data, I would have one single line as follows:
............Sales Rep............Account Number..............Invoice Number..........Sales Amount
............Mike....................12345...............................TX-EX34.....................80,000
............Jim......................34625...............................TX-EX34.....................70,000

**Richard Buttrey** · 02-07-2010, 06:50 PM

Hi,

I wasn't suggesting that the invoice numbers would would generate a single unique row, just that the invoice numbers would need to be extracted first and then used along with the rep numbers to generate the relevant rows. However since you don't have 2007 a rethink is needed. I'll take another look.

Regards