Solved

excel vba assistance in lookup column and row

Posted on 2014-02-26
5
600 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 5

Author Comment

by:CalmSoul
ID: 39890230
0
 
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 21

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 21

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone 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

Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
With User Account Control (UAC) enabled in Windows 7, one needs to open an elevated Command Prompt in order to run scripts under administrative privileges. Although the elevated Command Prompt accomplishes the task, the question How to run as script…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

790 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