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

 

[DanteAmor]

What the error message says

 

[Ravin]

What the error message says

run -time error '1004':
application - defined or object - defined error

it outputs the data c:d on enter item sheet

but fails at the last part returning results to output sheet

 

[DanteAmor]

Perform the test with the same file that you shared in post #5

Then check the data in the file that is in error, maybe some data is in error.
Do you have formulas?

 

[Ravin]

Perform the test with the same file that you shared in post #5

Then check the data in the file that is in error, maybe some data is in error.
Do you have formulas?

HI Dante

I'm using the same file with the same data, on the sheet example i sent you

i get the run time error

and when i debug the code it fails on

sh4.Range("A2").Resize(j, 9).Value = e

Rav

 

[Ravin]

HI Dante

I'm using the same file with the same data, on the sheet example i sent you

i get the run time error

and when i debug the code it fails on

sh4.Range("A2").Resize(j, 9).Value = e

Rav

ive dropped the file into same folder also

 

[DanteAmor]

Forget your file, I don't know what problem that file has.
Create a new file and copy the examples I put in post #7

 

[Ravin]

Forget your file, I don't know what problem that file has.
Create a new file and copy the examples I put in post #7

dante example.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	enter item		output results
2	100006		100006	100006		user enters items in column A from A2
3	100019		100019	100019		the number of items can vary
4	100020		100019	100020
5	100023		100019	100021		a lookup checks each item across data sheet						check A2 in column D:H data sheet			result in column H
6			100019	100022		where there are multiple results it lists all results as per c and d
7			100019	100023
8			100019	100024		the second stage is to check the results in column D this sheet against stock sheet
9			100020	100025
10			100023	100026		if it doesn’t appear on stock sheet I don’t need it returned
11						if the date in column C left(10) doesn’t equalt today I don’t need to see it
12						if column G = false I don’t need to see it
13
14						result will be in output
15
16						so in this case one item in column D appeared in stock sheet with todays date and column G being true
17
18
enter item

 

[Ravin]

dante example.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	enter item		output results
2	100006		100006	100006		user enters items in column A from A2
3	100019		100019	100019		the number of items can vary
4	100020		100019	100020
5	100023		100019	100021		a lookup checks each item across data sheet						check A2 in column D:H data sheet			result in column H
6			100019	100022		where there are multiple results it lists all results as per c and d
7			100019	100023
8			100019	100024		the second stage is to check the results in column D this sheet against stock sheet
9			100020	100025
10			100023	100026		if it doesn’t appear on stock sheet I don’t need it returned
11						if the date in column C left(10) doesn’t equalt today I don’t need to see it
12						if column G = false I don’t need to see it
13
14						result will be in output
15
16						so in this case one item in column D appeared in stock sheet with todays date and column G being true
17
18
enter item

 

[Ravin]

dante example.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	item	desc	status	item	SKU_DESCRIPTION	CATEGORY_DESCRIPTION	PRODUCT_STATUS	selling item			SELLING_SKU	SKU_DESCRIPTION	PRODUCT_STATUS
2	100006	Elux 14cm Warming Drawer EED14800AX	ACTIVE	100006	Elux 14cm Warming Drawer EED14800AX	Showroom Kitchens	ACTIVE	100006			100005	Zan F/S Washing Machine ZWH6160P White	DELETE
3	100019	Kitchen Installation Hippo Skip Bag	ACTIVE	100019	Kitchen Installation Hippo Skip Bag	Installations	ACTIVE	100019			100006	Elux 14cm Warming Drawer EED14800AX	ACTIVE
4	100019	Kitchen Installation Hippo Skip Bag	ACTIVE	100019	Kitchen Installation Hippo Skip Bag	Installations	ACTIVE	100020			100019	Kitchen Installation Hippo Skip Bag	ACTIVE
5	100019	Kitchen Installation Hippo Skip Bag	ACTIVE	100019	Kitchen Installation Hippo Skip Bag	Installations	ACTIVE	100021			100020	Bathroom Installation Hippo Skip Bag	ACTIVE
6	100019	Kitchen Installation Hippo Skip Bag	ACTIVE	100019	Kitchen Installation Hippo Skip Bag	Installations	ACTIVE	100022			100023	Vieste Close Coupled Pan	ACTIVE
7	100019	Kitchen Installation Hippo Skip Bag	ACTIVE	100019	Kitchen Installation Hippo Skip Bag	Installations	ACTIVE	100023			100024	Vieste Close Coupled Cistern	ACTIVE
8	100019	Kitchen Installation Hippo Skip Bag	ACTIVE	100019	Kitchen Installation Hippo Skip Bag	Installations	ACTIVE	100024			100025	Vieste 550mm 1th Basin	ACTIVE
9	100020	Bathroom Installation Hippo Skip Bag	ACTIVE	100020	Bathroom Installation Hippo Skip Bag	Installations	ACTIVE	100025			100026	Vieste Full Pedestal	ACTIVE
10	100023	Vieste Close Coupled Pan	ACTIVE	100023	Vieste Close Coupled Pan	Showroom Bathrooms	ACTIVE	100026			100027	Vieste 450mm Cloakroom Basin	ACTIVE
data

 

[Ravin]

dante example.xlsx
	A	B	C	D	E	F	G	H	I
1	sku	url	date	addToBasketButtonPresent	clickAndCollectPresent	deliveryPromise	stockCheckPresent	numberOfStores	price
2	100006	k	2021-03-24T06:00:19.199Z	TRUE	TRUE	Within 14 days	FALSE	0	£329
3	100007	k	2021-03-19T06:00:19.199Z	TRUE	FALSE	Within 42 days from our supplier	FALSE	0	£4,250
4	100008	k	2021-03-19T06:00:19.199Z	TRUE	FALSE	Within 42 days from our supplier	FALSE	0	£5,050
5	100009	k	2021-03-19T06:00:19.199Z	TRUE	FALSE	Within 42 days from our supplier	FALSE	0	£5,350
6	100010	k	2021-03-19T06:00:19.199Z	TRUE	FALSE	Within 42 days from our supplier	FALSE	0	£5,600
7	100016	k	2021-03-19T06:00:19.197Z	TRUE	TRUE	Next day available	FALSE	224	£50
8	100264	k	2021-03-19T06:00:19.200Z	TRUE	TRUE	Next day available	FALSE	226	£8.25
9	100269	k	2021-03-19T06:00:19.196Z	TRUE	TRUE	Next day available	FALSE	197	£6.30
10	100270	k	2021-03-19T06:00:19.196Z	TRUE	TRUE	Next day available	FALSE	227	£10
11	100272	k	2021-03-19T06:00:19.199Z	TRUE	TRUE	Next day available	FALSE	225	£11
12	100320	k	2021-03-19T06:00:19.235Z	TRUE	TRUE	Next day available	FALSE	189	£37
13	100321	k	2021-03-19T06:00:19.235Z	TRUE	TRUE	Next day available	FALSE	187	£37
14	100322	k	2021-03-19T06:00:19.235Z	TRUE	TRUE	Next day available	FALSE	191	£37
15	100329	k	2021-03-19T06:00:19.198Z	TRUE	TRUE	Next day available	FALSE	162	£6.50
16	100330	k	2021-03-19T06:00:19.235Z	TRUE	TRUE	Next day available	FALSE	200	£37
17	100393	k	2021-03-19T06:00:19.236Z	TRUE	TRUE	Within 14 days	FALSE	1	£60
18	100394	k	2021-03-19T06:00:19.196Z	TRUE	TRUE	Within 14 days	FALSE	1	£80
19	100395	k	2021-03-19T06:00:19.196Z	TRUE	FALSE	Within 14 days	FALSE	0	£100
stock