Solved

# excel vba assistance in lookup column and row

Posted on 2014-02-26
595 Views
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
``````

OUTPUT:

Coffee|Store 5
Tea|Store2,Store3,Store4,Store5
Green Tea|Store1,Store4
0
Question by:CalmSoul
• 2
• 2

LVL 5

Author Comment

0

LVL 92

Expert Comment

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 20

Expert Comment

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
``````

Then the formula for Coffee is
``````=Stores(F2;\$A\$1:\$E\$1;A2:E2)
``````
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

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 20

Accepted Solution

Ejgil Hedegaard earned 500 total points
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

### Suggested Solutions

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.