Formula for text string

BullandKhmer

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.

BullandKhmer

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 1-4
  • ASNo 1-4
  • ASRank 1-4
  • Assessment 1-4
• I added a parent row and ancestors column
• In the parent row I
  • Added 1,2,3,4 in the ASNo1-4 Columns
  • Added column names to the parent row of the Assessment date columns

Then used the formulas:

ASNum 1-4:

=IF($Ancestors@row = 1, VALUE(Bananas@row - DATE(2000, 1, 1)), "")

ASNo 1-4:

=VALUE(RANKEQ([A1 Num]@row, $[A1 Num]@row:$[A4 Num]@row, 0) + "" + PARENT())

AS Rank 1-4:

=RANKEQ([A1 No]@row, $[A1 No]@row:$[A4 No]@row, 0)

Assessment 1-4:

=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), "")

Nick Korna

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 As1order-As4order 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!

BullandKhmer

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 1-4
  • ASNo 1-4
  • ASRank 1-4
  • Assessment 1-4
• I added a parent row and ancestors column
• In the parent row I
  • Added 1,2,3,4 in the ASNo1-4 Columns
  • Added column names to the parent row of the Assessment date columns

Then used the formulas:

ASNum 1-4:

=IF($Ancestors@row = 1, VALUE(Bananas@row - DATE(2000, 1, 1)), "")

ASNo 1-4:

=VALUE(RANKEQ([A1 Num]@row, $[A1 Num]@row:$[A4 Num]@row, 0) + "" + PARENT())

AS Rank 1-4:

=RANKEQ([A1 No]@row, $[A1 No]@row:$[A4 No]@row, 0)

Assessment 1-4:

=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), "")