Improve company productivity with a Business Account.Sign Up

x
?
Solved

Excel list values from rows

Posted on 2011-03-18
4
Medium Priority
?
213 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 2000 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 93

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

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

This tutorial is about creating a new Microsoft Online User Profile account along with how to transfer your files and settings. You may be faced with this situation if your existing user profile has become corrupted.
Debits & Credits have been the foundation of financial record keeping since 1494 - over 500 years. Excel is a brilliant tool for leveraging this ancient power - not least with Pivot Tables, sorting and filtering.  This article seeks by illustration …
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

607 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