Copying data from one worksheet to another using VBA/Formula

[DarrenW81]

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!

Sheet 1 Example:			Sheet 2 Example:
COLUMN A	COLUMN B	COLUMN C?		COLUMN A	COLUMN B	COLUMN C
GUCL	1			HYDE	1	Unrestricted, Single rate
GUCL	1			LOND	1	Economy 7, 23.30 - 06.30
GUCL	1			MIDE	1	Prepayment Two Rate
GUCL	1			NORW	1	Summer Unrestricted
HYDE	1			SOUT	1	O/P -F- 8HRS + 2.5HRS PROG TIME/S
HYDE	1			SPOW	1	8.5 Hour White Meter
HYDE	1			SWEB	1	11.5 Hour Off Peak - TS
HYDE	1			YELG	1	NHH non-prog 1r credit meter and T/S
HYDE	1			GUCL	1	8.5 Hour White Meter
IPNL	1			IPNL	1	8.5 Hr White Meter
IPNL	1			HYDE	2	Dynamic-htg units
IPNL	1			LOND	2	Economy 7, 00.00 - 07.00
IPNL	1			MANW	2	7-Hour E7
IPNL	1			MIDE	2	Prepayment Two Rate
IPNL	1			SEEB	2	Credit single rate
IPNL	1			SOUT	2	O/P -F- 8HRS + 2.5HRS NON - PROG TIME/S
IPNL	1			SPOW	2	8.5 Hour White Meter
				SWEB	2	11.5 Hour Off Peak - TS
				YELG	2	NHH non-prog 1r credit meter and T/S
				GUCL	2	8.5 Hour White Meter
				NORW	2	E7 Key RTS
				PENL	2	E7 Key RTS

<tbody>
</tbody>

 

[Legacy 396030]

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.

 

[FDibbins]

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")

 

[DarrenW81]

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!

Any further assistance would again be much appreciated.

Thanks guys.

 

[FDibbins]

It worked for me...

	A​	B​	C​
3​	COLUMN A	COLUMN B	COLUMN C?
4​	GUCL	1	8.5 Hour White Meter
5​	GUCL	1	8.5 Hour White Meter
6​	GUCL	1	8.5 Hour White Meter
7​	GUCL	1	8.5 Hour White Meter
8​	HYDE	1	Unrestricted, Single rate
9​	HYDE	1	Unrestricted, Single rate
10​	HYDE	1	Unrestricted, Single rate
11​	HYDE	1	Unrestricted, Single rate
12​	HYDE	1	Unrestricted, Single rate
13​	IPNL	1	8.5 Hr White Meter
14​	IPNL	1	8.5 Hr White Meter
15​	IPNL	1	8.5 Hr White Meter
16​	IPNL	1	8.5 Hr White Meter
17​	IPNL	1	8.5 Hr White Meter
18​	IPNL	1	8.5 Hr White Meter
19​	IPNL	1	8.5 Hr White Meter
20​	IPNL	1	8.5 Hr White Meter

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?