Hi! I’m a complete novice to excel formulas and VBA. I’m not even sure if excel can do what I’m trying. I've checked all of the posts I could find and nothing seems to fit.
I’m trying to have excel compare the details on sheet1 against the details on sheet2 to return the sheet2 row number if a match is found in column A and a true, false or an #n/a response for the amount in column C of that same row. If nothing matches, it should return an error #n/a. Some of my worksheets can have up to 25,000 lines of data and the account numbers in column A may appear more than once with different amounts in column C.
This is what I’m starting with. I have 2 sheets in the same workbook. Each sheet has 3 columns. Both sheets are sorted in ascending order on column A. Column A has an account number which can be any combination of numbers, letters and dashes/slashes. Column B has an account name which again can be numbers, letters, and dashes/slashes. Column C has an amount.
What I’m running into is that MATCH returns the row number in column A and VLOOKUP returns a true/false or n/a in column C, with both based on the first instance of the same account number in column A. Is there a way to have excel continue looking down the columns for other matches until the end of the data?
This is the code I have. I’m not sure if it can be tweaked or if it needs to be something completely different. As I said, I’m a complete novice. I'm only starting to learn formulas and don’t know of others yet that may be able to produce the results I’m looking for.
This is an example of what it’s returning.
Sheet 1
Col A Col B Col C Col D Col E
Acct # Name Amt #? Amt?
X-1 X Inc. $25.00 2 True
X-1 X Inc. $10.00 2 False
X-1 X Inc. $17.00 2 False
Z-2 Z Ltd $45.00 #N/A #N/A
Sheet 2
Col A Col B Col C
Acct # Name Amt
X-1 X Inc. $25.00
X-1 X Inc. $17.00
X-1 X Inc. $10.00
Could someone possibly help me figure out what I’m missing?
Thank you!
I’m trying to have excel compare the details on sheet1 against the details on sheet2 to return the sheet2 row number if a match is found in column A and a true, false or an #n/a response for the amount in column C of that same row. If nothing matches, it should return an error #n/a. Some of my worksheets can have up to 25,000 lines of data and the account numbers in column A may appear more than once with different amounts in column C.
This is what I’m starting with. I have 2 sheets in the same workbook. Each sheet has 3 columns. Both sheets are sorted in ascending order on column A. Column A has an account number which can be any combination of numbers, letters and dashes/slashes. Column B has an account name which again can be numbers, letters, and dashes/slashes. Column C has an amount.
What I’m running into is that MATCH returns the row number in column A and VLOOKUP returns a true/false or n/a in column C, with both based on the first instance of the same account number in column A. Is there a way to have excel continue looking down the columns for other matches until the end of the data?
This is the code I have. I’m not sure if it can be tweaked or if it needs to be something completely different. As I said, I’m a complete novice. I'm only starting to learn formulas and don’t know of others yet that may be able to produce the results I’m looking for.
Code:
Sub REC()
Sheets("Sheet1").Select
Range("D2").Select
ActiveCell.FormulaR1C1 = "=MATCH(RC[-3],Sheet2!C[-3],0)"
Selection.NumberFormat = "0_);[Red](0)"
Range("E2").Select
ActiveCell.FormulaR1C1 =
"=VLOOKUP(RC1,Sheet2!C[-4]:C[26],3,FALSE)=Sheet1!RC[-2]"
lastrow = Range("A" & Rows.Count).End(xlUp).Row
Range("D2").AutoFill Destination:=Range("D2:D" & lastrow)
Range("E2").AutoFill Destination:=Range("E2:E" & lastrow)
End sub
This is an example of what it’s returning.
Sheet 1
Col A Col B Col C Col D Col E
Acct # Name Amt #? Amt?
X-1 X Inc. $25.00 2 True
X-1 X Inc. $10.00 2 False
X-1 X Inc. $17.00 2 False
Z-2 Z Ltd $45.00 #N/A #N/A
Sheet 2
Col A Col B Col C
Acct # Name Amt
X-1 X Inc. $25.00
X-1 X Inc. $17.00
X-1 X Inc. $10.00
Could someone possibly help me figure out what I’m missing?
Thank you!