Sort Data Range With Blanks and Non-Blanks - Group Until Next Instance of Non-Blank Data

[thom.mattson]

Hello,

Per the example below, I would like to sort the data below by column A, but keep this grouped with the associated data in C and D that are adjacent to blanks in column A. In other words, I would like to sort the data by groups that start with each consecutive instance of text in column A. So there's data in A1 and B1, and Data in C2:C6 and D2:D6 that needs to remain grouped. I've tried filtering the data to exclude blanks from column A and then sorting, but of course that jumbles up the 'hidden' data for each row that is blank in column A.

Any help would be greatly appreciated.

Many thanks,
Matthew


<TABLE style="WIDTH: 486pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=648 border=0><COLGROUP><COL style="WIDTH: 72pt; mso-width-source: userset; mso-width-alt: 3510" width=96><COL style="WIDTH: 108pt; mso-width-source: userset; mso-width-alt: 5266" width=144><COL style="WIDTH: 171pt; mso-width-source: userset; mso-width-alt: 8338" width=228><COL style="WIDTH: 135pt; mso-width-source: userset; mso-width-alt: 6582" width=180><TBODY><TR style="HEIGHT: 22.5pt" height=30><TD class=xl67 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 72pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 22.5pt; BACKGROUND-COLOR: transparent" width=96 height=30>[A]
LT




</TD><TD class=xl66 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 108pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=144>
[master acct no]




</TD><TD class=xl68 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 171pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=228>[C] </TD><TD class=xl68 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 135pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=180>[D]




</TD></TR><TR style="HEIGHT: 33.75pt" height=45><TD class=xl67 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 72pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 33.75pt; BACKGROUND-COLOR: transparent" width=96 height=45></TD><TD class=xl69 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 108pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=144></TD><TD class=xl70 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 171pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=228>
Send To / Receive From




</TD><TD class=xl70 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 135pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=180>[acct no.]</TD></TR><TR style="HEIGHT: 33.75pt" height=45><TD class=xl67 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 72pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 33.75pt; BACKGROUND-COLOR: transparent" width=96 height=45></TD><TD class=xl69 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 108pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=144></TD><TD class=xl70 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 171pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=228>Send To / Receive From</TD><TD class=xl70 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 135pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=180>[acct no.]</TD></TR><TR style="HEIGHT: 33.75pt" height=45><TD class=xl67 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 72pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 33.75pt; BACKGROUND-COLOR: transparent" width=96 height=45></TD><TD class=xl69 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 108pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=144></TD><TD class=xl70 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 171pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=228>Send To / Receive From</TD><TD class=xl70 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 135pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=180>[acct no.]</TD></TR><TR style="HEIGHT: 33.75pt" height=45><TD class=xl67 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 72pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 33.75pt; BACKGROUND-COLOR: transparent" width=96 height=45></TD><TD class=xl69 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 108pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=144></TD><TD class=xl70 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 171pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=228>Send To / Receive From</TD><TD class=xl70 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 135pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=180>[acct no.]





</TD></TR></TBODY></TABLE>
<TABLE style="WIDTH: 486pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=648 border=0><COLGROUP><COL style="WIDTH: 72pt; mso-width-source: userset; mso-width-alt: 3510" width=96><COL style="WIDTH: 108pt; mso-width-source: userset; mso-width-alt: 5266" width=144><COL style="WIDTH: 171pt; mso-width-source: userset; mso-width-alt: 8338" width=228><COL style="WIDTH: 135pt; mso-width-source: userset; mso-width-alt: 6582" width=180><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD class=xl67 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 72pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" width=96 height=21>TC</TD><TD class=xl66 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 108pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=144>[master acct no]</TD><TD class=xl68 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 171pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=228></TD><TD class=xl68 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 135pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=180></TD></TR><TR style="HEIGHT: 33.75pt" height=45><TD class=xl67 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 72pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 33.75pt; BACKGROUND-COLOR: transparent" width=96 height=45></TD><TD class=xl69 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 108pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=144></TD><TD class=xl70 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 171pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=228>Send To / Receive From</TD><TD class=xl70 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 135pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=180>[acct no.]</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl67 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 72pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" width=96 height=21>SG</TD><TD class=xl66 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 108pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=144>[master acct no]</TD><TD class=xl68 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 171pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=228></TD><TD class=xl68 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 135pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=180></TD></TR><TR style="HEIGHT: 33.75pt" height=45><TD class=xl67 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 72pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 33.75pt; BACKGROUND-COLOR: transparent" width=96 height=45></TD><TD class=xl69 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 108pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=144></TD><TD class=xl70 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 171pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=228>Send To / Receive From</TD><TD class=xl70 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 135pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=180>[acct no.]</TD></TR><TR style="HEIGHT: 22.5pt" height=30><TD class=xl67 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 72pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 22.5pt; BACKGROUND-COLOR: transparent" width=96 height=30></TD><TD class=xl69 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 108pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=144></TD><TD class=xl70 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 171pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=228>Receive From</TD><TD class=xl70 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 135pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=180>[acct no.]</TD></TR></TBODY></TABLE><TABLE style="WIDTH: 486pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=648 border=0><COLGROUP><COL style="WIDTH: 72pt; mso-width-source: userset; mso-width-alt: 3510" width=96><COL style="WIDTH: 108pt; mso-width-source: userset; mso-width-alt: 5266" width=144><COL style="WIDTH: 171pt; mso-width-source: userset; mso-width-alt: 8338" width=228><COL style="WIDTH: 135pt; mso-width-source: userset; mso-width-alt: 6582" width=180><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD class=xl67 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 72pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" width=96 height=21>LT</TD><TD class=xl66 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 108pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=144>[master acct no]</TD><TD class=xl68 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 171pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=228></TD><TD class=xl68 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 135pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=180></TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 72pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=96 height=20></TD><TD class=xl69 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 108pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=144></TD><TD class=xl70 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 171pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=228>Send To</TD><TD class=xl70 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 135pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=180>[acct no.]</TD></TR><TR style="HEIGHT: 33.75pt" height=45><TD class=xl67 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 72pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 33.75pt; BACKGROUND-COLOR: transparent" width=96 height=45></TD><TD class=xl69 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 108pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=144></TD><TD class=xl70 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 171pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=228>Send To / Receive From</TD><TD class=xl70 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 135pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=180>[acct no.]</TD></TR></TBODY></TABLE>

 

[Jenawade]

I use the free ASAP Utilitities addin. It has a "copy values to empty cells below filled cells in selection" option that would take each instance of text in columns A & B and fill them down in the blank cells. Your A2-A6, B2-B6 will be filled in with what's in A1, B2, and on down your worksheet, and then you can just sort by column A.