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!
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,804
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Sep 6, 2016
Messages
17
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

Board Regular
Joined
Sep 13, 2016
Messages
92
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:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,804
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
 

MattieP

New Member
Joined
Sep 6, 2016
Messages
17
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,804
Office Version
  1. 365
Platform
  1. Windows
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 :)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,303
Messages
5,595,324
Members
413,986
Latest member
Elizsk

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
Top