Google Answers Logo
View Question
Q: Excel Formula ( Answered 5 out of 5 stars,   3 Comments )
Subject: Excel Formula
Category: Computers
Asked by: tnsdan-ga
List Price: $10.00
Posted: 28 Sep 2005 07:45 PDT
Expires: 28 Oct 2005 07:45 PDT
Question ID: 573710
I'm not sure if Excel can do this, I don't have much experience doing
this sort of thing, but I figured I would throw it out there...  I
have a bunch of horses that I am showing to prospective buyers.  For
the sake of this example, lets say there are 5 (although there are
actually dozens, which I why I would like the spreadsheet to be able
to do this work).  Not every buyer is going to look at every horse.  I
would like to be able to make a chart like the one below, where the
last column automatically provides me with a list of the horses that a
buyer looked at.  Ie:

		Horse 1	Horse 2	Horse 3	Horse 4	Horse 5	List
Buyer1		Yes	No	No	No	Yes	Horse 1, Horse 5
Buyer 2	        No	Yes	Yes	No	No	Horse 2, Horse 5
Buyer 3	        Yes	No	No	Yes	No	Horse 1, Horse 4

Step by step instructions on how to set this up will be needed.


Request for Question Clarification by maniac-ga on 28 Sep 2005 20:12 PDT
Hello Tnsdan,

If you are satisfied by the su420man's comment, I suggest you close
the question. That way, you won't be charged for an answer.

If not, please make a clarification request to indicate what more you need.

For example, I can suggest a way to make the spreadsheet appear
exactly as you described it without any visible cells of intermediate
values, etc. The solution would also be expandable (when you add more


Clarification of Question by tnsdan-ga on 29 Sep 2005 06:04 PDT
First, thank you su420man for the comments.  This certainly did the
trick, although I had a feeling that there was perhaps a more
"elegant" solution.  So, to Maniac, please feel free to answer the
question.  I am definitely going to need to add horses without
rewriting the formulas at the end.

Subject: Re: Excel Formula
Answered By: maniac-ga on 29 Sep 2005 19:30 PDT
Rated:5 out of 5 stars
Hello Tnsdan,

I created a short Word Basic function that does what you ask in an "elegant" way.

To add the function to your spreadsheet, do the following steps
starting with the spreadsheet open in Excel:

[1] Use the menu item Tools -> Macro -> Visual Basic Editor

At this point, if you already have macros, a "module window" may be
displayed and the next step is not required.

[2] In the upper right, you should have a "project" window that shows
the name of your spreadsheet. Select that row and then use the menu
item Insert -> Module. At this point, a module window should appear
and the module is added to your worksheet in the project window.

[3] Copy / paste the function (at the end of the answer) into the module window.

[4] Return to Excel using the "Close and Return to Microsoft Excel" menu item.

I had the horse data in cells A7 through F10. In Cell G8 (the first
row with yes / no answers), I entered the following formula:
(note that the formula refers to all cells in a row including the
buyer and result cells)
Adjust the references above (A$7:G$7 and A8:G8) to the cells of the
horse names and the cells of the yes / no values in your spreadsheet.
The dollar signs in the first reference force the row number to be
unchanged when the formula is copied down.

If you get the expected answer, you can now copy down this formula to
the remaining rows.

Inserting new horses can occur in any column after the first column
and before the result column. The function will automatically adjust
for the new columns.

If any part of the answer is unclear or you need additonal features,
please make a clarification request. I would be glad to help further.


Function HorseList(Names, YesNo) As String
    TheResult$ = ""
    For i = 2 To Names.Count - 1
        If StrComp(YesNo(1, i).Value, "yes", 1) = 0 Then
            If TheResult$ = "" Then
                TheResult$ = Names(1, i).Value
                TheResult$ = TheResult$ & ", " & Names(1, i).Value
            End If
        End If
    Next i
    HorseList = TheResult$
End Function
tnsdan-ga rated this answer:5 out of 5 stars and gave an additional tip of: $1.50
How elegant:)  Thanks!

Subject: Re: Excel Formula
From: su420man-ga on 28 Sep 2005 08:16 PDT
This is a commnet but will satisy your question. You have it answered for free :)
Instead of giving a one step answer in excel at times it is better to
break such things into smaller tasks. Do the following:
1. Enter the formula: =IF(B2="Yes",B$1&",","") in the FIRST row ONCE
at the very end of the horse list. What this formula does is looks at
the value of B2 cell and if it yes, puts the value of B1 cell with an
appended comma, else puts blank. NOTE WHEN I SAY FIRST ROW, it means
the first row of yes and NOs.
2. Copy the formula in THAT ROW ITSELF as many times as you had
horses. So if you had 12 horses , copy the above CELL and paste it is
11 columns after the last column ONLY on the first ROW.
3. Now copy the above row of FORMULAS in as many ROWS as you have.

Play around by changing a few things and you will be expert at using
such formulas. Try to figure why I put B$1 and not B1 in the above

good luck.
Subject: Re: Excel Formula
From: su420man-ga on 28 Sep 2005 08:18 PDT
You can also concatenate all the results of the formula by entering
=G2&H2&I2 etc to concatenate the individual formulas
Subject: Re: Excel Formula
From: gypsyvaneerhorse-ga on 06 Oct 2005 08:41 PDT
There is no exact formula. However, following information may be of use to u.

Gypsy Vanner Horses 

Vanner Horse Origins:

Over a half a century ago, a vision was born to create a very special
horse. A magical, almost mythical , colorful, compact, small Draft
horse. A horse powerful enough to pull the decorative caravans the
Gypsy calls home, yet gentle and trustworthy for the keeping of their
children and worldly possessions. The selective breeding of a few
dedicated Gypsies has produced what is now becoming known as the Gypsy
Vanner Horse.

The horse breeds utilized for the foundation of their new breed,
Clydesdale, Shire, Friesian and Dales Pony each have imparted some
traits and characteristics individual to their heritage. All of them
combined have contributed to the long flowing hair characteristics
that the Gypsy Vanner Horse will be remembered by. A true Gypsy Vanner
Horse will have feathers emanating from the knees in the front and
just below the hocks in the rear, long flowing manes, forelocks and
tails that will drag on the ground.

Important Disclaimer: Answers and comments provided on Google Answers are general information, and are not intended to substitute for informed professional medical, psychiatric, psychological, tax, legal, investment, accounting, or other professional advice. Google does not endorse, and expressly disclaims liability for any product, manufacturer, distributor, service or service provider mentioned or any opinion expressed in answers or comments. Please read carefully the Google Answers Terms of Service.

If you feel that you have found inappropriate content, please let us know by emailing us at with the question ID listed above. Thank you.
Search Google Answers for
Google Answers  

Google Home - Answers FAQ - Terms of Service - Privacy Policy