Solved

# Excel list values from rows

Posted on 2011-03-18
189 Views
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

0
Question by:JonMny

LVL 24

Expert Comment

Pretty sure you would need VBA to produce the results like that. A formula could produce the results in different cells.
0

LVL 39

Accepted Solution

nutsch earned 500 total points
YOu need a user-defined function for that. Here is one: paste into a workbook module and call it from your worksheet like:

=concatplusif(A1:A9,",",1,1)

Thomas
``````Public Function concatPlusIf(rng As Range, sep As String, lgCriteriaOffset As Long, varCriteria As Variant, Optional noDup As Boolean = False, Optional skipEmpty As Boolean = False) As String
'concatenates a range with the specified separator
Dim cl As Range, strTemp As String

If noDup Then

Dim newCol As New Collection

On Error Resume Next

For Each cl In rng.Cells
If skipEmpty = False Or Len(Trim(cl.Text)) > 0 Then
If cl.Offset(, lgCriteriaOffset) = varCriteria Then newCol.Add cl.Text, cl.Text
End If
Next

For i = 0 To newCol.Count
strTemp = strTemp & newCol(i) & sep
Next

Else

For Each cl In rng.Cells
If skipEmpty = False Or Len(Trim(cl.Text)) > 0 Then
If cl.Offset(, lgCriteriaOffset) = varCriteria Then strTemp = strTemp & cl.Text & sep
End If
Next

End If

concatPlusIf = Left(strTemp, Len(strTemp) - Len(sep))

End Function
``````
0

LVL 8

Expert Comment

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

LVL 92

Expert Comment

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.
0

## Featured Post

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.