How to filter/sort, but keep every third row together?

cpmurray1985

New Member
Joined
Mar 10, 2022
Messages
15
Office Version
  1. 365
Platform
  1. Windows
Hello!

I'm sure this is simple, but I appear to be running into an issue. I am trying to filter a chart based on group, so that whenever I select the specific group, it pulls the rows with it. However, it will only display the group name and the same row line.

For example, what I would have:

GroupColumn BColumn CColumn DColumn F
139
Group AX228
317
Group BY46A
55B
64C

What I want if I sort by Group A


GroupColumn BColumn CColumn DColumn F
139
Group AX228
317

What I get instead:

GroupColumn BColumn CColumn DColumn F
Group Ax228


Is something like this possible even if there are spaces above and below Group A? I have over 200+ groups I need to filter that have three rows associated like above, with a blank cell above and below. I tried merging cells, indexing, but it didn't seem to help.
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
I don't think AutoFilter will work correctly if there's a 'gap' between the group listings in column A - only the first Group name will show. A crude solution would be to repeat the group names in column A, in the case of your example for group A above and below, with group B the two cells below, and then format the font to white so they wouldn't be visible. Also, if you wanted to keep the gap between groups, you could put a character in column A in the gap (e.g. "Z") and format that font to white as well. That way your filter would work. I'm sure there must be a better solution that this...
 
Upvote 0
Your source data table would benefit greatly by being "flattened", which would eliminate the blank entries within each group. An easy way to do this would be in Power Query, but you can do it with formulas if you're willing to add some helper columns. In this example, I moved the Column A and Colum B entries up or down within the group to confirm that the entire group still gets assigned the correct details. To apply this:
  1. insert blank row between header and data table
  2. use formula to determine where entire rows are blank (All Blanks column)
  3. use blank rows to delineate groups and add columns that determine each group's upper and lower blank rows
  4. use OFFSET function with the FILTER function to populate the missing data
  5. replace your column A and column B data with the newly created columns by using Paste Special > Values
  6. in the future, if possible, ensure that each row is fully populated with the column A and B data
MrExcel_20220315.xlsx
ABCDEFGHIJK
1GroupColumn BColumn CColumn DColumn FAll BlanksGroup Upper Blank at RowGroup Lower Blank at RowGroup'Column B'
2TRUE22
3139FALSE26Group AX
4Group AX228FALSE26Group AX
5317FALSE26Group AX
6TRUE66  
7Group BY46AFALSE610Group BY
855BFALSE610Group BY
964CFALSE610Group BY
10TRUE1010  
1146AFALSE1014Group CY
1255BFALSE1014Group CY
13Group CY64CFALSE1014Group CY
14TRUE1414  
1546AFALSE1418Group DY
1655BFALSE1418Group DY
17Group DY64CFALSE1418Group DY
18TRUE1818  
Sheet6
Cell Formulas
RangeFormula
G2:G18G2=AND(A2:F2="")
H2:H18H2=XLOOKUP(TRUE,G$2:G2,ROW(G$2:G2),"",0,-1)
I2:I18I2=XLOOKUP(TRUE,G2:G$18,ROW(G2:G$18),"",0,1)
J3:J18J3=FILTER(OFFSET($A$1,$H3-1,0,$I3-$H3+1,1),OFFSET($A$1,$H3-1,0,$I3-$H3+1,1)<>"","")
K3:K18K3=FILTER(OFFSET($B$1,$H3-1,0,$I3-$H3+1,1),OFFSET($B$1,$H3-1,0,$I3-$H3+1,1)<>"","")
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,554
Messages
6,114,280
Members
448,562
Latest member
Flashbond

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