Solved

Excel list values from rows

Posted on 2011-03-18
4
191 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
ID: 35166561
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
ID: 35166596
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
ID: 35166604
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
ID: 35166945
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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

786 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