Merging to sheets

[krzysiek kubeczka]

Hi all,

I have the following problem to solve.

I have 2 seperate sheets with data which I need to be merged. I.e.
there is the following data:

Sheet1 - Sales 2006
Customer; Sales
A; 200
B; 400
C; 50
D; 500
E; 100

Sheet2 - Sales 2007
Customer; Sales;
A; 150
C; 30
D; 200
F; 500
G; 800

Result sheet - Sales 2006 / 2007
Customer; Sales 2006; Sales 2007
A; 200; 150
B; 400; - (or 0)
C; 50; 30
D; 500; 200
E; 100; - (or 0)
F; - (or 0); 500
G; - (or 0); 800

Do You have any idea how to solve such a task? Please help me with
that or give me at least some clues what and how to search for the
solution...

With best regards,

Chris

 

[Dave Peterson]

I would do this.

Create a new sheet.

Copy the customer from 2006 to column A. Include the single header row.

Copy the customer from 2007 to the bottom of the data in column A of that new
sheet. Do not include the header.

Use data|filter|advanced filter to get a list of unique customers. Put this
unique list in column B. Look at Debra Dalgleish's site for Advanced filter
with unique records.
http://contextures.com/xladvfilter01.html#FilterUR

Delete column A (since column B has that unique list).

Put 2006 in B1 and 2007 in C1
In B2, put this formula:
=vlookup(a2,'2006'!a:b,2,false)

in C2, put this formula:
=vlookup(a2,'2007'!a:b,2,false)

(This assumes that the worksheets are named 2006 and 2007.)

Copy those formulas down as far as you need.

Select columns B:C
Edit|copy
Edit|paste special|Values

Now remove the #n/a's for the cells that didn't have a matching customer in that
year.

Select columns B:C
edit|replace
what: #n/a
with: (leave blank)
replace all

====
This may sound like a lot of work, but once you do it a few times, you'll find
that you can do it in less than 5 minutes.

 

[krzysiek kubeczka]

Thank You very much! It is a very helpful idea and solution. It
doesn't take much time even with the first try.

With regards,

Chris