Merge Sheets

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I want to merge two sheets and use the merged data in a new sheet.

Both my sheets contains similar info but there are fields that differ.

I need Column A from one sheet to look in Column D of the other one to see
if that one contains the same info. A simple return like False or True would
be enough for me.

One sheet contains 2500 entries and the other 4500.

I'll filter the results afterwards.

Any help would be appreciated

kubersluiper
 
One way ..

Assume col A in Sheet1 is to be compared with col D in Sheet2

In Sheet1,

Put in say, E2:
=IF(A2="","",ISNUMBER(MATCH(A2,Sheet2!D:D,0)))
Copy down to the last row of data in col A. Now you can autofilter on col E.
 
Max, Thank you for the prompt reply.

It gave me what I asked for! Brilliant

However, let me explain, I just realised this was not completely what I
needed.

Sheet1 contains all my up to date datum and sheet2 has "new datum".

Sheet1 contains 4500 entries and sheet2 only 2500.

Most of sheet2's info are also in sheet1 but obviously not everything.

I now need to put the new datum into sheet1. Sheet2 has got new columns
which I need to create in sheet1.

Let me give you an example,

Sheet1 has got name and number and address
Sheet2 only number(which corresponds with sheet1's number) but in sheet 2 I
have a field for attended a Meeting which there are no similar fields in
sheet1.
I now need to put that info into sheet1.

Thanks again and I hope this makes sense
 
welcome, glad we got that out of the way <g>

On your new query, using an INDEX/MATCH should work for you ..

Assuming you want to bring over Sheet2's col G values into Sheet1 based on
matching col A in Sheet1 vs col D in Sheet2

In Sheet1,

Put in say, E2:
=IF(A2="","",IF(ISNA(MATCH(A2,Sheet2!D:D,0)),"",INDEX(Sheet2!G:G,MATCH(A2,Sheet2!D:D,0))))
Copy down to the last row of data in col A.

Adapt this part in the formula: INDEX(Sheet2!G:G,..
to suit the col that you want to bring over from Sheet2

---
 
Max

After I have done this, I checked the two sheets manually and found that
about 200 entries are missing.

Can you please explain to me how this code work
=IF(A2="","",IF(ISNA(MATCH(A2,Sheet2!D:D,0)),"",INDEX(Sheet2!G:G,MATCH(A2,Sheet2!D:D,0))))

Let's say

In sheet1 with 4500 entries I have a number 10000 and that number is at A2,
but in Sheet2 number 10000 might be in D10, will this code still pick it up?

I tried to look for any missing entries but can't find any.

Any help will be appreciated

ajk
 
In sheet1 with 4500 entries I have a number 10000 and that number is at
A2,
but in Sheet2 number 10000 might be in D10, will this code still pick it
up?
Click to expand...

Yes, of course, it should. If it doesn't return correctly, then there's
probably some data inconsistencies present which is throwing the matching
off. Some numbers may be text numbers which won't match with real numbers. A
text number 10000 won't match with a real number 10000.

Would suggest that you try this to coerce all numbers in both** cols to real
numbers for proper matching. Copy an empty cell, then do a Paste special on
the entire col (for both cols) > check "Add" > OK.
**col A in Sheet1 & col D in Sheet2

---
 
Max,

Thanks again!

I had a suspicion it might be something like that.

I'll have a look and let you know!


Really appreciate your help
 
Please any 1 help me to find out the solution as listed below

''''''''''''please help me to find the result as shown as folliows
'Sheet1
Header 1 Header 2 Header 3 Header 4 Header 5 Header 6 Header 7
a1 b1 c1
a2 b2 c2
a3 b3 c3
a4 b4 c4
a5 b5 c5
a6 b6 c6
a7 b7 c7
a8 b8 c8
a9 b9 c9
a10 b10 c10
a11 b11 c11
a12 b12 c12
a13 b13 c13
a14 b14 c14
a15 b15 c15
a16 b16 c16
a17 b17 c17

'Sheet2
Header 1 Header 2 Header 3 Header 4 Header 5 Header 6 Header 7
a1 b1 c1
a2 b2 c2 d2
a3 b3 c3
a4 b4 c4
a5 b5 c5
a6 b6 c6 d6
a7 b7 c7
a8 b8 c8
a9 b9 c9
a10 b10 c10
a11 b11 c11 d11
a12 b12 c12
a13 b13 c13
a14 b14 c14
a15 b15 c15
a16 b16 c16
a17 b17 c17 d17

'Sheet3
Header 1 Header 2 Header 3 Header 4 Header 5 Header 6 Header 7
a1 b1 c1 d1 e1
a2 b2 c2 e2
a3 b3 c3 d3 e3
a4 b4 c4
a5 b5 c5 d5
a6 b6 c6 e6
a7 b7 c7 d7 e7
a8 b8 c8
a9 b9 c9
a10 b10 c10 d10 e10
a11 b11 c11
a12 b12 c12 d12
a13 b13 c13 d13 e13
a14 b14 c14
a15 b15 c15
a16 b16 c16 d16 e16
a17 b17 c17



'RESULT_SHEET
Header 1 Header 2 Header 3 Header 4 Header 5 Header 6 Header 7
a1 b1 c1 d1 e1
a2 b2 c2 d2 e2
a3 b3 c3 d3 e3
a4 b4 c4
a5 b5 c5 d5
a6 b6 c6 d6 e6
a7 b7 c7 d7 e7
a8 b8 c8
a9 b9 c9
a10 b10 c10 d10 e10
a11 b11 c11 d11
a12 b12 c12 d12
a13 b13 c13 d13 e13
a14 b14 c14
a15 b15 c15
a16 b16 c16 d16 e16
a17 b17 c17 d17
 
Back
Top