Not enough columns

Posted by Ron Berger on January 25, 2002 11:17 AM

I need to create a look up matrix of 1000 columns wide by 1000 rows deep. Excel only goes to 256 columns. Does anyone have a solution. I'm guessing that there might be a lookup function that would allow the columns to extend to additional pages in a work book.
Has anyone run into this before?

Posted by Ian Bartlett on January 25, 2002 12:03 PM

How about:

Split your data over 4 sheets, 250 columns each.

In a 5th sheet, use 4 VLOOKUP/HLOOKUP formulae in cells A1 to A4, one looking at each matrix. In the lookup's Range Lookup field, enter FALSE.

3 of the formulae should return #N/A, 1 should give your result.

In a fifth cell, use something like
=IF(ISERROR(A1)=FALSE,A1,"") & IF(ISERROR(A2)=FALSE,A2,"") & IF(ISERROR(A3)=FALSE,A3,"") & IF(ISERROR(A4)=FALSE,A4,"")

The concatenated result should be your expected result.

HTH,

Ian

Posted by Jack in the UK on January 26, 2002 3:54 PM

Hi--

Excel is 65536 by 256columns! Well not quite it is possible to get excel to 638 columns under developemnet conditions, by 1,000,000 rows. But its nasty stuff

I would go Lutus to get the range you require, but ive never got on with lotus, it anoys me, but has 1,000,000 rows! and i dont get on with it no VBA its IBM code...........yuck!

Now to get your data i see this
1) stand the dat on its head ie row becone columns and columns rows ie sideways on.
2) split the data in a book ie part 1. part 2 and so on..

Excel i love but has limits like rows + columns... i would love to set any amount or R1C1 i wish but.................MSoft say no.

HTH
Jack