100K plus lines of data, DOUBLE VLOOKUP help required.

[Noobllianz]

[TABLE="class: cms_table_cms_table_grid"]
<tbody>[TR]
[TD]
[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Customer Name[/TD]
[TD]Customer No#[/TD]
[TD]Order #[/TD]
[TD]Order Price[/TD]
[TD]Order Date[/TD]
[TD]Description[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]John Doe[/TD]
[TD]Cust001[/TD]
[TD]1000item[/TD]
[TD]$1000[/TD]
[TD]2011/01/01[/TD]
[TD]Laptop[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Jane Doe[/TD]
[TD]Cust002[/TD]
[TD]2000item[/TD]
[TD]$2000[/TD]
[TD]2012/01/01[/TD]
[TD]Monitor[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]John Smith[/TD]
[TD]Cust003[/TD]
[TD]3000item[/TD]
[TD]$3000[/TD]
[TD]2013/01/01[/TD]
[TD]Motherboard[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Jane Smith[/TD]
[TD]Cust004[/TD]
[TD]4000item[/TD]
[TD]$4000[/TD]
[TD]2014/01/01[/TD]
[TD]Keyboard[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Doe Smith[/TD]
[TD]Cust005[/TD]
[TD]5000item[/TD]
[TD]$5000[/TD]
[TD]2015/01/01[/TD]
[TD]Mouse[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]John Doe[/TD]
[TD]Cust001[/TD]
[TD]6000item[/TD]
[TD]$6000[/TD]
[TD]2016/01/01[/TD]
[TD]SSD[/TD]
[/TR]
</tbody>[/TABLE]
Hi Guys.

I was able to get advice on how to partial search Customer names/customer numbers in order to get their whole history displayed.

I used the formula to refer to a SUMPRODUCT cell next to the Search cell for reference.

=IF(ROWS(G$1:G1)>$B$7,"",INDEX(Sheet1!$Q$2:$W$1000000,AGGREGATE(15,6,(ROW($Q$2:$Q$1000000)-ROW(Sheet1!$A$2)+1)/ISNUMBER(SEARCH($A$7,$Q$2:



I have heard that Double vlookups will be able to search faster.

If I wish to apply the same ROWS, AGGREGATE, SEARCH with Double Vlookup, how can i formula this so that I can search for the Customer Name, and display all relevant information more quickly without lag.

Thanks for your help!

-B

 

[TheMathLab]

Hey B.

Could you not just format the data as a table and then filter?

 

[Noobllianz]

Hey B.

Could you not just format the data as a table and then filter?

Hi!
Thank you so much for the response.

This file is in a shared folder and is being updated constantly by a separate department.

We are unable to edit or amend the master workbook.


I I was thinking a initial partial search for a single column of info(client name) then adjacent rows can be pulled using an exact match. Just want to know if there is any way to incorporate the small/row function into the double vlookup formula.


I just cant work it out.

Thanks!

 

[offthelip]

What about loading the whole table into a variant array and doing the whole thing in VBA, it will be very fast.

 

[Noobllianz]

What about loading the whole table into a variant array and doing the whole thing in VBA, it will be very fast.

So basically, I can use my external workbook and use vba from within to access data from a different work book?

Does the variant array store the whole thing in vba?

I'm a bit confused.

Can you advise on what I need to study

Thanks

 

[offthelip]

Try reading this article which should get you started:
https://excelmacromastery.com/excel-vba-array/

Try googling "variant arrays Excel VBA" and see what else you can find

The answer to your questions is : Yes and Yes in possibly a number of variant arrays