Functions, Arrays and number/text stored as text

[pepenacho]

I have a small array problem.

I have two tables in two XLS files. Let's call them Table1 and Table2.

Table1 is basically a database holding hundreds of lines. Its Column-A is
the key to everything. The records in this field are multilisted.

Until recently, everything in Column-A was numbers stored as numbers. Due to
a user request, now I have both numbers and letters mixed together, for
example, 123, 941B, 456L, 123, 7468, 999A, 456L, 941B. There is other useful
data in Column-B and beyond.

In Table2, I built a basic dashboard, whereby if I fill in a piece of data
that matches a piece of data in Column-A in Table1, I get back some analysis.
To do this I used mostly VLOOKUPS and ARRAYS. However, they were built to
take in a number value and not a Text/Number stored as a text.

For example, what's the first match look like in Table1 Colum-F (VLOOKUP),
how often does it occur (IF/COUNT array), when was the last entry for this
number (IF/MAX array), etc.

I fixed the VLOOKUPS using a CONCATENATE function that converted my input in
Table2 to a Text/Number stores as text, by adding an apostrophe to the input
field, then I re-referenced my VLOOKUPS.

I tried it on the array and it hates this. I tried searching for some other
function that would do the trick and can't find one.

Any ideas?

MRG (Pepe)

 

[Bob Phillips]

VLOOKUP should work fine with text/numbers.

What are your formulae that don't work, and what is the data like?

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Ashish Mathur]

Hi,

May I request you to mail me the workbook at (e-mail address removed) and
explain the problem very clealry.

--
Regards,

Ashish Mathur
www.ashishmathur.com
http://www.linkedin.com/in/excelenthusiasts

 

[Spiky]

I have a small array problem.

I have two tables in two XLS files. Let's call them Table1 and Table2.

Table1 is basically a database holding hundreds of lines. Its Column-A is
the key to everything. The records in this field are multilisted.

Until recently, everything in Column-A was numbers stored as numbers. Due to
a user request, now I have both numbers and letters mixed together, for
example, 123, 941B, 456L, 123, 7468, 999A, 456L, 941B. There is other useful
data in Column-B and beyond.

In Table2, I built a basic dashboard, whereby if I fill in a piece of data
that matches a piece of data in Column-A in Table1, I get back some analysis.
To do this I used mostly VLOOKUPS and ARRAYS. However, they were built to
take in a number value and not a Text/Number stored as a text.

For example, what's the first match look like in Table1 Colum-F (VLOOKUP),
how often does it occur (IF/COUNT array), when was the last entry for this
number (IF/MAX array), etc.

I fixed the VLOOKUPS using a CONCATENATE function that converted my input in
Table2 to a Text/Number stores as text, by adding an apostrophe to the input
field, then I re-referenced my VLOOKUPS.

I tried it on the array and it hates this. I tried searching for some other
function that would do the trick and can't find one.

Any ideas?

MRG (Pepe)

It sounds like you just need to perfect your data in Table 2 to match
Table 1. If you are using Concatenate just to get it in the proper
form, why can't you just have it entered in the proper form in the
first place?

If it's odd codes (you listed 123, 941B, 456L, etc), get the full list
of potential codes, stick it somewhere hidden in your file and use
data validation in your dashboard to make sure there are no errors in
entry. As mentioned, Vlookup should work on text/number combos no
problem.