Numbers as Text Causing Problems

[AirMaximus88]

Hi All,

I'm sure this is a common theme on this board, but numbers appearing as text is causing me a helluva problem.

I'm running an awkwardly complicated series of if statements with vlookups matching property codes in the first column and returning data from second column (see sample data below).

98400	x
00B	x2
00C	x3
00D	x4
00D	x5
00E	x6
00F	x7
00G	x8
00G	x9
00K	x10
00L	x11
0TG	x12
2BA	x13
2BB	x14
A14011	x15
A14012	x16

<tbody>
</tbody>

These data are populated on thirty or more sheets and are a mix of text and numbers. Vlookups are sometimes failing to match their counterparts on the sheets because one is represented as text frequently with apostrophes preceding the numbers. Because some of these property codes are preceded by 0's or letters, I cannot make the whole column numbers (0's disappear, etc.).

How can I best proceed?

I should mention these lists are 4,000 rows on average.

Max

 

[oldbrewer]

if all your look up codes are in one table and the first column is in ascending order, I don't see what the problem is.

 

[anglais428]

can you provide some examples of the cells you are looking up?

 

[oldbrewer]

00B	x2					00000D	x18
00C	x3					00B	x2
00D	x4					00C	x3
00D	x5					00D	x4
00E	x6					00D	x5
00F	x7					00E	x6
00G	x8					00F	x7
00G	x9					00G	x8
00K	x10					00G	x9
00L	x11					00K	x10
0TG	x12					00L	x11
2BA	x13					0E	x17
2BB	x14					0TG	x12
A14011	x15					2BA	x13
A14012	x16					2BB	x14
0E	x17					A14011	x15
00000D	x18					A14012	x16
						this is in ascending order
	is your problem you want 0TG and 1TG both to return x12

<colgroup><col width="64" span="10" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[shawnhet]

This may be too simple(and you've already tried it) but have you tried formatting everything including the numbers on both the source and the lookup sheets as text?

I can't see a reason why you should continue to get the kinds of reasons you mention if everything is text.

Cheers,

 

[AirMaximus88]

@oldbrewer The formula is not returning the incorrect values (which is always a bonus), it's returning #N/A even though it should have an exact match.

Property Ref				Address Line 1	CCTV	Door Entry	Emergency Lighting
39800	W_39800	blk-39800	Coldwest Way	4291 Coldwest Way	No Asset Found	TVF	Road Address Match

<tbody>
</tbody>

This is a sample of the information I have and the information I return. I'm hunting assets for our properties (CCTV, Door Entry, Emergency Lighting, etc.) that have been lost in the system somewhere. The asset sheets we have are up to date, but manually entered data (i.e. inaccurate/typos everywhere), and with several different iterations of property codes within each sheet.

I have the property code in it's most simple form on the left, then concatenated with a "W_", then "blk-". Then a simple form of the address (that hopefully isn't misspelled by a clumsy data entry clerk). I will include a list of data that I'm searching in, and the code that pulls in the further messages for inspection.

@shawnhet Yeah, I tried converting it all to text, some of the 5 digit numbers were preceeded by apostrophes with no explanation. Needless to say, this caused the vlookup to return #N/A.

 

[AirMaximus88]

ADDRESS	BLK CODE	CONTRACTOR
1 Generic Road	001	TVF (UK) LTD
2 Generic Road	001	TVF (UK) LTD
3 Generic Road	001	TVF (UK) LTD
4 Generic Road	001	TVF (UK) LTD
5 Generic Road	22	TVF
6 Generic Road	849	TVF (UK) LTD
7 Generic Road	850	TVF (UK) LTD
8 Generic Road	858	TVF (UK) LTD
15-23 Generic Block of Flats	860	TVF (UK) LTD
The Generic Country Home Name, Generic Road, Generica	868	TVF
400 Generic Lane	884	TVF (UK) LTD
401 Generic Lane	904	TVF (UK) LTD
402 Generic Lane	944	TVF (UK) LTD
403a Generic Lane	13600	TVF (UK) LTD
404 Generic Lane	13601	TVF (UK) LTD
405 Generic Lane	13701	TVF (UK) LTD
406 Generic Lane	14100	TVF (UK) LTD
407 Generic Lane	14201	TVF (UK) LTD
408 Generic Lane	14400	TVF (UK) LTD
409 Generic Lane	14801	TVF (UK) LTD
410 Generic Lane	14901	TVF (UK) LTD
411 Generic Lane	14902	TVF (UK) LTD
412 Generic Lane	15301	TVF (UK) LTD
413 Generic Lane	15700	TVF (UK) LTD
414 Generic Lane	21000	TVF (UK) LTD
415 Generic Lane	21800	TVF (UK) LTD
416 Generic Lane	22000	TVF (UK) LTD

<tbody>
</tbody>

 

[AirMaximus88]

=IF(ISNA(VLOOKUP(B3,TABLE-1-BLKCODE, 2, FALSE)), IF(ISNA(VLOOKUP(C3, TABLE-1-BLKCODE, 2, FALSE)), IF(ISNA(VLOOKUP(D3,TABLE-1-BLKCODE, 2, FALSE)), IF(ISNA(VLOOKUP(CONCATENATE("*", E3, "*"), ADRESS-1, 11, FALSE)), IF(ISNA(VLOOKUP(CONCATENATE("*", E3, "*"), ADRESS-2 , 10, FALSE)), "No Asset Found", "Road Address Match"), "Road Address Match"), VLOOKUP(D3, TABLE-1-BLKCODE, 2, FALSE)), VLOOKUP(C3,TABLE-1-BLKCODE, 2, FALSE)), VLOOKUP(B3,TABLE-1-BLKCODE, 2, FALSE))

 

[AirMaximus88]

Formula is quite difficult to interpret. It is essentially this:

IF prop_codeA returns nothing, look for prop_codeB.

IF prop_codeB returns nothing, look for prop_codeC.

IF prop_codeC returns nothing, look for simple_road_address.

IF simple_road_address is present - say Road Address Match (then I manually have to search for the property and if it's the correct address, update our system).

If simple_road_address is not present - asset not found.

The final statements are if(prop_code matches, return contractor)



Does that make sense? I'm aware of its overcomplicated looks, but it was the only logical way I could see of efficiently sifting through the data of 55,000 properties and 25 asset types. My supervisor suggested manually checking each property at one point... at which point I explained the math (55,000*25*however many different iterations of block codes we discover...)

That's not happening.

 

[AirMaximus88]

In the simplified table I've produced, this code is more accurate:


=IF(ISNA(VLOOKUP(B3,TABLE-1-BLKCODE, 2, FALSE)), IF(ISNA(VLOOKUP(C3, TABLE-1-BLKCODE, 2, FALSE)), IF(ISNA(VLOOKUP(D3,TABLE-1-BLKCODE, 2, FALSE)), IF(ISNA(VLOOKUP(CONCATENATE("*", E3, "*"), ADDRESS-1, 3, FALSE)), "No Asset Found", "Road Address Match"), VLOOKUP(D3, TABLE-1-BLKCODE, 2, FALSE)), VLOOKUP(C3,TABLE-1-BLKCODE, 2, FALSE)), VLOOKUP(B3,TABLE-1-BLKCODE, 2, FALSE))