Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

excel vba assistance in lookup column and row

Posted on 2014-02-26
5
Medium Priority
?
612 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
  • 2
  • 2
5 Comments
 
LVL 93

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 23

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 23

Accepted Solution

by:
Ejgil Hedegaard earned 2000 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
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…

783 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