FINDING SIMILAR RECORDS BETWEEN 2 SPREADSHEETS

[Colleen10]

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"

 

[Burke]

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

 

[Colleen10]

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.

 

[Colleen10]

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]

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

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.

 

[Colleen10]

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