Functions, Arrays and number/text stored as text
- From: pepenacho <pepenacho@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Tue, 22 Apr 2008 10:20:01 -0700
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)
.
- Follow-Ups:
- Re: Functions, Arrays and number/text stored as text
- From: Spiky
- Re: Functions, Arrays and number/text stored as text
- From: Ashish Mathur
- Re: Functions, Arrays and number/text stored as text
- From: Bob Phillips
- Re: Functions, Arrays and number/text stored as text
- Prev by Date: Re: Macro for Large workbook
- Next by Date: Filtering for more than 2+ "does contain" criteria
- Previous by thread: eliminating duplicate lines
- Next by thread: Re: Functions, Arrays and number/text stored as text
- Index(es):
Relevant Pages
|
