VBA / macro to match multiple Criteria on two sheets then copy and paste cell value

C

CliffWeb

New Member
Joined
Aug 15, 2016
Messages
23
I need a macro to match/find Rep list and ID on Sheet 1 with Sheet 2 then copy and paste Skill Level from sheet 2 under the correct Skill on Sheet 1. I have a list of over 300 Reps and about 20 different skills and each skills has a levels ranging from 1-8. Below is a quick a example of how sheet 1 should look after comparing the list matching and copying the value from the Data to sheet 1. Sorry I don't know how to add the grid lines but Rep name is A3 and so on.



Rep 1Rep ID 1
Rep 2Rep ID 2
Rep 3Rep ID 3
Rep 4Rep ID 4
Rep 5Rep ID 5
Rep 1Rep ID 1
Rep 2Rep ID 2
Rep 3Rep ID 3
Rep 4Rep ID 4
Rep 5Rep ID 5

<tbody>
[TD="bgcolor: #00ffff, colspan: 4, align: center"]Sheet 1[/TD]

[TD="bgcolor: #00ffff"]Rep Name[/TD]
[TD="bgcolor: #00ffff"]Rep ID[/TD]
[TD="bgcolor: #00ffff, align: right"]1510[/TD]
[TD="bgcolor: #00ffff, align: right"]1511[/TD]
[TD="bgcolor: #00ffff, align: right"]1512[/TD]
[TD="bgcolor: #00ffff, align: right"]1516[/TD]
[TD="bgcolor: #00ffff, align: right"]1517[/TD]
[TD="bgcolor: #00ffff, align: right"]1688[/TD]

[TD="align: right"]6[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]

[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]5[/TD]

[TD="align: right"]5[/TD]

[TD="align: right"]3[/TD]

[TD="align: right"]2[/TD]

[TD="align: right"]3[/TD]

[TD="align: right"]4[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]3[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: #00ff00, colspan: 5, align: center"]Sheet 2 Data Sheet[/TD]

[TD="bgcolor: #00ff00"]Rep Name[/TD]
[TD="bgcolor: #00ff00"]Rep ID[/TD]
[TD="bgcolor: #00ff00"]Skill01[/TD]
[TD="bgcolor: #00ff00"]Skill01 Lvl[/TD]
[TD="bgcolor: #00ff00"]Skill02[/TD]
[TD="bgcolor: #00ff00"]Skill02 Lvl[/TD]
[TD="bgcolor: #00ff00"]Skill03[/TD]
[TD="bgcolor: #00ff00"]Skill03 Lvl[/TD]

[TD="align: right"]1517[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1688[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1516[/TD]
[TD="align: right"]6[/TD]

[TD="align: right"]1510[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1511[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1512[/TD]
[TD="align: right"]5[/TD]

[TD="align: right"]1688[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1516[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1510[/TD]
[TD="align: right"]5[/TD]

[TD="align: right"]1516[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1510[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1512[/TD]
[TD="align: right"]4[/TD]

[TD="align: right"]1511[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1517[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1688[/TD]
[TD="align: right"]1[/TD]

</tbody>
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Sort by date Sort by votes
Try this for Results in sheet1.
Code: 
[COLOR="Navy"]Sub[/COLOR] MG15Aug53
[COLOR="Navy"]Dim[/COLOR] Dn [COLOR="Navy"]As[/COLOR] Range, ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dic [COLOR="Navy"]As[/COLOR] Object, Col [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Ray [COLOR="Navy"]As[/COLOR] Variant, Txt [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String,[/COLOR] p [COLOR="Navy"]As[/COLOR] Variant

Ray = Sheets("Sheet2").Range("A3").CurrentRegion
[COLOR="Navy"]Set[/COLOR] Dic = CreateObject("scripting.dictionary")
Dic.CompareMode = vbTextCompare
    [COLOR="Navy"]For[/COLOR] n = 2 To UBound(Ray, 1)
        Txt = Ray(n, 1) & Ray(n, 2)
            [COLOR="Navy"]If[/COLOR] Not Dic.Exists(Txt) [COLOR="Navy"]Then[/COLOR]
                [COLOR="Navy"]Set[/COLOR] Dic(Txt) = CreateObject("Scripting.Dictionary")
            [COLOR="Navy"]End[/COLOR] If
            [COLOR="Navy"]For[/COLOR] ac = 3 To UBound(Ray, 2) [COLOR="Navy"]Step[/COLOR] 2
                [COLOR="Navy"]If[/COLOR] Not Dic(Txt).Exists(Ray(n, ac)) [COLOR="Navy"]Then[/COLOR]
                    Dic(Txt).Add (Ray(n, ac)), Ray(n, ac + 1)
                [COLOR="Navy"]End[/COLOR] If
            [COLOR="Navy"]Next[/COLOR] ac
    [COLOR="Navy"]Next[/COLOR] n
   
[COLOR="Navy"]Set[/COLOR] Rng = Sheets("Sheet1").Range("A3").CurrentRegion
[COLOR="Navy"]For[/COLOR] n = 4 To Rng.Count
    [COLOR="Navy"]With[/COLOR] Sheets("Sheet1")
        [COLOR="Navy"]If[/COLOR] Dic.Exists(.Range("A" & n).Value & .Range("B" & n).Value) [COLOR="Navy"]Then[/COLOR]
            [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] p [COLOR="Navy"]In[/COLOR] Dic(.Range("A" & n).Value & .Range("B" & n).Value)
                Col = Application.Match(p, Rng(1).Offset(1).Resize(, Rng.Columns.Count), 0)
                Sheets("Sheet1").Cells(n, Col) = _
                Dic(.Range("A" & n).Value & .Range("B" & n).Value).item(p)
            [COLOR="Navy"]Next[/COLOR] p
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]Next[/COLOR] n

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
Thanks Mick, Everything works below Rep 1. For some reason rep one row is not filling in. If you don't mind can you fill me on what the code is doing or where it is doing it? because I may have to alter the Col, location for the the Skill01 and so on. Because of the raw data has more Columns in between the rep ID and and where Skillo1 is located. example Skill01 on the raw data actually starts on H Column. The rest of the Skills and Lvl continues from there on.
 
Upvote 0
I assumed that "Rep Name " in both sheets started in "A3", that might be the problem.

The code should take account of various numbers of columns and rows, if you are still having problems , perhaps you could show a more realistic example of your data, or send an Example file via "Box.com" (Free file sharing).

The code is basically taking all the data in sheet2 and comparing it with sheet1 columns "A & B", then matching the "Skill" numbers in sheet 2 with the headers in sheet 1 row 3. and allocating the sheet 2 values.
 
Upvote 0
MickG said:
I assumed that "Rep Name " in both sheets started in "A3", that might be the problem.

The code should take account of various numbers of columns and rows, if you are still having problems , perhaps you could show a more realistic example of your data, or send an Example file via "Box.com" (Free file sharing).

The code is basically taking all the data in sheet2 and comparing it with sheet1 columns "A & B", then matching the "Skill" numbers in sheet 2 with the headers in sheet 1 row 3. and allocating the sheet 2 values.
Click to expand...

The Rep name does start on A3 for both sheets. Thanks for the explanation. I work is really appreciated.
 
Upvote 0
MickG said:
You're welcome
Click to expand...
Mick, being that I can't take the original take from work to the net. I tried to give the best representation of the work itself, here is a closer representation of the Data sheet . I getting out of of script error. No changes needed to be made to the first sheet. I just wanted to expand on the Data sheet. I just got box here is a link. https://app.box.com/s/j4mki1oumxz1r1ggrudok9xvs9bniq87
 
Upvote 0
Change lines as shown below:-
Code: 
 'Change line below to 3
    For n =[B][/B][COLOR="#FF0000"][/COLOR][COLOR="#FF0000"][SIZE=5] 3 [/SIZE][/COLOR]To UBound(Ray, 1)
        Txt = Ray(n, 1) & Ray(n, 2)
            If Not Dic.Exists(Txt) Then
                Set Dic(Txt) = CreateObject("Scripting.Dictionary")
            End If
            'CHange code below to "6"
            For ac = [B][/B][COLOR="#FF0000"][SIZE=5]6[/SIZE][/COLOR] To UBound(Ray, 2) Step 2
                If Not Dic(Txt).Exists(Ray(n, ac)) Then
                    Dic(Txt).Add (Ray(n, ac)), Ray(n, ac + 1)
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
VBA Code To Loop Through One Worksheet and Paste Certain Values Into Another
Replies
5
Views
139
Peter_SSs
Peter_SSs
L
  • Question Question
Macro to correct entries across multiple sheets
Replies
1
Views
188
Lux Aeterna
L
T
  • Question Question
VLookup Multiple Values to Rows
Replies
5
Views
542
Peter_SSs
Peter_SSs
S
  • Solved
VBA: Index Match formular with multiple criterias (lookup an value based on multiple criteria)
Replies
9
Views
804
Alex Blakenburg
A
K
  • Solved
Code to search job id from multiple sources and update main spreadsheet.
2
Replies
10
Views
642
mumps
mumps

Forum statistics

Threads
1,223,911
Messages
6,175,329
Members
452,635
Latest member
laura12345

Share this page

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