Excel help and advice needed please

[Dark Horse]

Hi,
I'm not very good with Excel, which must be obvious or I wouldn't be asking
for help would I?
I have a spreadsheet which has grown to hold 23,562 data entries from 7888
name categories, and I need to trim it by deleting old and excess data.
Doing this manually will take too long to consider doing it on a regular
basis, and there must be some method of speeding the process up.
I can manage to get the data into alphabetical and date order, and run the
subtotal tool to show how many entries are under each heading..
The smallest entry is 1, and the largest entry found so far is 17.
Ideally I'd like no more than three entries per heading, and quickly and
easily delete everything in excess of that on a weekly basis.
Can it be done?
Easily?
Using ordinary language?
I'd really appreciate it if anyone can point me in the right direction.
Thanks
Dave

 

[Bob Phillips]

In an adjacent column add

=COUNTIF(A:A,A1)>1

and the autofilter this new column for a value of TRUE, and delete the
visible rows.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

 

[Roger Govier]

Hi Dave

Assuming your data has Dates in column A and Category in column B, with
headers in row 1.
Also, assuming you want to retain what are the latest entries for each
category and discard the rest.

First, sort the whole block of data by Date>Descending.
Use a spare column and enter
=COUNTIF($B$2:B2,B2)
Copy down for the entire length of your data.

Then Data>Filter>Autofilter> use the dropdown on this new column and
select Custom>Greater than >3
Mark the visible rows>right click>Delete Rows
Data>Filter>Show All

NB. Work on a copy of your data first, just in case you make any
mistakes.

 

[Dark Horse]

Hi Dave

Assuming your data has Dates in column A and Category in column B, with
headers in row 1.
Also, assuming you want to retain what are the latest entries for each
category and discard the rest.

First, sort the whole block of data by Date>Descending.
Use a spare column and enter
=COUNTIF($B$2:B2,B2)
Copy down for the entire length of your data.

Then Data>Filter>Autofilter> use the dropdown on this new column and
select Custom>Greater than >3
Mark the visible rows>right click>Delete Rows
Data>Filter>Show All

NB. Work on a copy of your data first, just in case you make any mistakes.

My spreadsheet has a header row at the top, and the data has names in column
A, the date doesn't appear until Column M. My dates are always sorted by
date descending, so that the latest data is at the top of the entries. I
have no empty columns until column X.

My knowledge is very limited, but do I do a COUNTIF in column X? Would that
be COUNTIF($A$2:A2,A2)?

Is there no way to do this using subtotal instead, as I am happy with use of
that function?
Thanks

I want to discard every entry more than three

 

[Dark Horse]

In an adjacent column add

=COUNTIF(A:A,A1)>1

and the autofilter this new column for a value of TRUE, and delete the
visible rows.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

I'm not at all sure what this does, or why, or how you know what data is
where.

 

[Roger Govier]

Hi

You still haven't said which column holds the Category or heading that
you are not wanting more than 3 of.
If it is Names, then yes in X2 enter
=COUNTIF($A$2:A2,A2)

The formula counts how many times that name appears in an ever expanding
number of rows as you copy down the table.
When it has been applied, using an Autofilter on column X and selecting
the values greater than 3, will select all the rows where the name
appears more than 3 times, so those rows can be deleted.
You will then be left with no more than 3 entries for each name.

If is not Name that want to act upon but Category as you mentioned in
your first post, make the formula in column X carry out the Countif on
the column letter containing the categories.

 

[Bob Phillips]

Try it and see what happens.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

 

[Dark Horse]

What happens is that I get various returns of false and true that are not
what I want at all.
First 6 entries (beginning in cell A2) are
AB = False
AB = True
AB = True
ABN = True
ADE = False
AFKW = False
AFKW = True
AFKW = True

So if I delete all the False entries I am losing data I need and keeping
data I want to lose!
Great Joke...
Thanks

 

[Dark Horse]

Column A holds the defining category, but it is the rows across from the 4th
onward that I wish to delete.

It worked up to a point, but you could have warned me how long it would take
to delete the number of rows involved - I was beginning to panic.
Having to do it one number at a time was also very time consuming.
Thankfully, in future, there won't be so much dross to clear out.
Thanks for your help.

 

[Bob Phillips]

so maybe filter on the other value?

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

 

[Dark Horse]

No need, another suggestion worked perfectly well and does the job
brilliantly.