Can this be done?

[No]

I have a excel spreadsheet that I have created which is for the organization
of my domains. Each domain has it's own worksheet which contains all the
information pertaining to that domain.

What I want to do is create another worksheet that will list just the names
of each domain and the date that it is due for renewal. On this worksheet I
want any date that is due within 30 days to appear in read. This way I can
look at one sheet and know which ones are due for renewal, can this be done.

 

[Ron de Bruin]

You can use auto filter now to sort on the date column

I mean Sort (sorry) and not Auto filter

 

[Andy B]

No

Use Conditional Formatting to show when the renewal date is within 30 days.
Select the first cell and go to Format/Conditional Formatting. Set the
condition to 'Formula is' and then input:
=AND(A2>=TODAY(),A2<TODAY()+30)
Then format the background to red (or whatever), and click OK.
This will highlight cells which contain a date that is today or within 30
days of today.

Andy.

 

[AlfD]

Hi!

Have you tried sorting the list by name and then using Subtotal base
on the names?

Al

 

[AlfD]

Dark Horse:

The last time I felt that sort of frustration it was about 4 o'clock i
the morning and the answers had to be there next (no, that) day.

Let me give it to you straight. It can be done.

Maybe better take the hurdles one at a time, because your problem i
really two problems.

One: to clean out the backlog of unwanted duplicates.

Two to stop putting even more duplicates in.

If you have a disk space problem, can you archive material? If i
really is akin to the ark, have you got a tape streamer? Or even acces
to a CD burner? Make sure that somewhere your insurance is carefull
saved: the last known-to-be-working version.

Take Dave's advice and work on a cut-down version : even 1000 lines i
quite a big sample.

I'm going to stop now, until you come back and say you are ready fo
the next bit. Sufficient unto the day...

Stick with it: I'm not saying I (personally) can or will solve you
problems. I'm saying that YOU will be able to solve them with the hel
and support that there is in these parts. OK, you might have to tak
the odd bit on trust (like I usually feel when I see complicate
sumproduct formulae - but even those can grow on you.)

Cheers!

Al

 

[Dark Horse]

Hi Alf,
Unfortunately I feel this frustrated every day; life is my only limitation -
and because of that I get angry, and at times I do tend to lash out.
It wasn't always like this, and I once had friends - but they've all drifted
away or been driven away by me.
Right, enough of that.

It can be done - and that's what I wanted to hear.

It was always my thought that I would have to resize the sheet before
anything else, otherwise everything else would have to be done twice.
From what you say though, this seems to be taking the shape of a two-step
operation - which I can't see the point of.
However, I am here to listen and learn.

I can archive onto floppy disc only, I have a programme that splits large
files into floppy sized chunks. I back up once a week with just data - no
formulae or macro's to keep the size down. The most I would lose is 5 days
entries.
I have one mess-about file that is smaller, that I use to experiment on and
try new ideas in.

My machine cannot connect to any modern media, because it is pre -USB. I
have serial ports and a slow old 56k modem. No chance of connecting to CD
writers or anything like that.

So, where do we start?

Dave

 

[AlfD]

Dark Horse

At this stage I'm only going to tackle the slimming-down of th
existing database.

As I understand it, you want to end up with no more than 3 record
matching any given name.

You also want those to be the most recent records.

Check?

Now: are the names unique? (If I call up Smith you won't tell me I hav
to keep 3 for John Smith and 3 for Sam Smith (no connection with th
Tadcaster brewing brothers)?

If they are not unique, will you make them so (adding a 1,2,3 o
whatever to the end of them would do).

First: make a copy of these notes: you'll need them.

1. Load your file. If you have one you are experimenting on, that woul
do fine. I've run this through with a database of 14595 records and i
worked. But I've more power and memory than you can call on, so
suggest you do it on something smallish - maybe 1000 records, but I'
guessing.

2. You have a column of names and a column of dates.
Do a Sort using the Name as the first criterion (Ascending is fine) an
Date as the second criterion (Descending is ESSENTIAL).
The reason for that is that we are aiming for a first-in-first-ou
pruning of the records and we will be working from the bottom of th
database to the top in due course.

3. Now go to Data|Subtotals on the menu and make the followin
choices:
-----At each change in NAME
-----Use function COUNT
----Add Subtotal to any convenient column which doesn't have an
numbers greater than 3 in it : a text column will do fine. Later o
I'll be using column H, but that's only because it suited my data.
-----Check the box for Summary below data (probably alread
checked)
-----Check the box to replace current subtotals (you don't need t
because (at the moment) you haven't got any and it's probably alread
checked.) Just my belt & braces.
Now click OK.
Be patient if you have much data in there. Could be time for a cuppa.

4. At the end of this, you should have little groups of records wit
the same name and a number in your chosen column (mine was H)tellin
you how many there are in each group. If you had to do the res
manually, this wouldn't be an unhelpful point to start from. But yo
don't.

5.Next, open the code page associated with your worksheet. Alt + F11 i
a quick way.
In this code page paste in the following sub:

Private Sub CommandButton1_Click()
Application.ScreenUpdating = False ''''''''To stop flashing & speed i
up
Dim r as Integer
Dim s as Variant

For r=1000 to 2 Step -1
s=Range("H" & r).value'''''''''' This is the H I mentioned above
if IsNumeric(s) then
If s>3 then Rows(r-1).Delete
End If
Next r
End Sub

6.All you need now is a trigger for this. I'm sure there are mor
elegant ways than this but I simply but a command button on th
worksheet. Assuming there are no others there already using its name
it will connect to the above routine.

Save your work several times along the way and particularly now.

Click the button and the result should be a list as you have specified
It took about 2 minutes to run on my machine with 14000+ records wit
from 1 to 9 entries against each name.

Stopping again.
Let us know how you get on

Alf

PS If there's anybody out there can check my work I'd be more tha
gratefu

 

[AlfD]

Dark Horse (Or is it Dave)?

One other thing I omitted to mention. Make sure auto-calculation is
enabled when running the macro, which depends on the sub-totals
updating themselves on the fly.

You probably leave auto-calculate on all the time, but I thought I'd
best mention it: belt and braces again.

Alf

 

[Dark Horse]

1. Check, all the names ARE unique.

2. I ALWAYS sort by name ascending and date descending - its a golden rule
here.

3. I have to highlight the name column first - right?
I think I managed to figure that out.
And At each change in NAME I substitute whatever title I have for that
column?
Add subtotal to....
Here I am not being given any choice, the column heading is preselected with
a tick in a box - and if I remove that tick I cannot use the OK button.
So I click on OK and the progress boxes crawl across the screen while I wait
nervously.
Eventually I am presented with a new column A with a name and the word Count
beside the name.
Also, my old row 1 with the column names/titles in has disappeared
completely.
In the old column A, which has now become column B I have the names as
before - but with a break after each set with a number in it corresponding
to the number of entries for each name.

4. I copied your sub into a blank document and removed your comments in
preparation for pasting into my sheet.
Unsure what to replace your H with, should it be A or B? Tried A and B both
just to be sure.

5. I used alt/F11 and was presented with a blank grey screen.
I selected view code from the menu under view to get a blank sheet to paste
into.
Was that correct, or should I have taken a different step?
Once code was pasted I clicked the X in top right corner as I didn't know
what else to do.

6. That's where I get stuck, because nothing seems to happen.
What exactly is the command button and where is it?
I tried Run Sub/UserForm from within the code page and nothing happened.
I tried F5 and nothing happened.
Or is there some step that I have missed?

 

[Dark Horse]

It is Dave, the other is a nickname.
Auto calc WAS on .

 

[AlfD]

Hi Dave!

I suggest you get a copy of your penultimate post in front of you (th
longish one running from 1 to 6). We need to sing from the sam
hymn-sheet.

1. Fine
2. Fine.
3. No. I admit wasn't explicit about this. Don't select a column. Jus
click on a cell in the body of the data table. Now you will be able t
track through the points in my last notes on Subtotals.

[Learning point: You knew you had made a decision (hence your questio
"-right?"). It didn't deliver the scenario I predicted. If you trus
me: you have to be prepared to question your own assumptions and g
back and try another one.]

So: you need to go back to point 3 and just click on a cell in the dat
table. Take it from there and follow the original script.


Now: There's another small deviation.

4. No: don't copy my sub into a blank document and take out it
comments. Why? Because you don't need to and it's just anothe
potential source of error. So why take chances? See below.

The issue of whether to take A or B should resolve itself if you stic
to the straight and narrow on 3 above. The main thing is that it is th
column with Names which is going to be the determinant of when
subtotal will be inserted.

5. Now to the sub.

First thing is: don't do this bit before the first part is up an
secure. Sort out the Subtotals step until you have a table with ALL o
your data columns available and sub-divided by name into neat bundle
with a number in an appropriate column. (I described that last time.)

So: it looks as though you got to the code page and pasted the sub
Good. (But if there are still problems later, this is the point I woul
want to look at first.)

Now you need to get back to the worksheet with your database on it.
One way is to click the Microsoft Excel button on the task bar at th
bottom of the screen. There are various ways: they aren't all tha
intuitive. Your click on the big X probably did the job fine.

6. This is the simple bit! Go to View|Toolbars and select Contro
Toolbox. This will produce a toolbar which you might like to drag t
the side of the screen and park there for convenience.

Now hover your cursor over the 6th item down which looks like a butto
and carries the tooltips message Command Button. Left-Click on this
Now move your cursor to a convenient spot on your database sheet. Yo
should have a cross-hair cursor now. Draw a box with your mouse (lef
button down). Any size: any location will do.

This is to be the command button I referred to.

I'm going to assume (FLW) that you have no other buttons on this sheet
Tell me if you have.

Go back to the Control Toolbox toolbar. Click the first box (toolti
should say Exit Design Mode).

Now (for the nth time) save your work.

Ready now to go. Click on this button we have just created.

Tell us how you get on.

Al

 

[Dark Horse]

Hi Alf,
Clicking a cell in the body of the data table?
Does this mean click anywhere in the spreadsheet?
Say for instance cell D7?
Because if it do we have a problem.
If I do that I get a warning message:
Microsoft Excel cannot determine which row in your list or selection
contains column labels, which are required for this command.

However I think I managed to get around that by selecting a cell in column A
(the column I want counted) and then OK, but I had to select a column within
the sheet to mess up with the output - it wouldn't let me use an empty one -
which would have made more sense to me.
So, maybe data table means the column I want to work with?

Okay, I followed all the steps and ended up with my data split into neat
little packages of names, then Count and the total in the designated column.
I pasted the sub, and changed the H to A.
Swapped to the excel sheet and created the command button.
Clicked on said button and ..............................
Nothing.
Nothing at all seems to happen, the command button gets a nice dotted line
around it, the sheet flickers - but essentially it all remains exactly as it
was before the button was pressed.
Should I have changed the r to the column where I placed the subtotals?
I won't try this without advice, just in case because I feel I may have
missed some earlier step somewhere - or done something wrong.
Over to you

Hi Dave!

I suggest you get a copy of your penultimate post in front of you (the
longish one running from 1 to 6). We need to sing from the same
hymn-sheet.

1. Fine
2. Fine.
3. No. I admit wasn't explicit about this. Don't select a column. Just
click on a cell in the body of the data table. Now you will be able to
track through the points in my last notes on Subtotals.

[Learning point: You knew you had made a decision (hence your question
"-right?"). It didn't deliver the scenario I predicted. If you trust
me: you have to be prepared to question your own assumptions and go
back and try another one.]

So: you need to go back to point 3 and just click on a cell in the data
table. Take it from there and follow the original script.


Now: There's another small deviation.

4. No: don't copy my sub into a blank document and take out its
comments. Why? Because you don't need to and it's just another
potential source of error. So why take chances? See below.

The issue of whether to take A or B should resolve itself if you stick
to the straight and narrow on 3 above. The main thing is that it is the
column with Names which is going to be the determinant of when a
subtotal will be inserted.

5. Now to the sub.

First thing is: don't do this bit before the first part is up and
secure. Sort out the Subtotals step until you have a table with ALL of
your data columns available and sub-divided by name into neat bundles
with a number in an appropriate column. (I described that last time.)

So: it looks as though you got to the code page and pasted the sub.
Good. (But if there are still problems later, this is the point I would
want to look at first.)

Now you need to get back to the worksheet with your database on it.
One way is to click the Microsoft Excel button on the task bar at the
bottom of the screen. There are various ways: they aren't all that
intuitive. Your click on the big X probably did the job fine.

6. This is the simple bit! Go to View|Toolbars and select Control
Toolbox. This will produce a toolbar which you might like to drag to
the side of the screen and park there for convenience.

Now hover your cursor over the 6th item down which looks like a button
and carries the tooltips message Command Button. Left-Click on this.
Now move your cursor to a convenient spot on your database sheet. You
should have a cross-hair cursor now. Draw a box with your mouse (left
button down). Any size: any location will do.

This is to be the command button I referred to.

I'm going to assume (FLW) that you have no other buttons on this sheet.
Tell me if you have.

Go back to the Control Toolbox toolbar. Click the first box (tooltip
should say Exit Design Mode).

Now (for the nth time) save your work.

Ready now to go. Click on this button we have just created.

Tell us how you get on.

Alf

 

[AlfD]

Dave:

Have you got columns in your database with no headings? That woul
explain the warning message. Always a good idea to have headings.

Al

 

[AlfD]

Right Dave:

I've read the rest of your post.

First: are you happy with the reconstructed data (after subtotals)
Does this "look" right? (You will know your data well enough to b
surprised if Mrs Bloggs put in 23 orders when you knopw its only 5).

Let's assume you like the look of it.

You say "nothing at all seems to happen". But you say the shee
flickers.

At what point did you cease to have flickers? Did you hit something t
stop them? Did they cease by "the effluxion of time" as they say? Ar
they still continuing?

An allied question: how many rows in your data?

Because, as I said some long time ago, this can take time and might b
hampered by too much data for your system.

BTW: you wouldn't get much joy from putting the count in an empt
column. Nothing there to count...


Al

 

[Dark Horse]

One column didn't have a heading, but it does now

 

[Dark Horse]

OK Alf,
I'm happy that there are no glaring errors in the reconstructed data, it all
looks and feels right.
I'm presuming that at some time - when this all works as I hope - it will
eventually look as it did to begin with, with just the excess lines taken
out to leave just 3 entries for each name?

In other words, no name Count and subtotal beneath each bunch of names?
If it doesn't that causes problems with my next macro in that it doesn't
work with that layout at all.

When I press CommandButton1, the screen flickers once only - then there is a
dotted line around the button, but no sign of anything else going on at all.
Ready appears in bottom left corner, and that is usually gone when some
other operation is taking place and reappears when an operation is completed
I think.

I have 100 lines/rows of data, and I have left the machine untouched by
human hands for up to 30 minutes so far - as I thought that would be enough
for just so little data?

 

[AlfD]

Dave:

Yes: you can drop the subtotals at any time. The dialogue box you use
to put them in contains a button to "Remove all".

With 100 records 30 seconds would likely be excessive. So it sounds a
thought the button isn't connected to the sub. Let's find out.

Have the Control Toolbox toolbar visible. Now identify the first butto
on it. This button toggles between Design Mode and Exit Design Mode
You want to be in design mode. You'll know you are when the toolti
reads "Exit Design Mode".

Now go to your button and click on it. It should "grow" handles. The
click the Properties button on the Control Toolbox - its tooltip say
"Properties".

The Properties window appears. What is the name in the line labelle
Name? I'm expecting it to be CommandButton1. If it doesn't, have yo
another button somewhere that is?
No matter.

Close this window (use the x).

Double-left click on "our" button. This opens the code page associate
with your worksheet. In particular, it will do one of two things
Either it will take you to the sub we inserted or it will create th
top and bottom lines of a new sub.

If it is the latter, copy the original sub in to the space between th
these two lines making sure you delete the top and bottom lines of th
*original* sub. (because its name is wrong).

If it actually links you to the original sub then return to th
worksheet, click the "Exit Design Mode" button and try your comman
button again.

Al

 

[Dark Horse]

Hi Alf,
Right, a lot to get through.


You'll have to walk me through "Remove all" when and if we get this thing
working.

I had the Control Toolbox toolbar visible.
The first button was not in design mode, so I clicked it to enter design
mode.

I clicked on the button and it did "grow" handles.
Then I clicked the Properties button on the Control Toolbox.
The Properties window appeared, and the name in the line labelled Name was
CommandButton1.
I closed the properties window (using the x).

I did double-left click on "our" button and that did open the code page
associated with my worksheet.
The flashing cursor appeared after 'Private Sub CommandButton1_Click()
in a blank line before
Application.Screenupdating = False.

I took it that this meant it had linked to the original sub and so returned
to the worksheet and clicked the "Exit Design Mode" button and tried the
command
button again.
There was a brief flicker, and the command button remained with dotted lines
around it - and the sub didn't do what it was supposed to.
However, the control toolbox toolbar remained visible, and I thought that
should have disappeared after I clicked Exit Design Mode - so I close it by
clicking the X.
Pressed the button once more, and got the same response - it has still not
removed names that appear more than 3 times.

Dave

 

[AlfD]

Hi Dave!

You once wrote you had some small familiarity with debugging (my words
not yours).

Want to try? (I have my ways, but I'm no expert and I may not use th
best ways of doing things. But they usually work for me.)

Go to the sub.
Change the screenupdating value from false to true. Then we can see th
action if there is any.

Set your cursor in the line Next r and then press F9. The brow
breakpoint marking should show.

Back to the worksheet. Use control toolbox to put "handles" our butto
and drag it down somewhere near your last rows of data. Again, so w
can see the action if there is any.

Now Exit Design Mode. Then click our button.

Describe to me what happens.

Al

 

[Dark Horse]

Everything flashed, and then I was taken to the visual basic screen where
the brown dot (breakpoint) is and Next r had a yellow arrow over the brown
dot and next r was highlighted in yellow.