Link to home
Start Free TrialLog in
Avatar of JonMny
JonMny

asked on

Excel list values from rows

I have a list of names in rows

A            B

Joe         1
Fred
Sam       1
Sue        1

I would like to create a formula that will show the names with a value of 1 in a cell

so the result would be somthing like

Joe,Sam,Sue

Fred not in list because value <> 1/Blank

 
Avatar of StephenJR
StephenJR
Flag of United Kingdom of Great Britain and Northern Ireland image

Pretty sure you would need VBA to produce the results like that. A formula could produce the results in different cells.
ASKER CERTIFIED SOLUTION
Avatar of nutsch
nutsch
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of ragnarok89
ragnarok89

Here's a macro that will do it, regardless of the number of rows
Sub list()

Dim list As String
r = 1
list = ""

While Range("a" & r).Value <> ""
    If Cells(r, 2).Value <> "" Then list = list & Cells(r,2).Value & ", "
    r = r + 1
Wend

    Range("C1").Value = list  

End Sub

Open in new window

Avatar of Patrick Matthews
JonMny,

If you go to the Articles tab of EE and search for my Dictionary article, I have a code example there that is very similar to your question.