combining multiple columns

[cwyenberg]

I have customer sales data from multiple years. Each year we have repeat customers, former customers that had no sales and new customers.

I wish to condense the customer list from multiple years and display the corresponding sales amounts in the following columns.

I've attached an example showing two years of sales history but may have 3 or four years to compare. the desired result is highlighted in yellow.

I am only assuming that this requires VBA and perhaps could be solved with a formula.

Thanks

[skywriter]

H2:

Please Login or Register  to view this content.

I2:

Please Login or Register  to view this content.

Both formulas can be copied down.

[cwyenberg]

This should work. At the same time I'll need to combine the the customer name columns (A and C) into one column and remove duplicates. How would this be accomplished?

[FlameRetired]

Try this in G2 and fill down.

Please Login or Register  to view this content.

This works for the two years in question. It's too cumbersome for more than that. I'm looking for another approach for 3, 4, 5 .... etc. years.

Do you have data in a workbook similar to what you posted with more than 2 years / lists to work with? Having more data might be helpful.

BTW it appears that the list in column C has all you need already. I just now noticed. That's why this formula works.

[shukla.ankur281190]

Put below formula in G2 cell with Ctrl+Shift+Enter and drag down. Below G1 formula is refer by Mr.Shiva.

Please Login or Register  to view this content.

Put below formula in H2 cell and drag down.

Please Login or Register  to view this content.

Put below formula in I2 cell and drag down.

Please Login or Register  to view this content.

[FlameRetired]

@cwyenberg

Leave it to me to overlook the obvious. Copy and paste each customer list into one column. Then use remove duplicates.

[cwyenberg]

While all suggestions worked, a combination of skywriter and FlameRetired solutions gave me what I needed.

Thanks to all

CW

[skywriter]

Thanks for marking the thread as solved.