Macro Lookup

[Ravin]

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

 

[Ravin]

do you want me to share via dropbox again

Dropbox

www.dropbox.com

 

[DanteAmor]

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
	A	B	C	D
1	Enter Component SKU Below		Component SKU	Selling SKU
2	167938		167938	167938
3	173923		173923	173923
4	187407		187407	187407
5	231808		231808	231808
6	322140		322140	322140
7	234432		234432	234432
8	206011		206011	206011
9	234613		234613	234613
10	103206		103206	103206
11	234438		234438	234438
12	231007		231007	231007
13	157665		157665	157665
14	230987		230987	230987
15	231006		231006	231006
16	234440		234440	234440
17	173918		173918	173918
18	220570		220570	220570
19	231756		231756	231756
20	192444		192444	192444
21	148193		148193	148193
22	231838		231838	231838
23	192393		192393	192393
24	234434		234434	234434
25	230990		230990	230990
26	206012		206012	206012
27	157747		157747	157747
28	193834		193834	193834
29	158535		158535	158535
30	234609		234609	234609
31	230160		230160	230160
32	231836		231836	231836
33	230338		230338	230338
34	142248		142248	142248
35	167938		167938	167938
36	167939		167939	167939
37	142249		142249	142249
38	148825		148825	148825
39	161559		161559	161559
40	230345		230345	230345
41	230371		230371	230371
42	142251		142251	142251
43	229487		229487	229487
44	161750		161750	161750
45	230321		230321	230321
46	230101		230101	230101
47	229476		229476	229476
48	230194		230194	230194
49	233741		233741	233741
50	233740		233740	233740
51	233743		233743	233743
52	233745		233745	233745
53	230322		230322	230322
54	230208		230208	230208
55	230201		230201	230201
56	229501		229501	229501
57	231794		231794	231794
58	233744		233744	233744
59	229492		229492	229492
enter item

 

[Ravin]

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
	A	B	C	D
1	Enter Component SKU Below		Component SKU	Selling SKU
2	167938		167938	167938
3	173923		173923	173923
4	187407		187407	187407
5	231808		231808	231808
6	322140		322140	322140
7	234432		234432	234432
8	206011		206011	206011
9	234613		234613	234613
10	103206		103206	103206
11	234438		234438	234438
12	231007		231007	231007
13	157665		157665	157665
14	230987		230987	230987
15	231006		231006	231006
16	234440		234440	234440
17	173918		173918	173918
18	220570		220570	220570
19	231756		231756	231756
20	192444		192444	192444
21	148193		148193	148193
22	231838		231838	231838
23	192393		192393	192393
24	234434		234434	234434
25	230990		230990	230990
26	206012		206012	206012
27	157747		157747	157747
28	193834		193834	193834
29	158535		158535	158535
30	234609		234609	234609
31	230160		230160	230160
32	231836		231836	231836
33	230338		230338	230338
34	142248		142248	142248
35	167938		167938	167938
36	167939		167939	167939
37	142249		142249	142249
38	148825		148825	148825
39	161559		161559	161559
40	230345		230345	230345
41	230371		230371	230371
42	142251		142251	142251
43	229487		229487	229487
44	161750		161750	161750
45	230321		230321	230321
46	230101		230101	230101
47	229476		229476	229476
48	230194		230194	230194
49	233741		233741	233741
50	233740		233740	233740
51	233743		233743	233743
52	233745		233745	233745
53	230322		230322	230322
54	230208		230208	230208
55	230201		230201	230201
56	229501		229501	229501
57	231794		231794	231794
58	233744		233744	233744
59	229492		229492	229492
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]

I already found the issue, however the output in the "output" sheet is correct.

Change this line

sh1.Range("C2").Resize(k, 2).Value = d

For this

sh1.Range("C2").Resize(UBound(d, 1), 2).Value = d