Excel - Data Merge \ Sort

[ThomasBrown]

Hello There,

I have two worksheets in the same excel document.

Worksheet 1:

1 4
2 8
3 2
4 5
5 3


Worksheet 2:

3 2
3 5
2 2
5 8

Now I need to join\sort\merge these two worksheets on a third worksheet so I get a result as follows

1 4 0
2 8 2
3 2 2
3 5
4 4 0
5 3 8


Is something like this possible?

Many Thanks
Thomas

 

[StuckInTheMiddle]

Hi Thomas,

To answer your question, yes this is possible to count both sheets and sum them up on a third sheet using VLOOKUPS

Why do you want 2 rows of 3?

1 4 0
2 8 2
3 2 7
4 4 0
5 3 8


If you really want this then the following will work for you,

On your 3rd worksheet in the A column add 1,2,3,4,5

Then copy/paste this following into the B column

=VLOOKUP(A1,Sheet1!A1:B10,2)

And then do the same for the C column with this formula

=VLOOKUP(A1,Sheet2!A1:B10,2)


you can expand the 10 in range A1:B10 to however many rows you are counting. Hope this helps

A.

 

[StuckInTheMiddle]

If you want to take this a step further and avoid getting N/As when the Vlookup doesn't find a value to return. Use these formulas instead

=IF(ISNA(VLOOKUP(A1,Sheet1!A1:B10,2)),0,VLOOKUP(A1,Sheet1!A1:B10,2))

The same goes for counting Sheet2

=IF(ISNA(VLOOKUP(A1,Sheet2!A1:B10,2)),0,VLOOKUP(A1,Sheet2!A1:B10,2))

 

[KenWright]

I think some of your example data is incorrect, ie no 0 exists for Index 1, and Index 4 should have a value of 5 not 4.

One way, though a tad roundabout:-

Drop W2 Data underneath W1 data, and insert a column at the front, tagging each set of data with W1 or W2 as appropriate. With your data that will give a table such as this:-

Sheet Index Values
W1 1 4
W1 2 8
W1 3 2
W1 4 5
W1 5 3
W2 3 2
W2 3 5
W2 2 2
W2 5 8

Now add a 4th column, call it say Count, and assuming your column header of Count is in say cell D2, put the following formula in D3 and copy down:-

=SUMPRODUCT(--($A$3:A3=A3),--($B$3:B3=B3))

This will give you a table as follows:-

Sheet Index Data Count
W1 1 4 1
W1 2 8 1
W1 3 2 1
W1 4 5 1
W1 5 3 1
W2 3 2 1
W2 3 5 2
W2 2 2 1
W2 5 8 1

Select all the data, do Data / Pivot Table and Chart report, hit Next / Next / Finish.

Now drag:-
INDEX into the ROW fields
SHEET into the COLUMN fields
Values into the DATA field

Then drag COUNT in between the ROW fields and the DATA field.

This will give you a table as follows:-


Sum of Data2 Sheet
Index Count W1 W2
1 1 4
2 1 8 2
3 1 2 2
2 5
4 1 5
5 1 3 8

You can now copy the table and paste special as values, then delete the second column, select the entire first column from the first piece of data down and do Edit / Go To Special / Blanks. Then type = and hit the UP arrow once and hit CTRL+ENTER to enter it. This fills all the blanks in the index field with the values above.

Copy and paste special as values the first column and you are done.

You might actually prefer some of the earlier views so maybe you don't need all the steps.

Regards
Ken...........



----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[KenWright]

Grrrr - where the header reads Data, read Values instead.

This all assumes you want 2 rows for 3. If not then it gets a whole lot easier.

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[StuckInTheMiddle]

Thanks Ken, I should have read the original post more clearly.