Re: Functions, Arrays and number/text stored as text

Tech-Archive recommends: Speed Up your PC by fixing your registry

Hi,

May I request you to mail me the workbook at ask@xxxxxxxxxxxxxxxx and explain the problem very clealry.

--
Regards,

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

"pepenacho" <pepenacho@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:16797DAA-0F79-4DA1-BF66-F4418A8DAD74@xxxxxxxxxxxxxxxx
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)

.

Relevant Pages

  • Re: Functions, Arrays and number/text stored as text
    ... Table1 is basically a database holding hundreds of lines. ... everything in Column-A was numbers stored as numbers. ... To do this I used mostly VLOOKUPS and ARRAYS. ... number (IF/MAX array), etc. ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Remove items from GetRows array
    ... > I am pulling data from 3 tables. ... > table1 holds item details ... > I'm looping through the array to find duplicate records in the data I ... > through and pull out a column value to concatenate with the first ...
    (microsoft.public.inetserver.asp.general)
  • Functions, Arrays and number/text stored as text
    ... I have a small array problem. ... Let's call them Table1 and Table2. ... Table1 is basically a database holding hundreds of lines. ... To do this I used mostly VLOOKUPS and ARRAYS. ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Functions, Arrays and number/text stored as text
    ... Let's call them Table1 and Table2. ... Table1 is basically a database holding hundreds of lines. ... To do this I used mostly VLOOKUPS and ARRAYS. ... number (IF/MAX array), etc. ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Getting Data From / Adding Data To Access DB Table
    ... Let's say I've created a table in an access database called Table1 ... array and an integer array. ... In short: Build a connection to the database, use an OleDbCommand and SQL select command to query the data, use a OleDbDatareader to read the data in a loop and put them into an array - but you should learn it from the start: ...
    (microsoft.public.dotnet.languages.vb)