Comparing and matching data in multiple columns in two sheets using a macro

vdf99

New Member
Joined
Oct 27, 2014
Messages
3
Hi

I have two sheets with information.
The first one contains four columns with values that need to be matched on the second sheet.
The second sheet contains five columns from were I want to move the fifth columns value to the first sheet when a match is found.

In my quest to find a solution too this I have found a macro in this thread: http://www.mrexcel.com/forum/excel-...-compare-move-data-one-worksheet-another.html

That macro have been of great us to me in correcting huge amount of data.

I cant find a macro that can do this on multiple columns and I tried to see if I could learn to make my own macro.
But I found out that I dont possess the skills to make a for me so advanced macro.

Can anyone please help me with a macro that will work on multiple columns.

I am using Excel 2010 and Win 7 Enterprise with service pack 1.


Thanks in advance


Here are examples of the data.

Sheet1
PSYS_NO
TAG
SPEC
MATERIAL
MATSPEC
SIZE
Innerdiameter (mm)
161.1101
P11076
B150A
CARBON STEEL
ASTM A106-GRB
0,50
161.1101
P11078
B150A
CARBON STEEL
ASTM A106-GRB
1,00
161.1102
FOS11001
B300A
CARBON STEEL
ASTM A106-GRB
1,00
161.1102
P11006
B300A
CARBON STEEL
ASTM A106-GRB
2,00
161.1102
P11007
B300A
CARBON STEEL
ASTM A106-GRB
2,00
161.1102
P11015
B300A
CARBON STEEL
ASTM A106-GRB
1,50

<tbody>
</tbody>

Sheet2
SPEC
MATERIAL
MATSPEC
SIZE
Innerdiameter (mm)
B300A
CARBON STEEL
ASTM A106-GRB
0,50
13,88
B300A
CARBON STEEL
ASTM A106-GRB
0,75
18,85
B300A
CARBON STEEL
ASTM A106-GRB
1,00
24,30
B300A
CARBON STEEL
ASTM A106-GRB
1,25
32,46
B300A
CARBON STEEL
ASTM A106-GRB
1,50
38,10
B300A
CARBON STEEL
ASTM A106-GRB
2,00
49,25

<tbody>
</tbody>
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Code:
Sub myMacro()
    WS1 = "Sheet1"
    WS2 = "Sheet2"
    LR1 = Sheets(WS1).Range("A" & Rows.Count).End(xlup).Row
    LR2 = Sheets(WS2).Range("A" & Rows.Count).End(xlup).Row
    i = 2
    Do Until i > LR1
        SPEC1 = Sheets(WS1).Range("C" & i).Value
        MATERIAL1 = Sheets(WS1).Range("D" & i).Value
        MATSPEC1 = Sheets(WS1).Range("E" & i).Value
        SIZE1 = Sheets(WS1).Range("F" & i).Value
        ii = 2
        Do Until ii > LR2
            SPEC2 = Sheets(WS2).Range("A" & ii).Value
            MATERIAL2 = Sheets(WS2).Range("B" & ii).Value
            MATSPEC2 = Sheets(WS2).Range("C" & ii).Value
            SIZE2 = Sheets(WS2).Range("D" & ii).Value
            InnerDiameter = Sheets(WS2).Range("E" & ii).Value
            If SPEC1 = SPEC2 AND_
               MATERIAL1 = MATERIAL2 AND _
               MATSPEC1 = MATSPEC2 AND _
               SIZE1 = SIZE2 Then
                Range("G" & i).Value = InnerDiameter
            End If
            ii = ii + 1
        Loop
        i = i + 1
    Loop
End Sub
 
Upvote 0
Thanks for the help WarPiglet.

I have tried the macro and get Syntax error, I have marked the text in red where I get the error.

I tried to figure out what was wrong, but I can´t.

Can you see the problem?


Sub myMacro()
WS1 = "Sheet1"
WS2 = "Sheet2"
LR1 = Sheets(WS1).Range("A" & Rows.Count).End(xlUp).Row
LR2 = Sheets(WS2).Range("A" & Rows.Count).End(xlUp).Row
i = 2
Do Until i > LR1
SPEC1 = Sheets(WS1).Range("C" & i).Value
MATERIAL1 = Sheets(WS1).Range("D" & i).Value
MATSPEC1 = Sheets(WS1).Range("E" & i).Value
SIZE1 = Sheets(WS1).Range("F" & i).Value
ii = 2
Do Until ii > LR2
SPEC2 = Sheets(WS2).Range("A" & ii).Value
MATERIAL2 = Sheets(WS2).Range("B" & ii).Value
MATSPEC2 = Sheets(WS2).Range("C" & ii).Value
SIZE2 = Sheets(WS2).Range("D" & ii).Value
InnerDiameter = Sheets(WS2).Range("E" & ii).Value
If SPEC1 = SPEC2 AND_
MATERIAL1 = MATERIAL2 AND _
MATSPEC1 = MATSPEC2 AND _
SIZE1 = SIZE2 Then

Range("G" & i).Value = InnerDiameter
End If
ii = ii + 1
Loop
i = i + 1
Loop
End Sub
 
Upvote 0
I have found the error, it is just a missing space between "and" and underscore in this part If SPEC1 = SPEC2 AND_

After I corrected this, it worked like a charm.

I am very grateful for your help WarPiglet, this script have been very helpful for me.


Corrected script:

Sub myMacro()
WS1 = "Sheet1"
WS2 = "Sheet2"
LR1 = Sheets(WS1).Range("A" & Rows.Count).End(xlUp).Row
LR2 = Sheets(WS2).Range("A" & Rows.Count).End(xlUp).Row
i = 2
Do Until i > LR1
SPEC1 = Sheets(WS1).Range("C" & i).Value
MATERIAL1 = Sheets(WS1).Range("D" & i).Value
MATSPEC1 = Sheets(WS1).Range("E" & i).Value
SIZE1 = Sheets(WS1).Range("F" & i).Value
ii = 2
Do Until ii > LR2
SPEC2 = Sheets(WS2).Range("A" & ii).Value
MATERIAL2 = Sheets(WS2).Range("B" & ii).Value
MATSPEC2 = Sheets(WS2).Range("C" & ii).Value
SIZE2 = Sheets(WS2).Range("D" & ii).Value
InnerDiameter = Sheets(WS2).Range("E" & ii).Value
If SPEC1 = SPEC2 AND _
MATERIAL1 = MATERIAL2 AND _
MATSPEC1 = MATSPEC2 AND _
SIZE1 = SIZE2 Then

Range("G" & i).Value = InnerDiameter
End If
ii = ii + 1
Loop
i = i + 1
Loop
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,163
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top