Copying data from one worksheet to another using VBA/Formula

D

DarrenW81

New Member
Joined
Mar 6, 2017
Messages
2
Hi,

I'm hoping this is a relatively straight forward query for my first post on this forum.

I have 2 worksheets which I'll call 'Sheet 1' and 'Sheet 2'. Both contain values in Column A and Column B, with an additional description Column C on Sheet 2.

Sheet 2 contains raw data which acts as a reference for Sheet 1 to create various combinations of that data.

What I want is for Sheet 1 to be able to cycle through row by row, and when a combination of A+B matches A+B from Sheet 2, it inserts the description from Column C on Sheet 2 into Sheet 1.

Can this be done using formulas such as 'IF' or 'VLOOKUP', or only through VBA, if at all? I'm happy to use either formulas or VBA as I'm hoping to build my knowledge on both.

I've enclosed examples below of my 2 sheets, with colours (pretty...) which I hope will illustrate my point, and any help would be much appreciated.

Thanks!

[TABLE="width: 629"]
<tbody>[TR]
[TD="class: xl70, width: 174, bgcolor: transparent, colspan: 2"]Sheet 1 Example:
[/TD]
[TD="width: 98, bgcolor: transparent"][/TD]
[TD="width: 72, bgcolor: transparent"][/TD]
[TD="class: xl70, width: 176, bgcolor: transparent, colspan: 2"]Sheet 2 Example:
[/TD]
[TD="width: 318, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: transparent"]COLUMN A
[/TD]
[TD="class: xl70, bgcolor: transparent"]COLUMN B
[/TD]
[TD="class: xl70, bgcolor: transparent"]COLUMN C?
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"]COLUMN A
[/TD]
[TD="class: xl70, bgcolor: transparent"]COLUMN B
[/TD]
[TD="class: xl70, bgcolor: transparent"]COLUMN C
[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]GUCL
[/TD]
[TD="class: xl64, bgcolor: transparent"]1
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"]HYDE
[/TD]
[TD="class: xl66, bgcolor: transparent"]1
[/TD]
[TD="class: xl67, bgcolor: transparent"]Unrestricted, Single rate
[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]GUCL
[/TD]
[TD="class: xl64, bgcolor: transparent"]1
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]LOND
[/TD]
[TD="class: xl63, bgcolor: transparent"]1
[/TD]
[TD="bgcolor: transparent"]Economy 7, 23.30 - 06.30
[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]GUCL
[/TD]
[TD="class: xl64, bgcolor: transparent"]1
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]MIDE
[/TD]
[TD="class: xl63, bgcolor: transparent"]1
[/TD]
[TD="bgcolor: transparent"]Prepayment Two Rate
[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]GUCL
[/TD]
[TD="class: xl64, bgcolor: transparent"]1
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]NORW
[/TD]
[TD="class: xl63, bgcolor: transparent"]1
[/TD]
[TD="bgcolor: transparent"]Summer Unrestricted
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]HYDE
[/TD]
[TD="class: xl66, bgcolor: transparent"]1
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]SOUT
[/TD]
[TD="class: xl63, bgcolor: transparent"]1
[/TD]
[TD="bgcolor: transparent"]O/P -F- 8HRS + 2.5HRS PROG TIME/S
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]HYDE
[/TD]
[TD="class: xl66, bgcolor: transparent"]1
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]SPOW
[/TD]
[TD="class: xl63, bgcolor: transparent"]1
[/TD]
[TD="bgcolor: transparent"]8.5 Hour White Meter
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]HYDE
[/TD]
[TD="class: xl66, bgcolor: transparent"]1
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]SWEB
[/TD]
[TD="class: xl63, bgcolor: transparent"]1
[/TD]
[TD="bgcolor: transparent"]11.5 Hour Off Peak - TS
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]HYDE
[/TD]
[TD="class: xl66, bgcolor: transparent"]1
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]YELG
[/TD]
[TD="class: xl63, bgcolor: transparent"]1
[/TD]
[TD="bgcolor: transparent"]NHH non-prog 1r credit meter and T/S
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]HYDE
[/TD]
[TD="class: xl66, bgcolor: transparent"]1
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"]GUCL
[/TD]
[TD="class: xl64, bgcolor: transparent"]1
[/TD]
[TD="class: xl65, bgcolor: transparent"]8.5 Hour White Meter
[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]IPNL
[/TD]
[TD="class: xl69, bgcolor: transparent"]1
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent"]IPNL
[/TD]
[TD="class: xl69, bgcolor: transparent"]1
[/TD]
[TD="class: xl68, bgcolor: transparent"]8.5 Hr White Meter
[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]IPNL
[/TD]
[TD="class: xl69, bgcolor: transparent"]1
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]HYDE
[/TD]
[TD="class: xl63, bgcolor: transparent"]2
[/TD]
[TD="bgcolor: transparent"]Dynamic-htg units
[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]IPNL
[/TD]
[TD="class: xl69, bgcolor: transparent"]1
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]LOND
[/TD]
[TD="class: xl63, bgcolor: transparent"]2
[/TD]
[TD="bgcolor: transparent"]Economy 7, 00.00 - 07.00
[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]IPNL
[/TD]
[TD="class: xl69, bgcolor: transparent"]1
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]MANW
[/TD]
[TD="class: xl63, bgcolor: transparent"]2
[/TD]
[TD="bgcolor: transparent"]7-Hour E7
[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]IPNL
[/TD]
[TD="class: xl69, bgcolor: transparent"]1
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]MIDE
[/TD]
[TD="class: xl63, bgcolor: transparent"]2
[/TD]
[TD="bgcolor: transparent"]Prepayment Two Rate
[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]IPNL
[/TD]
[TD="class: xl69, bgcolor: transparent"]1
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]SEEB
[/TD]
[TD="class: xl63, bgcolor: transparent"]2
[/TD]
[TD="bgcolor: transparent"]Credit single rate
[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]IPNL
[/TD]
[TD="class: xl69, bgcolor: transparent"]1
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]SOUT
[/TD]
[TD="class: xl63, bgcolor: transparent"]2
[/TD]
[TD="bgcolor: transparent"]O/P -F- 8HRS + 2.5HRS NON - PROG TIME/S
[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]IPNL
[/TD]
[TD="class: xl69, bgcolor: transparent"]1
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]SPOW
[/TD]
[TD="class: xl63, bgcolor: transparent"]2
[/TD]
[TD="bgcolor: transparent"]8.5 Hour White Meter
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]SWEB
[/TD]
[TD="class: xl63, bgcolor: transparent"]2
[/TD]
[TD="bgcolor: transparent"]11.5 Hour Off Peak - TS
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]YELG
[/TD]
[TD="class: xl63, bgcolor: transparent"]2
[/TD]
[TD="bgcolor: transparent"]NHH non-prog 1r credit meter and T/S
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]GUCL
[/TD]
[TD="class: xl63, bgcolor: transparent"]2
[/TD]
[TD="bgcolor: transparent"]8.5 Hour White Meter
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]NORW
[/TD]
[TD="class: xl63, bgcolor: transparent"]2
[/TD]
[TD="bgcolor: transparent"]E7 Key RTS
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]PENL
[/TD]
[TD="class: xl63, bgcolor: transparent"]2
[/TD]
[TD="bgcolor: transparent"]E7 Key RTS
[/TD]
[/TR]
</tbody>[/TABLE]
 
Sort by date Sort by votes
Try this in cell C2 Sheet1!:

=IFERROR(INDEX(Sheet2!$C$2:$C$20,MATCH(1,($A2=Sheet2!$A$2:$A$20)*($B2=Sheet2!$B$2:$B$20),0)),"")

Then press CTRL+SHIFT+ENTER to make it an array. Then drag down the column. Change the number's "20" in formula for a larger range.
 
Upvote 0
You could also use this regular formula. If you have a lot of data, array formulas could slow your file down...
=IFERROR(INDEX(Sheet2!$C$4:$C$26,MATCH(Sheet1!A2&" "&Sheet1!B2,INDEX(Sheet2!$A$4:$A$25&" "&Sheet2!$B$4:$B$25,0),0)),"Not Found")
 
Upvote 0
Hi both,

Thanks for your responses. I've tried both versions of the formula you've both given, they stand up without kicking me out in the first instance, but the result they're both bringing back is the 'value_if_error' (so, a blank cell for your version BillyRaySpivy, and "Not Found" populates the cell for your version, FDibbins).

Stepping through each of the function arguments, I'm not sure if it's the INDEX or MATCH function that's causing the issue, as the Value for IFERROR returns as '#N/A'.

Just to also give you an indication of the volumes of data that I'm dealing with in real terms, my Sheet 1 contains over 50000 rows, and Sheet 2 contains around 4000, so I'm looking for the quickest way to populate 50000 rows with a description without having to copy and paste them all! :laugh:

Any further assistance would again be much appreciated.

Thanks guys.
 
Upvote 0
It worked for me...
[Table="width:, class:grid"][tr][td] [/td][td]
A​
[/td][td]
B​
[/td][td]
C​
[/td][/tr]
[tr][td]
3​
[/td][td]COLUMN A [/td][td]COLUMN B[/td][td]COLUMN C?[/td][/tr]

[tr][td]
4​
[/td][td]GUCL[/td][td]1[/td][td]8.5 Hour White Meter[/td][/tr]

[tr][td]
5​
[/td][td]GUCL[/td][td]1[/td][td]8.5 Hour White Meter[/td][/tr]

[tr][td]
6​
[/td][td]GUCL[/td][td]1[/td][td]8.5 Hour White Meter[/td][/tr]

[tr][td]
7​
[/td][td]GUCL[/td][td]1[/td][td]8.5 Hour White Meter[/td][/tr]

[tr][td]
8​
[/td][td]HYDE[/td][td]1[/td][td]Unrestricted, Single rate[/td][/tr]

[tr][td]
9​
[/td][td]HYDE[/td][td]1[/td][td]Unrestricted, Single rate[/td][/tr]

[tr][td]
10​
[/td][td]HYDE[/td][td]1[/td][td]Unrestricted, Single rate[/td][/tr]

[tr][td]
11​
[/td][td]HYDE[/td][td]1[/td][td]Unrestricted, Single rate[/td][/tr]

[tr][td]
12​
[/td][td]HYDE[/td][td]1[/td][td]Unrestricted, Single rate[/td][/tr]

[tr][td]
13​
[/td][td]IPNL[/td][td]1[/td][td]8.5 Hr White Meter[/td][/tr]

[tr][td]
14​
[/td][td]IPNL[/td][td]1[/td][td]8.5 Hr White Meter[/td][/tr]

[tr][td]
15​
[/td][td]IPNL[/td][td]1[/td][td]8.5 Hr White Meter[/td][/tr]

[tr][td]
16​
[/td][td]IPNL[/td][td]1[/td][td]8.5 Hr White Meter[/td][/tr]

[tr][td]
17​
[/td][td]IPNL[/td][td]1[/td][td]8.5 Hr White Meter[/td][/tr]

[tr][td]
18​
[/td][td]IPNL[/td][td]1[/td][td]8.5 Hr White Meter[/td][/tr]

[tr][td]
19​
[/td][td]IPNL[/td][td]1[/td][td]8.5 Hr White Meter[/td][/tr]

[tr][td]
20​
[/td][td]IPNL[/td][td]1[/td][td]8.5 Hr White Meter[/td][/tr]
[/table]
C4=IFERROR(INDEX(Sheet2!$C$4:$C$26,MATCH(Sheet1!A4&" "&Sheet1!B4,INDEX(Sheet2!$A$4:$A$25&" "&Sheet2!$B$4:$B$25,0),0)),"Not Found")
copied down

Did you check to make sure the ranges in my suggestion, matched your actual data?
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question Question
VBA Questions - Copying dynamic data from one tab to another and deleting filtered rows
Replies
1
Views
504
DanteAmor
DanteAmor
H
  • Question Question
VBA code to pull data from one worksheet to another
Replies
0
Views
256
hrayani
H
S
  • Question Question
VBA to copy data from one worksheet to another (only specific columns)
Replies
2
Views
1K
SpireiteSteve
S
P
  • Question Question
copy data from one column and paste to another as values for multiple worksheet
Replies
0
Views
462
priisha
P
M
  • Solved
VBA Transpose data from one sheet into another
Replies
9
Views
667
madi1004
M

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