puck1263 said:
So, all of the pertinent columns are "numbers stored as text."
So all should be text.
Let's be precise to minimize confusion and chasing your tail.
In "PV tool list", yes, all of B3:B33 are text.
In "Equipment list", all of A2
512 are text __expect__ that D502 is a
number. That is, ISNUMBER(D502) returns TRUE.
In "Raw data", B3:B309 is a "random" __mix__ of text and numbers. Again,
when I say "number", I mean that ISNUMBER(...) returns TRUE.
**********
(I suspect that is the root cause of your problem.)
**********
In "IBQ by tool", you have a pivot table A5:C93. Sorry, but I am not a
pivot table person. So I cannot determine the source of the pivot data.
In either case, A13:A25 are indeed text. But A5:A12 are numbers, __not__
text.
**********
(But I suspect the problem is __not__ with how the pivot table is defined
and created, but with the "random" mix of text and numbers in B3:B309 in
"Raw data".)
**********
Also, F5:F35 are text.
F36:F93 are numeric zeros. But that might be due to the fact that you "had
to strip out soooo much". So I will ignore F36:F93 and the VLOOKUP #N/A
errors in G36:G39, unless you tell us that you want to fix those as well.
(Easy to do.)
Aside.... C5:C25 are all numbers __except__ that C24 is text. Again, I
suspect that is due to the source of the pivot table ("Raw data"?).
**********
(But that has nothing to do with your problems with VLOOKUP.)
**********
puck1263 said:
Only thing I can think of is cells where I have a
formula to copy another cell. How do I tell the formula
to put the result as text?
The only formulas I see are the VLOOKUP formulas in G5:G93 in "IBQ by tool".
The formulas are of the form =VLOOKUP(F5,$A$5:$C$93,3). Explanation:
1. The formula looks up the value in F5 in the table A5:A93.
2. If an "approximate" match is found, VLOOKUP returns the corresponding
value from C5:C93.
Your problem with VLOOKUP has nothing to do with the type (text or number)
of the result (C5:C93).
Instead, your problem has to do with the mismatch of types between lookup
value in column F and lookup table in column A.
That is, column F is text (ignoring F36:F93), but column A is a mix of text
and numbers.
Ostensibly, the fix is in column A: it should be all text, since that seems
to be your intention.
But I presume that the mix of text and numbers in column A of "IBQ by tool"
is due to the mix of numbers in column B of "Raw data".
-----
So I believe the real fix lies in correcting the data in column B in "Raw
data".
That data are constants, not formulas.
But perhaps the root cause of the problem -- that is, the mix of text and
numbers in column B in "Raw data" -- is how you sourced that data.
It appears that you might have tried to remedy the problem by changing the
format of column B in "Raw data" to Text.
But if you do that after the fact -- after the data is entered or
imported -- that does not alter the numeric type of the data.
For example, if you enter the number 123 into a cell whose format is
General, then change the format to Text, the contents of the cell is still
numeric. That is, ISNUMBER(...) returns TRUE.
Ostensibly, you must "re-enter" the data, for example by pressing F2, then
Enter. But that must be done cell by cell.
The better way is to use the Text To Column feature. Be sure to press Next
until the last dialog box; and in the last dialog box, select Text for the
column format.
Alternatively, if you imported the data in column B in "Raw data", you can
set the column format to Text in the last dialog box of the import wizard.
For more specific help, you need to provide more details about the origin of
the data in column B in "Raw data" -- that is, how they became a mix of text
and numbers.
