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]

.

 

[DanteAmor]

You could share your book in the cloud. Just a sample of data.

__***__
You could upload a copy of your file to a free site such www.dropbox.com or google drive. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.
__***__

Identify the results you want in the book with yellow color. Explain on each sheet the search you want.

 

[DanteAmor]

Or better yet. Use XL2BB tool to put the examples in the post

 

[Ravin]

You could share your book in the cloud. Just a sample of data.

__***__
You could upload a copy of your file to a free site such www.dropbox.com or google drive. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.
__***__

Identify the results you want in the book with yellow color. Explain on each sheet the search you want.

Dropbox

www.dropbox.com

Dante hope this is clearer than my initial explanation

 

[Ravin]

Dropbox

www.dropbox.com

Dante hope this is clearer than my initial explanation

was this place to share ok?

 

[DanteAmor]

I already see your file.
Here I put the examples with XL2BB tool minisheets.

dante example.xlsx
	A	B	C	D	E	F						L			O
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
enter item

dante example.xlsx
	A	B	C	D	E	F	G	H
1	item	desc	status	item	SKU_DESCRIPTION	CATEGORY_DESCRIPTION	PRODUCT_STATUS	selling item
2	100006	Elux 14cm Warming Drawer EED14800AX	ACTIVE	100006	Elux 14cm Warming Drawer EED14800AX	Showroom Kitchens	ACTIVE	100006
3	100019	Kitchen Installation Hippo Skip Bag	ACTIVE	100019	Kitchen Installation Hippo Skip Bag	Installations	ACTIVE	100019
4	100019	Kitchen Installation Hippo Skip Bag	ACTIVE	100019	Kitchen Installation Hippo Skip Bag	Installations	ACTIVE	100020
5	100019	Kitchen Installation Hippo Skip Bag	ACTIVE	100019	Kitchen Installation Hippo Skip Bag	Installations	ACTIVE	100021
6	100019	Kitchen Installation Hippo Skip Bag	ACTIVE	100019	Kitchen Installation Hippo Skip Bag	Installations	ACTIVE	100022
7	100019	Kitchen Installation Hippo Skip Bag	ACTIVE	100019	Kitchen Installation Hippo Skip Bag	Installations	ACTIVE	100023
8	100019	Kitchen Installation Hippo Skip Bag	ACTIVE	100019	Kitchen Installation Hippo Skip Bag	Installations	ACTIVE	100024
9	100020	Bathroom Installation Hippo Skip Bag	ACTIVE	100020	Bathroom Installation Hippo Skip Bag	Installations	ACTIVE	100025
10	100023	Vieste Close Coupled Pan	ACTIVE	100023	Vieste Close Coupled Pan	Showroom Bathrooms	ACTIVE	100026
data

In fact your example with SKU 100006 in column G you have False, for it to work it must be True, correct?

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	TRUE	0	£329
stock

so in this case one item in column D appeared in stock sheet with todays date and column G being true

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	TRUE	0	£329
output

 

[Ravin]

I already see your file.
Here I put the examples with XL2BB tool minisheets.

dante example.xlsx
	A	B	C	D	E	F						L			O
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
enter item

dante example.xlsx
	A	B	C	D	E	F	G	H
1	item	desc	status	item	SKU_DESCRIPTION	CATEGORY_DESCRIPTION	PRODUCT_STATUS	selling item
2	100006	Elux 14cm Warming Drawer EED14800AX	ACTIVE	100006	Elux 14cm Warming Drawer EED14800AX	Showroom Kitchens	ACTIVE	100006
3	100019	Kitchen Installation Hippo Skip Bag	ACTIVE	100019	Kitchen Installation Hippo Skip Bag	Installations	ACTIVE	100019
4	100019	Kitchen Installation Hippo Skip Bag	ACTIVE	100019	Kitchen Installation Hippo Skip Bag	Installations	ACTIVE	100020
5	100019	Kitchen Installation Hippo Skip Bag	ACTIVE	100019	Kitchen Installation Hippo Skip Bag	Installations	ACTIVE	100021
6	100019	Kitchen Installation Hippo Skip Bag	ACTIVE	100019	Kitchen Installation Hippo Skip Bag	Installations	ACTIVE	100022
7	100019	Kitchen Installation Hippo Skip Bag	ACTIVE	100019	Kitchen Installation Hippo Skip Bag	Installations	ACTIVE	100023
8	100019	Kitchen Installation Hippo Skip Bag	ACTIVE	100019	Kitchen Installation Hippo Skip Bag	Installations	ACTIVE	100024
9	100020	Bathroom Installation Hippo Skip Bag	ACTIVE	100020	Bathroom Installation Hippo Skip Bag	Installations	ACTIVE	100025
10	100023	Vieste Close Coupled Pan	ACTIVE	100023	Vieste Close Coupled Pan	Showroom Bathrooms	ACTIVE	100026
data

In fact your example with SKU 100006 in column G you have False, for it to work it must be True, correct?

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	TRUE	0	£329
stock

so in this case one item in column D appeared in stock sheet with todays date and column G being true

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	TRUE	0	£329
output

yes apologies in the example i sent column G should have been true

 

[DanteAmor]

Try this:

Sub Output_Results()
  Dim sh1 As Worksheet, sh2 As Worksheet, sh3 As Worksheet, sh4 As Worksheet
  Dim a As Variant, b As Variant, c As Variant, d As Variant, e As Variant
  Dim i As Long, j As Long, k As Long
  Dim dic As Object
  Dim itm As Variant
  Dim fec As Date
  Dim sta As Boolean
  
  Set sh1 = Sheets("enter item")
  Set sh2 = Sheets("data")
  Set sh3 = Sheets("stock")
  Set sh4 = Sheets("output")
  Set dic = CreateObject("Scripting.Dictionary")
  
  sh1.Range("C2:D" & Rows.Count).ClearContents
  sh4.Range("A2:I" & Rows.Count).ClearContents
  
  a = sh1.Range("A2", sh1.Range("A" & Rows.Count).End(3)).Value2
  b = sh2.Range("A2", sh2.Range("H" & Rows.Count).End(3)).Value2
  c = sh3.Range("A2", sh3.Range("I" & Rows.Count).End(3)).Value2
  ReDim d(1 To UBound(b, 1), 1 To 2)
  ReDim e(1 To UBound(c, 1), 1 To 9)
  
  For i = 1 To UBound(b, 1)
    If dic.exists(b(i, 1)) Then
      dic(b(i, 1)) = dic(b(i, 1)) & "|" & b(i, 8)
    Else
      dic(b(i, 1)) = b(i, 8)
    End If
  Next
  
  For i = 1 To UBound(a, 1)
    If dic.exists(a(i, 1)) Then
      For Each itm In Split(dic(a(i, 1)), "|")
        k = k + 1
        d(k, 1) = a(i, 1)
        d(k, 2) = itm
      Next
    End If
  Next
  
  dic.RemoveAll
  For i = 1 To UBound(c, 1)
    dic(CStr(c(i, 1))) = c(i, 1) & "|" & c(i, 2) & "|" & c(i, 3) & "|" & c(i, 4) & "|" & _
                   c(i, 5) & "|" & c(i, 6) & "|" & c(i, 7) & "|" & c(i, 8) & "|" & c(i, 9)
  Next
  
  For i = 1 To UBound(d, 1)
    If dic.exists(d(i, 2)) Then
      fec = CDate(Left(Split(dic(d(i, 2)), "|")(2), 10))
      sta = Split(dic(d(i, 2)), "|")(6)
      If fec = Date And sta = True Then
        j = j + 1
        k = 0
        For Each itm In Split(dic(d(i, 2)), "|")
          k = k + 1
          e(j, k) = itm
        Next
      End If
    End If
  Next
  
  'output sheet "enter item"
  sh1.Range("C2").Resize(k, 2).Value = d
  'output sheet "output"
  sh4.Range("A2").Resize(j, 9).Value = e
End Sub

 

[Ravin]

Try this:

Sub Output_Results()
  Dim sh1 As Worksheet, sh2 As Worksheet, sh3 As Worksheet, sh4 As Worksheet
  Dim a As Variant, b As Variant, c As Variant, d As Variant, e As Variant
  Dim i As Long, j As Long, k As Long
  Dim dic As Object
  Dim itm As Variant
  Dim fec As Date
  Dim sta As Boolean
 
  Set sh1 = Sheets("enter item")
  Set sh2 = Sheets("data")
  Set sh3 = Sheets("stock")
  Set sh4 = Sheets("output")
  Set dic = CreateObject("Scripting.Dictionary")
 
  sh1.Range("C2:D" & Rows.Count).ClearContents
  sh4.Range("A2:I" & Rows.Count).ClearContents
 
  a = sh1.Range("A2", sh1.Range("A" & Rows.Count).End(3)).Value2
  b = sh2.Range("A2", sh2.Range("H" & Rows.Count).End(3)).Value2
  c = sh3.Range("A2", sh3.Range("I" & Rows.Count).End(3)).Value2
  ReDim d(1 To UBound(b, 1), 1 To 2)
  ReDim e(1 To UBound(c, 1), 1 To 9)
 
  For i = 1 To UBound(b, 1)
    If dic.exists(b(i, 1)) Then
      dic(b(i, 1)) = dic(b(i, 1)) & "|" & b(i, 8)
    Else
      dic(b(i, 1)) = b(i, 8)
    End If
  Next
 
  For i = 1 To UBound(a, 1)
    If dic.exists(a(i, 1)) Then
      For Each itm In Split(dic(a(i, 1)), "|")
        k = k + 1
        d(k, 1) = a(i, 1)
        d(k, 2) = itm
      Next
    End If
  Next
 
  dic.RemoveAll
  For i = 1 To UBound(c, 1)
    dic(CStr(c(i, 1))) = c(i, 1) & "|" & c(i, 2) & "|" & c(i, 3) & "|" & c(i, 4) & "|" & _
                   c(i, 5) & "|" & c(i, 6) & "|" & c(i, 7) & "|" & c(i, 8) & "|" & c(i, 9)
  Next
 
  For i = 1 To UBound(d, 1)
    If dic.exists(d(i, 2)) Then
      fec = CDate(Left(Split(dic(d(i, 2)), "|")(2), 10))
      sta = Split(dic(d(i, 2)), "|")(6)
      If fec = Date And sta = True Then
        j = j + 1
        k = 0
        For Each itm In Split(dic(d(i, 2)), "|")
          k = k + 1
          e(j, k) = itm
        Next
      End If
    End If
  Next
 
  'output sheet "enter item"
  sh1.Range("C2").Resize(k, 2).Value = d
  'output sheet "output"
  sh4.Range("A2").Resize(j, 9).Value = e
End Sub

thanks Dante

the code fails on sh4.Range("A2").Resize(j, 9).Value = e

outputting to the output sheet