Cell mismatch - not sure why

[itsrich]

In my workbook I have two tabs

1. Database
   1. Column C = Vendor PN
   2. Column A looks up F100 = XLOOKUP(C3,Data!B:B,Data!A:A,"Not in DAX")
2. Data
   1. Column B is a list of Vendor PN copied and paste from a CSV pull from DAX
   2. Column A is the F100

Some cells on the Database Tab with the XLookUp fail to find match returning "Not in DAX".

Current Resolution: I search the number and find it on the Data tab and then copy it to the Database tab.

Also, when I click into the cell and hit enter, it also fixes the issue.

What else can I provide to help solve this? I don't wish to upload the entire file.

 

[Joe4]

What do these values you are looking up an matching on look like?
Are any of them all numbers?

The two most common causes of failed lookups are:
- trying to look up data of different data types (i.e. comparing "valid numbers" to "numbers entered as text" - the values you are matching MUST be the same data type!)
- some of your entries have extra spaces or other characters in them (especially at the end) causing things not to match

Two useful functions to aid you. Find a record that is currently NOT matching, but should be.
Then, enter this formulas to check the data you are matching on. The should return the same values for both sides:

=ISNUMBER(cell reference)
=LEN(cell reference)

 

[itsrich]

Always good to develop a tools for solving problems.

I am working with Cell 237 on Database and it matches Cell 562 on Data

• Cell 237 on Database ~ ISNUMBER = False, LEN = 5
• Cell 562 on Data ~ ISNUMBER = True, LEN = 5
• sample of Vendor PN

5123PW192

62X24 CURT

6303G

7920BK-2H

8Z100SQP0Z/P638/18BK

9420A330

A-HZST-2358G-JFEA-160-70T-8BMF

BAGGAGEMAG-1

BMA90140

BSEC-11A

co-01g-25-248

So they are not the same format. Currently 590 cells that appear to have this issue.

I can brut force fix this by... in every cell hit F2, press enter.

I pull from DAX daily and paste to Data tab the days updated info.

It looks like there is a number/text format issue. So, I need to be careful about how I paste to keep formats straight. Correct?

 

[Joe4]

Yes, it would probably be best to get them all the same format, presumably Text.

Note that if the data is already exported, you can convert the whole column of data by using "Text to Columns" (found under the "Data" menu) and converting the whole column to Text.

 

[itsrich]

Yes, it would probably be best to get them all the same format, presumably Text.

Note that if the data is already exported, you can convert the whole column of data by using "Text to Columns" (found under the "Data" menu) and converting the whole column to Text.

I export from DAX to CSV file. I open the CSV file from the workbook I am in and use the delimiting in the import tool. I designate this column B as text (some Vendor PN begin with zero). I do not convert. Then I copy and paste from the CSV to my working file. Is there a better way?

 

[Joe4]

CSV files are a pain, because Excel tries to "guess" the format/data type of each cell on its own, and sometimes guesses wrong, which drops leading zeroes, etc.
Can you export the DAX file to an Excel file, instead of CSV?
If Excel is not an option, is Tab Delimited an option?
I do not use DAX, so I do not know which options are available to you.

 

[RoryA]

If you made the data text in the lookup table, just make the lookup value text too:

=XLOOKUP(C3&"",Data!B:B,Data!A:A,"Not in DAX")

 

[Joe4]

If you made the data text in the lookup table, just make the lookup value text too:

=XLOOKUP(C3&"",Data!B:B,Data!A:A,"Not in DAX")

That won't fix the issue of if there are leading zeroes that have been dropped though, right?

 

[RoryA]

No, but if it's coming through as text, the leading zeroes should not have been dropped if they were in the source that was copied from. (personally I would use Power Query rather than opening a CSV, parsing it, then copying and pasting)

 

[Joe4]

No, but if it's coming through as text, the leading zeroes should not have been dropped if they were in the source that was copied from. (personally I would use Power Query rather than opening a CSV, parsing it, then copying and pasting)

Yeah, working with CSV has always been a pain, with all the "auto conversions" Excel does on the data!