Conditional concatenate across rows and columns for concatenate

Question

I have an attendance excel sheet that looks like this. Each student is a row, each class date is a column. I have 48 classes in all, 66 students in all (Putting this in because complexity of solution must be feasible).

Now, For each row, ie each student, I want to know the dates of classes missed. For student 6, I want "9,19,23" to be displayed in the last cell in that row.

For each row, I want the Roll numbers of students that missed the classes that day. For date= 24, I want "7,8,9,10" to display in the last cell in that column.

How do I do this conditional concatenate without hard coding values for each cell? Is there some range formula I can use?

Answer 1

The following two formulas return the desired results:

Rows:

=LEFT(CONCAT(IF(B6:L6="a",(B1:K1&","),"")),LEN(CONCAT(IF(B6:L6="a",(B1:L1&","),"")))-1)

Columns:

=LEFT(CONCAT(IF(L2:L11="a",(A2:A11&","),"")),LEN(CONCAT(IF(L2:L11="a",(A2:A11&","),"")))-1)

Now that's done with functions available in 2016. (I cannot test this part, but it might've been necessary to make them array formulas using {CSE} entry.)

Obviously they are similar. The idea is to test the row (or column) for the value sought ("a" in this case) using a range as the base for the IF() test. Return "" if FALSE, and the tricky part... how to return the values AND put commas in between them?

Curiously, if you specify the range to draw your output from AND add &"," to the end of it, you do NOT get something like 91923, but rather Excel understands you'd like the comma added after every return. Two things: You have to put that string in parentheses or Excel can misunderstand what you are trying for. And the reason you don't get multiple commas in a row (like ",,9,,,,,19,23,,") is that the construction gives only returns for TRUE results so all the blanks that would lead to "blank," entries showing consecutive commas simply don't happen.

You DO end up with a comma at the end though, hence the LEFT() that doubles the formula length, but does strip off that last comma.

Another interesting trick is not specifying the test range as the range for output but rather specifying the column (or row) headers as the output so that you get the desired output directly.

Nowadays (ultra-modern 2021...) we have TEXTJOIN() and SPILL functionality. So definitely no {CSE} needed, and just a simple formula because TEXTJOIN() will not have a comma at the end:

=TEXTJOIN(",",TRUE,IF(B6:L6="a",B1:L1,""))

In this one, those blank returns it seems could occur DO occur so you definitely need to specify TRUE for the second parameter in TEXTJOIN() so as to keep from having the consecutive commas problem. You still need the test in order to decide which of the header cells are to return a value and which are not though. For the columns:

=TEXTJOIN(",",TRUE,IF(L2:L11="a",A2:A11,""))

Again, obviously very similar to the one for the rows.

Answer 2

Okay, this is kinda messy, but it works as long as you're willing to make and hide a bunch of extra columns.

Basically, the idea is to create a whole grid of IF statements (one for each cell in the main attendance data), and then hide all of those columns and concatenate their values into one column.

To the right of your main data, in a cell in the second row (the line for the first student) add the following formula:

=IF(B2="a",B$1&",","")

What this is doing is saying that if the student in this row was absent on the given day, write the day here, otherwise leave it blank. (The $ in B$1 is essential because this will keep the output looking at the first row, even in copy/pasted versions of the formula.)

Then, copy/paste that formula into a full range of cells equal to the number of cells of attendance data (so if the attendance data is 48 by 66 cells, then the IF statements should also fill 48 by 66 cells).

Now you should see 48 columns of mostly blank cells, but with some cells filled in to match days the students were absent.

Then, in a column to the right of all of that, in the second row, add a formula kinda like this:

=CONCATENATE(X2,Y2,Z2,...etc...)

Replace X2,Y2,Z2,etc with the cells of the IF statements (you can do this quickly by clicking on each cell, then hitting the comma key).

When you press enter, that cell should look something like this: 6,13,18,

Fill this formula down the sheet to account for every row/student.

Now, if the trailing comma is too painful to look at, you can create another column to look at the concatenate column and return all of the characters of that column except for the last one.

=IF(LEN(X2)>0,LEFT(X2,LEN(X2)-1),"")

Replace X2 with the cell that has the concatenated string. (Including IF(LEN(X2)>0 makes it so that you don't get an error if the student had perfect attendance.

Then just hide all the columns except for the final one and you're good to go!

Alternatively, I think VBA has a concatenateif function that would help, but I don't know how to use it.