Displaying a List without blank rows

Davidns

Board Regular
Joined
May 20, 2011
Messages
136
Office Version
  1. 365
Platform
  1. Windows
I have a sheet that has two columns - one with a name and the other a date.
I use another sheet to show all results for a certain date. For example, sheet one shows:

David 10/1/19
Adam 9/30/19
Mary 8/1/19
Jill 8/15/19
Mark 10/1/19

Sheet two then has a user defined date input. Say, for example, 10/1/19. The results then show up as:

David
Blank row
Blank row
Blank row
Mark

Ideally, I would like to see the results without blank rows, but am having trouble figuring out how to do so. Any suggestions would be appreciated.
Thanks!
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
How are you showing the results in sheet two? Are you using a macro? If so, please post your code.
 
Upvote 0
Good question. Sheet two actually shows every row in Sheet 1, but I use conditional formatting to "white out" the rows that do not have the desired date, thus only showing the rows that have the desired date. This is probably not the best way to produce the results I want, but it does work. The problem, as I wrote, is that it ends up displaying a bunch of "empty" rows. I keep thinking there may be a search function based on font/color, and if so, perhaps I could then create a third sheet that only displays the colored cells. If this isn't possible, perhaps there is a better way to produce sheet 2 itself?
 
Upvote 0
Why not simply use autofilter for the selected date on Sheet2 ??
 
Upvote 0
How about


Book1
AB
1NameDate
2Tom Pearce01/10/2019
3Bill Brewer02/10/2019
4Jan Stewer03/10/2019
5Peter Gurney03/10/2019
6Peter Davy05/10/2019
7Dan'l Whiddon01/10/2019
8Harry Hawke07/10/2019
9Tom Cobley01/10/2019
Sheet1



Book1
A
101/10/2019
2Tom Pearce
3Dan'l Whiddon
4Tom Cobley
5
6
Sheet2
Cell Formulas
RangeFormula
A2=IFERROR(INDEX(Sheet1!$A$2:$A$9,AGGREGATE(15,6,(ROW(Sheet1!$A$2:$A$9)-ROW(Sheet1!$A$2)+1)/(Sheet1!$B$2:$B$9=$A$1),ROWS($A$2:$A2))),"")
 
Upvote 0
Glad we could help & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
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