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

[TABLE="width: 384"]
<colgroup><col span="6"></colgroup><tbody>[TR]
[TD]BRAND[/TD]
[TD]MANUFACTURE[/TD]
[TD]DEPARTMENT[/TD]
[TD]CATEGORY[/TD]
[TD="colspan: 2"]RANK IN BRAND[/TD]
[/TR]
[TR]
[TD]0.05[/TD]
[TD]Other Mfr[/TD]
[TD]Health & Beauty[/TD]
[TD]Accessories[/TD]
[TD="colspan: 2"]0.05Health & Beauty1[/TD]
[/TR]
[TR]
[TD]0.05[/TD]
[TD]Other Mfr[/TD]
[TD]General Merchandise[/TD]
[TD]Appliances[/TD]
[TD="colspan: 2"]0.05General Merchandise1[/TD]
[/TR]
[TR]
[TD]1 Cal[/TD]
[TD]Other Mfr[/TD]
[TD]Beverages[/TD]
[TD]Energy Drinks - On The Go[/TD]
[TD="colspan: 2"]1 CalBeverages1[/TD]
[/TR]
[TR]
[TD]10 Things I Hate About You[/TD]
[TD]Other Mfr[/TD]
[TD]General Merchandise[/TD]
[TD]DVDs[/TD]
[TD="colspan: 2"]10 Things I Hate About YouGeneral Merchandise1[/TD]
[/TR]
[TR]
[TD]100 Pipers[/TD]
[TD]Other Mfr[/TD]
[TD]Liquor[/TD]
[TD]Spirits, Mixers[/TD]
[TD="colspan: 2"]100 PipersLiquor1[/TD]
[/TR]
[TR]
[TD]1000 Hour[/TD]
[TD]Other Mfr[/TD]
[TD]Health & Beauty[/TD]
[TD]Cosmetics - Eyes[/TD]
[TD="colspan: 2"]1000 HourHealth & Beauty1[/TD]
[/TR]
[TR]
[TD]1000 Hour[/TD]
[TD]Other Mfr[/TD]
[TD]Health & Beauty[/TD]
[TD]Cosmetics - Nails[/TD]
[TD="colspan: 2"]1000 HourHealth & Beauty3[/TD]
[/TR]
[TR]
[TD]1000 Hour[/TD]
[TD]Other Mfr[/TD]
[TD]Health & Beauty[/TD]
[TD]Colourants[/TD]
[TD="colspan: 2"]1000 HourHealth & Beauty2[/TD]
[/TR]
[TR]
[TD]1000 Hour[/TD]
[TD]Other Mfr[/TD]
[TD]Health & Beauty[/TD]
[TD]First Aid Footcare[/TD]
[TD="colspan: 2"]1000 HourHealth & Beauty4[/TD]
[/TR]
[TR]
[TD]1000 Hour[/TD]
[TD]Other Mfr[/TD]
[TD]General Merchandise[/TD]
[TD]Toys[/TD]
[TD="colspan: 2"]1000 HourGeneral Merchandise1[/TD]
[/TR]
[TR]
[TD]100Bon[/TD]
[TD]Other Mfr[/TD]
[TD]Health & Beauty[/TD]
[TD]Fragrances[/TD]
[TD="colspan: 2"]100BonHealth & Beauty1[/TD]
[/TR]
[TR]
[TD]101 Dalmations[/TD]
[TD]Other Mfr[/TD]
[TD]General Merchandise[/TD]
[TD]DVDs[/TD]
[TD="colspan: 2"]101 DalmationsGeneral Merchandise1[/TD]
[/TR]
[TR]
[TD]111skin[/TD]
[TD]Other Mfr[/TD]
[TD]Health & Beauty[/TD]
[TD]Crm/Lotn - Face Other[/TD]
[TD="colspan: 2"]111skinHealth & Beauty1[/TD]
[/TR]
[TR]
[TD]12 Days of Christmas[/TD]
[TD]Other Mfr[/TD]
[TD]Snacking[/TD]
[TD]Biscuit - Seasonal[/TD]
[TD="colspan: 2"]12 Days of ChristmasSnacking1[/TD]
[/TR]
[TR]
[TD]12 Days of Christmas[/TD]
[TD]Other Mfr[/TD]
[TD]Snacking[/TD]
[TD]Confect Seasonal[/TD]
[TD="colspan: 2"]12 Days of ChristmasSnacking2[/TD]
[/TR]
[TR]
[TD]12 Days of Christmas[/TD]
[TD]Other Mfr[/TD]
[TD]General Merchandise[/TD]
[TD]Gifts[/TD]
[TD="colspan: 2"]12 Days of ChristmasGeneral Merchandise1[/TD]
[/TR]
[TR]
[TD]17 Again[/TD]
[TD]Other Mfr[/TD]
[TD]General Merchandise[/TD]
[TD]DVDs[/TD]
[TD="colspan: 2"]17 AgainGeneral Merchandise1[/TD]
[/TR]
[TR]
[TD]1800[/TD]
[TD]Other Mfr[/TD]
[TD]Liquor[/TD]
[TD]Spirits, Mixers[/TD]
[TD="colspan: 2"]1800Liquor2[/TD]
[/TR]
[TR]
[TD]19 Crimes[/TD]
[TD]Other Mfr[/TD]
[TD]Liquor[/TD]
[TD]Wine[/TD]
[TD="colspan: 2"]19 CrimesLiquor2[/TD]
[/TR]
[TR]
[TD]1964[/TD]
[TD]Other Mfr[/TD]
[TD]General Merchandise[/TD]
[TD]Clothing[/TD]
[TD="colspan: 2"]1964General Merchandise1[/TD]
[/TR]
[TR]
[TD]1st Years[/TD]
[TD]Other Mfr[/TD]
[TD]Baby Needs[/TD]
[TD]Baby Accessories[/TD]
[TD="colspan: 2"]1st YearsBaby Needs1[/TD]
[/TR]
[TR]
[TD]2 Guns[/TD]
[TD]Other Mfr[/TD]
[TD]General Merchandise[/TD]
[TD]DVDs[/TD]
[TD="colspan: 2"]2 GunsGeneral Merchandise1[/TD]
[/TR]
[TR]
[TD]2012[/TD]
[TD]Other Mfr[/TD]
[TD]General Merchandise[/TD]
[TD]DVDs[/TD]
[TD="colspan: 2"]2012General Merchandise1[/TD]
[/TR]
[TR]
[TD]20th Century Fox[/TD]
[TD]20th Century Fox[/TD]
[TD]General Merchandise[/TD]
[TD]DVDs[/TD]
[TD="colspan: 2"]20th Century FoxGeneral Merchandise1[/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD]Other Mfr[/TD]
[TD]General Merchandise[/TD]
[TD]DVDs[/TD]
[TD="colspan: 2"]21General Merchandise1[/TD]
[/TR]
[TR]
[TD]21 Jump Street[/TD]
[TD]Other Mfr[/TD]
[TD]General Merchandise[/TD]
[TD]DVDs[/TD]
[TD="colspan: 2"]21 Jump StreetGeneral Merchandise1[/TD]
[/TR]
[TR]
[TD]22 Jump Street[/TD]
[TD]Other Mfr[/TD]
[TD]General Merchandise[/TD]
[TD]DVDs[/TD]
[TD="colspan: 2"]22 Jump StreetGeneral Merchandise1[/TD]
[/TR]
[TR]
[TD]22cm Patterned PVC Playball[/TD]
[TD]Other Mfr[/TD]
[TD]General Merchandise[/TD]
[TD]Toys[/TD]
[TD="colspan: 2"]22cm Patterned PVC PlayballGeneral Merchandise1[/TD]
[/TR]
[TR]
[TD]23rd Street Gin[/TD]
[TD]Other Mfr[/TD]
[TD]Liquor[/TD]
[TD]Spirits, Mixers[/TD]
[TD="colspan: 2"]23rd Street GinLiquor1[/TD]
[/TR]
[TR]
[TD]24 Daily[/TD]
[TD]Private Label[/TD]
[TD]Health & Beauty[/TD]
[TD]First Aid Footcare[/TD]
[TD="colspan: 2"]24 DailyHealth & Beauty1[/TD]
[/TR]
[TR]
[TD]24 Daily[/TD]
[TD]Private Label[/TD]
[TD]Health & Beauty[/TD]
[TD]Mouth Wash[/TD]
[TD="colspan: 2"]24 DailyHealth & Beauty2[/TD]
[/TR]
[TR]
[TD]24 Daily[/TD]
[TD]Private Label[/TD]
[TD]Health & Beauty[/TD]
[TD]Liquid Soap[/TD]
[TD="colspan: 2"]24 DailyHealth & Beauty3[/TD]
[/TR]
[TR]
[TD]24 Daily[/TD]
[TD]Private Label[/TD]
[TD]Health & Beauty[/TD]
[TD]Crm/Lotn - Face Other[/TD]
[TD="colspan: 2"]24 DailyHealth & Beauty4[/TD]
[/TR]
[TR]
[TD]24 Daily[/TD]
[TD]Private Label[/TD]
[TD]Health & Beauty[/TD]
[TD]Cotton Products[/TD]
[TD="colspan: 2"]24 DailyHealth & Beauty5[/TD]
[/TR]
[TR]
[TD]24 Daily[/TD]
[TD]Private Label[/TD]
[TD]Health & Beauty[/TD]
[TD]Shamp, Cond & Treat[/TD]
[TD="colspan: 2"]24 DailyHealth & Beauty6[/TD]
[/TR]
[TR]
[TD]24 Daily[/TD]
[TD]Private Label[/TD]
[TD]Health & Beauty[/TD]
[TD]Crm/Lotn - Hnd / Bdy[/TD]
[TD="colspan: 2"]24 DailyHealth & Beauty7[/TD]
[/TR]
[TR]
[TD]24 Daily[/TD]
[TD]Private Label[/TD]
[TD]Baby Needs[/TD]
[TD]Wipes[/TD]
[TD="colspan: 2"]24 DailyBaby Needs1[/TD]
[/TR]
[TR]
[TD]24 Daily[/TD]
[TD]Private Label[/TD]
[TD]Baby Needs[/TD]
[TD]Baby Accessories[/TD]
[TD="colspan: 2"]24 DailyBaby Needs2[/TD]
[/TR]
[TR]
[TD]24 Daily[/TD]
[TD]Private Label[/TD]
[TD]Health & Beauty[/TD]
[TD]Shower Gel[/TD]
[TD="colspan: 2"]24 DailyHealth & Beauty8[/TD]
[/TR]
[TR]
[TD]24 Daily[/TD]
[TD]Private Label[/TD]
[TD]Health & Beauty[/TD]
[TD]Toothbrush - Man[/TD]
[TD="colspan: 2"]24 DailyHealth & Beauty9[/TD]
[/TR]
[TR]
[TD]24 Daily[/TD]
[TD]Private Label[/TD]
[TD]Health & Beauty[/TD]
[TD]Dental Floss[/TD]
[TD="colspan: 2"]24 DailyHealth & Beauty10[/TD]
[/TR]
[TR]
[TD]24 Daily[/TD]
[TD]Private Label[/TD]
[TD]Health & Beauty[/TD]
[TD]Male B&R[/TD]
[TD="colspan: 2"]24 DailyHealth & Beauty11[/TD]
[/TR]
[TR]
[TD]24 Daily[/TD]
[TD]Private Label[/TD]
[TD]Health & Beauty[/TD]
[TD]Crm/Lotn - Thpt Clns[/TD]
[TD="colspan: 2"]24 DailyHealth & Beauty12[/TD]
[/TR]
[TR]
[TD]24 Daily[/TD]
[TD]Private Label[/TD]
[TD]Health & Beauty[/TD]
[TD]Toothbrush - Pwr[/TD]
[TD="colspan: 2"]24 DailyHealth & Beauty13[/TD]
[/TR]
[TR]
[TD]24 Daily[/TD]
[TD]Private Label[/TD]
[TD]Health & Beauty[/TD]
[TD]Depilatories[/TD]
[TD="colspan: 2"]24 DailyHealth & Beauty14[/TD]
[/TR]
[TR]
[TD]24 Daily[/TD]
[TD]Private Label[/TD]
[TD]Health & Beauty[/TD]
[TD]Toothpaste[/TD]
[TD="colspan: 2"]24 DailyHealth & Beauty15[/TD]
[/TR]
[TR]
[TD]24 Daily[/TD]
[TD]Private Label[/TD]
[TD]Health & Beauty[/TD]
[TD]Female B&R[/TD]
[TD="colspan: 2"]24 DailyHealth & Beauty16
[/TD]
[/TR]
</tbody>[/TABLE]




Sample data for ws1

[TABLE="width: 640"]
<colgroup><col width="64" span="10" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]Alka-Seltzer[/TD]
[TD="class: xl65, width: 64"]Alka-Seltzer[/TD]
[TD="class: xl65, width: 64"]Health & Beauty[/TD]
[TD="class: xl65, width: 64"]Containers[/TD]
[TD="class: xl65, width: 64, align: right"]5[/TD]
[TD="class: xl65, width: 64, align: right"]539[/TD]
[TD="class: xl65, width: 64"]Alka-SeltzerAlka-SeltzerHealth & BeautyContainers5[/TD]
[TD="class: xl65, width: 64, align: right"]0[/TD]
[TD="class: xl65, width: 64, align: right"]0[/TD]
[TD="class: xl65, width: 64, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl65"]Icy Hot[/TD]
[TD="class: xl65"]Private Label[/TD]
[TD="class: xl65"]Health & Beauty[/TD]
[TD="class: xl65"]First Aid Essential Oils/Lotions[/TD]
[TD="class: xl65, align: right"]5[/TD]
[TD="class: xl65, align: right"]548[/TD]
[TD="class: xl65"]Icy HotPrivate LabelHealth & BeautyFirst Aid Essential Oils/Lotions5[/TD]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl65, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl65"]Signature Home[/TD]
[TD="class: xl65"]Private Label[/TD]
[TD="class: xl65"]Household Products[/TD]
[TD="class: xl65"]Toilet Cleaners[/TD]
[TD="class: xl65, align: right"]5[/TD]
[TD="class: xl65, align: right"]577[/TD]
[TD="class: xl65"]Signature HomePrivate LabelHousehold ProductsToilet Cleaners5[/TD]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl65, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl65"]Signature Home[/TD]
[TD="class: xl65"]Private Label[/TD]
[TD="class: xl65"]General Merchandise[/TD]
[TD="class: xl65"]Household Gloves[/TD]
[TD="class: xl65, align: right"]5[/TD]
[TD="class: xl65, align: right"]577[/TD]
[TD="class: xl65"]Signature HomePrivate LabelGeneral MerchandiseHousehold Gloves5[/TD]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl65, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl65"]Crest[/TD]
[TD="class: xl65"]Procter & Gamble[/TD]
[TD="class: xl65"]Health & Beauty[/TD]
[TD="class: xl65"]Toothbrush - Pwr[/TD]
[TD="class: xl65, align: right"]5[/TD]
[TD="class: xl65, align: right"]578[/TD]
[TD="class: xl65"]CrestProcter & GambleHealth & BeautyToothbrush - Pwr5[/TD]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl65, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl65"]Soleil[/TD]
[TD="class: xl65"]Other Mfr[/TD]
[TD="class: xl65"]Health & Beauty[/TD]
[TD="class: xl65"]Razors & Blades[/TD]
[TD="class: xl65, align: right"]5[/TD]
[TD="class: xl65, align: right"]602[/TD]
[TD="class: xl65"]SoleilOther MfrHealth & BeautyRazors & Blades5[/TD]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl65, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl65"]Irish Spring[/TD]
[TD="class: xl65"]Other Mfr[/TD]
[TD="class: xl65"]Health & Beauty[/TD]
[TD="class: xl65"]Liquid Soap[/TD]
[TD="class: xl65, align: right"]5[/TD]
[TD="class: xl65, align: right"]609[/TD]
[TD="class: xl65"]Irish SpringOther MfrHealth & BeautyLiquid Soap5[/TD]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl65, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl65"]Ponds[/TD]
[TD="class: xl65"]Unilever[/TD]
[TD="class: xl65"]Health & Beauty[/TD]
[TD="class: xl65"]Facial Tissues[/TD]
[TD="class: xl65, align: right"]5[/TD]
[TD="class: xl65, align: right"]613[/TD]
[TD="class: xl65"]PondsUnileverHealth & BeautyFacial Tissues5[/TD]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl65, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl65"]Just For Men[/TD]
[TD="class: xl65"]Other Mfr[/TD]
[TD="class: xl65"]Health & Beauty[/TD]
[TD="class: xl65"]Colourants[/TD]
[TD="class: xl65, align: right"]5[/TD]
[TD="class: xl65, align: right"]626[/TD]
[TD="class: xl65"]Just For MenOther MfrHealth & BeautyColourants5[/TD]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl65, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl65"]Just For Men[/TD]
[TD="class: xl65"]GlaxoSmithKline[/TD]
[TD="class: xl65"]Health & Beauty[/TD]
[TD="class: xl65"]Toothpaste[/TD]
[TD="class: xl65, align: right"]5[/TD]
[TD="class: xl65, align: right"]662[/TD]
[TD="class: xl65"]BioteneGlaxoSmithKlineHealth & BeautyToothpaste5[/TD]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl65, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl65"]Dentu-Crème[/TD]
[TD="class: xl65"]GlaxoSmithKline[/TD]
[TD="class: xl65"]Health & Beauty[/TD]
[TD="class: xl65"]Denture Care[/TD]
[TD="class: xl65, align: right"]5[/TD]
[TD="class: xl65, align: right"]662[/TD]
[TD="class: xl65"]Dentu-CrèmeGlaxoSmithKlineHealth & BeautyDenture Care5[/TD]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl65, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl65"]Love Beauty & Planet[/TD]
[TD="class: xl65"]Unilever[/TD]
[TD="class: xl65"]Health & Beauty[/TD]
[TD="class: xl65"]Cream / Lotion[/TD]
[TD="class: xl65, align: right"]5[/TD]
[TD="class: xl65, align: right"]707[/TD]
[TD="class: xl65"]Love Beauty & PlanetUnileverHealth & BeautyCream / Lotion5[/TD]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl65, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl65"]Motrin[/TD]
[TD="class: xl65"]Other Mfr[/TD]
[TD="class: xl65"]Health & Beauty[/TD]
[TD="class: xl65"]Analgesics[/TD]
[TD="class: xl65, align: right"]5[/TD]
[TD="class: xl65, align: right"]711[/TD]
[TD="class: xl65"]MotrinOther MfrHealth & BeautyAnalgesics5[/TD]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl65, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl65"]Bengay[/TD]
[TD="class: xl65"]Other Mfr[/TD]
[TD="class: xl65"]Health & Beauty[/TD]
[TD="class: xl65"]Medicinal[/TD]
[TD="class: xl65, align: right"]5[/TD]
[TD="class: xl65, align: right"]711[/TD]
[TD="class: xl65"]BengayOther MfrHealth & BeautyMedicinal5[/TD]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl65, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl65"]One A Day[/TD]
[TD="class: xl65"]Bayer[/TD]
[TD="class: xl65"]Health & Beauty[/TD]
[TD="class: xl65"]Digestive Remedies[/TD]
[TD="class: xl65, align: right"]5[/TD]
[TD="class: xl65, align: right"]716[/TD]
[TD="class: xl65"]One A DayBayerHealth & BeautyDigestive Remedies5[/TD]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl65, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl65"]Dove[/TD]
[TD="class: xl65"]Other Mfr[/TD]
[TD="class: xl65"]Health & Beauty[/TD]
[TD="class: xl65"]Shamp, Cond & Treat[/TD]
[TD="class: xl65, align: right"]5[/TD]
[TD="class: xl65, align: right"]735[/TD]
[TD="class: xl65"]DoveOther MfrHealth & BeautyShamp, Cond & Treat5[/TD]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl65, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl65"]Ajax[/TD]
[TD="class: xl65"]Colgate Palmolive[/TD]
[TD="class: xl65"]Household Products[/TD]
[TD="class: xl65"]Dishwashing - Manual[/TD]
[TD="class: xl65, align: right"]5[/TD]
[TD="class: xl65, align: right"]779[/TD]
[TD="class: xl65"]AjaxColgate PalmoliveHousehold ProductsDishwashing - Manual5[/TD]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl65, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl65"]Suavitel[/TD]
[TD="class: xl65"]Colgate Palmolive[/TD]
[TD="class: xl65"]Household Products[/TD]
[TD="class: xl65"]Fabric Care[/TD]
[TD="class: xl65, align: right"]5[/TD]
[TD="class: xl65, align: right"]779[/TD]
[TD="class: xl65"]SuavitelColgate PalmoliveHousehold ProductsFabric Care5[/TD]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl65, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl65"]Fabuloso[/TD]
[TD="class: xl65"]Colgate Palmolive[/TD]
[TD="class: xl65"]Household Products[/TD]
[TD="class: xl65"]Fabric Care[/TD]
[TD="class: xl65, align: right"]5[/TD]
[TD="class: xl65, align: right"]779[/TD]
[TD="class: xl65"]FabulosoColgate PalmoliveHousehold ProductsFabric Care5[/TD]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl65, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl65"]Murphys[/TD]
[TD="class: xl65"]Other Mfr[/TD]
[TD="class: xl65"]Health & Beauty[/TD]
[TD="class: xl65"]Liquid Soap[/TD]
[TD="class: xl65, align: right"]5[/TD]
[TD="class: xl65, align: right"]779[/TD]
[TD="class: xl65"]MurphysOther MfrHealth & BeautyLiquid Soap5[/TD]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl65, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl65"]Tylenol[/TD]
[TD="class: xl65"]Johnson & Johnson[/TD]
[TD="class: xl65"]Health & Beauty[/TD]
[TD="class: xl65"]Medicinal[/TD]
[TD="class: xl65, align: right"]5[/TD]
[TD="class: xl65, align: right"]809[/TD]
[TD="class: xl65"]TylenolJohnson & JohnsonHealth & BeautyMedicinal5[/TD]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl65, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl65"]Simply Sleep[/TD]
[TD="class: xl65"]Other Mfr[/TD]
[TD="class: xl65"]Health & Beauty[/TD]
[TD="class: xl65"]Vitamins[/TD]
[TD="class: xl65, align: right"]5[/TD]
[TD="class: xl65, align: right"]813[/TD]
[TD="class: xl65"]Simply SleepOther MfrHealth & BeautyVitamins5[/TD]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl65, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl65"]Nature Bounty[/TD]
[TD="class: xl65"]Other Mfr[/TD]
[TD="class: xl65"]Health & Beauty[/TD]
[TD="class: xl65"]Vitamins[/TD]
[TD="class: xl65, align: right"]5[/TD]
[TD="class: xl65, align: right"]844[/TD]
[TD="class: xl65"]Nature BountyOther MfrHealth & BeautyVitamins5[/TD]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl65, align: right"]1[/TD]
[TD="class: xl65, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl65"]Top Care[/TD]
[TD="class: xl65"]Other Mfr[/TD]
[TD="class: xl65"]Health & Beauty[/TD]
[TD="class: xl65"]Digestive Remedies[/TD]
[TD="class: xl65, align: right"]5[/TD]
[TD="class: xl65, align: right"]896[/TD]
[TD="class: xl65"]Top CareOther MfrHealth & BeautyDigestive Remedies5[/TD]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl65, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl65"]Duo[/TD]
[TD="class: xl65"]Other Mfr[/TD]
[TD="class: xl65"]Health & Beauty[/TD]
[TD="class: xl65"]Cosmetics[/TD]
[TD="class: xl65, align: right"]5[/TD]
[TD="class: xl65, align: right"]902[/TD]
[TD="class: xl65"]DuoOther MfrHealth & BeautyCosmetics5[/TD]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl65, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl65"]OGX[/TD]
[TD="class: xl65"]Other Mfr[/TD]
[TD="class: xl65"]Health & Beauty[/TD]
[TD="class: xl65"]Liquid Soap[/TD]
[TD="class: xl65, align: right"]5[/TD]
[TD="class: xl65, align: right"]906[/TD]
[TD="class: xl65"]OGXOther MfrHealth & BeautyLiquid Soap5[/TD]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl65, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl65"]Up & Up[/TD]
[TD="class: xl65"]Private Label[/TD]
[TD="class: xl65"]Health & Beauty[/TD]
[TD="class: xl65"]Facial Tissues[/TD]
[TD="class: xl65, align: right"]5[/TD]
[TD="class: xl65, align: right"]930[/TD]
[TD="class: xl65"]Up & UpPrivate LabelHealth & BeautyFacial Tissues5[/TD]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl65, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl65"]Viviscal[/TD]
[TD="class: xl65"]Other Mfr.[/TD]
[TD="class: xl65"]Health & Beauty[/TD]
[TD="class: xl65"]Shamp, Cond & Treat[/TD]
[TD="class: xl65, align: right"]5[/TD]
[TD="class: xl65, align: right"]953[/TD]
[TD="class: xl65"]ViviscalOther Mfr.Health & BeautyShamp, Cond & Treat5[/TD]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl65, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl65"]Viviscal[/TD]
[TD="class: xl65"]Other Mfr[/TD]
[TD="class: xl65"]Health & Beauty[/TD]
[TD="class: xl65"]Vitamins[/TD]
[TD="class: xl65, align: right"]5[/TD]
[TD="class: xl65, align: right"]955[/TD]
[TD="class: xl65"]ViviscalOther MfrHealth & BeautyVitamins5[/TD]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl65, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl65"]23andMe[/TD]
[TD="class: xl65"]Other Mfr[/TD]
[TD="class: xl65"]Health & Beauty[/TD]
[TD="class: xl65"]Medicinal[/TD]
[TD="class: xl65, align: right"]5[/TD]
[TD="class: xl65, align: right"]959[/TD]
[TD="class: xl65"]23andMeOther MfrHealth & BeautyMedicinal5[/TD]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl65, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl65"]Lip Smacker[/TD]
[TD="class: xl65"]Other Mfr[/TD]
[TD="class: xl65"]Health & Beauty[/TD]
[TD="class: xl65"]Fabric Care[/TD]
[TD="class: xl65, align: right"]5[/TD]
[TD="class: xl65, align: right"]979[/TD]
[TD="class: xl65"]Lip SmackerOther MfrHealth & BeautyCosmetics5[/TD]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl65, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl65"]Claratin[/TD]
[TD="class: xl65"]Bayer[/TD]
[TD="class: xl65"]Health & Beauty[/TD]
[TD="class: xl65"]Upper Respiratory Tract[/TD]
[TD="class: xl65, align: right"]5[/TD]
[TD="class: xl65, align: right"]992[/TD]
[TD="class: xl65"]ClaratinBayerHealth & BeautyUpper Respiratory Tract5[/TD]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl65, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl65"]Bull Frog[/TD]
[TD="class: xl65"]Other Mfr[/TD]
[TD="class: xl65"]Health & Beauty[/TD]
[TD="class: xl65"]Sun Care[/TD]
[TD="class: xl65, align: right"]5[/TD]
[TD="class: xl65, align: right"]1002[/TD]
[TD="class: xl65"]Bull FrogOther MfrHealth & BeautySun Care5[/TD]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl65, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl65"]Shoprite[/TD]
[TD="class: xl65"]Private Label[/TD]
[TD="class: xl65"]Health & Beauty[/TD]
[TD="class: xl65"]Toothbrush - Man[/TD]
[TD="class: xl65, align: right"]5[/TD]
[TD="class: xl65, align: right"]1004[/TD]
[TD="class: xl65"]ShopritePrivate LabelHealth & BeautyToothbrush - Man5[/TD]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl65, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl65"]Every Man Jack[/TD]
[TD="class: xl65"]Other Mfr[/TD]
[TD="class: xl65"]Health & Beauty[/TD]
[TD="class: xl65"]Shower Gel[/TD]
[TD="class: xl65, align: right"]5[/TD]
[TD="class: xl65, align: right"]1008[/TD]
[TD="class: xl65"]Every Man JackOther MfrHealth & BeautyShower Gel5[/TD]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl65, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl65"]MegaRed[/TD]
[TD="class: xl65"]Other Mfr[/TD]
[TD="class: xl65"]Health & Beauty[/TD]
[TD="class: xl65"]Vitamins[/TD]
[TD="class: xl65, align: right"]5[/TD]
[TD="class: xl65, align: right"]1018[/TD]
[TD="class: xl65"]MegaRedOther MfrHealth & BeautyVitamins5[/TD]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl65, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl65"]Nivea[/TD]
[TD="class: xl65"]Other Mfr[/TD]
[TD="class: xl65"]Health & Beauty[/TD]
[TD="class: xl65"]Mens Skin Care[/TD]
[TD="class: xl65, align: right"]5[/TD]
[TD="class: xl65, align: right"]1021[/TD]
[TD="class: xl65"]NiveaOther MfrHealth & BeautyMens Skin Care5[/TD]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl65, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl65"]No-Ad[/TD]
[TD="class: xl65"]Other Mfr[/TD]
[TD="class: xl65"]Health & Beauty[/TD]
[TD="class: xl65"]Sun Care[/TD]
[TD="class: xl65, align: right"]5[/TD]
[TD="class: xl65, align: right"]1022[/TD]
[TD="class: xl65"]No-AdOther MfrHealth & BeautySun Care5[/TD]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl65, align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
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 Question
Speed Up Formula for +3000 rows
Replies
5
Views
1K
Fluff
Fluff

Forum statistics

Threads
1,223,885
Messages
6,175,180
Members
452,615
Latest member
bogeys2birdies

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