Macro Lookup

Ravin

Board Regular
Joined
Aug 24, 2012
Messages
67
I need some help on the best way to action this

I have a list of IDs in a single column A in excel, the number of IDs will vary in length over time (tab called ID)

e.g
100
200
300

I need to lookup those IDs against another sheet that contains 100k rows (tab called Lookup)

the lookup result is always in column H which is 5 columns away from the lookup row the lookup start is Column D

the challenge is the original ID will have multiple results

i.e.
ID Result
100 A
100 B
100 C
200 G
300 H

and I need all the results in one column, next to original ID as above called output A

then I need to take all those result references and lookup against another sheet in excel (Stock)

the result lookup will be in column A in that sheet

but this time I dont want to see the reference result if it doesnt appear on stock sheet via the lookup
and if it does appear on sheet only if the data in column H= FALSE
and finally I want a date check on column C where date = today but the date is formatted as so "2021-03-19T06:00:19.196Z" so it needs to check the first 10 characters

and want to call this output B

A Geoff 2021-03-19 True True Yes True 1 80

A being the original result - and the entire row of data that appeared on stock tab - that satisfied those conditions
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,619
Office Version
  1. 2007
Platform
  1. Windows
I ran the macro and it generates in the sheet "enter item" all these records, I don't understand what the problem is.

Dante example v2.xlsm
ABCD
1Enter Component SKU BelowComponent SKUSelling SKU
2167938167938167938
3173923173923173923
4187407187407187407
5231808231808231808
6322140322140322140
7234432234432234432
8206011206011206011
9234613234613234613
10103206103206103206
11234438234438234438
12231007231007231007
13157665157665157665
14230987230987230987
15231006231006231006
16234440234440234440
17173918173918173918
18220570220570220570
19231756231756231756
20192444192444192444
21148193148193148193
22231838231838231838
23192393192393192393
24234434234434234434
25230990230990230990
26206012206012206012
27157747157747157747
28193834193834193834
29158535158535158535
30234609234609234609
31230160230160230160
32231836231836231836
33230338230338230338
34142248142248142248
35167938167938167938
36167939167939167939
37142249142249142249
38148825148825148825
39161559161559161559
40230345230345230345
41230371230371230371
42142251142251142251
43229487229487229487
44161750161750161750
45230321230321230321
46230101230101230101
47229476229476229476
48230194230194230194
49233741233741233741
50233740233740233740
51233743233743233743
52233745233745233745
53230322230322230322
54230208230208230208
55230201230201230201
56229501229501229501
57231794231794231794
58233744233744233744
59229492229492229492
enter item
 

Ravin

Board Regular
Joined
Aug 24, 2012
Messages
67
I ran the macro and it generates in the sheet "enter item" all these records, I don't understand what the problem is.

Dante example v2.xlsm
ABCD
1Enter Component SKU BelowComponent SKUSelling SKU
2167938167938167938
3173923173923173923
4187407187407187407
5231808231808231808
6322140322140322140
7234432234432234432
8206011206011206011
9234613234613234613
10103206103206103206
11234438234438234438
12231007231007231007
13157665157665157665
14230987230987230987
15231006231006231006
16234440234440234440
17173918173918173918
18220570220570220570
19231756231756231756
20192444192444192444
21148193148193148193
22231838231838231838
23192393192393192393
24234434234434234434
25230990230990230990
26206012206012206012
27157747157747157747
28193834193834193834
29158535158535158535
30234609234609234609
31230160230160230160
32231836231836231836
33230338230338230338
34142248142248142248
35167938167938167938
36167939167939167939
37142249142249142249
38148825148825148825
39161559161559161559
40230345230345230345
41230371230371230371
42142251142251142251
43229487229487229487
44161750161750161750
45230321230321230321
46230101230101230101
47229476229476229476
48230194230194230194
49233741233741233741
50233740233740233740
51233743233743233743
52233745233745233745
53230322230322230322
54230208230208230208
55230201230201230201
56229501229501229501
57231794231794231794
58233744233744233744
59229492229492229492
enter item

if you run it now yes it will produce output 1 correctly

if you change the data in output tab so the dates are all 2021-04-07

then re run macro

output 1 result is different
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,619
Office Version
  1. 2007
Platform
  1. Windows
I already found the issue, however the output in the "output" sheet is correct.

Change this line
VBA Code:
sh1.Range("C2").Resize(k, 2).Value = d


For this
VBA Code:
sh1.Range("C2").Resize(UBound(d, 1), 2).Value = d
 

Watch MrExcel Video

Forum statistics

Threads
1,129,383
Messages
5,635,951
Members
416,889
Latest member
dhegs

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