CODEWITHSUNDEEP
excelcsvexcel-formula
Danijel333
Danijel333

Reputation: 19

Excel: How can I delete all rows except the fifth

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

enter image description here

Upvotes: 0

Views: 94

Answers (3)

WeAreOne
WeAreOne

Reputation: 1343

You could make a helper column with this formula:

=MOD(ROW(A2)-ROW($A$2)+1;5)

Replace semicolon with comma, if your Excel version needs.

ROW($A$2) is the first row of the data.

Then apply a filter Data--> Filter

and remove the tick for the 0

Then delete rows.

then remove the filter

enter image description here

enter image description here

Delete sheet rows

enter image description here

Remove Filter

enter image description here

enter image description here

Remove helpercolumn

Then you can export the Excel file as csv. Opening a csv in Excel, changing it and saving, might not work.

Upvotes: 0

JvdV
JvdV

Reputation: 76000

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:

![enter image description here

  • Add the index column:

enter image description here

  • Add the custom column:

![enter image description here

  • Filter the custom column:

enter image description here

  • Delete the index- and custom column:

enter image description here

  • End up with only every 5th row:

enter image description here

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"

Upvotes: 3

Dominique
Dominique

Reputation: 17575

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 :-)

Upvotes: 0

Related Questions