Can this be done?

  • Thread starter Thread starter Dark Horse
  • Start date Start date
D

Dark Horse

I have a large spreadsheet containing data.
The data begins with a name in column A, and it is that entry I am asking
for help with.
Some entries have one row, other have 10 rows, and I'd like to know if there
is a way whereby adding new entries will automatically delete any entry for
a name that has a total of more than three entries?
For instance, I have:
Aardvark on 1st January
Aardvark on 12th December
Aardvark on 16th November
At the end of each day I integrate the entries for the day and sort into
date order.
If I add another Aardvark on February 20th can I automatically remove the
16th November entry?
Failing that, can I run a macro to limit all entries of Aardvark to 3?
My spreadsheet is too large to go through each name and do this manually, it
does/would just take too long.
Is there any trick or method I can use to help limit each entry of an
instance to 3 and delete all others by using most recent dates as a
criteria?
TIA
Dave
 
You can limit the # of entries for the same item to 3 in
column A using Validation:

1. Select column A.
2. Data > Validation
3. Allow: Custom
4. Formula:
=COUNTIF($A:$A,$A1)<=3

HTH
Jason
Atlanta, GA
 
Unfortunately I have 8236 rows of data, and some have only one entry while
other have up to 12.
I can't even figure out how to do what you suggested but I don't think it
can do what I need - as I need the whole row removing that is surplus to
requirements.
 
I have tried to understand, but am as dumb as anything it seems.
To recount:
1. The first row (A1:AB1) has headings.
2. All subsequent rows contain data.
3. There are no blank rows within the database.
4. There is a blank row at the end of the database, and a blank column at
the right of the database.
5. There are 6149 rows.
6. There are 2601 different names in Column A of those 6149 rows.
7. As I understand it, I would have to go through and set up an advanced
filter for each of those 2601 names - and any new ones that may be added to
the list subsequently?

If that is correct, it is just too much work to be done to fit the time
available to do it.
I was hoping that there may be some method of finding out how many similar
entries there are - maybe using countif?
I have a macro that will give me the average score by counting the names in
Column A and adding their totals before dividing by the number of times a
name appears.
I was hoping for a similar macro that would run before the other and reduce
all names in column A to a maximum of 3 entries for each instance of a name.

So, am I still missing something - or am I correct about my assumptions for
using an advance filter?
 
You could add a column to your table, to calculate if more than three
entries exist for that item. For example, if the names are in column D,
enter the following formula in row 2: =COUNTIF(D2:$D$5000,D2)>3

At the end of the day, filter that column for TRUE, select the visible
rows, and delete them.
 
See Debra's reply.

I gave you a reference for filter, it should have been a reference
for validation as the purpose is to prevent new entires not meeting
the criteria.

The formula is based on (adjusted from) the active cell and is effective
for each cell in the selection range. When you
select column A, you have cell A1 as the active cell. The formula
is adjusted for the selection based on that so the effective formula
when you try to enter something or change something in A1
=COUNTIF($A:$A,$A1)<=3
would for cell A3001 be
=COUNTIF($A:$A,$A3001)<=3 .

The range $A:$A refers to a range of the column A , the entire column
Cell $A3001 is the value of cell A3001
The $ (dollar signs) absolutely refer to column A no matter where the
the formula is copied to. But the row number adjusts depending on
where it is tested.

COUNTIF is looking at the range $A:$A for the value in $A3001
and that value is tested to see if it is less than or equal to 3
So the statement is either TRUE or FALSE. In order for validation
to allow you to make the entry -- it must be a TRUE statement.

Validation does not care if you already have 8 such entries, but it
will not allow you add another once validation is created that would
exceed 3. You can bypass (actually destroy) validation for a cell
by pasting a value in -- so it is not foolproof, and the validation for
that cell will have been wiped out so any value would be accepted
thereafter.

These formulas might help you visualize (test without validation)
B2: =COUNTIF($A$1:$A2,$A2)<=3 || provides either True or False
C2: =COUNTIF($A:$A,$A2)<=3 || provides either True or False
D2: =COUNTIF($A$1:$A2,$A2) || provides a count ov alue of A2 for cell A1 to A2
E2: =COUNTIF($A:$A,$A2) || provides a count of value of A2 for alll of column A

Filters, Validation, and Conditional Formatting are very similar to one
another. The purpose of the formula is test a condition and return
either True or False, and act upon it if the condition is True.

The web page I should have referred you to was
http://www.contextures.com/xlDataVal01.html
http://www.contextures.com/xlDataVal02.html
and perhaps one of my own (no pictures, no explanation)
http://www.mvps.org/dmcritchie/excel/validation.htm
 
My names are in Column A, so:
=COUNTIF(A2:$A$5000,A2)>3 would do it?
Does the 5000 limit the number of entries to that number? Where do I put it,
because what is row 2 today may not be row 2 after tomorrow's entries?
Also, how would I run it and what would it do - remember that there is an
initial criteria that I want the 3 most recent entries by date and not just
any 3 entries?
Filter for True?
Select visible rows? How?
This is starting to get more and more complicated, and unnerving, and I
still do not understand very much of what anyone is saying.
Do this, and then do that doesn't mean too much to someone struggling to
understand and who is scared that one mistake will wipe out months of work.
Maybe I should look for a beginners group first.
 
I found that the best way to learn was to try all the stuff against a copy of
the workbook. If I screwed it up, then I'd just delete that copy and reopen
another copy.

But remember that you can always close without saving if a change doesn't work
the way you intended.

And if you catch your mistake right away, you can do Edit|Undo.

Making mistakes is part of the learning process.
 
Once or twice I have done what you suggest here, in the hope that it would
be possible to ignore changes and not save them.
However, I have suffered the whole thing freezing solid and having to revert
to ctrl/alt/del to see Not Responding and having to 'end task'.
Then, upon reopening after rebooting, I have found errors in the sheet -
hence my scepticism and care.
 
Then remember to backup your workbook often. If you have trouble, you can
always revert to a previously saved version.

Sometimes I'll save the file as a new name before each time I do something
"dangerous".

I'll have:

myfile.xls
myfile_0530.xls
myfile_0545.xls

(I'll include the time in the filename so that I know which is which.)

I'll clean up later (delete the bad and rename the good to the correct name)
when everything is fine and I'm done.
 
Unfortunately, memory available does not always allow all of us to be quite
so cavalier with disc space.
I already have 6 spreadsheets for different intents and purposes, and I must
be aware of how much memory is needed for processing and calculating before
I can use too much.
If you were crewing with Noah aboard the ark you may appreciate what I am
working with!
I realise that my mental and hardware limitations - like my physical ones -
are laughable to some people, but its all I've got and I have to make do
with it.
Because of my physical limitations I try to find something mental to give my
mind something to occupy it and stop it atrophying.
Unfortunately I do not have the kind of brain that takes kindly to anything
mathematical or that follows a long and annoyingly logical process.
As soon as I find anything like that I want to ask why it has to be like
that, and lose my temper.
Some do, some don't - and I'm a don't, but hey we make folks like you look
good and put purpose into your lives.

Maybe it is time I changed direction and looked for something new for my
butterfly mind, because the only help I seem to attract here seems to assume
that I understand something - and points me to examples that mean zilch to
me.
I'm sorry that I can't be normal, and that I ask why so often - and that I
don't fit in to your mould of knowledge and understanding.

What would it have cost someone; anyone, to just say no - what you are
asking cannot be done that way?
Why not tell me how much work is involved in what I ask, rather than assume
I will understand what you are talking about?
At least I would know, rather than be led by the nose for some days and get
nowhere.

I'm not having a go, I'm annoyed at myself - I have dreams and ideas and
never get to complete any of them.
If I am saying anything at all, its is offer help sometimes - instead of
just trying to show how clever you are!
 
If you have trouble with diskspace, try the suggestion on a much smaller version
of the workbook first. If it works, try to implement for real.



Dark said:
Unfortunately, memory available does not always allow all of us to be quite
so cavalier with disc space.
I already have 6 spreadsheets for different intents and purposes, and I must
be aware of how much memory is needed for processing and calculating before
I can use too much.
If you were crewing with Noah aboard the ark you may appreciate what I am
working with!
I realise that my mental and hardware limitations - like my physical ones -
are laughable to some people, but its all I've got and I have to make do
with it.
Because of my physical limitations I try to find something mental to give my
mind something to occupy it and stop it atrophying.
Unfortunately I do not have the kind of brain that takes kindly to anything
mathematical or that follows a long and annoyingly logical process.
As soon as I find anything like that I want to ask why it has to be like
that, and lose my temper.
Some do, some don't - and I'm a don't, but hey we make folks like you look
good and put purpose into your lives.

Maybe it is time I changed direction and looked for something new for my
butterfly mind, because the only help I seem to attract here seems to assume
that I understand something - and points me to examples that mean zilch to
me.
I'm sorry that I can't be normal, and that I ask why so often - and that I
don't fit in to your mould of knowledge and understanding.

What would it have cost someone; anyone, to just say no - what you are
asking cannot be done that way?
Why not tell me how much work is involved in what I ask, rather than assume
I will understand what you are talking about?
At least I would know, rather than be led by the nose for some days and get
nowhere.

I'm not having a go, I'm annoyed at myself - I have dreams and ideas and
never get to complete any of them.
If I am saying anything at all, its is offer help sometimes - instead of
just trying to show how clever you are!
Click to expand...
 
I still haven't been told if it is possible without having to make an entry
for every existing name, and then each new one.
In fact I haven't been told anything in terms that I can understand.
 
I'm sure Dave, and everyone else who has posted in this thread, is
trying to help you.

As I understand it, you'd like to automatically delete all but the
latest three entries for any name in column A, based on the dates in
another column.

This should be possible by using a macro, but a macro would require some
work to set up. If you aren't familiar with programming, you might find
it difficult to get it working correctly.

Instead of using a macro, you could enter all the data each day, then do
a bit of manual work to clear the old entries, using one of the methods
previously suggested. If you'd prefer that, someone might outline the
steps for you.

Neither option should require that you maintain a separate list of all
existing names, or new ones.

Dark said:
I still haven't been told if it is possible without having to make an entry
for every existing name, and then each new one.
In fact I haven't been told anything in terms that I can understand.

If you have trouble with diskspace, try the suggestion on a much smaller
version

of the workbook first. If it works, try to implement for real.




quite

must

before

am
Click to expand...
ones -
give my
anything

look

assume

to

I

assume

get
can
something
to
Click to expand...
sheet -
against a
and
Click to expand...
do I
there
and
unnerving,
months
than
=COUNTIF(D2:$D$5000,D2)>3
Click to expand...
entry I
Click to expand...
 
You are probably right, most people are trying to help me - in their own
way, and it is me who cannot understand what the message is.

I am aware of the manual way, it is how I do it now - and what made me ask
if there was a better way.
Trawling through one of six spreadsheets that each gets larger every day is
very wearing - and of course takes longer each day.
I have been at this for almost 3 years now, and have never been closer to
giving it all up than I am now - which is why I asked for help.

I have even tried colour coding the latest entries to make them easier to
find, and so delete excess entries quicker - but it still takes too long.
Each spreadsheet takes me around 40-60 minutes to trawl through, and I am
never fit enough to do more than one at a go or half of the total in one
day - ergo I am always behind.

When you start something you don't always understand the whole thing and all
that it entails - because if you did you'd never have started.
But you believe in something and want to prove a point so you try and dance
the dance.

If possible, I would like to automatically delete all but the last three
entries for any name in column A, based on the dates in column M.
All entries are sorted on name and descending date before any other action,
so I figured to do that and then run a macro to take out excess entries
before running my calculations on the remaining data.

I'm not familiar with programming, but I can follow a debugger and see where
an error is.
However, if what I want cannot be done in the way that I want - then just
tell me, and I'll just go away.
If it can be done, then lead me towards understand that.
I know nothing is instant, I know that what I'm asking will take time and
patience.
I know that everyone will probably end up wanting to shout at each other.

But if no one is willing to help, then tell me that too and I'll consign one
more project to the list of cannot be done by someone in my condition and
move on to something new.

Is that too much to ask?
I thought that was why groups like this existed.

Debra Dalgleish said:
I'm sure Dave, and everyone else who has posted in this thread, is
trying to help you.

As I understand it, you'd like to automatically delete all but the
latest three entries for any name in column A, based on the dates in
another column.

This should be possible by using a macro, but a macro would require some
work to set up. If you aren't familiar with programming, you might find
it difficult to get it working correctly.

Instead of using a macro, you could enter all the data each day, then do
a bit of manual work to clear the old entries, using one of the methods
previously suggested. If you'd prefer that, someone might outline the
steps for you.

Neither option should require that you maintain a separate list of all
existing names, or new ones.
Click to expand...
 
Back
Top