Deleting in excel

[cs389]

Hi i have a large set of data but only want every 6th value. How do i delete the other 5 values for the whole data without deleting them five at a time which is taking ageesss?! also how do i then move the wanted values up so there arent lots of five space gaps?

Thanks

Carrie

 

[fred]

suppose you have a column A with a title. So on a separate column do this


=mod(row(),6) filter everything other than 1 and use go special and delete all visible rows in column A and B.

 

[cs389]

thats great thankyou! now how do i get all the rows to just be called 1,2,3,4 etc because at the mo theyre 3,9,15,21 (every 6) so it wont let me copy the data?

 

[SirJB7]

Hi, cs389!


I assume you have two title rows and that you've changed the formula to:

=MOD(ROW()-2,6)

in order to get 3, 9, ... when you filter column B for value 1.


I'll tell what I did as I get the values 1, 2, ... that you want to obtain.


Column A:

A1 : title 1

A2 : title 2

A3:AXXX : original data (XXX = last filled)


Column B:

B3:BXXX : =MOD(ROW()-2,6)


Select whole sheet, Data tab, Filter

Filter column B for <>1

Select entire filtered rows from YYY:ZZZ (YYY = first filtered, ZZZ = last filtered)

Right click on row numbers, "Eliminar filas" (I guess in english should be "Erase or remove or delete row")

Remove filter

... and you'd get 1, 2, ... on column B.


Just advise and upload a sample file if you can't manage to handle it.


Regards!

 

[wintermute]

Hi,


I just don't understand why use function.

Some time ago I used macro to do this, but it's not as efficient and safe as technique below.

Usually I input values from 1 to 6 into first 6 rows in supportive column and then copy and paste till end of data range, then use autofilter and delete all rows that contain values from 1 to 5. Simple.

 

[Montrey]

Thats a good idea Wintermute! Great thing about Excel is there are always multiple ways to do the same thing!


Good job thinking outside the box!