Compare two columns then add another if the data is the same

Question

I have two separate worksheets that I would like to search in and then populate a column if two columns represent the same data. I'm having a hard time explaining this so please have patience.

I have worksheet1 with column "A" having text and numbers in it. In the same sheet column "B" has the data that I want to show in worksheet2 if Both Column "A" match in both worksheets.

Example:

Worksheet1

Column A

Text text text (2012-R-0000)

blah blah blah

text text text (2012-R-0001)

Column B

20-204

20-405

40-609

Worksheet2

Column A

2012-R-0000

2012-R-0001

Column E

(empty) I would like the data in Worksheet1 Column B to be placed here.

Thank you in advance for any assistance with my question.

If you have difficulties achieving this in VBA, do it step by step. Learn how to loop over the cells in one column, how to check if two cells have the same value, how to check for partial matches in strings, how to reference cells on different worksheets. You might also consider using worksheet formulas for this. — arcadeprecinct, Jul 11 '16 at 10:36
Try using `Application.WorksheetFunction.VLookup` to compare between the two worksheets. Good begining will be to search this site, one example for instance can be found: http://stackoverflow.com/questions/20739717/vba-vlookup-reference-in-different-sheet — Shai Rado, Jul 11 '16 at 10:48

Karthick Gunasekaran · Accepted Answer · 2016-07-11T14:26:39.217

1

Assuming your worksheet 1 and worksheet 2 datas starts with A1

use the below formula at worksheet 2 in E1

=VLOOKUP("*"&A1&"*",Sheet3!A:B,2,FALSE)

French Formula:

=RECHERCHEV("*"&A1&"*";Sheet3!A:B;2;FALSE)

and drag down

Proof of Work

edited Jul 11 '16 at 14:26

answered Jul 11 '16 at 11:54

Karthick Gunasekaran

2,697
1
15
25

Thanks for your support !! – babou Jul 11 '16 at 12:08
Maybe cause of my name of sheet ? its "Sheet1" and "Sheet2" – babou Jul 11 '16 at 12:13
yes absolutely. I have worked out from sheet3 and sheet 4. In you case change sheet 3 to sheet 2. Don't forget to mark as answer and upvote if its helpful for you – Karthick Gunasekaran Jul 11 '16 at 12:15
give the sheet name in the formula – Karthick Gunasekaran Jul 11 '16 at 12:26
Standard seems to be General in french , so nothing to change :/ – babou Jul 11 '16 at 12:48
TY my love, a last thing if you have the time, IF there are 2 same A code from worksheet 1, add the B column them in column E in worksheet 2 :D Can you help me for that pls ? – babou Jul 12 '16 at 08:01
that you should ask for the separate question. Hope you understand – Karthick Gunasekaran Jul 12 '16 at 08:05
I have asked a new question "if Vlookup is the same, then add" – babou Jul 12 '16 at 08:31

score 0 · Answer 2 · answered Jul 11 '16 at 11:05

Use VLookUp in Worksheet 2 like this.

In cell E1 of Worksheet 2 write the following:

=VLOOKUP(A1;Worksheet1!$A$1:$B$30;2;FALSE)

Then simply drag the formula down. It will match the first column from both worksheets, then paste the corresponding data from column B in worksheet1 to column E in worksheet 2. You'll have to edit "Worksheet1" to match the name of the acctual worksheet, and the number 30 to match the number of rows in worksheet 1.

same problem that with Nan Avan Illai , can you help me ? :( — babou, Jul 11 '16 at 13:31

Compare two columns then add another if the data is the same

2 Answers2