vba Find matching value from one sheet to another

[bluefeather8989]

I have 2 worksheets in the same workbook (List, Data). In the data sheet I have a small list of items with the a number next to it.. I need a marco to input the numbers from data sheet to the corresponding items in List sheet.
Example: Data sheet 6 tomatos will input the number 6 next to tomatos in List sheet

Data

Shopping list V.2020.xlsx
	P	Q
2	6	Tomatoes
3	1	Bread white **
4	1	Hamburger buns
5	1	Hawaii buns
6	1	Hotdog buns
7	1	Bagels
8	6	Coffee creamer
9	1	Beef broth
10	3	Ragu
11	2	Spaghetti 1lb pkg
12	1	Spaghetti (Twist)
13	1	Pecans 2lb bag
14	1	Mountain Dew
15	12	Bai drink
16	4	Potato chips
17	2	Ham Steaks
18	2	Polish sausage
19	1	Loin Country Style Ribs (Pork)
20	1	Cubed beef
21	1	Roast
22	1	Corn on cob
Data

.
List

Shopping list V.2020.xlsx
	C	D
3		Aspirin for heart
4		Aunt Jemima syrup
5		Axe 2 in 1 shampoo
6		Bacon
7		Bagels
8		Bai drink
9		Beef broth
10		Black trash bags
11		Bounce dryer sheets
12		Bread wheat
13		Bread white **
14		Brown gravy mix
15		Brown rice
16		Butter
17		Butter (Spray) **
18		Butterscotch (Werthers original)
19		Carpet fresh powder
20		Carrots
21		Cascade Platinum
22		Cascade power dry (Dish Washer)
23		Cat food
24		Cat treats
25		Celery
26		Charmin ultra soft TP
27		Cheese Velveeta slices
28		Cheese White
29		Cheese yellow
30		Chicken Breast (not frozen)
31		Chicken broth
32		Chicken legs (not frozen)
33		Chicken Thighs (not frozen)
34		Chili
35		Club *******s
36		Coffee creamer
37		Coffee Folgers classic roast
38		Cooking Spray butter
39		Cool whip
40		Corn
41		Corn chips
42		Corn on cob
43		Cream cheese Philadelphia
44		Cream of celery
45		Cream of chicken
46		Creamed corn
47		Crisco cooking oil
48		Cubed beef
49		Dawn dish soap large
50		Dental floss picks
51		Diced tomatoes
52		Downey softener
53		Eggs
54		Febreze
55		Flower 5lb bag
56		French fried onions
57		French fries
58		Frozen Broccoli
59		Frozen green beans
60		Frozen Lima beans
61		Garlic croutons
62		Gorton breaded fish 26pkg.
63		Green/yellow sponges
64		Ham Steaks
65		Hamburger 12 pkg
66		Hamburger 1lb pkg
67		Hamburger buns
68		Hawaii buns
69		Hines 57
70		Hotdog buns
71		Hotdogs
72		Hydrogen peroxide
73		Italian green beans
74		Jell-O
75		Jimmy Dean sausage
76		ketchup 2pkg
77		Kidney beans ( dark red)
78		Kitty litter
79		Kleenex 3 pkg
80		Kraft Mac n cheese 5pkg.
81		Lasagna
82		Laundry soap Gain lavender
83		Lemon juice
84		Lipton tea bags decaf
85		Liquid hand soap
86		Loin Country Style Ribs (Pork)
87		Magic eraser wipes
88		Mandarin oranges
89		Milk
90		Mountain Dew
91		Mt. Olive dill pickles (long)
92		Mustard
93		Napkins (Vanity fair)
94		Neosporin with pain relief
95		Noodles
96		NyQuil green
97		Oatmeal Quaker Oats 1 min.
98		Olay soap
99		Olive oil extra virgin
100		Onion
101		Onion powder
102		Pace chunky salsa
103		Pancake mix
104		Pantene conditioner
105		Paper plates
106		Parmigiana cheese
107		Peanut butter
108		Pecans 2lb bag
109		Pepper McCormick's
110		Pepsi
111		Pine sol
112		Polish sausage
113		Popcorn kettle corn
114		Potato chips
115		Potato sticks
116		Potatoes
117		Puffs 3pkg
118		Quaker oatmeal 1minute
119		Ragu
120		Relish
121		Reynolds aluminum foil
122		Roast
123		Salad dressing (Italian)
124		Salt (Iodized)
125		Sensodyne
126		Shredded lettuce
127		Shredded mini wheats
128		Silver floss shredded Sauerkraut
129		Slab Pork Ribs
130		Soup (Chicken noodle)
131		Soup (Tomato)
132		Spaghetti (Twist)
133		Spaghetti 1lb pkg
134		Sugar 5lb bag
135		Sunflower seeds
136		Sweet n low
137		Tacos shells (Hard)
138		Tacos shells (soft)
139		Tartar sauce
140		Tater tots
141		Tide liquid
142		Tomato sauce
143		Tomatoes
144		Tooth picks
145		Toothpaste Sensodyne
146		Toothpaste Ultra bright
147		Velveeta shells n Cheese 3pkg.
148		Vinegar gal
149		Weber's Chicago steak seasoning
150		Welches grape jelly
151		Worcestershire sauce
152		Zip lock bags 1 qtr.
153		Zip lock bags 1gal
List

Cell Formulas
Range	Formula
D3:D153	D3	=IF(Data!D3="","ZZ",Data!D3)

 

[mumps]

Try:

Sub CopyData()
    Application.ScreenUpdating = False
    Dim Arr As Variant, i As Long, srcRng As Range, x As Long, srcWS As Worksheet, desWS As Worksheet
    Set srcWS = Sheets("Data")
    Set desWS = Sheets("List")
    lRow = desWS.Range("D" & Rows.Count).End(xlUp).Row
    lRow2 = srcWS.Range("P" & Rows.Count).End(xlUp).Row
    Set srcRng = srcWS.Range("Q2:Q" & lRow2)
    Arr = desWS.Range("D2:D" & lRow).Value
    For i = LBound(Arr) To UBound(Arr)
        If Not IsError(Application.Match(Arr(i, 1), srcRng, 0)) Then
            x = Application.Match(Arr(i, 1), srcRng, 0)
            desWS.Range("C" & i + 1) = srcWS.Range("P" & x + 1)
        End If
    Next i
    Application.ScreenUpdating = True
End Sub

 

[Marc L]

As it can be obviously achieved directly with a beginner level formula but if a VBA procedure is really necessary so just using the same formula :​

Sub Demo1()
    With Sheets("List").Range("C3", [List!D3].End(xlDown)(1, 0))
        .Formula = Replace("=IFERROR(INDEX(Data!$P$2:$P$#,MATCH(D3,Data!$Q$2:$Q$#,0)),"""")", "#", [Data!Q2].End(xlDown).Row)
        .Formula = .Value2
    End With
End Sub

 

[Marc L]

​

mumps,​

​

to avoid a duplicate MATCH calculation it should be obvious to place the codeline x = before the codeline If Not IsError​

just with the variable x declared as Variant …​

 

[mumps]

@Marc L
Thank you for that. I just thought that given the type of posted data, there would be no duplicate matches.