Check a list of values against multiple columns and output a list that appears in each?

[cloobless]

I'm stumped. Been trying this all morning.

I have an excel file with two sheets:
A. Master List + Output -- contains the base list of value that I want to check presence for against in all other columns in the "Daily Orders" sheet.
B. Daily Orders -- contains lists of each day's purchased items. Will grow to the right.

I'm trying to check for each item in the "master-list" to see if it appears in each column in the "daily orders" sheet. If it appears in each column, then print it in the "output" column, preferably to match the master-list order from top down.

Imagine I was trying to build a list over time that could output what food items from a master list are ordered on every date.

Is there a way to do this? Many thanks for any suggestions.

Master List and Output Sheet
[TABLE="class: grid, width: 700, align: left"]
<tbody>[TR]
[TD]MASTER-LIST[/TD]
[TD]VALUE-APPEARS-CONSISTENTLY-IN-EACH-DAILY-COLUMN-OUTPUT[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]apples[/TD]
[TD]apples[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]kiwi[/TD]
[TD]sausage[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]sausage[/TD]
[TD]cereal[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]cereal[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]potatoes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]











Daily Orders Sheet
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]AUG-1[/TD]
[TD]AUG-12[/TD]
[TD]AUG-15[/TD]
[TD]AUG-19[/TD]
[TD]+[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]apples[/TD]
[TD]apples[/TD]
[TD]chicken[/TD]
[TD]banana[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]banana[/TD]
[TD]sausage[/TD]
[TD]apples[/TD]
[TD]sausage[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]broth[/TD]
[TD]pasta[/TD]
[TD]sausage[/TD]
[TD]lettuce[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]celery[/TD]
[TD]cereal[/TD]
[TD]potatoes[/TD]
[TD]apples[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]sausage[/TD]
[TD]squash[/TD]
[TD]cereal[/TD]
[TD]kiwi[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]cereal[/TD]
[TD]potatoes[/TD]
[TD][/TD]
[TD]cereal[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Steve_]

I'm stumped. Been trying this all morning.

I have an excel file with two sheets:
A. Master List + Output -- contains the base list of value that I want to check presence for against in all other columns in the "Daily Orders" sheet.
B. Daily Orders -- contains lists of each day's purchased items. Will grow to the right.

I'm trying to check for each item in the "master-list" to see if it appears in each column in the "daily orders" sheet. If it appears in each column, then print it in the "output" column, preferably to match the master-list order from top down.

Imagine I was trying to build a list over time that could output what food items from a master list are ordered on every date.

Is there a way to do this? Many thanks for any suggestions.

Have you tried VLOOKUPs?

https://support.office.com/en-us/article/vlookup-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1

 

[cloobless]

Yes. I only understood how to output a statement (inlist) for true in an adjacent column. It isn't what I need, but I got it working.

I don't know how to:
a. check a completely different sheet of columns of varying length and new data added over time
b. loop through multiple columns to check
b. output the actual matching values in order

=IFERROR(IF(VLOOKUP(B2,$A:$A,1,FALSE)=B2,"INLIST"),"")

 

[Steve_]

Yes. I only understood how to output a statement (inlist) for true in an adjacent column. It isn't what I need, but I got it working.

I don't know how to:
a. check a completely different sheet of columns of varying length and new data added over time
b. loop through multiple columns to check
b. output the actual matching values in order

=IFERROR(IF(VLOOKUP(B2,$A:$A,1,FALSE)=B2,"INLIST"),"")

Okay. I am working on something else at the moment. What you need can be done. If someone doesnt beat me to it, Ill get it written sometime this afternoon I think.

 

[cloobless]

Thank you very much for taking a look. I appreciate it.

 

[Steve_]

Thank you very much for taking a look. I appreciate it.

Let me know how close this gets you..

Sub Go_Baby_GO()
    With Sheets("Sheet1")
        mArr = .Range(.Cells(1, 1), .Cells(.UsedRange.Rows.Count, .UsedRange.Columns.Count))
        For y = LBound(mArr, 2) To UBound(mArr, 2)
            For x = LBound(mArr, 1) + 1 To UBound(mArr, 1)
                If InStr(1, Prod_List, mArr(x, y)) = 0 Then
                    Prod_List = Prod_List & mArr(x, y) & "|"
                    pCount = pCount + 1
                End If
            Next x
        Next y
    End With
    Dim oArr()
    ReDim oArr(1 To 12, 1)
    Do Until Prod_List = ""
        oArr(LBound(oArr) + i, 0) = Split(Prod_List, "|")(0)
        Prod_List = Replace(Prod_List, Split(Prod_List, "|")(0) & "|", "", 1)
        For y = LBound(mArr, 2) To UBound(mArr, 2)
            isFound = False
            For x = LBound(mArr, 1) + 1 To UBound(mArr, 1)
                If mArr(x, y) = oArr(LBound(oArr) + i, 0) Then
                    DoEvents
                    isFound = True
                    Exit For
                End If
            Next x
            If isFound Then
                oArr(LBound(oArr) + i, 1) = True
            Else
                oArr(LBound(oArr) + i, 1) = False
                Exit For
            End If
        Next y
        i = i + 1
    Loop
    With Sheets("Sheet2")
        .Range(.Cells(1, 1), .Cells(UBound(oArr, 1), UBound(oArr, 2) + 1)) = oArr
    End With
End Sub

 

[cloobless]

Hi, Steve. Thank you so much for getting back to me. I think I installed the VBA correctly but I get an error:

Run-time error '9':
Subscript out of range

I think I have things set up basically the same as in the example above: master list in "sheet 1" and several columns of list data to check against in sheet 2...

EDIT: The debug highlights this line:
oArr(LBound(oArr) + i, 0) = Split(Prod_List, "|")(0)