Find Function Assistance

[extrudebg]

I have a spreadhseet with six workbooks that have 15 digit numbers in column A. On the 7th workbook I have a list of 9 digit numbers.

I am looking for a foumula to search column A of workbooks 1-6 for each of the 9 digit numbers located on workbook seven. Any suggestions??

[Richard Buttrey]

Hi,

Are the 9 digit numbers the first 9 characters of the 15 digit number?
What value do you want the formula to return? A Match() function for instance would return the row number.

Rgds

[extrudebg]

No, the 9 digits are not the first 9 of the 15.

This is what I currently have, tailored to search one spreadsheet only.

=IF(ISNUMBER(FIND(A2,'1'!A:A)),TRUE,FALSE)

Cell A2 on the spreadsheet is the 9 digit number, and the 15 digit numbers are in column A of Worksheet 1.

All my responses come back false, but if I alter the equation to search only the specific cell on Worksheet 1 that contains the information, then I get a True response.

Example: =IF(ISNUMBER(FIND(A2,'1'!A4152)),TRUE,FALSE)

Is there any way to alter this equation to search all cells in the column?

[Richard Buttrey]

I don't know of any single function or series of functions that will achieve this. A FIND() works on characters in a cell, not cells in a column. Similarly a MATCH() works on whole cells in a column.

The only option I fear would be a macro to test each cell with a FIND() (or in VBA syntax an Instr command), against every cell in the other sheet. Whilst that would be a simple macro to write, I suspect if you have over 4000 rows to search, and perhaps a similar number on the other sheet, then the number of combinations may rule it out because of the processing time.

Is there nothing in the structure of the numbers, or perhaps other supporting data that could simplify the problem?

Rgds

[NBVC]

How about perhaps:

=ISNUMBER(MATCH("*"&A2&"*",TEXT('1'!$A$1:$A$1000, "#"),0))

which is an array formula and you must confirm with CTRL+SHIFT+ENTER.

If you can make your entries in A1:A1000 text entries, then you don't need an array formula... you can use...ENTER only...

=ISNUMBER(MATCH("*"&A2&"*",'1'!$A$1:$A$1000,0))

Edit: forgot to say that for the second formula to work, the entries must be text strings...

[extrudebg]

The 9 digit numbers are account numbers from an old company system. The 15 digit numbers are the new account numbers. The 9 digit numbers are still included in the 15 digit with one number before and five after. Ex: X123456789XXXXX

I have tried the equations suggested but have had no luck.

thanks all for your assistance.

[NBVC]

My first formula must be confirmed with the CTRL, SHIFT and ENTER keys... and cannot reference whole column such as A:A....

[extrudebg]

Excellent...I put in a line range instead of the column as a whole and it's working great.

Thanks!