Returning Formula without blank cells

Scott93m

New Member
Joined
Jun 26, 2019
Messages
8
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?
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Hi Scott93m,

Can you please share a sample data here ?

Thanks,
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.

 
Upvote 0
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?
 
Upvote 0
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

Missing?FNameLNameCWSP Owner
MissingShadaiAndersonScott
CheyanneApodacaVianney
EmilianoArellanoBeth
MissingEvelynArellano MendiolaRick
MissingKevinArmendarizEve
MissingKevinBorunda GranilloSonia
MissingJose IgnacioCabreraScott
MissingReneCastellanosVianney
EdgarCerrilloBeth
GabrielChavez LaraRick
MissingArianaCoria TorresEve
MissingKateDe LunaSonia
AbelDiaz CortinasScott
JuliannaDosal CasiasVianney


<colgroup><col span="4"></colgroup><tbody>
</tbody>
 
Upvote 0
Hi Scott93m,

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


Book1
ABCD
1Missing?FNameLNameCWSP Owner
2MissingShadaiAndersonScott
3CheyanneApodacaVianney
4EmilianoArellanoBeth
5MissingEvelynArellano MendiolaRick
6MissingKevinArmendarizEve
7MissingKevinBorunda GranilloSonia
8MissingJose IgnacioCabreraScott
9MissingReneCastellanosVianney
10EdgarCerrilloBeth
11GabrielChavez LaraRick
12MissingArianaCoria TorresEve
13MissingKateDe LunaSonia
14AbelDiaz CortinasScott
Sheet1



Book1
FG
1Records2
2ScottShadai Anderson
3Jose Ignacio Cabrera
4
5
6
7
8
9
Sheet1
Cell Formulas
RangeFormula
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.
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top