Vlookup nightmare in large data set

[zakynthos]

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


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!

 

[TiaXL]

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]

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]

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.

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]

Thanks for that:

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


-- removed inline image ---

 

[zakynthos]

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]

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

 

[SydneyGeek]

You may find that INDEX and MATCH will speed things up for you.
Take a look at this blog post.

Denis

 

[zakynthos]

Sorry!!, correction to Merlin data tab:

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

 

[Aladin Akyurek]

Is 'Peoplesoft Data'!$A:A sorted in ascending order? If not, could you arrange for that and keep it sorted?