how to handle Monster Excel data

[vaibhav_kurane007]

i am using formula excel was too slow what i do?


=IFERROR(IFERROR(IFERROR(IFERROR(VLOOKUP(E4996,DMA!$A$1:$D$10000,COLUMN(DMA!B1964),0),VLOOKUP(E4996,'DMA 2'!$A$1:$D$10000,COLUMN('DMA 2'!B1964),0)),(VLOOKUP(E4996,'DMA 3'!$A$1:$D$10000,COLUMN('DMA 3'!B1964),0))),(VLOOKUP(E4996,'DMA 4'!$A$1:$D$10000,COLUMN('DMA 4'!B1964),0))),VLOOKUP(E4996,'DMA 5'!$A$1:$D$10000,COLUMN('DMA 5'!B1964),0))

 

[jkpieterse]

Is your data really 10,000 rows? or are you referring to 10,000 rows so as not to miss any data? If so, consider converting your ranges to tables and only refer to the actual data.
In addition, having data spanning more than one worksheet often leads to problems like these. Better to have all data in a single table.

 

[vaibhav_kurane007]

Is your data really 10,000 rows? or are you referring to 10,000 rows so as not to miss any data? If so, consider converting your ranges to tables and only refer to the actual data.
In addition, having data spanning more than one worksheet often leads to problems like these. Better to have all data in a single table.

Not possible to add 5 sheet data on one table those are different data. At time only 10000 rows are actually used. So please get me short formula for Get 5 sheets data on one sheet.

 

[Artik]

Jan had something else in mind. References to ranges in other sheets work slower than references to ranges in the same sheet. That's why he suggests that the data be in one sheet, not one table.

Also, search formulas are unfortunately slow. Especially as they occur in large quantities.

Artik

 

[jkpieterse]

Perhaps this is faster?

=LET(data,VSTACK(DMA!$A$1:$D$10000,'DMA 2'!$A$1:$D$10000,'DMA 3'!$A$1:$D$10000,'DMA 4'!$A$1:$D$10000,'DMA 5'!$A$1:$D$10000),VLOOKUP(E4996,data,COLUMN('DMA 2'!B1964),0))