Okay, that's interesting - I've never struck non-breaking spaces before.
Definately something to look out for, thanks.

The source for this file was a flat file export from a database, it
basically produces a text output that you can open with Excel and do what you
like with.

--
Regards - Peter


"Niek Otten" wrote:

<part number 1672312 with no spaces before or after

It may have no spaces before or after, but there may still be differences between text and number.
Test that with the ISNUMBER() or ISTEXT() functions, and compare the length of the items with the LENGTH() function for the
presence of invisible characters (like non-breaking spaces, which often occur in files downloaded from the Internet)

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"Peter" wrote in message ...
|I have a spreadsheet with 2 lists of data, each list contains a number of
| columns, the 2 lists are not equal in length, list 1 is about 2,600 rows and
| list 2 is about 50,000 rows
|
| The left most column in each list contains part numbers, these are unique
| values that sometimes contain alpha characters and sometimes not.
|
| The first list is a 'finished' list that I wish to use for display, but the
| second list has a column that contains a statistic that I have been asked to
| incorporate into the finished list.
|
| The second list also has most of the part numbers present in the first list
| (about 94%), so I didn't think the task would be too hard, a relatively
| simple formula should do the trick - but my formula has returned results that
| I do not understand.
|
| My formula is thus:
|
| "=IF(COUNTIF($Q$2:$Q49959,A2636)<0,OFFSET($Q$1,MA TCH(A2636,$Q$2:$Q$49959,1),4,1,1),0)"
|
| Now the formula works fine with the exception of the MATCH function, the
| cell A2636 has the part number 1672312 with no spaces before or after.
|
| The part number 1672312 exists in the second list in cell Q1557, again with
| no spaces before or after and as can be seen by the formula, the value I want
| is in the fifth column of the list.
|
| The MATCH function seems to want to match to a part number 167-2222 which is
| located in the cell Q7638.
|
| I tried altering the format of the part number columns to text, but it made
| no difference. I also tried altering the MATCH_TYPE argument of the function
| from 1 to 0 to force an exact match, the formula then returns an error.
|
| Now I do not consider myself an Excel novice and I know that this task would
| be easily accomplished with a little VBA, but that's not the point. I have
| read the help file again to make sure that I was using the MATCH function
| correctly. I tried alternatives like VLOOKUP to see what it matched to, again
| it matches to the same incorrect cell.
|
| I suspect formatting of the part number columns somehow, but I don't know
| how - the part number columns contain absolute values, no underlying
| formula's, no linked cells or anything like that. Using the Formula Evaluate
| tool shows me that the cell A2636 evaluates to the correct part number, but
| from there is goes south.
|
| Anyone have any ideas?
| --
| Regards - Peter
|