expanding list in same column

[FreakyGirl]

Hello

i have an event coming up where the number of players on each team wont be the same.
60 employees - wont increase once games have started.
12 bosses - may increase once games have started.

to compensate, one team (bosses) will keep advancing players - win or lose - while the other team (employees) only advances the winners.

eventually the gap between the 2 teams will lessen & there will be 1 employee left to play against whatever boss is up next in the rotation for a final game.


i think the solution would be to to keep 2 dynamic lists (one for each team) to keep track automatically based on a 'w' or 'l' in the column between the 2 teams.

if a 'w' is there, then both the employee AND the boss move under the last listed name in the appropiate column.

if a 'l' is there only the boss moves under the last listed name in the boss column

employee example: i shorted the number of names so it wasnt to long but the idea is the same. full example attached.

ann
betsy
w carol
diane
evelyn

since carol won she would show up under evelyn (last name listed) once the 'w' was put by her name.

w ann
betsy
w carol
diane
evelyn
carol

next ann won & she would show up under carol once i put the 'w' next to her name

w ann
betsy
w carol
diane
evelyn
carol
ann

now evelyn had a loss so she will not continue but the boss who played her will still move down as did the other bosses.

w ann
betsy
w carol
diane
l evelyn
carol
ann

bosses

zach (played ann)
yohan
xavior (played carol)
walter
victor (played evelyn)
uri
thomas
stan
randy
quincey
peter
oscar
xavior
zach
victor

im not sure what forumla to use in the first available cell (A29 & D15) & dragdown based on example on worksheet) in either column to produce these results.

perhaps there is a better way?

thanks in advance for any help,
FreakyGirl

 

[NARAYANK991]

Hi ,

Probably if you upload your workbook with all of this data in it , answers may be more easily given , especially since you are talking of cell references such as A29 and D15.

Narayan

 

[FreakyGirl]

LOL sorry about that.

file attached now.

 

[NARAYANK991]

Hi ,

First let us discuss the data.

You mentioned in your initial post that there would be 60 employees ; the uploaded workbook does not have all 60. Let us assume that there will be 60 , in the range B3:B62.

The list of bosses is in the range D3:D14.

We should keep these as the master lists , and the transaction data should be kept in a separate place ; if the names will all be unique , we can use the names themselves , otherwise we should give each name a unique ID , and use these unique IDs in the transactions.

Working with the lists in situ and having the transactions alongside is not going to make it easy to come up with the right formulae.

Let us wait and see if others can come up with a solution using the existing structure.

Narayan

 

[FreakyGirl]

Hello

thank you for your reply.

i didnt put in all the names to keep it small for testing purposes.

in following your thought over keeping the data in B C D as master lists...
what about continuing the rotations in 3 new columns (G, H, I)?

since D15 would be the first cell with no name, put a 'none' in C15 which would trigger the the name in B15 to go to G3...
the name in B16 would go to G4
the name in B17 would go to G5 & so on.

as the W/L is marked in the C column the winning employees would move to the first available space in G column & the bosses would move to I column.

i updated the file to show the above. the gray names in G are those who havent had a chance to play cause not enough bosses & the pinkish names have moved on from the previous round.

this process would be continued until the final round.

would that be easier?

thanks,
FreakyGirl