Eliminating blank cells from ms excel

How to use an MS Excel formula to remove cells with formulas in them but are blank?

In the attached MS Excel application on the sheet named "JHA1" the user checks the boxes that apply and the selections are transferred to sheet "JHA2". The  problem is I don't know how to remove the blank cells in column H on sheet JHA2.

I have tried several formulas I found on line, but nothing worked
JHA-App.xlsx
cssc1Asked:
Who is Participating?
 
Naresh PatelConnect With a Mentor TraderCommented:
Try This on sheet JHA1 Cell G2 {=IFERROR(INDEX($D$4:$D$100, MATCH(0,COUNTIF($G$1:G1, $D$4:$D$100), 0)),"")} ctrl+shift+Entre...Drop Down
0
 
Rob HensonConnect With a Mentor Finance AnalystCommented:
Do you want them removed or will just hidden be OK?

On JHA2 select H10:I33 and then from the Data menu select Filter. This will put a dropdown box in each of cells H10 & I10. With the dropdown in H10 you can see the list of values available to filter and at the bottom of the list is an option for blanks. Deselect that option and click OK. The rows with blank will now be hidden. To unhide them select the dropdown again and click on the Select All in the list of options.
0
 
Naresh PatelTraderCommented:
{=IFERROR(LOOKUP(2,1/((COUNTIF($G$1:G1,$D$4:$D$100)=0)*($D$4:$D$100<>"")),$D$4:$D$100),"")} ctrl+shift+entre

Upgraded  removes Blanks but the only drawback ...it list from bottom to Top
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
cssc1Author Commented:
Works great, thanks
0
 
Naresh PatelTraderCommented:
Thumbs UP :)
0
 
Rob HensonFinance AnalystCommented:
Another option would be to use a pivot table.

See attached. Change some of the entries and then right click on the Pivot table, column A on JHA2, and select refresh.
JHA-App.xlsx
0
 
cssc1Author Commented:
This works great except, the selections have blanks cells between them.
0
 
Naresh PatelTraderCommented:
try upgraded i.e. my second post
0
 
cssc1Author Commented:
Unless I am using the formula wrong, it produces repeats

{=IFERROR(LOOKUP(2,1/((COUNTIF($G$1:G1,$D$4:$D$100)=0)*($D$4:$D$100<>"")),$D$4:$D$100),"")}
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.