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!

[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.

[binu_acs]

@brightonle can you provide the output based on your input file for better understanding?

[brightonle]

I want to concate the three columns "Present" "Absent" and "Not Scheduled" (three columns from the date column) and create a key that would either be "100" "010" "001" "000". With this it will determine the attendance. For the first contact. On 7/1/2024 if you add the columns up it would be "Absent" =  "001" "000". On 7/2/2024 he would be "Present" = "100" and so on. I attached a new input data. 

[binu_acs]

@brightonle can you also upload the expected output from your input file, im still trying to understand your requirement.