

Subject:
Excel Formula
Category: Computers Asked by: tnsdanga 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!  
 


Subject:
Re: Excel Formula
Answered By: maniacga 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 
tnsdanga
rated this answer:
and gave an additional tip of:
$1.50
How elegant:) Thanks! 

Subject:
Re: Excel Formula
From: su420manga 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 formula. good luck. 
Subject:
Re: Excel Formula
From: su420manga 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: gypsyvaneerhorsega 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. http://www.elbriovanner.com/gypsyvannerhorses/howtobuygypsyvannerhorses5.htm 
If you feel that you have found inappropriate content, please let us know by emailing us at answerssupport@google.com with the question ID listed above. Thank you. 
Search Google Answers for 
Google Home  Answers FAQ  Terms of Service  Privacy Policy 