data with more than 65536 lines
 

Dear,

I have an excel file with tons of data, multiple sheets (~63MB in size). I
want to compile it into one sheet and make use of vlookup. But I encounter
the limit which one sheet cannot exceed 65536 lines...appreciate any
suggestions ^_^

data with more than 65536 lines
 

If your lookup table has to be split onto two sheets, you can use
something like this:

=IF(ISNA(vlookup1),IF(ISNA(vlookup2),"not present",vlookup2),vlookup1)

where vlookup1 is looking at the first sheet - like VLOOKUP(A1,Sheet1!
A:B,2,0) - and vlookup2 is looking at the second sheet.

Hope this helps.

Pete

On Dec 22, 8:23*am, Sephiroth
wrote:
Dear,

I have an excel file with tons of data, multiple sheets (~63MB in size). I
want to compile it into one sheet and make use of vlookup. But I encounter
the limit which one sheet cannot exceed 65536 lines...appreciate any
suggestions *^_^

data with more than 65536 lines
 

Sephiroth;156174 Wrote:
Dear,

I have an excel file with tons of data, multiple sheets (~63MB in
size). I
want to compile it into one sheet and make use of vlookup. But I
encounter
the limit which one sheet cannot exceed 65536 lines...appreciate any
suggestions ^_^

You can also enter your data in Access and use XL as front end to
analyze the data


--
Pecoflyer

Cheers - MS Excel Newbie
------------------------------------------------------------------------
Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=43311

data with more than 65536 lines
 

Sephiroth wrote:
Dear,

I have an excel file with tons of data, multiple sheets (~63MB in size). I
want to compile it into one sheet and make use of vlookup. But I encounter
the limit which one sheet cannot exceed 65536 lines...appreciate any
suggestions ^_^

Excel 2007 allows up to 1 million lines.

Personally, I like the Access front end approach suggested by another
poster.

Bill