Excel - help with a formula / lookup

[eyetea]

Hi all,

 

Trying to assist with our activities week planning! Finance created a spreadsheet that was unusable for the people trying to plan the week - I've tidied it up and sorted it out so it can give them a bit of clearer information, they're able to sort the data based on names / preferences. Table looks similar to below (Just with >650 students and >30 activities!)

 

 

I'm currently trying, and struggling, to create another table that will display the students name and the names of their chosen activities based on preference, as per below (data entered manually for the example!) so they can have a clear list of who's preference is what!

 

 

Any help would be gratefully appreciated!

[ThomL]

For each student is there a priority assigned for all available activities or are there a set amount e.g. each student picks 5 of the 30 and then ranks based on their preference?

[LeeCSE]

I think I understand what you are trying to do and this should work, though someone who knows Excel better than me might have a cleaner way:

 

=IF(B2=1,"A1",IF(C2=1,"A2",IF(D2=1,"A3",IF(E2=1,"A4",IF(F2=1,"A5")))))

 

=IF(B2=2,"A1",IF(C2=2,"A2",IF(D2=2,"A3",IF(E2=2,"A4",IF(F2=2,"A5")))))

 

=IF(B2=3,"A1",IF(C2=3,"A2",IF(D2=3,"A3",IF(E2=3,"A4",IF(F2=3,"A5")))))

 

If it helps PM me and I'll send you a excel file showing this.

[Seb1780]

INDEX / MATCH is your friend here

 

There's a good instruction here https://www.deskbright.com/excel/using-index-match/

[ThomL]

That'll be a long formula, 30 nested if statements

 

- - - Updated - - -

 

INDEX / MATCH is your friend here

 

There's a good instruction here https://www.deskbright.com/excel/using-index-match/

 

This is the way i was going to approach it too

[eyetea]

Thanks for all the ideas - in the end I got my own back on the head of computing and sent him a help request!

 

Final formula (which is essentially doubled to put in blanks rather than N/A) - =IF(ISNA(OFFSET(DATA!$B$1,0,MATCH(E$1,DATA!$C22:$AS22,0))),"",OFFSET(DATA!$B$1,0,MATCH(E$1,DATA!$C22:$AS22,0)))

[pcstru]

A simple solution is just to use Index (or even Vlookup), so assuming the two lists are in the same order of students, the cells in the second table can just be

 

Index( , preference cell in table 1);

 

This should work because the integers recorded as the preference are the offset to the activity itself in the header.

Edited March 23, 2017 by pcstru

[ThomL]

I followed through with the Index/match as it seemed simpler and easier to read/understand whats happening.

 

Here's the code I used to perform the lookup/remove N/A results:

=IFNA(INDEX(ActivityListData!$B$1:$K$1,MATCH(1,ActivityListData!B2:K2,0)),"")

 

Link to working example: http://www.filedropper.com/indexmatchdemo

 

Its worth pointing out that the preference value match is static (value following match) this was based on the way I had setup the spreadsheet - For production use i'd probably just reference the header cells and validate integers only during data population or something similar. This would leave the only static reference as the title/headers of the cells for the activities which i don't think is too bad?

Edited March 23, 2017 by ThomL