Concate Batch Rows

brightonle

Hello! I was working on trying to format a sheet and got stuck on how to concate. I want to concate a batch of 4 rows the date "Present" "Absent" "Not scheduled", so every 4 rows will be added to one column which would end up being the date. Then I want the value to be changed to "Present" "Absent" "Not scheduled" depending on the concated number in the row. So for example if the column is "100" it would be "Present", ""010" "Absent", and "001" "Not Scheduled". Thanks!

CONCATE-ATTENDANCE.xlsx

Concate-Attendance.yxmd

TUSHAR050392

Hey @brightonle , I think it is a bit tricky to achieve but you can follow below steps and see if it works for you.

1. Use Transpose tool, select Program and Student as key columns and rest everything as data column.

2. Then Use Multi-row formula, update existing field and select Name. Then use the formula in expression - IF RIGHT(Name,4) = '2024' then Name ELSE [ROW-1: Name] ENDIF.(If you have more years data like 2023, then you can use OR statement RIGHT(Name,4) = '2024'  OR RIGHT(Name,4) = '2023' .

3. Use summarize tool, group by Program, Student, Name and concatenate by value (make sure to remove comma from separator).

4. Use a formula tool to put an if else condition which will be if RIGHT(Value,3) = "100" it would be "Present", ""010" "Absent", and "001" "Not Scheduled"

Hope this helps.