Excel 2007 : A need for multiple formulae none of which I understand

[The Sponsor]

I have an Excel worksheet which has in:
Column A: A List of unique IDs
Column B: Names of the Members
Columns C - N: A horse's name. Each horse's name is unique by row, but not overall. Thus
Row 1 is heading
Row 2 might read: 1F - Adam - Bobs Dog - Bill's Horse - Cat - Dog - Bee - Frankel - Cow - Ant - Zoo - Ann - Sue - Paul
Row 3 might read: 2F - Brian - Cat - Stoat - Dog - Badger - Cod - Ryan - Cow - Ant - Zoo - Sue - Ann - Crow
So what i want to achieve is an index showing all the UNIQUE horse's names and the "owners" unique ID beside them.
Thus
Bobs Dog: 1F
Bill's Horse: 1F
Cat: 1F: 2F
etc etc
The horse's names are in alphabetical order by row.
I can and have created manually a master list of Unique horses names, by cutting and pasting all the horses name's into a single alpha-ordered column and then removing duplicates.
How do I make my life quicker and easier
Many Thanks
The Sponsor

[Søren Larsen]

I think this sounds like a good case for a pivot table. Go to "Insert" (ribbon) and choose "PivotTable".

[The Sponsor]

Thanks for that Soren, but I really struggled to understand how I could sort the information correctly.
Effectively I have some 200 rows each with a unique ID, a username and twelve horses. Each "stable" of twelve horses, is froma list of some 1000+ horses.
All (and I use the word advisedly!)I want to do is have that list of horses show the unique ID of each member who picked the horse beside the name.

[Andy Pope]

Here is how to use the INDEX function to string out the horse names into a single column.

[The Sponsor]

Hi Andy
That was where I got to once before - after much reading! But here's the thing.
From the example there are 24 horses (2 rows times 12 horses). However there are only 17 UNIQUE horses, Seven are duplicated. So how do I get to the stage of having
HORSE NAME 1: 1F: 33M: 44F: 122M (or whatever the unique IDsS might be?)
HORSE NAME 2: 14M
HORSE NAME 3: 17F: 34M
Thanks for the help
N

[The Sponsor]

Getting increasingly desperate now!
Here is a screen shot that might explain things better.
Capture 1.jpg
Col A is a unique ID (Col B is irrelevant)and Cols C-N each contain a horses name.
Row 1 is a header
Row 2 and on are all unique. No row has a horse name repeated within that row.
I can create if it helps a horse list with no repetitions.
What I desperately need to do id have a complete formula that tells Excel to take eacc horse and tell me which people (or Unique IDs) have it in their "stable"or horizontal list of 12 horses.
So I end up with in another sheet or somewhere else on the page
a list of unique horses in column A and a list of Unique Ids of those people who have selected that horse.
It might look like
CAMELOT (GB) 2F 4M 45F 88F 102M
BILL'S HORSE (IRE) 6M
JOHN'S PEARL 34M 72F 74M
Please help if you can.

[Andy Pope]

There are some more formula in the attached to get unique list of horses.
If you copy paste/transpose the ID column you can create a matrix of owners.

Or you could simply use the strung out list as data for a pivot table and build a table of Horse/IDs

[The Sponsor]

Sorry didn't know how to attach a file

[arlu1201]

The Sponsor,

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

[The Sponsor]

Sorry - does the new title comply?
The trouble is I have no idea what my problem is - other than I don't know. It has something to do (possibly - but only according to some) with index or maybe its pivot table. And of course the more I try to explain the more complicated it gets.
So my apologies again, but I'm not a forum expert nor indeed an excel anything.

[arlu1201]

To Attach a File:

1. Scroll down to the window below your post Additional Options
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.