Help a stupid old man PLEASE [SOLVED]

[thesponsor]

Hi everyone

I run a NFP club which runs a Fantasy Horseracing Competition. Some 190+ members each pay a sub and all the profits go to charity - last year some £2000. I have recently taken over as Honorary Secretary of the club and started to try to automate the system. So far I have managed through friends to get to the stage where I have a spreadsheet,

https://docs.google.com/spreadsheet/ccc?key=0AtpfWzIZgdk3dE51VGoybk9fajN3RWdyZkhSZEtpU0E

that shows a unique ID in column A, the members name in column B and their "stable" or selections in Columns C - N, there being twelve horses in their stable.

Now whilst no horse is repeated in any row, it will be repeated throughout the array c2-N196

I have fiddled around with pivot tables; countif; index and I'm now blind, much nearer death and generally having to accept that I no nothing.

How do I get a list of unique horsenames, which shows the unique IDs of those members who have selected that horse.

Thus for example

DOBBIN (GB): 2F - 7F - 22M - 49F - 101M

BOB THE HORSE (IRE): 44M

EXCEL WONDERHORSE (USA): 89F - 98F

I woulod be so grateful if someone could give me step by step instructions.

Many thanks

Nick

 

[xld]

If you change the format to just columns of Ref and Horse name, maybe one for choice #, the pivot would be easy.

 

[vijaySharma]

Hello theSponsor,


Welcome to Chandorg


If VBA and a bit of SQL is not an issue...


have a look at the file


https://docs.google.com/open?id=0BxRkrdCm95qbNksyVURnZE1iZkk


1. I created a separate sheet for all Unique Horses

2. Press ALT+F8 funtion key and then select the Macro called "UpdateMembers"

3. Hit Run


This will update the Members code against each of the Horse one by one (this will take some time)


HTH


~VijaySharma

 

[dan_l]

I'll take Discourse in the 3rd........


Is this something you're going to be doing once a week? Or is this something you just need once?

 

[SirJB7]

Hi, the sponsor!


How do members select horses and where from? I was wondering about setting data validation for each row in C:N and get list from precisely the unique horse list you're asking for, and additionally setting conditional formatting to prevent duplicates in each stable row... So it looks like a dog trying to bite his tail... Or I build the list from the stable, or I fill the stable from the list...


Maybe there's a web resource from where you can "get" the horse's names.


Regards!

 

[thesponsor]

Hi everyone - sorry got bogged down with real work and not this charity stuff!

So answers below


Dan_I: Just once a year.


Sir JB7: The Members select their own horses, which are checked against an excellent publication called "Horses in Training" and are entered on an Access Database. the database then delivers a report which is the one in the downloadable docs. Some of the Members own horses, so random selection is simply not an option. I can easily create a separate list of de-duplicated horses names - but where to go from there and is it a necessary step?


Vijay - I'll try it right now and report back


XLD: Let me try this macro first

 

[thesponsor]

Vijay

It may be that you are a genius!

I'm just playing with it at the moment and will report back further.

So far so good

N

 

[thesponsor]

Hi Vijay

Brilliant - small question. What happens when I need to do it again.

So everytime I produce that excel report always called combinedhorsesreport.xls do I create a macro and cut and paste the formulas in?

This really isn't my field at all.

Many many thanks

Nick

 

[vijaySharma]

Hello theSponsor,


when you need to work on this again you would need to create a list of Unique Horse list in Sheet 1.


Replace the old report data with the new one and simple run the macro again.


If your list of choices for horses is going to remain static (12 columns)currently then we can also write another SQL query to get the unique list automatically to be updated in sheet1 and then run the macro.


Do let me know if you need me to update the workbook with this and post again.


Also, thanks for your lovely comments above.


~VijaySharma