Merging 2 sheets

K

Khan

I have this old technical problem that every one is facing ....
How to merge data on two different sheets knowing that they share
one unique column (called one-to-one merging) ??

Example :

Sheet1 : Sheet2 :

A B C A D F

Now the combined result on another sheet will be :

Sheet3:

A B C D F

Important remark : The sheets i am using contain more than 60,000 rows
per sheet,
and please try to think of a solution
other than using the function VLOOKUP.

Thanks.
 
D

Don Guillett

If??? the rows are identical (probably not) then just copy the columns....
If not, show us examples of your layout
 
G

GB

Khan said:
Important remark : The sheets i am using contain more than 60,000 rows
per sheet,
and please try to think of a solution
other than using the function VLOOKUP.

Thanks.
Click to expand...

vlookup is too slow, too large, what? vlookup (or match) is there to do
precisely the job you defined.

Is this a one-off job or does it need to be updated whenever data is added?

Alternatives are to sort both source sheets into the same order, that sort
of thing, as Don has pointed out.
 
K

Khan

vlookup is too slow, too large, what? vlookup (or match) is there to do
precisely the job you defined.

Is this a one-off job or does it need to be updated whenever data is added?

Alternatives are to sort both source sheets into the same order, that sort
of thing, as Don has pointed out.
Click to expand...

The rows are absolutely not identical (below sample doesn't show
that), so copying fields is not an option.
The job is repeated monthly.
VLOOKUP is most efficient and retrieves the matching data as my
request...... that i know,
but when you have more than 60,000 rows and i mean it will reach more
than that, then just draging
the formula (on 3 fields) to all rows is time consuming and too slow
and Excel in some cases will hang.

This is an example :
First sheet containing data which we have to fetch matching
information by account number from the other sheet.

Account WASSTATUS WASENR WASOVERDUEAGE

1 COLLECTION 77291 90 DPD
2 REJECTED 0 0 DPD
3 TERMINATED 0 0 DPD
4 COLLECTION 80493 0 DPD
5 COLLECTION 67788 30 DPD
6 COLLECTION 132091 59 DPD
7 REJECTED 0 0 DPD
8 COLLECTION 78351 30 DPD
9 REJECTED 0 0 DPD
10 REJECTED 0 0 DPD
11 CANCELLED 0 0 DPD
12 COLLECTION 203208 30 DPD
13 COLLECTION 217745 0 DPD
14 COLLECTION 248821 0 DPD
15 REJECTED 0 0 DPD
16 REJECTED 0 0 DPD
17 SUSPENDED 0 0 DPD
18 TERMINATED 0 0 DPD
19 COLLECTION 88733 30 DPD
20 COLLECTION 175918 0 DPD
21 COLLECTION 100350 30 DPD
22 TERMINATED 0 0 DPD
23 TERMINATED 0 0 DPD
24 COLLECTION 63988 0 DPD
25 COLLECTION 98107 0 DPD
26 COLLECTION 130214 0 DPD
27 COLLECTION 167072 0 DPD
28 COLLECTION 134874 0 DPD
29 COLLECTION 177736 0 DPD

Sheet2:
Account ISSTATUS ISENR ISOVERDUEAGE

1 COLLECTION 75944 90 DPD
2 REJECTED 1 DPD
3 REJECTED 0 DPD
4 TERMINATED 0 DPD
5 COLLECTION 78938 0 DPD
6 COLLECTION 67788 61 DPD
7 COLLECTION 127000 61 DPD
8 REJECTED 0 DPD
9 COLLECTION 77029 30 DPD
10 REJECTED 0 DPD
11 REJECTED 0 DPD
12 CANCELLED 0 DPD
13 COLLECTION 199064 30 DPD
14 COLLECTION 213540 0 DPD
15 COLLECTION 243580 0 DPD
16 REJECTED 0 DPD
17 REJECTED 0 DPD
18 SUSPENDED 0 DPD
19 TERMINATED 0 DPD
20 COLLECTION 87077 30 DPD
21 COLLECTION 172212 0 DPD
22 COLLECTION 100350 61 DPD
23 TERMINATED 0 DPD
24 TERMINATED 0 DPD
25 COLLECTION 62641 0 DPD
26 COLLECTION 96041 0 DPD
27 COLLECTION 127700 0 DPD
28 COLLECTION 163553 0 DPD
29 COLLECTION 132270 0 DPD

Result:

Account WASSTATUS WASENR WASOVERDUEAGE ISSTATUS ISENR
ISOVERDUEAGE

1 COLLECTION 77291 90 DPD COLLECTION 75944 90 DPD
2 REJECTED 0 0 DPD REJECTED 1 DPD
3 TERMINATED 0 0 DPD REJECTED 0 DPD
4 COLLECTION 80493 0 DPD TERMINATED 0 DPD
5 COLLECTION 67788 30 DPD COLLECTION 78938 0 DPD
6 COLLECTION 132091 59 DPD COLLECTION 67788 61 DPD
7 REJECTED 0 0 DPD COLLECTION 127000 61 DPD
8 COLLECTION 78351 30 DPD REJECTED
0DPD
9 REJECTED 0 0 DPD COLLECTION 77029 30 DPD
10 REJECTED 0 0 DPD REJECTED 0 DPD
11 CANCELLED 0 0 DPD REJECTED 0 DPD
12 COLLECTION 203208 30 DPD CANCELLED 0 DPD
13 COLLECTION 217745 0 DPD COLLECTION 199064 30 DPD
14 COLLECTION 248821 0 DPD COLLECTION 213540 0 DPD
15 REJECTED 0 0 DPD COLLECTION 243580 0 DPD
16 REJECTED 0 0 DPD REJECTED 0 DPD
17 SUSPENDED 0 0 DPD REJECTED 0 DPD
18 TERMINATED 0 0 DPD SUSPENDED 0 DPD
19 COLLECTION 88733 30 DPD TERMINATED 0 DPD
20 COLLECTION 175918 0 DPD COLLECTION 87077 30 DPD
21 COLLECTION 100350 30 DPD COLLECTION 172212 0 DPD
22 TERMINATED 0 0 DPD COLLECTION 100350 61 DPD
23 TERMINATED 0 0 DPD TERMINATED 0 DPD
24 COLLECTION 63988 0 DPD TERMINATED 0 DPD
25 COLLECTION 98107 0 DPD COLLECTION 62641 0 DPD
26 COLLECTION 130214 0 DPD COLLECTION 96041 0 DPD
27 COLLECTION 167072 0 DPD COLLECTION 127700 0 DPD
28 COLLECTION 134874 0 DPD COLLECTION 163553 0 DPD
29 COLLECTION 177736 0 DPD COLLECTION 132270 0 DPD

Appreciate your replies
Thanks.
 
K

Klemen25

I apologize to mention this, but just to be sure...
You do not need to drag the formula- you can write it in one cell,
select the entire column and hit ctrl+v.
But I think you know that....
Good luck
 
D

Don Guillett

I'm still not quite sure what your end result should look like. There are
several things you can do.
1. Have a macro copy your lookup formulas
2. Do the above and change to values to reduce calculation overhead of all
these formulas
3. I would use a macro to place the data, possibly using find or FindNext or
for/each.

I would have to see a sample workbook with before/after examples. Send to my
address below if you like.
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
vlookup is too slow, too large, what? vlookup (or match) is there to do
precisely the job you defined.

Is this a one-off job or does it need to be updated whenever data is
added?

Alternatives are to sort both source sheets into the same order, that sort
of thing, as Don has pointed out.
Click to expand...

The rows are absolutely not identical (below sample doesn't show
that), so copying fields is not an option.
The job is repeated monthly.
VLOOKUP is most efficient and retrieves the matching data as my
request...... that i know,
but when you have more than 60,000 rows and i mean it will reach more
than that, then just draging
the formula (on 3 fields) to all rows is time consuming and too slow
and Excel in some cases will hang.

This is an example :
First sheet containing data which we have to fetch matching
information by account number from the other sheet.

Account WASSTATUS WASENR WASOVERDUEAGE

1 COLLECTION 77291 90 DPD
2 REJECTED 0 0 DPD
3 TERMINATED 0 0 DPD
4 COLLECTION 80493 0 DPD
5 COLLECTION 67788 30 DPD
6 COLLECTION 132091 59 DPD
7 REJECTED 0 0 DPD
8 COLLECTION 78351 30 DPD
9 REJECTED 0 0 DPD
10 REJECTED 0 0 DPD
11 CANCELLED 0 0 DPD
12 COLLECTION 203208 30 DPD
13 COLLECTION 217745 0 DPD
14 COLLECTION 248821 0 DPD
15 REJECTED 0 0 DPD
16 REJECTED 0 0 DPD
17 SUSPENDED 0 0 DPD
18 TERMINATED 0 0 DPD
19 COLLECTION 88733 30 DPD
20 COLLECTION 175918 0 DPD
21 COLLECTION 100350 30 DPD
22 TERMINATED 0 0 DPD
23 TERMINATED 0 0 DPD
24 COLLECTION 63988 0 DPD
25 COLLECTION 98107 0 DPD
26 COLLECTION 130214 0 DPD
27 COLLECTION 167072 0 DPD
28 COLLECTION 134874 0 DPD
29 COLLECTION 177736 0 DPD

Sheet2:
Account ISSTATUS ISENR ISOVERDUEAGE

1 COLLECTION 75944 90 DPD
2 REJECTED 1 DPD
3 REJECTED 0 DPD
4 TERMINATED 0 DPD
5 COLLECTION 78938 0 DPD
6 COLLECTION 67788 61 DPD
7 COLLECTION 127000 61 DPD
8 REJECTED 0 DPD
9 COLLECTION 77029 30 DPD
10 REJECTED 0 DPD
11 REJECTED 0 DPD
12 CANCELLED 0 DPD
13 COLLECTION 199064 30 DPD
14 COLLECTION 213540 0 DPD
15 COLLECTION 243580 0 DPD
16 REJECTED 0 DPD
17 REJECTED 0 DPD
18 SUSPENDED 0 DPD
19 TERMINATED 0 DPD
20 COLLECTION 87077 30 DPD
21 COLLECTION 172212 0 DPD
22 COLLECTION 100350 61 DPD
23 TERMINATED 0 DPD
24 TERMINATED 0 DPD
25 COLLECTION 62641 0 DPD
26 COLLECTION 96041 0 DPD
27 COLLECTION 127700 0 DPD
28 COLLECTION 163553 0 DPD
29 COLLECTION 132270 0 DPD

Result:

Account WASSTATUS WASENR WASOVERDUEAGE ISSTATUS ISENR
ISOVERDUEAGE

1 COLLECTION 77291 90 DPD COLLECTION 75944 90 DPD
2 REJECTED 0 0 DPD REJECTED 1 DPD
3 TERMINATED 0 0 DPD REJECTED 0 DPD
4 COLLECTION 80493 0 DPD TERMINATED 0 DPD
5 COLLECTION 67788 30 DPD COLLECTION 78938 0 DPD
6 COLLECTION 132091 59 DPD COLLECTION 67788 61 DPD
7 REJECTED 0 0 DPD COLLECTION 127000 61 DPD
8 COLLECTION 78351 30 DPD REJECTED
0DPD
9 REJECTED 0 0 DPD COLLECTION 77029 30 DPD
10 REJECTED 0 0 DPD REJECTED 0 DPD
11 CANCELLED 0 0 DPD REJECTED 0 DPD
12 COLLECTION 203208 30 DPD CANCELLED 0 DPD
13 COLLECTION 217745 0 DPD COLLECTION 199064 30 DPD
14 COLLECTION 248821 0 DPD COLLECTION 213540 0 DPD
15 REJECTED 0 0 DPD COLLECTION 243580 0 DPD
16 REJECTED 0 0 DPD REJECTED 0 DPD
17 SUSPENDED 0 0 DPD REJECTED 0 DPD
18 TERMINATED 0 0 DPD SUSPENDED 0 DPD
19 COLLECTION 88733 30 DPD TERMINATED 0 DPD
20 COLLECTION 175918 0 DPD COLLECTION 87077 30 DPD
21 COLLECTION 100350 30 DPD COLLECTION 172212 0 DPD
22 TERMINATED 0 0 DPD COLLECTION 100350 61 DPD
23 TERMINATED 0 0 DPD TERMINATED 0 DPD
24 COLLECTION 63988 0 DPD TERMINATED 0 DPD
25 COLLECTION 98107 0 DPD COLLECTION 62641 0 DPD
26 COLLECTION 130214 0 DPD COLLECTION 96041 0 DPD
27 COLLECTION 167072 0 DPD COLLECTION 127700 0 DPD
28 COLLECTION 134874 0 DPD COLLECTION 163553 0 DPD
29 COLLECTION 177736 0 DPD COLLECTION 132270 0 DPD

Appreciate your replies
Thanks.
 
G

GB

So, which of the fields in your example is the account number? Surely, not
1,2,3 etc?


vlookup is too slow, too large, what? vlookup (or match) is there to do
precisely the job you defined.

Is this a one-off job or does it need to be updated whenever data is
added?

Alternatives are to sort both source sheets into the same order, that sort
of thing, as Don has pointed out.
Click to expand...

The rows are absolutely not identical (below sample doesn't show
that), so copying fields is not an option.
The job is repeated monthly.
VLOOKUP is most efficient and retrieves the matching data as my
request...... that i know,
but when you have more than 60,000 rows and i mean it will reach more
than that, then just draging
the formula (on 3 fields) to all rows is time consuming and too slow
and Excel in some cases will hang.

This is an example :
First sheet containing data which we have to fetch matching
information by account number from the other sheet.

Account WASSTATUS WASENR WASOVERDUEAGE

1 COLLECTION 77291 90 DPD
2 REJECTED 0 0 DPD
3 TERMINATED 0 0 DPD
4 COLLECTION 80493 0 DPD
5 COLLECTION 67788 30 DPD
6 COLLECTION 132091 59 DPD
7 REJECTED 0 0 DPD
8 COLLECTION 78351 30 DPD
9 REJECTED 0 0 DPD
10 REJECTED 0 0 DPD
11 CANCELLED 0 0 DPD
12 COLLECTION 203208 30 DPD
13 COLLECTION 217745 0 DPD
14 COLLECTION 248821 0 DPD
15 REJECTED 0 0 DPD
16 REJECTED 0 0 DPD
17 SUSPENDED 0 0 DPD
18 TERMINATED 0 0 DPD
19 COLLECTION 88733 30 DPD
20 COLLECTION 175918 0 DPD
21 COLLECTION 100350 30 DPD
22 TERMINATED 0 0 DPD
23 TERMINATED 0 0 DPD
24 COLLECTION 63988 0 DPD
25 COLLECTION 98107 0 DPD
26 COLLECTION 130214 0 DPD
27 COLLECTION 167072 0 DPD
28 COLLECTION 134874 0 DPD
29 COLLECTION 177736 0 DPD

Sheet2:
Account ISSTATUS ISENR ISOVERDUEAGE

1 COLLECTION 75944 90 DPD
2 REJECTED 1 DPD
3 REJECTED 0 DPD
4 TERMINATED 0 DPD
5 COLLECTION 78938 0 DPD
6 COLLECTION 67788 61 DPD
7 COLLECTION 127000 61 DPD
8 REJECTED 0 DPD
9 COLLECTION 77029 30 DPD
10 REJECTED 0 DPD
11 REJECTED 0 DPD
12 CANCELLED 0 DPD
13 COLLECTION 199064 30 DPD
14 COLLECTION 213540 0 DPD
15 COLLECTION 243580 0 DPD
16 REJECTED 0 DPD
17 REJECTED 0 DPD
18 SUSPENDED 0 DPD
19 TERMINATED 0 DPD
20 COLLECTION 87077 30 DPD
21 COLLECTION 172212 0 DPD
22 COLLECTION 100350 61 DPD
23 TERMINATED 0 DPD
24 TERMINATED 0 DPD
25 COLLECTION 62641 0 DPD
26 COLLECTION 96041 0 DPD
27 COLLECTION 127700 0 DPD
28 COLLECTION 163553 0 DPD
29 COLLECTION 132270 0 DPD

Result:

Account WASSTATUS WASENR WASOVERDUEAGE ISSTATUS ISENR
ISOVERDUEAGE

1 COLLECTION 77291 90 DPD COLLECTION 75944 90 DPD
2 REJECTED 0 0 DPD REJECTED 1 DPD
3 TERMINATED 0 0 DPD REJECTED 0 DPD
4 COLLECTION 80493 0 DPD TERMINATED 0 DPD
5 COLLECTION 67788 30 DPD COLLECTION 78938 0 DPD
6 COLLECTION 132091 59 DPD COLLECTION 67788 61 DPD
7 REJECTED 0 0 DPD COLLECTION 127000 61 DPD
8 COLLECTION 78351 30 DPD REJECTED
0DPD
9 REJECTED 0 0 DPD COLLECTION 77029 30 DPD
10 REJECTED 0 0 DPD REJECTED 0 DPD
11 CANCELLED 0 0 DPD REJECTED 0 DPD
12 COLLECTION 203208 30 DPD CANCELLED 0 DPD
13 COLLECTION 217745 0 DPD COLLECTION 199064 30 DPD
14 COLLECTION 248821 0 DPD COLLECTION 213540 0 DPD
15 REJECTED 0 0 DPD COLLECTION 243580 0 DPD
16 REJECTED 0 0 DPD REJECTED 0 DPD
17 SUSPENDED 0 0 DPD REJECTED 0 DPD
18 TERMINATED 0 0 DPD SUSPENDED 0 DPD
19 COLLECTION 88733 30 DPD TERMINATED 0 DPD
20 COLLECTION 175918 0 DPD COLLECTION 87077 30 DPD
21 COLLECTION 100350 30 DPD COLLECTION 172212 0 DPD
22 TERMINATED 0 0 DPD COLLECTION 100350 61 DPD
23 TERMINATED 0 0 DPD TERMINATED 0 DPD
24 COLLECTION 63988 0 DPD TERMINATED 0 DPD
25 COLLECTION 98107 0 DPD COLLECTION 62641 0 DPD
26 COLLECTION 130214 0 DPD COLLECTION 96041 0 DPD
27 COLLECTION 167072 0 DPD COLLECTION 127700 0 DPD
28 COLLECTION 134874 0 DPD COLLECTION 163553 0 DPD
29 COLLECTION 177736 0 DPD COLLECTION 132270 0 DPD

Appreciate your replies
Thanks.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

merging sheets 1
Copy unique values problem 5
vlookup 4
Multiple conditions 3
Please help with Excel Formula 2
Merging to sheets 2
Charts - Moving data range over 3 sheets 1
VBA Macro - Copying specific data to different sheets in a workbookand linking the InputBox to a com 7

Top