Vlookup nightmare in large data set

zakynthos

Board Regular
Joined
Mar 28, 2011
Messages
169
Hi,

Columns E,F, G on a sheet called 'Briefings' contain the following formula:

=VLOOKUP($B4,'Peoplesoft Data'!$A:$C,3,FALSE)
=VLOOKUP($B4,'Peoplesoft Data'!$A:$D,4,FALSE)
=VLOOKUP($B4,'Peoplesoft Data'!$A:$E,5,FALSE)

Column K on this sheet has a conditional formula:

=IF(AND(B4='Merlin data'!C2,Briefings!I4='Merlin data'!M2,'Merlin data'!B2>0),1,0)

My problem is extremely slow/static running, I assume because of the extremely large data set being referenced on the sheets 'Peoplesoft Data', Merlin data etc


Data on Briefings is typically about 10,000 rows
Data on People Soft is typically about 20,000 rows
Data on Merln data sheet is 100,000 rows!!!

Ideally I would like to convert the 3 Vlookups and the conditional function above to VBA and if so, would this speed up the problem. Could they reference the data sets without the need to import them into the file first? The 3 files are:

Briefing ID Tracking.xlsx
Peoplesoft_Data_CS_and_Cons_Sales.csv
Merlin Data.csv


:confused:Any help would be most welcome - the file is now over 30 Mb after importing the data files and any amendment, save etc takes about 10 min to update or freezes - a nightmare!
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

TiaXL

Board Regular
Joined
Jun 17, 2011
Messages
124
I have had similar problems and yes converting it into VBA will remove the slow down. I did a workbooks_Open macro to fill the columns with the formula then copy>patespecial values over the top. It will run each time the file is opened so shouold always be up to date.

This removes the unresponsive issue.
 

zakynthos

Board Regular
Joined
Mar 28, 2011
Messages
169
Thanks for this.

I have also considered the approach you used and realise that would prevent the freezing but all those Vlookups etc referencing up to 100,000 rows will still take an unacceptably long time to update.

I'm using VBA (that I had help on in this forum) to import the data to avoid selecting/using the clipboard and that has worked fine considerably speeding up this part of the process

Are you able to give me the VBA for the Vllokups and conditional formulas I'm using as this is the main problem I've got to address?

Many thanks:)
 

TiaXL

Board Regular
Joined
Jun 17, 2011
Messages
124
Thanks for this.

I have also considered the approach you used and realise that would prevent the freezing but all those Vlookups etc referencing up to 100,000 rows will still take an unacceptably long time to update.

I'm using VBA (that I had help on in this forum) to import the data to avoid selecting/using the clipboard and that has worked fine considerably speeding up this part of the process

Are you able to give me the VBA for the Vllokups and conditional formulas I'm using as this is the main problem I've got to address?

Many thanks:)

I can give you a rough guide although a sample of your data would be good.

Code:
sub vlookups()
 
workbooks.open("Your workbook")
'open all the files you need for your data
 
windows("Main.xls").activate
'activate sheet where the formulas are required
 
range("b2").select
'change this to be the range where the formula should go
 
activecell.formula = "=vlookup(blabla your formula,1,false)"
activecell.offset(0,-1).select
selection.end(xldown).select
activecell.offset(0,1).select
range(selection, selection.end(xlup)).select
selection.filldown
selection.copy
selection.pastespecial xlpastevalues
 
end sub

that is a short example, post a copy of your spreadsheet and I can be more specific
 

zakynthos

Board Regular
Joined
Mar 28, 2011
Messages
169

ADVERTISEMENT

Thanks for that:

The Briefings sheet looks like this (formula in columns E-G and K already given):


-- removed inline image ---
 

zakynthos

Board Regular
Joined
Mar 28, 2011
Messages
169
Sorry, just checked and screen prints from workbook not visible - did try checking how to post sample spreadsheet but seems facility currently not available.

Briefings

B C D E F G .......K

<table border="0" cellpadding="0" cellspacing="0" width="64"><col width="64"><tr height="20"> <td class="xl65" style="height:15.0pt;width:48pt" height="20" width="64">700627156</td> </tr></table>
 

zakynthos

Board Regular
Joined
Mar 28, 2011
Messages
169

ADVERTISEMENT

Sorry, just checked and screen prints from workbook not visible - did try checking how to post sample spreadsheet but seems facility currently not available.

Briefings tab:

Column B 700627156
Col C; Ellie Pollard
Col D M7T52
Col E =VLOOKUP($B4,'Peoplesoft Data'!$A:$C,3,FALSE)

Col F =VLOOKUP($B4,'Peoplesoft Data'!$A:$D,4,FALSE)

Col G =VLOOKUP($B4,'Peoplesoft Data'!$A:$E,5,FALSE)
Col I 34943
Col K =IF(AND(B4='Merlin data'!C2,Briefings!I4='Merlin data'!M2,'Merlin data'!B2>0),1,0)

Merlin data tab:

Col A 600419745
Col B Blackburn
Col C Naureen Wilayat
Col M 34516

Peoplesoft data tab

Col A 60010645
Col B MYZLVF
Col C Y
Col D Rat Riches
Col E LANCASTER
 

zakynthos

Board Regular
Joined
Mar 28, 2011
Messages
169
Sorry!!,:eek: correction to Merlin data tab:

Col B: 1
Col C: 600419745
Col D: Blackburn
Col E: Naureen Wilayat
Col M: 34516
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,203
Is 'Peoplesoft Data'!$A:A sorted in ascending order? If not, could you arrange for that and keep it sorted?
 

Watch MrExcel Video

Forum statistics

Threads
1,130,219
Messages
5,640,957
Members
417,183
Latest member
CuteLeo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top