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

MattieP

New Member
Joined
Sep 6, 2016
Messages
17
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
UPCCountryPriceDate
1USA101/3
2CHINA201/3
3USA251/9
4VIET NAM121/8
5SRI LANKA101/1

<tbody>
</tbody>

Size Sheet
UPCCOUNTRYPRICEDATE
2
2
2
2
3
3
3
3
1
1
1
5
5
4
4

<tbody>
</tbody>

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!
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
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?
 
Upvote 0
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.
 
Upvote 0
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 :

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

and then fill down.

In Q2 on Size Type :

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

and then fill down.

In R2 on Size Type :

Code:
 = 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.
 
Last edited:
Upvote 0
How about
Code:
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
 
Upvote 0
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?
 
Upvote 0
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 :)
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,034
Members
448,543
Latest member
MartinLarkin

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