View Question
 Question
 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. Thanks!``` 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 horses). --Maniac``` 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. Thanks!```
 Subject: Re: Excel Formula Answered By: maniac-ga on 29 Sep 2005 19:30 PDT Rated:
 ```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: =HorseList(A\$7:G\$7,A8:G8) (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. --Maniac 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 Else TheResult\$ = TheResult\$ & ", " & Names(1, i).Value End If End If Next i HorseList = TheResult\$ End Function```
 tnsdan-ga rated this answer: and gave an additional tip of: \$1.50 `How elegant:) Thanks!`

 ```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 formula. good luck.```
 ```You can also concatenate all the results of the formula by entering =G2&H2&I2 etc to concatenate the individual formulas```
 ```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. http://www.elbriovanner.com/gypsy-vanner-horses/how-to-buy-gypsy-vanner-horses5.htm```