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 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), "")
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 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!
-
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), "")
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!