Results 1 to 7 of 7

Thread: Check a list of values against multiple columns and output a list that appears in each?
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jul 2014
    Posts
    58
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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-LIST VALUE-APPEARS-CONSISTENTLY-IN-EACH-DAILY-COLUMN-OUTPUT
    apples apples
    kiwi sausage
    sausage cereal
    cereal
    potatoes











    Daily Orders Sheet
    AUG-1 AUG-12 AUG-15 AUG-19 +
    apples apples chicken banana
    banana sausage apples sausage
    broth pasta sausage lettuce
    celery cereal potatoes apples
    sausage squash cereal kiwi
    cereal potatoes cereal

  2. #2
    Board Regular
    Join Date
    Apr 2010
    Posts
    167
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

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

    Quote Originally Posted by cloobless View Post
    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/art...8-93a18ad188a1

  3. #3
    Board Regular
    Join Date
    Jul 2014
    Posts
    58
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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 by cloobless; Aug 2nd, 2019 at 01:54 PM.

  4. #4
    Board Regular
    Join Date
    Apr 2010
    Posts
    167
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

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

    Quote Originally Posted by cloobless View Post
    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.

  5. #5
    Board Regular
    Join Date
    Jul 2014
    Posts
    58
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

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

  6. #6
    Board Regular
    Join Date
    Apr 2010
    Posts
    167
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

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

    Quote Originally Posted by cloobless View Post
    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

  7. #7
    Board Regular
    Join Date
    Jul 2014
    Posts
    58
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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 by cloobless; Aug 2nd, 2019 at 06:49 PM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •