Match Two Columns, Find Cell on Other Sheet, Paste Multiple Cells

[MattieP]

Hi forum members,

I hit a road block a few days ago with some code I have been writing. I have two sets of data that I am trying to combine, and I can combine them by using a unique identifier that I have in both lists. On my Result sheet, the unique identifier is a UPC (column D), and within one set of data it has Country (column I), Price (column J), and Sales Dates (column K). My other set of data I have on another sheet (Size), and it also has the UPC. I need to match up the UPCs to move the correct information from the Result sheet to the Size sheet, but the UPCs are not in the same order. See below for the example:

Result Sheet
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]UPC[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Country[/TD]
[TD]Price[/TD]
[TD]Date[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]USA[/TD]
[TD]10[/TD]
[TD]1/3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]CHINA[/TD]
[TD]20[/TD]
[TD]1/3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]USA[/TD]
[TD]25[/TD]
[TD]1/9[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]VIET NAM[/TD]
[TD]12[/TD]
[TD]1/8[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]SRI LANKA[/TD]
[TD]10[/TD]
[TD]1/1[/TD]
[/TR]
</tbody>[/TABLE]

Size Sheet
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]UPC[/TD]
[TD]COUNTRY[/TD]
[TD][/TD]
[TD]PRICE[/TD]
[TD]DATE[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I have tried looking in so many places to figure this out but I couldn't seem to find the answer. I want to be able to continue the code within VBA as I have information being moved from one sheet to the other (not being matched though, just copying and pasting by using column header names). I know I can do it easily with Match Index using formulas for one piece of information, but having multiple cells in different columns move from one to the other seems to have put me in a bad spot. Does anyone know of any tips to make this happen? I appreciate any feedback, thanks so much!

 

[Fluff]

Hi, a few questions
1) Which col on the size sheet has the UPC?
2) Which cols on Size should cols I,J & K (from result) go to?
3) Are your UPC actually unique as you have them many times on the Size sheet?

 

[MattieP]

Hi Fluff, thanks for the questions.
1) Column D has the UPC on the Size sheet.
2) Column I (Result) goes to P (Size), Column J (Result) goes to Q (Size), and Column K (Result) goes to R (Size).
3) The UPCs are unique as in each UPC on Result will have a specific Country/Price/Date, but the UPC shows up multiple times on the Size sheet which needs to be filled in. An example is that the UPC 12345 on Result sheet shows up once, but on the Size sheet, UPC 12345 is in multiple rows that needs to inherit the information from the Result sheet.

Thanks so much for your help, let me know if you need any other clarification.

 

[ajamess]

If I understand correctly you just need a vlookup or index/match.

I'm going to assume these are sheets in the same workbook named "Size" and "Result", and for all of them the column headers are in 1st row and data begins on the 2nd. Adjust accordingly.

In P2 on Size Type :

 = index(Result!I:I, match(d2,Result!D:D,0))

and then fill down.

In Q2 on Size Type :

 = index(Result!J:J, match(d2,Result!D:D,0))

and then fill down.

In R2 on Size Type :

 = index(Result!K:K, match(d2,Result!D:D,0))

and then fill down.

edit:

Whoops apparently didn't read the last row. If the UPC's are unique I don't understand why you need to match one more than one piece of information. The above index match's should work fine in VBA based on what I understand from your tables. If you want you could save the match portion as a variable as you loop through each row. That way you're only matching once instead of all 3.

 

[Fluff]

How about

Sub MatchCopy()

   Dim Cl As Range
   Dim SzWs As Worksheet
   Dim ResWs As Worksheet

Application.ScreenUpdating = False
   
   Set SzWs = Worksheets("Size")
   Set ResWs = Worksheets("Result")

   With CreateObject("scripting.dictionary")
      For Each Cl In ResWs.Range("D2", ResWs.Range("D" & Rows.Count).End(xlUp))
         If Not .Exists(Cl.Value) Then .Add Cl.Value, Cl.Offset(, 5).Resize(, 3).Value
      Next Cl
      For Each Cl In SzWs.Range("D2", SzWs.Range("D" & Rows.Count).End(xlUp))
         If .Exists(Cl.Value) Then Cl.Offset(, 12).Resize(, 3).Value = .Item(Cl.Value)
          Next Cl
   End With

End Sub

 

[MattieP]

Thanks ajamess - although I am trying to stay away from using formulas in cells that people can go into the file and delete or overwrite, especially if they try to reuse the macro and have pasted or altered the cells. That method did work however, so thank you.

Fluff - that is amazing! That works perfectly. I guess for the future for my knowledge, if I were to add more columns that I am trying to move over instead of just 3 (let's say maybe 2 more get added so now I have 5 total to move), how would I go about updating the code to pull more columns to the right? I would just rather understand how the code works so that I can make any alterations myself. Would it be altering the Resize value to make it continue going right, so from 3 to 5?

 

[Fluff]

Glad we could help & thanks for the feedback.

if I were to add more columns that I am trying to move over instead of just 3 (let's say maybe 2 more get added so now I have 5 total to move). Would it be altering the Resize value to make it continue going right, so from 3 to 5?

Yes that's absolutely right