• Status: Solved
• Priority: Medium
• Security: Public
• Views: 262

# Lotus 123 help!!!

Hi,
first excuse my english it isn't so perfect...

well, I have a file with more o less 4000(this change monthy) rows of data
ok, I need a macro or a script that tell me how many rows with data there are, then extract the 5 percent of this data to another worksheet.
explaining:
if I have 4000 rows with data in the file I need to extract the 5% of this data, (4000/100)*5, to another worksheet. but it have to be "sorter", if I run the macro/script again it can't give me the same information...

I don't know if I was clear, but try to understand, please, my original language is spanish, so excuse my english again, thanks
0
CyJacK
• 9
• 5
1 Solution

Commented:

To find out how many rows of data you have insert the following formula above or below your data @count(from..to)
so if the data is on Sheet A and starts on row 5 and ends on row 3987 and starts in column A then the formula would be @count(A:A5..A:A3987)

To extract the 5% of the data exactly the same every time I will need to know the criteria which determines how you are going to sort the data. If you can give a small set of data as an example that would help.

Please also let us know how you want the data in the second sheet. Do you want it as formulae or as values?

Hope to hear from you soon.
0

Author Commented:
Well, is a phone numbers database, what I really need is a script o macro.
I want this script/macro count me the numbers I have in the sheet, the quantity of numbers change every 2 days, so,
I need to extract the 5% of this quantity, this percent go to another sheet for printing.
0

Commented:
CyJacK - The @count() formula I gave last time will do the counting for you but will not tell you whether they are new numbers.

I am still finding it very difficult to understand what it is that you are trying to do.

My understanding of your requirement is as follows:

* You have a list of telephone numbers which are either changed or added to every day. By changed I mean that some people get new numbers so the entry is up-dated.

* You want to indentify all the changed or new entries and copy them to a new sheet.

I do not understand the 5% requirement. Is the 5% your estimate of the number of changes or new entries each day?

Please describe much more completely exactly what you want to do. Give examples and the results you want. After I get that I am sure I will be able to help.
0

Author Commented:
okey...
let's see.

let me explain you...

Imagine you have a database of 4000 phone numbers and names.

You have to make call to the 5% of this numbers.

but, you can't call the sames numbers everyday.

so, this 5% have to be diferent every time.

The database is update every two days, so there can be 6500 numbers 14000 numbers, etc.

I need to count how many numbers are in the sheet.
(I used @count(range) thanks for that).

ok.

Know I want to extract the percent of the quantity in the sheet, if I have 4000 numbers I've to take 200 numbers to another sheet...

those 200 numbers can't be the same, if I do the process again.

I am always in the messenger from 8:00 am to 5:00pm :
pichardopavel@hotmail.com

Thanks for dedicate time to my problem...
0

Commented:
Excellent - I have only just read you message.

I think the best way is to use a macro to enter the the date number in say column C. The macro I would suggest is:

\A     {goto}C1~
\B     {if @cellpointer("contents")<>""}{d}{branch \B}
{if @cellpointer("contents")="Z"}{quit}
'@NOW()/rv~~
{branch \B}

Copy and paste this into an unused  space of your spreadsheet. Macro \A contains macro \B so that it loops round macro \B until the line contains a Z. So on the line below the last entry in the list in column C place a Z. That just makes sure the macro does actually stop. The macro assumes that the datenumber is being put into column C.

If you run this every day you will be able to ensure that only those new entries get a new date added into column C. Then you can sort the data base by datenumber or if you format column C to a date then it will be seen as a date.

Having sorted the database by date all those with a date after a specific date will be obvious. These can then be copied onto the next sheet and you can decide the criteria which will determine those that need to be called. I'm happy to help on this point but I need some more help from you to decide how to choose one number rather than another number.

Hope this helps - please ask if you are not used to using macros and I will give you step by step instructions.

0

Commented:
CyJacK - just so you know it is 16:35 here now!
0

Author Commented:
okey, explain me step by step how do I use it.

ps. In which country you are?, I am in Dominican Republic..
0

Commented:
UK

Right let's have a go.

Let me assume that you have the database in columns A and B on Sheet A. You will use column C for the datenumber which the macro enters.

On a new sheet which you can name Macros enter the following:

In A1 put '\A
In A2 put '\B
In B1 put {goto}A:C1~
In B2 put {if @cellpointer("contents")<>""}{d}{branch \B}
In B3 put {if @cellpointer("contents")="Z"}{quit}
In B4 put '@NOW~/rv~~
In B5 put {branch \B}

Highlight (with the mouse) A1 and A2, go into Range/Name/UseLabels. As soon as you have pressed the UseLabels button \A and \B should show in the dialogue box as Named Ranges. Press OK.

By the way it is important for you to enter '\A in A1 not just \A. Try it and you will see the difference!

To make the macro work, place the Z at the bottom of column C and then press CTRL+A  just once. Even if you are in the Macros sheet the \A macro will go to Sheet A and start in column C.  If you forget to put the Z in the sheet, to stop the macro just press CTRL+BREAK.

The macro works by, first of all going to A:C1, checking whether there is anything in the cell, if there is it moves down one cell, if it is empty it enters the datenumber by using @NOW and converting that to a value. It needs to be a value so that it is not up-dated every time Enter in pressed. It then loops round again to the beginning of \B.

This should work but if it doesn't do let me know and we'll work out what the problem is.

Regards
0

Commented:
For some reason which I cannot understand at the moment one of the lines of the macro will not work - so please use the following instead:

In A1 put '\A
In A2 put '\B
In B1 put {goto}A:C1~
In B2 put {if @cellpointer("contents")<>""}{d}{branch \B}
In B3 put '@NOW~/rv~~
In B4 put {branch \B}

You will need to stop the macro with CTRL+BREAK as the line that was "In B3 put {if @cellpointer("contents")="Z"}{quit}" - does not work.

I have used that sort of line many times before so I really do not understand why it will not work this time. If I manage to get it to work I will let you know.
0

Author Commented:
ok, great!!

But, I still have the problem of extrating the 5% of this numbers.
example:

Sheet A               Sheet B
Column A                        Column A

1     5692408               2204586
2      2204586               6985489
3      5698684               2201111
4       2204597               2154854
5        6985489               4568984
6     2154854
7      5692402
8     2201111
9     4568984
10    5684598
11    ....

Ok, lets think the sheet A is my database, so I daily extract a 50% of the numbers, I have 10 numbers in column a so, the 50% is 5 number
this 5 numbers that represent the 50% of Sheet A have to go to Sheet B.
but it have to be a ramdom process, if I do the process again the numbers in sheet B have to be diferent.

Example 2: Here we make the process again with the same numbers...

Sheet A               Sheet B
Column A                        Column A

1     5692408               5692408
2      2204586               2204597
3      5698684               5692402
4       2204597               4568984
5        6985489               6985489
6     2154854
7      5692402
8     2201111
9     4568984
10    5684598
11    ....

Ojala y lo entiendas,pero gracias por tu ayuda, realmente me estas ayudando...

(Oj Allah(wanna God) you understand it, but thanks for you help, you're really helping me)
0

Commented:
Ok let's have a go.

Create a new macro as follows:

\D {goto}A:D1~
'@int(@rand*100)/rv~~
{d}
{branch \D}

Since you now know how to create and use macros now, I have not given the full instructions. If you need them let me know.

This macro goes to A:D1 and inserts a random number less than 100 and converts it into a value and then moves down a line. This can then be used for your random selection of numbers.

On Sheet B put the following formula in B:A1 and then copy it down the column:

@IF(A:D1<B:D1,A:A1,"")

This will copy all those telephone numbers which have a random number less than the contents of cell B:D1  Obviously you can change cell B:D1 to what you want. In B:D1 you need to have one number which if you change only that one cell it will change the selection criteria. This will give you much greater flexibility.

Hope that helps
0

Commented:
CyJacK - How did you get with this one? - patrickab

0

Author Commented:
Ohhh, I'm sorry for don't give you the thanks(I was Sick and I can't get connected ), you really help me a lot...

0

Commented:
CyJacK - good to hear from you - sorry to hear that you've been ill hope you are getting better now.