Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Excel list values from rows

Posted on 2011-03-18
4
Medium Priority
?
207 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article helps those who get the 0xc004d307 error when trying to rearm (reset the license) Office 2013 in a Virtual Desktop Infrastructure (VDI) and/or those trying to prep the master image for Microsoft Key Management (KMS) activation. (i.e.- C…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
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…

636 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