Two worksheets: Compare two columns in same row and add data in another column

[SEOSEO.dk]

Hi experts

I have been searching for days now, but not found a usefull result for this issue - so please help me out:

I have two worksheets!
Sheet1
A B C D E F
1 Pos. Description ItemNo. QTY Material HeatNo.
2 1 Motor&shaft TE26191 1 N/A N/A
3 3 Impeller TE26C90 2 N/A Steel N/A
4 5 Flange TE26030 1 Steel N/A
5 6 Ring TE26070 1 Steel N/A
6 "...empty rows"

Sheet2
D E F G H I
1 Pos. Description ItemNo. QTY Material HeatNo.
2 1 Shaft N/A N/A N/A 215205
3 3 Blade N/A N/A N/A 520548
4 3 Hub N/A N/A N/A 30277.8
5 3 Weldingwire N/A N/A N/A EC215027
6 5 Flange N/A N/A N/A 1008
7 6 Ring N/A N/A N/A 221064
8 "empty rows"

Now I want to COMPARE "Pos." AND "Description" from the range on sheet2 with the range in sheet1. WHEN there is a MATCH in both columns in one row, I want to add the data from column 6 (HeatNo.). IF there is NO MATCH in one row, I want to copy the hole row from sheet2 to the first empty row within the range on sheet1.

So a macro using VB come out with this result:
Sheet1
A B C D E F
1 Pos. Description ItemNo. QTY Material HeatNo.
2 1 Motor&shaft TE26191 1 N/A N/A
3 3 Impeller TE26C90 2 N/A Steel N/A
4 5 Flange TE26030 1 Steel 1008
5 6 Ring TE26070 1 Steel 221064
6 1 Shaft N/A N/A N/A 215205
7 3 Blade N/A N/A N/A 520548
8 3 Hub N/A N/A N/A 30277.8
9 3 Weldingwire N/A N/A N/A EC215027
10 "empty rows"

I hope you clever people can help me out?

Br Susan

[JBeaucaire]

Give this a try, be sure to read it through. It assumes column AA is empty on both sheets.

Please Login or Register  to view this content.

[xladept]

Hi Susan,

This may be what you want:

'Now I want to COMPARE "Pos." AND "Description" from the range on sheet2
'with the range in sheet1.
'WHEN there is a MATCH in both columns in one row,
'I want to add the data from column 6 (HeatNo.).
'IF there is NO MATCH in one row, I want to copy the hole row from sheet2
'to the first empty row within the range on sheet1.

Please Login or Register  to view this content.

Directions for running the routine(s) just supplied

Copy the code to the clipboard

Press ALT + F11 to open the Visual Basic Editor.

Open a macro-enabled Workbook or save your Workbook As Macro-Enabled

Select “Module” from the Insert menu

Type "Option Explicit" then paste the code under it

With the cursor between Sub and End Sub press F5 (F8 to Single Step)

OR

Press ALT + Q to close the code window.

Press ALT + F8 then double click on the macro name

[JBeaucaire]

Thanks for the feedback, Susan. If that takes care of your original query, please select Thread Tools from the menu above and mark the thread as solved. Thanks.