Competition conclusion sheet help

**hmekk** · 05-16-2013, 06:19 PM

Hi,

I've been trying to do this competition sheet for this weeks (weekend) competition but i have some troubles with conclusion sheet.

In conclusion sheet there are two tables - one is more of a overall and other is more in depth.

What I need.

table on the left:
select all competitors name+ last name and fill in the table with all competitors names.
Sum of comp points in data sheet between competitions for each name (first and second competition)
Rank the competitors based on competition pints (if the sum is equal for two competitors then its should place first the one who's overall weight is bigger)

Table on the right:
select all competitors name+ last name and fill in the table with all competitors names.
fill in comp points for each competition (first and second)
Fill in weight for each competition (first and second)
sum both of those in relevant column
Rank the competitors based on competition pints (if the sum is equal for two competitors then its should place first the one who's overall weight is bigger)

i have messed with this table for 3 days and googled myself to nuts yet i cannot find a way to do this. Can this be even done with just commands? or this should be done in VBA?

All help is appreciated as timeline is kicking in and competition is in few days.
I have also attached the relevant excel table.

Also - data sheet has already been filled and in conclusion sheet left table has also been manually filled to elaborate how it should look like.

Kevadsärg_1_11.xls

**JBeaucaire** · 05-16-2013, 07:30 PM

I almost get what you want. Can you complete your example by filling in the answers from the sample data? Seeing the end goal lets is know when we get it right.

If you need to reduce your sample data to less rows to make it easier to mock up fully that's fine, just don't reduce too far.

**FDibbins** · 05-16-2013, 07:45 PM

just *dont* reduce it too far?

**hmekk** · 05-17-2013, 02:38 AM

Not sure how to rezise more

as it does now.
filled in the right table also.

I just has 19 names.

attahched the latest excel.Kevadsärg_1_11_12.xls

**humdingaling** · 05-17-2013, 04:26 AM

Pivot table x 2 (filter on name and blank) and sumifs

if you want to expand to sector d
may need to reshuffle the pivot table a little bit as well as the formulas

**hmekk** · 05-17-2013, 04:50 AM

Hi,

it kinda fits - yet it doesn't fill in the competitors names.

Edit: also when i add names to the 1 comp then it goes haywire. yet when i add them to the second one everything is OK ...

**humdingaling** · 05-17-2013, 09:15 AM

ok to fill in competitors names
assign number 1-19 on the left of the first pivot table (outside the pivot table itself)
then you can do vlookup against first name and surname

adding of names...i dont quiet understand

i do admit this method isnt really robust to add people i, as you would need to rejig the pivot table and possibly change formulas on conclusion page for it to work
however it is simple enough in order to do that it shouldnt take very long to do so