Vlookup in Array rather than looping

B

bayles

Board Regular
Joined
Oct 31, 2013
Messages
54
Hi all,

I am doing a vlookup on loop and it is taking a long time to cycle through 20,000 rows. I am new to arrays, dictionary etc so looking for some guidance on this one.

Much appreciated.

Here is the code that works fine albeit slow along with some sample data:

Sub test()

Dim rng As Range
Dim ws As Worksheet
Set ws = Sheets("BRANDS")
Set ws1 = Sheets("CHECKING")

Set rng = ws.Range(ws.Range("F2"), ws.Range("A1000000").End(xlUp).Offset(0, 5))
Set rng1 = Range(ws1.Range("F1"), ws1.Range("F1").End(xlDown))
rng.FormulaR1C1 = "=RC[-5]&RC[-2]"
rng.Value = rng.Value

For Each Cell In rng1

If Not Cell.Offset(0, 2) = 0 Or Not Cell.Offset(0, 3) = 0 Or Not Cell.Offset(0, 4) = 0 Then
Cell.Offset(0, 5) = "0"
Else
If IsError(Application.VLookup(Cell.Offset(0, -5) & Cell.Offset(0, -2), Worksheets("BRANDS").Range("F:F"), 1, False)) Then
Cell.Offset(0, 5) = "1"
Else
Cell.Offset(0, 5) = "0"
End If
End If
Next Cell

End Sub



ws sample data

BRANDMANUFACTUREDEPARTMENTCATEGORYRANK IN BRAND
0.05Other MfrHealth & BeautyAccessories0.05Health & Beauty1
0.05Other MfrGeneral MerchandiseAppliances0.05General Merchandise1
1 CalOther MfrBeveragesEnergy Drinks - On The Go1 CalBeverages1
10 Things I Hate About YouOther MfrGeneral MerchandiseDVDs10 Things I Hate About YouGeneral Merchandise1
100 PipersOther MfrLiquorSpirits, Mixers100 PipersLiquor1
1000 HourOther MfrHealth & BeautyCosmetics - Eyes1000 HourHealth & Beauty1
1000 HourOther MfrHealth & BeautyCosmetics - Nails1000 HourHealth & Beauty3
1000 HourOther MfrHealth & BeautyColourants1000 HourHealth & Beauty2
1000 HourOther MfrHealth & BeautyFirst Aid Footcare1000 HourHealth & Beauty4
1000 HourOther MfrGeneral MerchandiseToys1000 HourGeneral Merchandise1
100BonOther MfrHealth & BeautyFragrances100BonHealth & Beauty1
101 DalmationsOther MfrGeneral MerchandiseDVDs101 DalmationsGeneral Merchandise1
111skinOther MfrHealth & BeautyCrm/Lotn - Face Other111skinHealth & Beauty1
12 Days of ChristmasOther MfrSnackingBiscuit - Seasonal12 Days of ChristmasSnacking1
12 Days of ChristmasOther MfrSnackingConfect Seasonal12 Days of ChristmasSnacking2
12 Days of ChristmasOther MfrGeneral MerchandiseGifts12 Days of ChristmasGeneral Merchandise1
17 AgainOther MfrGeneral MerchandiseDVDs17 AgainGeneral Merchandise1
1800Other MfrLiquorSpirits, Mixers1800Liquor2
19 CrimesOther MfrLiquorWine19 CrimesLiquor2
1964Other MfrGeneral MerchandiseClothing1964General Merchandise1
1st YearsOther MfrBaby NeedsBaby Accessories1st YearsBaby Needs1
2 GunsOther MfrGeneral MerchandiseDVDs2 GunsGeneral Merchandise1
2012Other MfrGeneral MerchandiseDVDs2012General Merchandise1
20th Century Fox20th Century FoxGeneral MerchandiseDVDs20th Century FoxGeneral Merchandise1
21Other MfrGeneral MerchandiseDVDs21General Merchandise1
21 Jump StreetOther MfrGeneral MerchandiseDVDs21 Jump StreetGeneral Merchandise1
22 Jump StreetOther MfrGeneral MerchandiseDVDs22 Jump StreetGeneral Merchandise1
22cm Patterned PVC PlayballOther MfrGeneral MerchandiseToys22cm Patterned PVC PlayballGeneral Merchandise1
23rd Street GinOther MfrLiquorSpirits, Mixers23rd Street GinLiquor1
24 DailyPrivate LabelHealth & BeautyFirst Aid Footcare24 DailyHealth & Beauty1
24 DailyPrivate LabelHealth & BeautyMouth Wash24 DailyHealth & Beauty2
24 DailyPrivate LabelHealth & BeautyLiquid Soap24 DailyHealth & Beauty3
24 DailyPrivate LabelHealth & BeautyCrm/Lotn - Face Other24 DailyHealth & Beauty4
24 DailyPrivate LabelHealth & BeautyCotton Products24 DailyHealth & Beauty5
24 DailyPrivate LabelHealth & BeautyShamp, Cond & Treat24 DailyHealth & Beauty6
24 DailyPrivate LabelHealth & BeautyCrm/Lotn - Hnd / Bdy24 DailyHealth & Beauty7
24 DailyPrivate LabelBaby NeedsWipes24 DailyBaby Needs1
24 DailyPrivate LabelBaby NeedsBaby Accessories24 DailyBaby Needs2
24 DailyPrivate LabelHealth & BeautyShower Gel24 DailyHealth & Beauty8
24 DailyPrivate LabelHealth & BeautyToothbrush - Man24 DailyHealth & Beauty9
24 DailyPrivate LabelHealth & BeautyDental Floss24 DailyHealth & Beauty10
24 DailyPrivate LabelHealth & BeautyMale B&R24 DailyHealth & Beauty11
24 DailyPrivate LabelHealth & BeautyCrm/Lotn - Thpt Clns24 DailyHealth & Beauty12
24 DailyPrivate LabelHealth & BeautyToothbrush - Pwr24 DailyHealth & Beauty13
24 DailyPrivate LabelHealth & BeautyDepilatories24 DailyHealth & Beauty14
24 DailyPrivate LabelHealth & BeautyToothpaste24 DailyHealth & Beauty15
24 DailyPrivate LabelHealth & BeautyFemale B&R24 DailyHealth & Beauty16

<colgroup><col span="6"></colgroup><tbody>
</tbody>




Sample data for ws1

Alka-SeltzerAlka-SeltzerHealth & BeautyContainers5539Alka-SeltzerAlka-SeltzerHealth & BeautyContainers5001
Icy HotPrivate LabelHealth & BeautyFirst Aid Essential Oils/Lotions5548Icy HotPrivate LabelHealth & BeautyFirst Aid Essential Oils/Lotions5000
Signature HomePrivate LabelHousehold ProductsToilet Cleaners5577Signature HomePrivate LabelHousehold ProductsToilet Cleaners5000
Signature HomePrivate LabelGeneral MerchandiseHousehold Gloves5577Signature HomePrivate LabelGeneral MerchandiseHousehold Gloves5000
CrestProcter & GambleHealth & BeautyToothbrush - Pwr5578CrestProcter & GambleHealth & BeautyToothbrush - Pwr5000
SoleilOther MfrHealth & BeautyRazors & Blades5602SoleilOther MfrHealth & BeautyRazors & Blades5000
Irish SpringOther MfrHealth & BeautyLiquid Soap5609Irish SpringOther MfrHealth & BeautyLiquid Soap5000
PondsUnileverHealth & BeautyFacial Tissues5613PondsUnileverHealth & BeautyFacial Tissues5000
Just For MenOther MfrHealth & BeautyColourants5626Just For MenOther MfrHealth & BeautyColourants5000
Just For MenGlaxoSmithKlineHealth & BeautyToothpaste5662BioteneGlaxoSmithKlineHealth & BeautyToothpaste5000
Dentu-CrèmeGlaxoSmithKlineHealth & BeautyDenture Care5662Dentu-CrèmeGlaxoSmithKlineHealth & BeautyDenture Care5000
Love Beauty & PlanetUnileverHealth & BeautyCream / Lotion5707Love Beauty & PlanetUnileverHealth & BeautyCream / Lotion5000
MotrinOther MfrHealth & BeautyAnalgesics5711MotrinOther MfrHealth & BeautyAnalgesics5000
BengayOther MfrHealth & BeautyMedicinal5711BengayOther MfrHealth & BeautyMedicinal5000
One A DayBayerHealth & BeautyDigestive Remedies5716One A DayBayerHealth & BeautyDigestive Remedies5000
DoveOther MfrHealth & BeautyShamp, Cond & Treat5735DoveOther MfrHealth & BeautyShamp, Cond & Treat5000
AjaxColgate PalmoliveHousehold ProductsDishwashing - Manual5779AjaxColgate PalmoliveHousehold ProductsDishwashing - Manual5000
SuavitelColgate PalmoliveHousehold ProductsFabric Care5779SuavitelColgate PalmoliveHousehold ProductsFabric Care5000
FabulosoColgate PalmoliveHousehold ProductsFabric Care5779FabulosoColgate PalmoliveHousehold ProductsFabric Care5000
MurphysOther MfrHealth & BeautyLiquid Soap5779MurphysOther MfrHealth & BeautyLiquid Soap5000
TylenolJohnson & JohnsonHealth & BeautyMedicinal5809TylenolJohnson & JohnsonHealth & BeautyMedicinal5000
Simply SleepOther MfrHealth & BeautyVitamins5813Simply SleepOther MfrHealth & BeautyVitamins5000
Nature BountyOther MfrHealth & BeautyVitamins5844Nature BountyOther MfrHealth & BeautyVitamins5010
Top CareOther MfrHealth & BeautyDigestive Remedies5896Top CareOther MfrHealth & BeautyDigestive Remedies5000
DuoOther MfrHealth & BeautyCosmetics5902DuoOther MfrHealth & BeautyCosmetics5000
OGXOther MfrHealth & BeautyLiquid Soap5906OGXOther MfrHealth & BeautyLiquid Soap5000
Up & UpPrivate LabelHealth & BeautyFacial Tissues5930Up & UpPrivate LabelHealth & BeautyFacial Tissues5000
ViviscalOther Mfr.Health & BeautyShamp, Cond & Treat5953ViviscalOther Mfr.Health & BeautyShamp, Cond & Treat5000
ViviscalOther MfrHealth & BeautyVitamins5955ViviscalOther MfrHealth & BeautyVitamins5000
23andMeOther MfrHealth & BeautyMedicinal595923andMeOther MfrHealth & BeautyMedicinal5000
Lip SmackerOther MfrHealth & BeautyFabric Care5979Lip SmackerOther MfrHealth & BeautyCosmetics5000
ClaratinBayerHealth & BeautyUpper Respiratory Tract5992ClaratinBayerHealth & BeautyUpper Respiratory Tract5000
Bull FrogOther MfrHealth & BeautySun Care51002Bull FrogOther MfrHealth & BeautySun Care5000
ShopritePrivate LabelHealth & BeautyToothbrush - Man51004ShopritePrivate LabelHealth & BeautyToothbrush - Man5000
Every Man JackOther MfrHealth & BeautyShower Gel51008Every Man JackOther MfrHealth & BeautyShower Gel5000
MegaRedOther MfrHealth & BeautyVitamins51018MegaRedOther MfrHealth & BeautyVitamins5000
NiveaOther MfrHealth & BeautyMens Skin Care51021NiveaOther MfrHealth & BeautyMens Skin Care5000
No-AdOther MfrHealth & BeautySun Care51022No-AdOther MfrHealth & BeautySun Care5000

<colgroup><col width="64" span="10" style="width:48pt"> </colgroup><tbody>
</tbody>
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Sort by date Sort by votes
Update: I have inserted a concatenate into ws but would be happy to not have to use that method by just comparing values in columns A & D from ws to ws1.

Thanks
 
Upvote 0
How are you trying to compare the 2 sheets?
 
Upvote 0
Hi Norie,

I want to see if the data in ws1 is in ws.

ws = the database

ws1 = data we are working with and need to ensure we have not enteredany errors.

Concatenate of column 1 & column 4 in both sheets.
 
Upvote 0
Is it definitely columns 1 & 4 on each sheet?

When I look at your sample data those columns don't appear to hold similar data.
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question
Speed Up Formula for +3000 rows
Replies
5
Views
908
Fluff
Fluff

Forum statistics

Threads
1,215,206
Messages
6,123,636
Members
449,109
Latest member
Sebas8956

Share this page

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
Back
Top