Multiple returned values into a single cell
 

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

Multiple returned values into a single cell
 

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" wrote:

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

Multiple returned values into a single cell
 

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...
-----------------------------------------------------------------------
"Jeff" wrote in message
...
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

Multiple returned values into a single cell
 

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

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

Multiple returned values into a single cell
 

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 Rothstein (MVP - VB)" wrote:

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

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

Multiple returned values into a single cell
 

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...
-----------------------------------------------------------------------
"Rick Rothstein (MVP - VB)" wrote in message
...
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

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

Multiple returned values into a single cell
 

Some comments: The space after the comma could be included in the
formula.

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.

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.

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(C 2="Yes",C1&"$","")&IF(D2="Yes",D1&"$","")&IF(E2="Y es",E1&"$",""),"$","",(COUNTIF(B2:E2,"=Yes")=0)+CO UNTIF(B2:E2,"=Yes")),"$",",")

Rick

Multiple returned values into a single cell
 

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

--
Don Guillett
SalesAid Software

"Jeff" wrote in message
...
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

Multiple returned values into a single cell
 

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

"Rick Rothstein (MVP - VB)" wrote in
message ...
Some comments: The space after the comma could be included in the
formula.

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.

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.

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(C 2="Yes",C1&"$","")&IF(D2="Yes",D1&"$","")&IF(E2="Y es",E1&"$",""),"$","",(COUNTIF(B2:E2,"=Yes")=0)+CO UNTIF(B2:E2,"=Yes")),"$",",")

Rick

Multiple returned values into a single cell
 

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

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

Multiple returned values into a single cell
 

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

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

"Rick Rothstein (MVP - VB)" wrote in
message ...
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

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

Multiple returned values into a single cell
 

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...
-----------------------------------------------------------------------
"T. Valko" wrote in message
...
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

"Rick Rothstein (MVP - VB)" wrote in message
...
Some comments: The space after the comma could be included in the formula.

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.

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.

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(C 2="Yes",C1&"$","")&IF(D2="Yes",D1&"$","")&IF(E2="Y es",E1&"$",""),"$","",(COUNTIF(B2:E2,"=Yes")=0)+CO UNTIF(B2:E2,"=Yes")),"$",",")

Rick

Multiple returned values into a single cell
 

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

"Rick Rothstein (MVP - VB)" wrote:

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

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