Solved

excel vba assistance in lookup column and row

Posted on 2014-02-26
5
604 Views
Last Modified: 2014-02-27
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
0
Comment
Question by:CalmSoul
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 39890369
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
 
LVL 22

Expert Comment

by:Ejgil Hedegaard
ID: 39890387
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
 
LVL 5

Author Comment

by:CalmSoul
ID: 39890497
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
 
LVL 22

Accepted Solution

by:
Ejgil Hedegaard earned 500 total points
ID: 39892631
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

632 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question