Excel: How can i delete all rows except the fifth-CodePudding

I have some Data in my Csv. file and i need to delete all rows except every 5th, how can i do that ?

CodePudding user response：

I'd advise you to load the CSV into PowerQuery. Though PQ by no means is my forte, I'd then take the following steps:

Add an Index-Column with a starting index of '1' and a standard increment of '1';
Add a custom column based on modulus 5, e.g.: =Number.Mod([Index],5)=0;
Filter your custom column based on 'TRUE' values;
Remove the index- & custom column.

For example:

Add the index column:

Add the custom column:

Filter the custom column:

Delete the index- and custom column:

End up with only every 5th row:

For what it's worth, this is the m-code of me loading the data from my worksheet (source). You can load the data through CSV:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each Number.Mod([Index],5)=0),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = true)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Index", "Custom"})
in
    #"Removed Columns"

CodePudding user response：

Not-tested pseudo-code, but it will be something like that:

for i=end downto beginning:
  if mod(i,5) != 0 then 
    Range(i,1).EntireRow.Delete
  end if
 step -1

It is crucial to go from end to beginning, or you'll mess up the indexes in your rows :-)