Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
FINDING SIMILAR RECORDS BETWEEN 2 SPREADSHEETS
I have one spreadsheet that shows the original invoice # of 003567457A and
new version spreadsheet that shows the new invoice is now 003567457AR. I'm trying to find a formula like vlookup that will match the newer version invoice with the older version invoice because it is "similar" |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
FINDING SIMILAR RECORDS BETWEEN 2 SPREADSHEETS
On Sep 17, 2:02*pm, Colleen10
wrote: I have one spreadsheet that shows the original invoice # of 003567457A and new version spreadsheet that shows the new invoice is now 003567457AR. *I'm trying to find a formula like vlookup that will match the newer version invoice with the older version invoice because it is "similar" Assuming the invoice # is in A1 in the original and the new invoice is in a table in C2:G20 and you want the value of the 3rd column vlookup(A1&"R",C2:G20,3,false) Burke |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
FINDING SIMILAR RECORDS BETWEEN 2 SPREADSHEETS
Thanks but I'm confused by your range of C2:G20. I realize this is an
example only so let me try and simplify...in my original spreadsheet (called BRP MFG BILLING), column A, I have the invoices that end in R, in my other spreadsheet (called GE CAD Mar(1)) , in column A I have the invoices not ending in R. I want to insert my forumla in column B in the 2nd spreadsheet beside the invoices without the R. so would my formula be: VLOOKUP('[BRP MFG BILLING.xls]Report1'!$A:$A&"R",A:A,1,FALSE) is returning no matches. "Burke" wrote: On Sep 17, 2:02 pm, Colleen10 wrote: I have one spreadsheet that shows the original invoice # of 003567457A and new version spreadsheet that shows the new invoice is now 003567457AR. I'm trying to find a formula like vlookup that will match the newer version invoice with the older version invoice because it is "similar" Assuming the invoice # is in A1 in the original and the new invoice is in a table in C2:G20 and you want the value of the 3rd column vlookup(A1&"R",C2:G20,3,false) Burke |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
FINDING SIMILAR RECORDS BETWEEN 2 SPREADSHEETS
Actually I was able to make the formula work by playing around with it a bit
but interestingly in some cases it returned the full value of the "R invoice but other times it just returned the "R" itself. Looks like some of the cells were formatted or maybe were not numeric value...it's ok, I can fix these with a concatenate. Thanks so much "Burke" wrote: On Sep 17, 2:02 pm, Colleen10 wrote: I have one spreadsheet that shows the original invoice # of 003567457A and new version spreadsheet that shows the new invoice is now 003567457AR. I'm trying to find a formula like vlookup that will match the newer version invoice with the older version invoice because it is "similar" Assuming the invoice # is in A1 in the original and the new invoice is in a table in C2:G20 and you want the value of the 3rd column vlookup(A1&"R",C2:G20,3,false) Burke |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
FINDING SIMILAR RECORDS BETWEEN 2 SPREADSHEETS
On Sep 17, 3:22*pm, Colleen10
wrote: Actually I was able to make the formula work by playing around with it a bit but interestingly in some cases it returned the full value of the "R invoice but other times it just returned the "R" itself. *Looks like some of the cells were formatted or maybe were not numeric value...it's ok, I can fix these with a concatenate. *Thanks so much "Burke" wrote: On Sep 17, 2:02 pm, Colleen10 wrote: I have one spreadsheet that shows the original invoice # of 003567457A and new version spreadsheet that shows the new invoice is now 003567457AR.. *I'm trying to find a formula like vlookup that will match the newer version invoice with the older version invoice because it is "similar" Assuming the invoice # is in A1 in the original and the new invoice is in a table in C2:G20 and you want the value of the 3rd column vlookup(A1&"R",C2:G20,3,false) Burke Colleen, glad you got it worked out, however be careful with your use of $A:$A as a lookup value and A:A as a lookup column, those references create at least 65536 rows each to look in for a match, if you have a lot of vlookups it will seriously slow down Excel due to the way it recalculates. You may be limited on what you can do in the file you are looking in (BRP MFG BILLING.xls) VLOOKUP('[BRP MFG BILLING.xls]Report1'!$A: $A&"R",A:A,1,FALSE) however you should be able to change the 1st argument to reference a single cell instead of $A:$A you should use $A1 and copy the formula down VLOOKUP('[BRP MFG BILLING.xls]Report1'! $A1&"R",A:A,1,FALSE). Also UNLESS you have or potentially could have close to 65536 invoices in BRP MFG BILLING.xls I would reduce A:A (in the 2nd argument) to fewer rows, for example if you have about 10,000 invoices change the 2nd argument to something like $A1:$A12,000 or a named range if that is feasible, it will recalculate much quicker since it doesn't refer to 65536 rows. It all depends on how much data you have and YMMV. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
FINDING SIMILAR RECORDS BETWEEN 2 SPREADSHEETS
Thanks Burke...believe it or not I did in fact have 65,000 records. We have
since found that the file I was sent is corrupt and was giving me the wierdest problems but all to say, thank you because this formula you gave me could be handy in the future....thanks so much "Colleen10" wrote: I have one spreadsheet that shows the original invoice # of 003567457A and new version spreadsheet that shows the new invoice is now 003567457AR. I'm trying to find a formula like vlookup that will match the newer version invoice with the older version invoice because it is "similar" |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delete Non-identical but Similar Records | Excel Discussion (Misc queries) | |||
similar names - two spreadsheets | Excel Worksheet Functions | |||
How to identify when similar records appear in two sets of data | Excel Discussion (Misc queries) | |||
Merging slightly similar records | Excel Discussion (Misc queries) | |||
getting unique records similar to the primary key in access | Excel Worksheet Functions |