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]

dante example.xlsx
	A	B	C	D	E	F	G	H	I
1	sku	url	date	addToBasketButtonPresent	clickAndCollectPresent	deliveryPromise	stockCheckPresent	numberOfStores	price
2
3
4
output

 

[Ravin]

 

[Ravin]

Hi Dante hopefully this is ok.

 

[DanteAmor]

That's because no records match.
Put records dated March 26 or the date of the execution.

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"
  If j = 0 Then
    MsgBox "No record matches"
  Else
    sh4.Range("A2").Resize(j, 9).Value = e
    MsgBox "End output"
  End If
End Sub

 

[Ravin]

That's because no records match.
Put records dated March 26 or the date of the execution.

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"
  If j = 0 Then
    MsgBox "No record matches"
  Else
    sh4.Range("A2").Resize(j, 9).Value = e
    MsgBox "End output"
  End If
End Sub

of course thanks Dante because it was debugging and Ihadnt picked up the smaple date was out of date thankyou so much

 

[Ravin]

of course thanks Dante because it was debugging and Ihadnt picked up the smaple date was out of date thankyou so much

Dante I have run the latest data, and the code runs perfectly

but the outputs vary

so the 1st output only displays 9 items rather than 29 items

BOM Macro v3 Working.xlsm
	A	B	C	D
1	Enter Component SKU Below		Component SKU	Selling SKU
2	206182		206182	206182
3	206011		206011	206011
4	234613		234613	234613
5	103206		103206	103206
6	234438		234438	234438
7	231007		231007	231007
8	157665		157665	157665
9	230987		230987	230987
10	231006		231006	231006
11	234440
12	173918
13	220570
14	231756
15	192444
16	148193
17	231838
18	192393
19	234434
20	230990
21	157664
22	230991
23	193862
24	206012
25	157747
26	193834
27	158535
28	192448
29	203383
30	205978
enter item

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C2:C1048576	Cell Value	>0	text	NO
A2:A1048576	Cell Value	>0	text	NO
D2:D1048576	Cell Value	>0	text	NO

BOM Macro v3 Working.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Component SKU	Component Description	JDA Status	Component SKU	SKU_DESCRIPTION	Category	JDA Status	Selling SKU			Selling Sku	Description	JDA 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	100019			100019	Kitchen Installation Hippo Skip Bag	ACTIVE
5	100019	Kitchen Installation Hippo Skip Bag	ACTIVE	100019	Kitchen Installation Hippo Skip Bag	Installations	ACTIVE	100019			100020	Bathroom Installation Hippo Skip Bag	ACTIVE
6	100019	Kitchen Installation Hippo Skip Bag	ACTIVE	100019	Kitchen Installation Hippo Skip Bag	Installations	ACTIVE	100019			100023	Vieste Close Coupled Pan	ACTIVE
7	100019	Kitchen Installation Hippo Skip Bag	ACTIVE	100019	Kitchen Installation Hippo Skip Bag	Installations	ACTIVE	100019			100024	Vieste Close Coupled Cistern	ACTIVE
8	100019	Kitchen Installation Hippo Skip Bag	ACTIVE	100019	Kitchen Installation Hippo Skip Bag	Installations	ACTIVE	100019			100025	Vieste 550mm 1th Basin	ACTIVE
9	100020	Bathroom Installation Hippo Skip Bag	ACTIVE	100020	Bathroom Installation Hippo Skip Bag	Installations	ACTIVE	100020			100026	Vieste Full Pedestal	ACTIVE
10	100023	Vieste Close Coupled Pan	ACTIVE	100023	Vieste Close Coupled Pan	Showroom Bathrooms	ACTIVE	100023			100027	Vieste 450mm Cloakroom Basin	ACTIVE
11	100024	Vieste Close Coupled Cistern	ACTIVE	100024	Vieste Close Coupled Cistern	Showroom Bathrooms	ACTIVE	100024			100028	Vieste Semi Pedestal	ACTIVE
12	100025	Vieste 550mm 1th Basin	ACTIVE	100025	Vieste 550mm 1th Basin	Showroom Bathrooms	ACTIVE	100025			100029	Vieste Comfort Height Pan	ACTIVE
13	100026	Vieste Full Pedestal	ACTIVE	100026	Vieste Full Pedestal	Showroom Bathrooms	ACTIVE	100026			100030	Vieste Soft Close Toilet Seat	ACTIVE
14	100027	Vieste 450mm Cloakroom Basin	ACTIVE	100027	Vieste 450mm Cloakroom Basin	Showroom Bathrooms	ACTIVE	100027			100035	40mm Sq/Rec Easy Plumb Kit	DELETE
15	100028	Vieste Semi Pedestal	ACTIVE	100028	Vieste Semi Pedestal	Showroom Bathrooms	ACTIVE	100028			100036	40mm Quad/Offset Quad Easy Plumb Kit	ACTIVE
16	100029	Vieste Comfort Height Pan	ACTIVE	100029	Vieste Comfort Height Pan	Showroom Bathrooms	ACTIVE	100029			100037	Frosted/Tan 25mm Waste Kit.	DELETE
17	100030	Vieste Soft Close Toilet Seat	ACTIVE	100030	Vieste Soft Close Toilet Seat	Showroom Bathrooms	ACTIVE	100030			100038	Sesto/Trento Cistern Fittings	ACTIVE
18	100036	40mm Quad/Offset Quad Easy Plumb Kit	ACTIVE	100036	40mm Quad/Offset Quad Easy Plumb Kit	Showroom Bathrooms	ACTIVE	100036			100041	Sesto Cistern	DELETE
19	100038	Sesto/Trento Cistern Fittings	ACTIVE	100038	Sesto/Trento Cistern Fittings	Showroom Bathrooms	ACTIVE	100038			100042	Trento Cistern	ACTIVE
20	100042	Trento Cistern	ACTIVE	100042	Trento Cistern	Showroom Bathrooms	ACTIVE	100042			100217	C RICA CRM Facia HL Frdg/Laund 600x722mm	DELETE
21	100343	April 8mm Glass Quadrant Enclosure 900	ACTIVE	100343	April 8mm Glass Quadrant Enclosure 900	Showroom Bathrooms	ACTIVE	100343			100218	ROCKFORD Plinth 2.5M	DELETE
22	100344	April 8mm Glass Slider Door Only 1200	ACTIVE	100344	April 8mm Glass Slider Door Only 1200	Showroom Bathrooms	ACTIVE	100344			100219	STAMFORD Single Oven Tower	DELETE
23	100345	April 8mm Glass Slider Enclosure 800	ACTIVE	100344	April 8mm Glass Slider Door Only 1200	Showroom Bathrooms	ACTIVE	100345			100220	STAMFORD Larder Unit 600mm	DELETE
24	100345	April 8mm Glass Slider Enclosure 800	ACTIVE	167488	April Enclosure side panel 800mm	Showroom Bathrooms	ACTIVE	100345			100221	TENBY Thick Decor End Tall	DELETE
25	100346	April 8mm Glass Slider Enclosure 900	ACTIVE	100344	April 8mm Glass Slider Door Only 1200	Showroom Bathrooms	ACTIVE	100346			100222	GALWAY Thick Decor End Tall	DELETE
26	100346	April 8mm Glass Slider Enclosure 900	ACTIVE	167489	April Enclosure side panel 900mm	Showroom Bathrooms	ACTIVE	100346			100343	April 8mm Glass Quadrant Enclosure 900	ACTIVE
27	100384	Venturi 6 Dble Door Offset Quad 1200x800	ACTIVE	100384	Venturi 6 Dble Door Offset Quad 1200x800	Showroom Bathrooms	ACTIVE	100384			100344	April 8mm Glass Slider Door Only 1200	ACTIVE
28	100393	Saluzzo Mono Basin Mixer	ACTIVE	100393	Saluzzo Mono Basin Mixer	Showroom Bathrooms	ACTIVE	100393			100345	April 8mm Glass Slider Enclosure 800	ACTIVE
29	100394	Saluzzo 2 tap hole Bath Filler no plate	ACTIVE	100394	Saluzzo 2 tap hole Bath Filler no plate	Showroom Bathrooms	ACTIVE	100394			100346	April 8mm Glass Slider Enclosure 900	ACTIVE
30	100395	Saluzzo Bath Shower Mixer 2th	ACTIVE	100395	Saluzzo Bath Shower Mixer 2th	Showroom Bathrooms	ACTIVE	100395			100384	Venturi 6 Dble Door Offset Quad 1200x800	ACTIVE
31	100397	Teramo Tall Basin Mixer	ACTIVE	100397	Teramo Tall Basin Mixer	Showroom Bathrooms	ACTIVE	100397			100393	Saluzzo Mono Basin Mixer	ACTIVE
32	100398	Teramo Compact Mono Basin Mixer	ACTIVE	100398	Teramo Compact Mono Basin Mixer	Showroom Bathrooms	ACTIVE	100398			100394	Saluzzo 2 tap hole Bath Filler no plate	ACTIVE
33	100400	Teramo Bath Shower Mixer 2th	ACTIVE	100400	Teramo Bath Shower Mixer 2th	Showroom Bathrooms	ACTIVE	100400			100395	Saluzzo Bath Shower Mixer 2th	ACTIVE
data

 

[Ravin]

so the list of items we detail in enter item sheet will vary from 5 items to a hundred items

the combinations which you detail in c2:D

doesnt seem to display all the combinations

it seems to only show 9 rows

but the next output seems to suggest it is looking up all the combinations

Rav

 

[Ravin]

That's because no records match.
Put records dated March 26 or the date of the execution.

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"
  If j = 0 Then
    MsgBox "No record matches"
  Else
    sh4.Range("A2").Resize(j, 9).Value = e
    MsgBox "End output"
  End If
End Sub

Hi Dante

So there is definitely a range length issue

so I have re run the data again today

my list of lookup items is about 100

it looks up and returns only 10 results for first output when i expected 100 results

but it doesnt look across all the range defined in data tab. There are 100k rows in data tab

and obviously it also doesnt then lookup all the results against stock

i've checked the formats and to make sure it works i have reformatted all items to text on each tab

Rav

Dante example v2.xlsm
	A	B	C	D	E
1	Enter Component SKU Below		Component SKU	Selling SKU
2	167938		103206	103206
3	173923		157665	157665
4	187407		148193	148193
5	231808		157747	157747
6	322140		158535	158535
7	234432		142248	142248
8	206011		142249	142249
9	234613		148825	148825
10	103206		161559	161559
11	234438
12	231007
13	157665
14	230987
15	231006
16	234440
17	173918
18	220570
19	231756
20	192444
21	148193
22	231838
enter item

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C2:C1048576	Cell Value	>0	text	NO
A2:A1048576	Cell Value	>0	text	NO
D2:D1048576	Cell Value	>0	text	NO

Dante example v2.xlsm
	A	B	C	D	E	F	G	H
1	Component SKU	Component Description	JDA Status	Component SKU	SKU_DESCRIPTION	Category	JDA Status	Selling SKU
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	100019
5	100019	Kitchen Installation Hippo Skip Bag	ACTIVE	100019	Kitchen Installation Hippo Skip Bag	Installations	ACTIVE	100019
6	100019	Kitchen Installation Hippo Skip Bag	ACTIVE	100019	Kitchen Installation Hippo Skip Bag	Installations	ACTIVE	100019
7	100019	Kitchen Installation Hippo Skip Bag	ACTIVE	100019	Kitchen Installation Hippo Skip Bag	Installations	ACTIVE	100019
8	100019	Kitchen Installation Hippo Skip Bag	ACTIVE	100019	Kitchen Installation Hippo Skip Bag	Installations	ACTIVE	100019
9	100020	Bathroom Installation Hippo Skip Bag	ACTIVE	100020	Bathroom Installation Hippo Skip Bag	Installations	ACTIVE	100020
10	100023	Vieste Close Coupled Pan	ACTIVE	100023	Vieste Close Coupled Pan	Showroom Bathrooms	ACTIVE	100023
11	100024	Vieste Close Coupled Cistern	ACTIVE	100024	Vieste Close Coupled Cistern	Showroom Bathrooms	ACTIVE	100024
12	100025	Vieste 550mm 1th Basin	ACTIVE	100025	Vieste 550mm 1th Basin	Showroom Bathrooms	ACTIVE	100025
13	100026	Vieste Full Pedestal	ACTIVE	100026	Vieste Full Pedestal	Showroom Bathrooms	ACTIVE	100026
14	100027	Vieste 450mm Cloakroom Basin	ACTIVE	100027	Vieste 450mm Cloakroom Basin	Showroom Bathrooms	ACTIVE	100027
15	100028	Vieste Semi Pedestal	ACTIVE	100028	Vieste Semi Pedestal	Showroom Bathrooms	ACTIVE	100028
16	100029	Vieste Comfort Height Pan	ACTIVE	100029	Vieste Comfort Height Pan	Showroom Bathrooms	ACTIVE	100029
17	100030	Vieste Soft Close Toilet Seat	ACTIVE	100030	Vieste Soft Close Toilet Seat	Showroom Bathrooms	ACTIVE	100030
18	100036	40mm Quad/Offset Quad Easy Plumb Kit	ACTIVE	100036	40mm Quad/Offset Quad Easy Plumb Kit	Showroom Bathrooms	ACTIVE	100036
19	100038	Sesto/Trento Cistern Fittings	ACTIVE	100038	Sesto/Trento Cistern Fittings	Showroom Bathrooms	ACTIVE	100038
data

Dante example v2.xlsm
	A	B	C	D	E	F	G	H	I
1	sku	url	date	addToBasketButtonPresent	clickAndCollectPresent	deliveryPromise	stockCheckPresent	numberOfStores	price
2	100006	bb	2021-04-01T06:00:19.652Z	TRUE	FALSE	Within 14 days	FALSE	0	329.00
3	100007	bb	2021-04-01T06:00:19.652Z	TRUE	FALSE	Within 21 days from our supplier	FALSE	0	4250.00
4	100008	bb	2021-04-01T06:00:19.664Z	TRUE	FALSE	Within 21 days from our supplier	FALSE	0	5050.00
5	100009	bb	2021-04-01T06:00:19.652Z	TRUE	FALSE	Within 21 days from our supplier	FALSE	0	5350.00
6	100010	bb	2021-04-01T06:00:19.652Z	TRUE	FALSE	Within 21 days from our supplier	FALSE	0	5600.00
7	100016	bb	2021-04-01T06:00:19.650Z	TRUE	TRUE	Next day available	FALSE	224	50.00
8	100264	bb	2021-04-01T06:00:19.665Z	TRUE	TRUE	Next day available	FALSE	223	8.25
9	100269	bb	2021-04-01T06:00:19.649Z	TRUE	TRUE	Next day available	FALSE	197	6.30
10	100270	bb	2021-04-01T06:00:19.649Z	TRUE	TRUE	Next day available	FALSE	204	10.00
11	100272	bb	2021-04-01T06:00:19.652Z	TRUE	TRUE	Next day available	FALSE	227	12.70
12	100320	bb	2021-04-01T06:00:19.668Z	TRUE	TRUE	Next day available	FALSE	185	37.00
13	100321	bb	2021-04-01T06:00:19.668Z	TRUE	TRUE	Next day available	FALSE	169	37.00
14	100322	bb	2021-04-01T06:00:19.668Z	TRUE	TRUE	Next day available	FALSE	180	37.00
15	100329	bb	2021-04-01T06:00:19.652Z	TRUE	TRUE	Next day available	FALSE	100	6.50
16	100330	bb	2021-04-01T06:00:19.668Z	TRUE	TRUE	Next day available	FALSE	192	37.00
17	100393	bb	2021-04-01T06:00:19.674Z	TRUE	FALSE	Within 14 days	FALSE	0	60.00
18	100394	bb	2021-04-01T06:00:19.649Z	TRUE	TRUE	Within 14 days	FALSE	1	80.00
stock

Rav

 

[DanteAmor]

my list of lookup items is about 100

You can share this file in the cloud. In the file put the output you want and explain it again.

 

[Ravin]

You can share this file in the cloud. In the file put the output you want and explain it again.

do you want me to share via dropbox again