Displaying a List without blank rows

Davidns

Board Regular
Joined
May 20, 2011
Messages
119
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!
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
8,610
How are you showing the results in sheet two? Are you using a macro? If so, please post your code.
 

Davidns

Board Regular
Joined
May 20, 2011
Messages
119
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?
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,303
Office Version
2013
Platform
Windows
Why not simply use autofilter for the selected date on Sheet2 ??
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,720
Office Version
365
Platform
Windows
How about

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Name</td><td style=";">Date</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Tom Pearce</td><td style="text-align: right;;">01/10/2019</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Bill Brewer</td><td style="text-align: right;;">02/10/2019</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">Jan Stewer</td><td style="text-align: right;;">03/10/2019</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">Peter Gurney</td><td style="text-align: right;;">03/10/2019</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">Peter Davy</td><td style="text-align: right;;">05/10/2019</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">Dan'l Whiddon</td><td style="text-align: right;;">01/10/2019</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">Harry Hawke</td><td style="text-align: right;;">07/10/2019</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">Tom Cobley</td><td style="text-align: right;;">01/10/2019</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br />

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: right;;">01/10/2019</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Tom Pearce</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Dan'l Whiddon</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">Tom Cobley</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet2</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">A2</th><td style="text-align:left">=IFERROR(<font color="Blue">INDEX(<font color="Red">Sheet1!$A$2:$A$9,AGGREGATE(<font color="Green">15,6,(<font color="Purple">ROW(<font color="Teal">Sheet1!$A$2:$A$9</font>)-ROW(<font color="Teal">Sheet1!$A$2</font>)+1</font>)/(<font color="Purple">Sheet1!$B$2:$B$9=$A$1</font>),ROWS(<font color="Purple">$A$2:$A2</font>)</font>)</font>),""</font>)</td></tr></tbody></table></td></tr></table><br />
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,720
Office Version
365
Platform
Windows
Glad we could help & thanks for the feedback
 

Forum statistics

Threads
1,085,493
Messages
5,383,981
Members
401,868
Latest member
herbalgirlskincare

Some videos you may like

This Week's Hot Topics

Top