Only show certain rows and the one immediately below

[fuzzyjonclay]

Hi everyone

I have a problem filtering out certain rows and wonder if someone could please help me.

The table below represents my spreadsheet. I have carried out an EXACT function to find cells underneath each other that are the same (e.g. David & David and Philip & Philip in ColumnB). This is represented by having TRUE in ColumnC on the first row of the matches.

What I would like to achieve is to only show the rows that have TRUE in ColumnC along with the row immediately below it. So, I would like to keep the 2 Davids and the 2 Philips, but get rid of the Sally and Sue rows.

[TABLE="width: 500"]
<tbody>[TR]
[TD]ColumnA[/TD]
[TD]ColumnB[/TD]
[TD]ColumnC[/TD]
[/TR]
[TR]
[TD]100[/TD]
[TD]David[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]101[/TD]
[TD]David[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]102[/TD]
[TD]Sally[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]103[/TD]
[TD]Philip[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]104[/TD]
[TD]Philip[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]105[/TD]
[TD]Sue[/TD]
[TD]FALSE[/TD]
[/TR]
</tbody>[/TABLE]

I hope this makes sense!

Many thanks for your help.

Jon

 

[pgc01]

Hi Jon

This is represented by having TRUE in ColumnC on the first row of the matches.

If I understand correctly you should have TRUE not just on the first row of the matches but on both rows of the matches.

You can then use autofilter and only display those rows.

 

[sandy666]

or with PowerQuery

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Column1[/td][td=bgcolor:#5B9BD5]Column2[/td][td][/td][td=bgcolor:#70AD47]Column1[/td][td=bgcolor:#70AD47]Column2[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

100​

[/td][td=bgcolor:#DDEBF7]David[/td][td][/td][td=bgcolor:#E2EFDA]

100​

[/td][td=bgcolor:#E2EFDA]David[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

101​

[/td][td]David[/td][td][/td][td]

101​

[/td][td]David[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

102​

[/td][td=bgcolor:#DDEBF7]Sally[/td][td][/td][td=bgcolor:#E2EFDA]

103​

[/td][td=bgcolor:#E2EFDA]Philip[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

103​

[/td][td]Philip[/td][td][/td][td]

104​

[/td][td]Philip[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

104​

[/td][td=bgcolor:#DDEBF7]Philip[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]

105​

[/td][td]Sue[/td][td][/td][td][/td][td][/td][/tr]
[/table]

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Kept Duplicates" = let columnNames = {"Column2"}, addCount = Table.Group(Source, columnNames, {{"Count", Table.RowCount, type number}}), selectDuplicates = Table.SelectRows(addCount, each [Count] > 1), removeCount = Table.RemoveColumns(selectDuplicates, "Count") in Table.Join(Source, columnNames, removeCount, columnNames, JoinKind.Inner)
in
    #"Kept Duplicates"[/SIZE]

 

[jmacleary]

Hi there.

You could make the formula in column C more complex, e.g. in C100 you would have: =IF(C99="TRUE","ALSO",your current formula here). This would put ALSO in the immediate row below, which you could filter on.

 

[fuzzyjonclay]

Hi all

Many thanks for your replies. I tried all 3 variants and they all did the job for me.

Best wishes
Jon