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

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Ravin

Board Regular
Joined
Aug 24, 2012
Messages
67
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

Well-known Member
Joined
Dec 3, 2018
Messages
12,622
Office Version
  1. 2007
Platform
  1. Windows
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

Board Regular
Joined
Aug 24, 2012
Messages
67
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

Board Regular
Joined
Aug 24, 2012
Messages
67

ADVERTISEMENT

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

Well-known Member
Joined
Dec 3, 2018
Messages
12,622
Office Version
  1. 2007
Platform
  1. Windows
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

Board Regular
Joined
Aug 24, 2012
Messages
67

ADVERTISEMENT

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
ABCDEFGHIJKLMNOP
1enter itemoutput results
2100006100006100006user enters items in column A from A2
3100019100019100019the number of items can vary
4100020100019100020
5100023100019100021a lookup checks each item across data sheetcheck A2 in column D:H data sheetresult in column H
6100019100022where there are multiple results it lists all results as per c and d
7100019100023
8100019100024the second stage is to check the results in column D this sheet against stock sheet
9100020100025
10100023100026if it doesn’t appear on stock sheet I don’t need it returned
11if the date in column C left(10) doesn’t equalt today I don’t need to see it
12if column G = false I don’t need to see it
13
14result will be in output
15
16so 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

Board Regular
Joined
Aug 24, 2012
Messages
67
dante example.xlsx
ABCDEFGHIJKLMNOP
1enter itemoutput results
2100006100006100006user enters items in column A from A2
3100019100019100019the number of items can vary
4100020100019100020
5100023100019100021a lookup checks each item across data sheetcheck A2 in column D:H data sheetresult in column H
6100019100022where there are multiple results it lists all results as per c and d
7100019100023
8100019100024the second stage is to check the results in column D this sheet against stock sheet
9100020100025
10100023100026if it doesn’t appear on stock sheet I don’t need it returned
11if the date in column C left(10) doesn’t equalt today I don’t need to see it
12if column G = false I don’t need to see it
13
14result will be in output
15
16so 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

Board Regular
Joined
Aug 24, 2012
Messages
67
dante example.xlsx
ABCDEFGHIJKLM
1itemdescstatusitemSKU_DESCRIPTIONCATEGORY_DESCRIPTIONPRODUCT_STATUSselling itemSELLING_SKUSKU_DESCRIPTIONPRODUCT_STATUS
2100006Elux 14cm Warming Drawer EED14800AXACTIVE100006Elux 14cm Warming Drawer EED14800AXShowroom KitchensACTIVE100006100005Zan F/S Washing Machine ZWH6160P WhiteDELETE
3100019Kitchen Installation Hippo Skip BagACTIVE100019Kitchen Installation Hippo Skip BagInstallationsACTIVE100019100006Elux 14cm Warming Drawer EED14800AXACTIVE
4100019Kitchen Installation Hippo Skip BagACTIVE100019Kitchen Installation Hippo Skip BagInstallationsACTIVE100020100019Kitchen Installation Hippo Skip BagACTIVE
5100019Kitchen Installation Hippo Skip BagACTIVE100019Kitchen Installation Hippo Skip BagInstallationsACTIVE100021100020Bathroom Installation Hippo Skip BagACTIVE
6100019Kitchen Installation Hippo Skip BagACTIVE100019Kitchen Installation Hippo Skip BagInstallationsACTIVE100022100023Vieste Close Coupled PanACTIVE
7100019Kitchen Installation Hippo Skip BagACTIVE100019Kitchen Installation Hippo Skip BagInstallationsACTIVE100023100024Vieste Close Coupled CisternACTIVE
8100019Kitchen Installation Hippo Skip BagACTIVE100019Kitchen Installation Hippo Skip BagInstallationsACTIVE100024100025Vieste 550mm 1th BasinACTIVE
9100020Bathroom Installation Hippo Skip BagACTIVE100020Bathroom Installation Hippo Skip BagInstallationsACTIVE100025100026Vieste Full PedestalACTIVE
10100023Vieste Close Coupled PanACTIVE100023Vieste Close Coupled PanShowroom BathroomsACTIVE100026100027Vieste 450mm Cloakroom BasinACTIVE
data
 

Ravin

Board Regular
Joined
Aug 24, 2012
Messages
67
dante example.xlsx
ABCDEFGHI
1skuurldateaddToBasketButtonPresentclickAndCollectPresentdeliveryPromisestockCheckPresentnumberOfStoresprice
2100006k2021-03-24T06:00:19.199ZTRUETRUEWithin 14 daysFALSE0£329
3100007k2021-03-19T06:00:19.199ZTRUEFALSEWithin 42 days from our supplierFALSE0£4,250
4100008k2021-03-19T06:00:19.199ZTRUEFALSEWithin 42 days from our supplierFALSE0£5,050
5100009k2021-03-19T06:00:19.199ZTRUEFALSEWithin 42 days from our supplierFALSE0£5,350
6100010k2021-03-19T06:00:19.199ZTRUEFALSEWithin 42 days from our supplierFALSE0£5,600
7100016k2021-03-19T06:00:19.197ZTRUETRUENext day availableFALSE224£50
8100264k2021-03-19T06:00:19.200ZTRUETRUENext day availableFALSE226£8.25
9100269k2021-03-19T06:00:19.196ZTRUETRUENext day availableFALSE197£6.30
10100270k2021-03-19T06:00:19.196ZTRUETRUENext day availableFALSE227£10
11100272k2021-03-19T06:00:19.199ZTRUETRUENext day availableFALSE225£11
12100320k2021-03-19T06:00:19.235ZTRUETRUENext day availableFALSE189£37
13100321k2021-03-19T06:00:19.235ZTRUETRUENext day availableFALSE187£37
14100322k2021-03-19T06:00:19.235ZTRUETRUENext day availableFALSE191£37
15100329k2021-03-19T06:00:19.198ZTRUETRUENext day availableFALSE162£6.50
16100330k2021-03-19T06:00:19.235ZTRUETRUENext day availableFALSE200£37
17100393k2021-03-19T06:00:19.236ZTRUETRUEWithin 14 daysFALSE1£60
18100394k2021-03-19T06:00:19.196ZTRUETRUEWithin 14 daysFALSE1£80
19100395k2021-03-19T06:00:19.196ZTRUEFALSEWithin 14 daysFALSE0£100
stock
 

Watch MrExcel Video

Forum statistics

Threads
1,130,083
Messages
5,639,980
Members
417,121
Latest member
DallyDally

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