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).
[TABLE="width: 120"]
<tbody>[TR]
[TD="class: xl68, width: 80, bgcolor: transparent"]98400
[/TD]
[TD="width: 80, bgcolor: transparent"]x
[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]00B
[/TD]
[TD="bgcolor: transparent"]x2
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]00C
[/TD]
[TD="bgcolor: transparent"]x3
[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]00D
[/TD]
[TD="bgcolor: transparent"]x4
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]00D
[/TD]
[TD="bgcolor: transparent"]x5
[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]00E
[/TD]
[TD="bgcolor: transparent"]x6
[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]00F
[/TD]
[TD="bgcolor: transparent"]x7
[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]00G
[/TD]
[TD="bgcolor: transparent"]x8
[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]00G
[/TD]
[TD="bgcolor: transparent"]x9
[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]00K
[/TD]
[TD="bgcolor: transparent"]x10
[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]00L
[/TD]
[TD="bgcolor: transparent"]x11
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]0TG
[/TD]
[TD="bgcolor: transparent"]x12
[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]2BA
[/TD]
[TD="bgcolor: transparent"]x13
[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]2BB
[/TD]
[TD="bgcolor: transparent"]x14
[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]A14011
[/TD]
[TD="bgcolor: transparent"]x15
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]A14012
[/TD]
[TD="bgcolor: transparent"]x16
[/TD]
[/TR]
</tbody>[/TABLE]

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]

[TABLE="width: 640"]
<colgroup><col width="64" span="10" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]00B[/TD]
[TD="width: 64"]x2[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"]00000D[/TD]
[TD="width: 64"]x18[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD]00C[/TD]
[TD]x3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]00B[/TD]
[TD]x2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]00D[/TD]
[TD]x4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]00C[/TD]
[TD]x3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]00D[/TD]
[TD]x5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]00D[/TD]
[TD]x4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]00E[/TD]
[TD]x6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]00D[/TD]
[TD]x5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]00F[/TD]
[TD]x7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]00E[/TD]
[TD]x6[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]00G[/TD]
[TD]x8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]00F[/TD]
[TD]x7[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]00G[/TD]
[TD]x9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]00G[/TD]
[TD]x8[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]00K[/TD]
[TD]x10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]00G[/TD]
[TD]x9[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]00L[/TD]
[TD]x11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]00K[/TD]
[TD]x10[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0TG[/TD]
[TD]x12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]00L[/TD]
[TD]x11[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2BA[/TD]
[TD]x13[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0E[/TD]
[TD]x17[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2BB[/TD]
[TD]x14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0TG[/TD]
[TD]x12[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A14011[/TD]
[TD]x15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2BA[/TD]
[TD]x13[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A14012[/TD]
[TD]x16[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2BB[/TD]
[TD]x14[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0E[/TD]
[TD]x17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]A14011[/TD]
[TD]x15[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]00000D[/TD]
[TD]x18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]A14012[/TD]
[TD]x16[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]this is in ascending order[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 6"]is your problem you want 0TG and 1TG both to return x12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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.

[TABLE="width: 864"]
<tbody>[TR]
[TD]Property Ref
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Address Line 1
[/TD]
[TD]CCTV
[/TD]
[TD]Door Entry
[/TD]
[TD]Emergency Lighting
[/TD]
[/TR]
[TR]
[TD]39800
[/TD]
[TD]W_39800
[/TD]
[TD]blk-39800
[/TD]
[TD]Coldwest Way
[/TD]
[TD]4291 Coldwest Way
[/TD]
[TD]No Asset Found
[/TD]
[TD]TVF
[/TD]
[TD]Road Address Match
[/TD]
[/TR]
</tbody>[/TABLE]

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]

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

 

[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))