delete ever 'x'th row

[Lapchien]

Guys, I have a worksheet with:

A
B
C
D
E
F

AA
BB
CC
DD
EE
FF

AAA
BBB
CCC
DDD
EEE
FFF



row 1 contains name, row 2 contains address etc. I need to be able to view
just the name, so evry 7th row only, and delete the rest. Can anyone help?

Thanks,
Lap

 

[Harald Staff]

Hi

Suggestion: Select the address rows. Go menu Data > Group and outline >
Group. Now the rows are collapseable.

 

[Max]

One way, using a helper column:

Assuming your data is in col A, row1 down

Insert a new row1 so that the data starts in row2 down

Put in say, B2: =MOD(ROW()-1,7)
Copy B2 down col B as many rows as there is data in col A

Do a Data > Filter > Autofilter on col B
(with autofilter drop menu in B1)

Select "1" from the autofilter drop menu

This gives you the view of all the names only

If needed, you can also copy over the filtered rows to a new sheet easily

Press Ctrl + A
(selects entire sheet)

Click Copy

Right-click on A1 in a new sheet > Paste Special > Check "Values" > OK

 

[Lapchien]

Perfect. Thank you.

One way, using a helper column:

Assuming your data is in col A, row1 down

Insert a new row1 so that the data starts in row2 down

Put in say, B2: =MOD(ROW()-1,7)
Copy B2 down col B as many rows as there is data in col A

Do a Data > Filter > Autofilter on col B
(with autofilter drop menu in B1)

Select "1" from the autofilter drop menu

This gives you the view of all the names only

If needed, you can also copy over the filtered rows to a new sheet easily

Press Ctrl + A
(selects entire sheet)

Click Copy

Right-click on A1 in a new sheet > Paste Special > Check "Values" > OK

 

[Max]

you're welcome, Lapchien!
Thanks for the feedback.

cheers
Max