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

cloobless

Board Regular
Joined
Jul 15, 2014
Messages
66
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
Joined
Apr 28, 2010
Messages
167
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
Joined
Jul 15, 2014
Messages
66
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
Joined
Apr 28, 2010
Messages
167
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
Joined
Jul 15, 2014
Messages
66
Thank you very much for taking a look. I appreciate it.
 

Steve_

Board Regular
Joined
Apr 28, 2010
Messages
167
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
Joined
Jul 15, 2014
Messages
66
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:

Forum statistics

Threads
1,085,834
Messages
5,386,224
Members
401,986
Latest member
crt54

Some videos you may like

This Week's Hot Topics

Top