?
Solved

Lotus 123 help!!!

Posted on 2003-03-24
16
Medium Priority
?
256 Views
Last Modified: 2010-04-22
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
Comment
Question by:CyJacK
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 5
16 Comments
 
LVL 45

Expert Comment

by:patrickab
ID: 8203077
Your english is better than my spanish so please be patient!

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
 
LVL 1

Author Comment

by:CyJacK
ID: 8209654
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
 
LVL 45

Expert Comment

by:patrickab
ID: 8210129
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 1

Author Comment

by:CyJacK
ID: 8210893
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
 
LVL 45

Expert Comment

by:patrickab
ID: 8218988
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
 
LVL 45

Expert Comment

by:patrickab
ID: 8219003
CyJacK - just so you know it is 16:35 here now!
0
 
LVL 1

Author Comment

by:CyJacK
ID: 8219115
okey, explain me step by step how do I use it.

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

Expert Comment

by:patrickab
ID: 8219596
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
 
LVL 45

Expert Comment

by:patrickab
ID: 8223685
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
 
LVL 1

Author Comment

by:CyJacK
ID: 8224237
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
 
LVL 45

Accepted Solution

by:
patrickab earned 200 total points
ID: 8224890
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
 
LVL 45

Expert Comment

by:patrickab
ID: 8246960
CyJacK - How did you get with this one? - patrickab

0
 
LVL 1

Author Comment

by:CyJacK
ID: 8269036
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...

Thanks for yours useful answers!!

0
 
LVL 45

Expert Comment

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

If you believe I have answered your question may I ask you to Accept an answer and grade it as you believe is correct.

Regards, Patrickab
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For Desktop Techs: How to retain a user's Notes configuration data when swapping out the end user's computer. (Assuming that you are not upgrading to a completely different version of Notes client) All you need to do is: 1) install Notes o…
This is an old article, please see an updated version of this article, located here: http://www.experts-exchange.com/articles/23619/Notes-8-5x-Windows-7-Notes-info-and-tips.html
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question