Comparing Lists - F2 - Format Cells

Hi,

I am comparing two lists I have received from two different sources. Both have Unique IDs by which I am trying to VLOOKUP to see if all the names in both the lists are there.

List 1 - List2 - Formula
12345 12345 VLOOKUP formula

The issue is that it gives #N/A for the above, but when I come to LIST1 which is the value i'm looking and go to its cell and press F2/Edit and press Enter THEN the value shows.

I have tried to use FORMAT CELLS to put both lists to General and both to Number and all that.

Please help.

Originally Posted by sachinattri

Hi,

I am comparing two lists I have received from two different sources. Both have Unique IDs by which I am trying to VLOOKUP to see if all the names in both the lists are there.

List 1 - List2 - Formula
12345 12345 VLOOKUP formula

The issue is that it gives #N/A for the above, but when I come to LIST1 which is the value i'm looking and go to its cell and press F2/Edit and press Enter THEN the value shows.

I have tried to use FORMAT CELLS to put both lists to General and both to Number and all that.

Please help.

try a

=TRIM(CLEAN(A1))

on the column, then copy and Paste Special = Values back to the original column.

hth
---

'Trim' ........Does not work.

I have a test XLS, very small 14kb file which I can send you if you want.

Thank you

Originally Posted by sachinattri

'Trim' ........Does not work.

I have a test XLS, very small 14kb file which I can send you if you want.

Thank you

Yes, in Explore (My Computer) add it to an Archive, name as .zip, and post it here, or send to my email.

---

Thanks again.

Here is the file. You will see in the A column all are #N/A, but as soon as you go to B2, press F2 and then Enter OR do a double click, it will start picking up the values.

Thanks so much.

I have though solved the problem thru' another trick up my sleeve (copying and pasting the values in Notepad and then back to the original column) but please tell me if there is a Function way of solving this.

Thanks very much.

Originally Posted by sachinattri

Thanks again.

Here is the file. You will see in the A column all are #N/A, but as soon as you go to B2, press F2 and then Enter OR do a double click, it will start picking up the values.

Thanks so much.

The items in column B2:B15 are not numbers, so the cure for your data would be

=TRIM(CLEAN(B2))+0

and copy - Paste Special = Values back over B

(If you rightmouse and 'Format Cell' set the Alignment, Horizontal to General, observe that the items are on the left, rather than the right of the cell.

OR

Use +0 to convert.

Put 0 (zero) in a cell and copy, then select B2:B15 and Paste, Special ADD your data will be cured.

hth

---

VERY COOL. Thank you very much and have a good day !