Returning Formula without blank cells

[Scott93m]

I am working on creating a list of students who have not turned in an assignment by staff adviser. I want a list that says Beth, here are your students who have not turned in the assignment. I can get a formula to return the students name by owner, HOWEVER, when a line of data doesn't return a result I get blank cells between the last item returned and the next line that returns data.

In this example I simply want Emiliano to fall in the cell directly below Shadai. However, there is a blank cell below Shadai. How do I prevent these blank cells from appearing? I only want to have cells that are filled in.

=IF(AND($AC4=$U$1,$AH4=U$24),CONCATENATE($AD4," ", $AE4),"") I use this formula to return students names by staff adviser. However, when I drag this formula down it will return cells that do not have text/values as blank cells. I want the list to delete all blank cells. How do I do that?

 

[Aryatect]

Hi Scott93m,

Can you please share a sample data here ?

Thanks,

 

[Scott93m]

Hi Scott93m,

Can you please share a sample data here ?

Thanks,

Yes, I'm not sure how to upload a file/picture though. Do you know how to do this? The pictures need a URL and I don't think mine have a URL because they are on my desktop.

 

[Aryatect]

I am also learning all the stuff here , but you can use sites like dropbox to share the pic. or you can use addins like "MrExcel HTML Maker", more details are given in Guidelines for Forum use.

You can also try to post some dummy data directly, but that is not recommended as it will be distorted.

 

[Scott93m]

I am also learning all the stuff here , but you can use sites like dropbox to share the pic. or you can use addins like "MrExcel HTML Maker", more details are given in Guidelines for Forum use.

You can also try to post some dummy data directly, but that is not recommended as it will be distorted.

Let's see if this worked. What I want to do is create a formula that will not have blank cells (the x's in the picture). It will just list the names of students missing their assignments without blanks in between students.

 

[Scott93m]

 

[Scott93m]

I'm not sure what's going on as far as how to upload pictures! I upload them to the MrExcel HTML maker and then past the URL above the picture and it doesn't seem to be attaching?

 

[Aryatect]

For testging Please Test here

 

[Scott93m]

Okay so what I'm hoping to do is return a list of students missing their assignments, grouped by the CWSP owner. So I want a list that has the students each CWSP Owner needs to talk to about missing assignements. Below is a quick example of my results (with the data set at the bottom).

I've been using If(AND(A2="Missing",D2="Scott"), Concatenate (B2, C2). The results I'm getting follow the forumla, the problem I'm running in to is that I get the results and there are 5 rows between Shadai Anderson and Jose Ignacio Cabrera. I just want a list with no empty rows in between results. Does that make sense? Thank you in advance for all the help!

Scott:
Shadai Anderson
Jose Ignacio Cabrera

Vianney:
Rene Castellanos

[TABLE="width: 404"]
<colgroup><col span="4"></colgroup><tbody>[TR]
[TD]Missing?[/TD]
[TD]FName[/TD]
[TD]LName[/TD]
[TD]CWSP Owner[/TD]
[/TR]
[TR]
[TD]Missing[/TD]
[TD]Shadai[/TD]
[TD]Anderson[/TD]
[TD]Scott[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Cheyanne[/TD]
[TD]Apodaca[/TD]
[TD]Vianney[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Emiliano[/TD]
[TD]Arellano[/TD]
[TD]Beth[/TD]
[/TR]
[TR]
[TD]Missing[/TD]
[TD]Evelyn[/TD]
[TD]Arellano Mendiola[/TD]
[TD]Rick[/TD]
[/TR]
[TR]
[TD]Missing[/TD]
[TD]Kevin[/TD]
[TD]Armendariz[/TD]
[TD]Eve[/TD]
[/TR]
[TR]
[TD]Missing[/TD]
[TD]Kevin[/TD]
[TD]Borunda Granillo[/TD]
[TD]Sonia[/TD]
[/TR]
[TR]
[TD]Missing[/TD]
[TD]Jose Ignacio[/TD]
[TD]Cabrera[/TD]
[TD]Scott[/TD]
[/TR]
[TR]
[TD]Missing[/TD]
[TD]Rene[/TD]
[TD]Castellanos[/TD]
[TD]Vianney[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Edgar[/TD]
[TD]Cerrillo[/TD]
[TD]Beth[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Gabriel[/TD]
[TD]Chavez Lara[/TD]
[TD]Rick[/TD]
[/TR]
[TR]
[TD]Missing[/TD]
[TD]Ariana[/TD]
[TD]Coria Torres[/TD]
[TD]Eve[/TD]
[/TR]
[TR]
[TD]Missing[/TD]
[TD]Kate[/TD]
[TD]De Luna[/TD]
[TD]Sonia[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Abel[/TD]
[TD]Diaz Cortinas[/TD]
[TD]Scott[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Julianna[/TD]
[TD]Dosal Casias[/TD]
[TD]Vianney

[/TD]
[/TR]
</tbody>[/TABLE]

 

[Aryatect]

Hi Scott93m,

Below solution is straight out of Mike "excelisfun" Girvin's Youtube channel, hope should help you:

Book1
	A	B	C	D
1	Missing?	FName	LName	CWSP Owner
2	Missing	Shadai	Anderson	Scott
3		Cheyanne	Apodaca	Vianney
4		Emiliano	Arellano	Beth
5	Missing	Evelyn	Arellano Mendiola	Rick
6	Missing	Kevin	Armendariz	Eve
7	Missing	Kevin	Borunda Granillo	Sonia
8	Missing	Jose Ignacio	Cabrera	Scott
9	Missing	Rene	Castellanos	Vianney
10		Edgar	Cerrillo	Beth
11		Gabriel	Chavez Lara	Rick
12	Missing	Ariana	Coria Torres	Eve
13	Missing	Kate	De Luna	Sonia
14		Abel	Diaz Cortinas	Scott
Sheet1

Book1
	F	G
1	Records	2
2	Scott	Shadai Anderson
3		Jose Ignacio Cabrera
4
5
6
7
8
9
Sheet1

Cell Formulas
Range	Formula
G1	=COUNTIFS($D$2:$D$14,$F$2,$A$2:$A$14,"Missing")
G2	{=IF(ROWS(D$2:D2)>$G$1,"",INDEX($B$2:$B$14&" "&$C$2:$C$14,SMALL(IF(($D$2:$D$14=$F$2)*($A$2:$A$14="Missing"),ROW($A$2:$A$14)-ROW($A$2)+1),ROWS(D$2:D2))))}
Press CTRL+SHIFT+ENTER to enter array formulas.