count 10 rows delete the same

**Murrayh4** · 06-20-2016, 01:53 AM

Hi, i need to count 10 rows of a spreadsheet that has matching data in column b and C then delete any other rows that has the same data, these follow one and another.

So there maybe 130 rows with same data i only need 10 the rest deleted.

When the name changes in column C, leave 10 rows delete the rest and so on.

Any and all help is vastly apprecited

Regards

M

**Keebellah** · 06-20-2016, 02:00 AM

Hi M, just out of curiosity. How is your VBA knowledge?
And, now less important, which version of Excel do you use?

**Murrayh4** · 06-20-2016, 02:03 AM

HI My VBA knowledge is not good and its excel 2013

CAn i accomplish what i need via formulas?

**Keebellah** · 06-20-2016, 02:34 AM

I'm not that great with formulas. I would go for macros (VBA)
If I understood you explanation, you've got a large number of rows that have matching data in B and C and they're all together, that is you probably got them sorted.
Could could try several things:
Autofilter: and then filter top 10.
Formulas: play around with a formula that checks if B and C below is the same and set a 1 or a counter
VBA: needs a macro.

A macro is nothing more and nothing more that a set of instructions that get carried out one after the other.
The idea is think of how you look at it when reading and asking yourself; "Is the next row idendtical?" Yes that add 1 to counter check next row and when you reach 10 you go into the next phase.
Is the next row still the same but my counter is already 10 then delete id.
Is the next row different reset the counter to 0 and start again from there.
And now very simply said, you translate this into code

Simple no?
I'll try and see if I can do something.
Do you have a dummy sample for me or is it just check values in column B and column C to be the same and then count 10 of those?

**Murrayh4** · 06-20-2016, 03:28 AM

HI, it bascially check column C if the value is the same count 10 rows, delete anything above 10 rows.

It can delete the whole row if that is easier after 10 rows, doesn't need to delete just Column B

When the value of column C changes restart, count 10 rows, delete rows above 10 until the value of column C changes again,
10 rows, delete, Column C value changes , 10 rows, delete and so on.

I can send some dummy data tomorrow if you need it.

Thanks
M

**Keebellah** · 06-20-2016, 03:57 AM

Okay, basically checking column C and just keep 10 row with the same value all others with same values delete entire row.
What is the content? numeric or string or can it be both?

So let's say you've got 10000 rows with let's say 200 different values in Column C the end result will be 2000 rows for each of the 200 different values?

**Murrayh4** · 06-20-2016, 06:49 AM

HI Column C is text, column B is a number the other columns are a mixture of date, text and numbers.

What you have stated here is correct

So let's say you've got 10000 rows with let's say 200 different values in Column C the end result will be
2000 rows for each of the 200 different values?

**Keebellah** · 06-20-2016, 07:17 AM

Okay, I'll 'build' something for you based on Column C, you can always modify the code if required.
I am assuming that Column C is sorted.

**Keebellah** · 06-20-2016, 08:05 AM

I managed to do if faster that I thought.
See attached file and the macro is commented explaining
Press the yellow Smiley in Sheet1 to run the macro
Sheet1 (2) is a copy of Sheet1 if you want to run it again, just copy the contents for columns A-D to Sheet1