Scheduling List - Need to Remove Blank Rows

[Snagrodla]

Good morning everyone. I need some help with putting together a scheduling list for training. I've been working on it for days to no avail. I just can't get my head around how Arrays, Index, Small, Row works in order to create the results I need. I have a large spreadsheet with the data (I'm attaching a small version of some of the info in generic terms). Sheet 1 has my data. What I need to do is take everyone from Column F and place them into their corresponding Group on Sheet 2. But when I use my formula, it comes out looking like Sheet 2 but I would like it to look like Sheet 3. Is there a way to do this? I would also love an explanation of how and why to set it up the correct way if possible. Thanks in advance!

The formula I used is =IF(F3="Group 1 - Class 1",A3,"")

[AliGW]

Welcome to the forum.

Please update your forum profile - your MS Office version is not New Jersey! Thanks.

[Snagrodla]

Sorry about that, all fixed now! Thanks.

[bluesky63]

Hi Snagrodla

Group 1
=IFERROR(IF(ROWS(E$17:E17)>COUNTA($A$17:$A$27),"",INDEX(A:A,SMALL(IF($A$17:$A$27<>"",ROW($A$17:$A$27)),ROWS(E$17:E17)))),"")
Group 2
=IFERROR(IF(ROWS(F$17:F17)>COUNTA($B$17:$B$26),"",INDEX(B:B,SMALL(IF($B$17:$B$26<>"",ROW($B$17:$B$26)),ROWS(F$17:F17)))),"")
Group 3
=IFERROR(IF(ROWS(G$17:G17)>COUNTA($C$17:$C$26),"",INDEX(C:C,SMALL(IF($C$17:$C$26<>"",ROW($C$17:$C$26)),ROWS(G$17:G17)))),"")

These are array formula, press CTRL-SHIFT-ENTER and drag down for each column

[Fluff13]

As you have 365, you may have the dynamic array functions. If you do, here is another option
In A17
=FILTER(Table1[Colulmn A],Table1[Colulmn F]=A16&" - Class 1")
then for b17, just change the A16 in the formula to B16 & similar for C17

[Snagrodla]

As you have 365, you may have the dynamic array functions. If you do, here is another option
In A17
=FILTER(Table1[Colulmn A],Table1[Colulmn F]=A16&" - Class 1")
then for b17, just change the A16 in the formula to B16 & similar for C17

Thank you, I think this worked for me!!!

[Snagrodla]

Hi Snagrodla

Group 1
=IFERROR(IF(ROWS(E$17:E17)>COUNTA($A$17:$A$27),"",INDEX(A:A,SMALL(IF($A$17:$A$27<>"",ROW($A$17:$A$27)),ROWS(E$17:E17)))),"")
Group 2
=IFERROR(IF(ROWS(F$17:F17)>COUNTA($B$17:$B$26),"",INDEX(B:B,SMALL(IF($B$17:$B$26<>"",ROW($B$17:$B$26)),ROWS(F$17:F17)))),"")
Group 3
=IFERROR(IF(ROWS(G$17:G17)>COUNTA($C$17:$C$26),"",INDEX(C:C,SMALL(IF($C$17:$C$26<>"",ROW($C$17:$C$26)),ROWS(G$17:G17)))),"")

These are array formula, press CTRL-SHIFT-ENTER and drag down for each column

Thank you for responding! I'm still trying to get this one to work for me. when I copied it into my real spreadsheet, which has 230 rows, not all the names were displayed that should've been displayed. I'm going to keep trying it though since I'm sure it was me having the change the row letters and numbers to get it to work on the real sheet! Thank you again!

[Fluff13]

Thank you, I think this worked for me!!!

You're welcome & thanks for the feedback.