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
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
Pretty sure you would need VBA to produce the results like that. A formula could produce the results in different cells.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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.
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.