Numbers as Text Causing Problems

AirMaximus88

New Member
Joined
Mar 13, 2014
Messages
16
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
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

oldbrewer

Board Regular
Joined
Apr 11, 2010
Messages
11,003
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

Well-known Member
Joined
Nov 23, 2009
Messages
634
Office Version
  1. 2016
Platform
  1. Windows
can you provide some examples of the cells you are looking up?
 

oldbrewer

Board Regular
Joined
Apr 11, 2010
Messages
11,003
00Bx200000Dx18
00Cx300Bx2
00Dx400Cx3
00Dx500Dx4
00Ex600Dx5
00Fx700Ex6
00Gx800Fx7
00Gx900Gx8
00Kx1000Gx9
00Lx1100Kx10
0TGx1200Lx11
2BAx130Ex17
2BBx140TGx12
A14011x152BAx13
A14012x162BBx14
0Ex17A14011x15
00000Dx18A14012x16
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

Well-known Member
Joined
Feb 12, 2011
Messages
547

ADVERTISEMENT

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

New Member
Joined
Mar 13, 2014
Messages
16
@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

New Member
Joined
Mar 13, 2014
Messages
16

ADVERTISEMENT

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

New Member
Joined
Mar 13, 2014
Messages
16
=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

New Member
Joined
Mar 13, 2014
Messages
16
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.
 
Last edited:

AirMaximus88

New Member
Joined
Mar 13, 2014
Messages
16
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))
 

Watch MrExcel Video

Forum statistics

Threads
1,118,852
Messages
5,574,662
Members
412,608
Latest member
Guromir
Top