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

cloobless

Board Regular
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
MASTER-LISTVALUE-APPEARS-CONSISTENTLY-IN-EACH-DAILY-COLUMN-OUTPUT
applesapples
kiwisausage
sausagecereal
cereal
potatoes

<tbody>
</tbody>











Daily Orders Sheet
AUG-1AUG-12AUG-15AUG-19+
applesappleschickenbanana
bananasausageapplessausage
brothpastasausagelettuce
celerycerealpotatoesapples
sausagesquashcerealkiwi
cerealpotatoescereal

<tbody>
</tbody>
 

Steve_

Board Regular
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

Board Regular
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"),"")
 
Last edited:

Steve_

Board Regular
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

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

Steve_

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

Let me know how close this gets you..


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

Board Regular
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)
 
Last edited:

Some videos you may like

This Week's Hot Topics

  • Get External Data (long shot question!)
    This is likely a long shot but I am wondering if it is at all possible for Excel to somehow 'change' the contents of a URL that is being linked to...
  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • Cell Formatting
    Good Morning, I need to format a few different cells in the following manners: A1 has to always add a colon (:) after whatever is typed in by a...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • Workbook_Change stopped working !
    I am working on an app to speed up & automate processing of Credit Cards statements. After data is input from a CSV file, it is presented to the...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
Top