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.

ColumnA	ColumnB	ColumnC
100	David	TRUE
101	David	FALSE
102	Sally	FALSE
103	Philip	TRUE
104	Philip	FALSE
105	Sue	FALSE

<tbody>
</tbody>

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

Column1	Column2		Column1	Column2
100​	David		100​	David
101​	David		101​	David
102​	Sally		103​	Philip
103​	Philip		104​	Philip
104​	Philip
105​	Sue

[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