1

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

Excel Sheet

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?

  • Pivot table? Otherwise, what have you tried? – Raystafarian Aug 27 '16 at 11:46
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.