excel vba assistance in lookup column and row

Lets us assume we have following spreadsheet columns A to F and 4 rows

I would like to write a excel VBA code or some kind of lookup function to find all occurrence of "1" and give products from column "F" and Stores from "Row 1" as sample provided below.

[A]             [B]            [C]           [D]             [E]            [F]
Store 1     Store 2     Store 3     Store 4     Store 5 
                                                                  1               Coffee
                      1           1                1           1               Tea
1                                                   1                              Green Tea

Open in new window


OUTPUT:

Coffee|Store 5
Tea|Store2,Store3,Store4,Store5
Green Tea|Store1,Store4
LVL 5
CalmSoulAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Patrick MatthewsCommented:
In G2, enter this formula, then copy down as needed:

=MID(IF(A2=1,","&A$1,"")&IF(B2=1,","&B$1,"")&IF(C2=1,","&C$1,"")&IF(D2=1,","&D$1,"")&IF(E2=1,","&E$1,""),2,1000)

If you want spaces:

=MID(IF(A2=1,", "&A$1,"")&IF(B2=1,", "&B$1,"")&IF(C2=1,", "&C$1,"")&IF(D2=1,", "&D$1,"")&IF(E2=1,", "&E$1,""),3,1000)
0
Ejgil HedegaardCommented:
Put this function in a module

Function Stores(Item As Range, StoreNames As Range, StoreUsed As Range) As String
    Application.Volatile
    Dim i As Integer
    Stores = ""
    For i = 1 To StoreNames.Columns.Count
        If StoreUsed.Cells(1, i) = 1 Then
            If Len(Stores) = 0 Then
                Stores = "|" + StoreNames.Cells(1, i)
            Else
                Stores = Stores + "," + StoreNames.Cells(1, i)
            End If
        End If
    Next i
    If Len(Stores) > 0 Then
        Stores = Item.Value + Stores
    End If
End Function

Open in new window


Then the formula for Coffee is
=Stores(F2;$A$1:$E$1;A2:E2)

Open in new window

First part = the cell with the item name
Second = range with the store names
Last = range with the numbers

See file
Stores.xlsm
0
CalmSoulAuthor Commented:
hi hgholt:

Thanks for fantastic solution ..

Can I make a list of items instead of "," for stores?

example:

Green Tea
Store 1
Store 3
Store 4
0
Ejgil HedegaardCommented:
That is possible, but not with something that works like a lookup function.

The function is a UDF (User Defined Function) and is like any formula in a cell, returning the result in the cell.
To return the result over several cells, making a list, makes the result unpredictable, as it will overwrite other cells.

Just for a demo, is a macro that will place the result on the sheet ResultList.
Using some named ranges on the sheet as input.
Press the button to run the macro.
Stores-with-list.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Spreadsheets

From novice to tech pro — start learning today.