Solved

Excel list values from rows

Posted on 2011-03-18
4
189 Views
Last Modified: 2012-05-11
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
Comment
Question by:JonMny
4 Comments
 
LVL 24

Expert Comment

by:StephenJR
Comment Utility
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

by:
nutsch earned 500 total points
Comment Utility
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

Open in new window

0
 
LVL 8

Expert Comment

by:ragnarok89
Comment Utility
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

0
 
LVL 92

Expert Comment

by:Patrick Matthews
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

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.

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now