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

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

DanteAmor

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

Well-known Member
Joined
Dec 3, 2018
Messages
12,622
Office Version
  1. 2007
Platform
  1. Windows
Or better yet. Use XL2BB tool to put the examples in the post
 

Ravin

Board Regular
Joined
Aug 24, 2012
Messages
67

ADVERTISEMENT

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.

Dante hope this is clearer than my initial explanation
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,622
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

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

dante example.xlsx
ABCDEFLO
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
enter item


dante example.xlsx
ABCDEFGH
1itemdescstatusitemSKU_DESCRIPTIONCATEGORY_DESCRIPTIONPRODUCT_STATUSselling item
2100006Elux 14cm Warming Drawer EED14800AXACTIVE100006Elux 14cm Warming Drawer EED14800AXShowroom KitchensACTIVE100006
3100019Kitchen Installation Hippo Skip BagACTIVE100019Kitchen Installation Hippo Skip BagInstallationsACTIVE100019
4100019Kitchen Installation Hippo Skip BagACTIVE100019Kitchen Installation Hippo Skip BagInstallationsACTIVE100020
5100019Kitchen Installation Hippo Skip BagACTIVE100019Kitchen Installation Hippo Skip BagInstallationsACTIVE100021
6100019Kitchen Installation Hippo Skip BagACTIVE100019Kitchen Installation Hippo Skip BagInstallationsACTIVE100022
7100019Kitchen Installation Hippo Skip BagACTIVE100019Kitchen Installation Hippo Skip BagInstallationsACTIVE100023
8100019Kitchen Installation Hippo Skip BagACTIVE100019Kitchen Installation Hippo Skip BagInstallationsACTIVE100024
9100020Bathroom Installation Hippo Skip BagACTIVE100020Bathroom Installation Hippo Skip BagInstallationsACTIVE100025
10100023Vieste Close Coupled PanACTIVE100023Vieste Close Coupled PanShowroom BathroomsACTIVE100026
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
ABCDEFGHI
1skuurldateaddToBasketButtonPresentclickAndCollectPresentdeliveryPromisestockCheckPresentnumberOfStoresprice
2100006k2021-03-24T06:00:19.199ZTRUETRUEWithin 14 daysTRUE0£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
ABCDEFGHI
1skuurldateaddToBasketButtonPresentclickAndCollectPresentdeliveryPromisestockCheckPresentnumberOfStoresprice
2100006k2021-03-24T06:00:19.199ZTRUETRUEWithin 14 daysTRUE0£329
output
 

Ravin

Board Regular
Joined
Aug 24, 2012
Messages
67
I already see your file.
Here I put the examples with XL2BB tool minisheets.

dante example.xlsx
ABCDEFLO
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
enter item


dante example.xlsx
ABCDEFGH
1itemdescstatusitemSKU_DESCRIPTIONCATEGORY_DESCRIPTIONPRODUCT_STATUSselling item
2100006Elux 14cm Warming Drawer EED14800AXACTIVE100006Elux 14cm Warming Drawer EED14800AXShowroom KitchensACTIVE100006
3100019Kitchen Installation Hippo Skip BagACTIVE100019Kitchen Installation Hippo Skip BagInstallationsACTIVE100019
4100019Kitchen Installation Hippo Skip BagACTIVE100019Kitchen Installation Hippo Skip BagInstallationsACTIVE100020
5100019Kitchen Installation Hippo Skip BagACTIVE100019Kitchen Installation Hippo Skip BagInstallationsACTIVE100021
6100019Kitchen Installation Hippo Skip BagACTIVE100019Kitchen Installation Hippo Skip BagInstallationsACTIVE100022
7100019Kitchen Installation Hippo Skip BagACTIVE100019Kitchen Installation Hippo Skip BagInstallationsACTIVE100023
8100019Kitchen Installation Hippo Skip BagACTIVE100019Kitchen Installation Hippo Skip BagInstallationsACTIVE100024
9100020Bathroom Installation Hippo Skip BagACTIVE100020Bathroom Installation Hippo Skip BagInstallationsACTIVE100025
10100023Vieste Close Coupled PanACTIVE100023Vieste Close Coupled PanShowroom BathroomsACTIVE100026
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
ABCDEFGHI
1skuurldateaddToBasketButtonPresentclickAndCollectPresentdeliveryPromisestockCheckPresentnumberOfStoresprice
2100006k2021-03-24T06:00:19.199ZTRUETRUEWithin 14 daysTRUE0£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
ABCDEFGHI
1skuurldateaddToBasketButtonPresentclickAndCollectPresentdeliveryPromisestockCheckPresentnumberOfStoresprice
2100006k2021-03-24T06:00:19.199ZTRUETRUEWithin 14 daysTRUE0£329
output

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

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,622
Office Version
  1. 2007
Platform
  1. Windows
Try this:

VBA Code:
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

Board Regular
Joined
Aug 24, 2012
Messages
67
Try this:

VBA Code:
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
 

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