Multiple returned values into a single cell

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I have an Excel spreadsheet, where I am trying to put multiple results, into
a single cell. For example, I have a dropdown selector "Yes" or "No" in a
row that states whether I want a column Market name returned:
a b c d
e
1 Atlanta Chicago Cincinnati
Cleveland
2 Yes Yes No
Yes
3

What I would like to do is this: return results, within a single cell (say,
a1), all markets that say "Yes", like this: "Atlanta,Chicago,Cleveland". I
would want them speparated by a comma...

Thanks for any help, it's driving me crazy. I've tried H and V lookups,
and I can bring back one market name, but getting the multiple names into
one cell is baffling.

Jeff
 
There's probably a more elegant solution out there, but it should work:

=SUBSTITUTE(IF(B2="yes",B1&", ","")&IF(C2="yes",C1&",
","")&IF(D2="yes",D1&", ","")&IF(E2="yes",E1&", ",""),",
","",COUNTIF(B2:E2,"yes"))

You can add more IF statements inside the SUBSTITUTE function if you have
more columns than your example suggests.

HTH,
Elkar
 
Jeff,

This formula will do it, but won't handle the comma and space sequence you want between the
cities. You'll get AtlantaChicagoCleveland.

=IF(B2="Yes",B1,"") & IF(C2="Yes",C1,"") & IF(D2="Yes",D1,"") & IF(E2="Yes",E1,"")

To handle the commas and spaces with a formula might send me into therapy. Someone might
find a manageable way. A user-defined function (UDF) will handle it nicely and reliably).
You'd paste this function into a regular module in the VBE of the workbook:

Function CityString(YesNo As Range, Cities As Range) As String
Dim i As Integer
Dim Yeses As Integer ' count of Yeses (Yes's?)
For i = 1 To YesNo.Count
If LCase(YesNo(i).Value) = "yes" Then
Yeses = Yeses + 1
If Yeses > 1 Then CityString = CityString & ", " 'need comma and space
CityString = CityString & Cities(i).Value
End If
Next i
End Function

Then put this in A1 (or any cell). It calls the function:

=CityString(B2:E2,B1:E1)

If you want more cities, just make the ranges in the function call bigger. You can have as
many as you want.
=CityString(B2:F2,B1:F1)
It picks up the city names from the cells, so they can be anything. It takes "Yes", "yes",
"YES" etc. It doesn't look for "No" -- it just looks for "Yes".
--
Earl Kiosterud
www.smokeylake.com

Note:
Top-posting is the norm around here.
Some folks prefer bottom-posting.
But if you bottom-post to a reply that's
already top-posted, the thread gets messy.
When in Rome...
 
This formula will do it, but won't handle the comma and space sequence you
want between the cities. You'll get AtlantaChicagoCleveland.

=IF(B2="Yes",B1,"") & IF(C2="Yes",C1,"") & IF(D2="Yes",D1,"") &
IF(E2="Yes",E1,"")

To handle the commas and spaces with a formula might send me into therapy.
Someone might find a manageable way
Click to expand...

What about like this...

=SUBSTITUTE(IF(B2="Yes",B1&",","")&IF(C2="Yes",C1&",","")&IF(D2="Yes",D1&",","")&IF(E2="Yes",E1&",",""),",","",COUNTIF(B2:E2,"=Yes"))

Rick
 
Wow...thanks all. My first post here, and I'm beyond satisifed. I can't
believe I spent a couple hours on this, and I didn't have an ampersand (&) in
my formula. But that did it! And thanks for the tips on the commas, too, I
can do with having some extra commas in there when the Market says "no".

Thanks again!!!
 
Rick

Pretty cool. The SUBSTITUTE took out the last comma. Clever. Some comments: The space
after the comma could be included in the formula. If there are no Yes conditions, it
returns an error. It'll get confounded if the city has a comma in it (but that's probably
not going to happen). The OP didn't say if he'd be expanding it for more cities, and it
will require some surgery to accommodate additional cities, and could exceed the allowable
formula length (somewhere around 60 cities if the maximum formula length is still 1024
characters). It has a consistent pattern, so it's doable without too much coffee.

--
Earl Kiosterud
www.smokeylake.com

Note: Some folks prefer bottom-posting.
But if you bottom-post to a reply that's
already top-posted, the thread gets messy.
When in Rome...
-----------------------------------------------------------------------
 
Some comments: The space after the comma could be included in the
formula.
Click to expand...

The OP doesn't show a space after his commas in the original postings sample
output, so I didn't include any either.
If there are no Yes conditions, it returns an error.
Click to expand...

The following formula should take care of that...

=SUBSTITUTE(IF(B2="Yes",B1&",","")&IF(C2="Yes",C1&",","")&IF(D2="Yes",D1&",","")&IF(E2="Yes",E1&",",""),",","",(COUNTIF(B2:E2,"=Yes")=0)+COUNTIF(B2:E2,"=Yes"))
It'll get confounded if the city has a comma in it.
Click to expand...

Yes, that is true... but then a comma would be a poor choice of delimiter
for a list of cities that could contain commas of their own. HOWEVER, not
being one to shrink away from a challenge<g>, this formula will produce a
comma delimited list where the items being delineated could possibly contain
commas of their own...

=SUBSTITUTE(SUBSTITUTE(IF(B2="Yes",B1&"$","")&IF(C2="Yes",C1&"$","")&IF(D2="Yes",D1&"$","")&IF(E2="Yes",E1&"$",""),"$","",(COUNTIF(B2:E2,"=Yes")=0)+COUNTIF(B2:E2,"=Yes")),"$",",")

Rick
 
here's a udf you might like where r is the row desired

Function an(r As Long)
For i = 4 To 1 Step -1
If UCase(Cells(r, i)) = "YES" Then ms = Cells(1, i) & "," & ms
Next i
an = Left(ms, Len(ms) - 1)
End Function
 
It'll get confounded if the city has a comma in it.

Never seen a city name with a comma in it. Can you name one? <g>

Biff
 
It'll get confounded if the city has a comma in it.
Never seen a city name with a comma in it. Can you name one? <g>
Click to expand...

You posted your response under my posting, but I wasn't the one who
originally suggested this. All I did was accept the statement as written (I
did note comma delimiters would be a bad idea in a list of items that could
contain commas) and took that as a challenge to write a formula that could
work around it (I think my formula does that). By the way, maybe Earl was
thinking of tacking on the state name for city names with ambiguous
locations, such as Kansas City (Missouri or Kansas).

Rick
 
You posted your response under my posting, but I wasn't the one who
originally suggested this.
Click to expand...

I was just being a smartass !

Sometimes "we" go to extremes trying to account for situations that will
never exist! And when "we" don't account for those nonexistent situations
someone is *always* there to remind you (read: rub your face in it!).

Biff
 
Biff,

No, I can't. Maybe a non-domestic city? And my comment about a comma in a city name was
almost frivolous, I admit. But I guess I leave it up to the OP to decide if it's important.
And someone'll come up with one, sho'nuff. If we've provided for it, then it'll never
happen! :)
--
Earl Kiosterud
www.smokeylake.com

Note: Top-posting has been the norm here.
Some folks prefer bottom-posting.
But if you bottom-post to a reply that's
already top-posted, the thread gets messy.
When in Rome...
-----------------------------------------------------------------------
 
This formula appears to be what I need for a project I'm working on, however,
it is returning an empty cell. Any suggestions? Thanks in advance!

mike
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Return multiple vales from a different spreadsheet into one cell 10
Multiple colums into a single cell 8
Returning a chronological record number for each record? 3
Excel clicking selects multiple cells instead of a single cell 2
NAME function working in reverse?? 1
counting text same cell multiple worksheets 11
Finding Corresponding Cell Value 2
Multiple text colours in a single cell 1

Back
Top