Formula for text string
OK, so I have a problem that I cant solve.
I have a sheet that (in the example) lists students and their assessment dates.
I have a column for student name, then columns for each assessment dates.
I want to build a single column that lists in date order each students "assessment date: assessment" as per the example below.
I'm open to using helper columns/rows and parent rows if required.
Does anyone have a solution or any ideas?
Cheers,
Adam.
Best Answer

Hey @Nick Korna,
Thanks for taking the time to answer my question. For a range of reasons you solution didn't work for me, but gave me enough ideas to build something that did work. Thank you.
The issues with your solution were:
 I had listed my columns as Assessment 1  Assessment 4 but these were just example names so the + ": Assessment " + MATCH(1, [As1Rank]@row:[As4Rank]@row, 0) syntax was never going to work in practice and the names of assessments varied from sheet to sheet.
 I thought that =VALUE(DATE(2024, 12, 31)  [Assessment 1]@row + "." + [As1Order]@row) was short sighted as it puts a hard limit on the usefulness of the solution.
 Same date assessments was the big problem when I was trying to work this out for myself but in practice, if they were on the same day it didn't matter which one came first.
I used and extended your ideas by:
 Adding more helper columns so
 ASNum 14
 ASNo 14
 ASRank 14
 Assessment 14
 I added a parent row and ancestors column
 In the parent row I
 Added 1,2,3,4 in the ASNo14 Columns
 Added column names to the parent row of the Assessment date columns
Then used the formulas:
ASNum 14:
=IF($Ancestors@row = 1, VALUE(Bananas@row  DATE(2000, 1, 1)), "")
ASNo 14:
=VALUE(RANKEQ([A1 Num]@row, $[A1 Num]@row:$[A4 Num]@row, 0) + "" + PARENT())
AS Rank 14:
=RANKEQ([A1 No]@row, $[A1 No]@row:$[A4 No]@row, 0)
Assessment 14:
=INDEX($Bananas$1:$Watermelon$1, 1, [A1 Rank]@row)
Assessment Dates:
=IF(Ancestors@row = 1, IF([Assessment 1]@row = "Bananas", Bananas@row, IF([Assessment 1]@row = "Apples", Apples@row, IF([Assessment 1]@row = "Pears", Pears@row, IF([Assessment 1]@row = "Watermelon", Watermelon@row)))) + ": " + [Assessment 1]@row + CHAR(10) + IF([Assessment 2]@row = "Bananas", Bananas@row, IF([Assessment 2]@row = "Apples", Apples@row, IF([Assessment 2]@row = "Pears", Pears@row, IF([Assessment 2]@row = "Watermelon", Watermelon@row)))) + ": " + [Assessment 2]@row + CHAR(10) + IF([Assessment 3]@row = "Bananas", Bananas@row, IF([Assessment 3]@row = "Apples", Apples@row, IF([Assessment 3]@row = "Pears", Pears@row, IF([Assessment 3]@row = "Watermelon", Watermelon@row)))) + ": " + [Assessment 3]@row + CHAR(10) + IF([Assessment 4]@row = "Bananas", Bananas@row, IF([Assessment 4]@row = "Apples", Apples@row, IF([Assessment 4]@row = "Pears", Pears@row, IF([Assessment 4]@row = "Watermelon", Watermelon@row)))) + ": " + [Assessment 4]@row + CHAR(10), "")
Answers

Hi @BullandKhmer,
You can do this with a bunch of hidden columns, but you will also need to add some for where you have assessments on the same date (such as Dave in the example):
Here the As1orderAs4order columns in yellow are only used if you have multiple assessments on the same day. If this is the case, you put the one you want FIRST with the the highest number and decreasing from there (so if all 4 assessments were on the same date, the first one would be 4 and the last would be 1, for example).
The green highlighted columns are then giving a number which can be used in a ranking.
The formulas for these are (using Assessment 1 in each, just change the assessment number in the first and the AsXNum reference in the second):
As1Num: =VALUE(DATE(2024, 12, 31)  [Assessment 1]@row + "." + [As1Order]@row)
As1Rank: =RANKEQ([As1Num]@row, [As1Num]@row:[As4Num]@row, 0)
If you have multiple years, then you can increase the date reference in the AsXNum formulas.
These are then used in the Assessment Dates column, with formula (you will need word wrap on for the column):
=INDEX([Assessment 1]@row:[Assessment 4]@row, 1, MATCH(1, [As1Rank]@row:[As4Rank]@row, 0)) + ": Assessment " + MATCH(1, [As1Rank]@row:[As4Rank]@row, 0) + " " + CHAR(10) + INDEX([Assessment 1]@row:[Assessment 4]@row, 1, MATCH(2, [As1Rank]@row:[As4Rank]@row, 0)) + ": Assessment " + MATCH(2, [As1Rank]@row:[As4Rank]@row, 0) + " " + CHAR(10) + INDEX([Assessment 1]@row:[Assessment 4]@row, 1, MATCH(3, [As1Rank]@row:[As4Rank]@row, 0)) + ": Assessment " + MATCH(3, [As1Rank]@row:[As4Rank]@row, 0) + " " + CHAR(10) + INDEX([Assessment 1]@row:[Assessment 4]@row, 1, MATCH(4, [As1Rank]@row:[As4Rank]@row, 0)) + ": Assessment " + MATCH(4, [As1Rank]@row:[As4Rank]@row, 0) + " " + CHAR(10)
Hope this helps, but if I've misunderstood something or you have any problems/questions then just let us know!

Hey @Nick Korna,
Thanks for taking the time to answer my question. For a range of reasons you solution didn't work for me, but gave me enough ideas to build something that did work. Thank you.
The issues with your solution were:
 I had listed my columns as Assessment 1  Assessment 4 but these were just example names so the + ": Assessment " + MATCH(1, [As1Rank]@row:[As4Rank]@row, 0) syntax was never going to work in practice and the names of assessments varied from sheet to sheet.
 I thought that =VALUE(DATE(2024, 12, 31)  [Assessment 1]@row + "." + [As1Order]@row) was short sighted as it puts a hard limit on the usefulness of the solution.
 Same date assessments was the big problem when I was trying to work this out for myself but in practice, if they were on the same day it didn't matter which one came first.
I used and extended your ideas by:
 Adding more helper columns so
 ASNum 14
 ASNo 14
 ASRank 14
 Assessment 14
 I added a parent row and ancestors column
 In the parent row I
 Added 1,2,3,4 in the ASNo14 Columns
 Added column names to the parent row of the Assessment date columns
Then used the formulas:
ASNum 14:
=IF($Ancestors@row = 1, VALUE(Bananas@row  DATE(2000, 1, 1)), "")
ASNo 14:
=VALUE(RANKEQ([A1 Num]@row, $[A1 Num]@row:$[A4 Num]@row, 0) + "" + PARENT())
AS Rank 14:
=RANKEQ([A1 No]@row, $[A1 No]@row:$[A4 No]@row, 0)
Assessment 14:
=INDEX($Bananas$1:$Watermelon$1, 1, [A1 Rank]@row)
Assessment Dates:
=IF(Ancestors@row = 1, IF([Assessment 1]@row = "Bananas", Bananas@row, IF([Assessment 1]@row = "Apples", Apples@row, IF([Assessment 1]@row = "Pears", Pears@row, IF([Assessment 1]@row = "Watermelon", Watermelon@row)))) + ": " + [Assessment 1]@row + CHAR(10) + IF([Assessment 2]@row = "Bananas", Bananas@row, IF([Assessment 2]@row = "Apples", Apples@row, IF([Assessment 2]@row = "Pears", Pears@row, IF([Assessment 2]@row = "Watermelon", Watermelon@row)))) + ": " + [Assessment 2]@row + CHAR(10) + IF([Assessment 3]@row = "Bananas", Bananas@row, IF([Assessment 3]@row = "Apples", Apples@row, IF([Assessment 3]@row = "Pears", Pears@row, IF([Assessment 3]@row = "Watermelon", Watermelon@row)))) + ": " + [Assessment 3]@row + CHAR(10) + IF([Assessment 4]@row = "Bananas", Bananas@row, IF([Assessment 4]@row = "Apples", Apples@row, IF([Assessment 4]@row = "Pears", Pears@row, IF([Assessment 4]@row = "Watermelon", Watermelon@row)))) + ": " + [Assessment 4]@row + CHAR(10), "")
Help Article Resources
Categories
Check out the Formula Handbook template!