PAB Please help

[bloubul]

Hi PAB
I trust that you are well. Please help me with the following formula.
I don't want to see "FALSE", I just want to see the numbers 12, 13, 16 etc.
How can I hide the "FALSE". In column "A" is 1 - 49
I J M
25 23.5 FALSE
27.5 29.5 FALSE
24 25.3 FALSE
23.5 23.2 FALSE
23 23.3 FALSE
28 27.5 FALSE
25.5 26.7 FALSE
27.5 28.3 FALSE
25.5 24.8 FALSE
23.5 23.7 FALSE
27.5 25.7 FALSE
25.5 25.5 12
25.5 25.5 13
27 26.7 FALSE
25 25.5 FALSE
29.5 29.5 16
27.5 28.7 FALSE
25 25.8 FALSE
26.5 24.7 FALSE
27 26.7 FALSE
24.5 26.5 FALSE
23 25.0 FALSE
26 26.7 FALSE

The formula I use are: =IF(OR(I2=J2),A2)

Thanks in advance

BlouBul

 

[PAB]

Hi BlouBul,

Do you mean something like this...

=IF(I2=J2,A2,"")

I hope this helps!

Regards,
PAB


-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
(1) Mathematics is the language of nature.
(2) Everything around us can be represented and understood through numbers.
(3) If you graph the numbers of any system, patterns emerge. Therefore, there are patterns, everywhere in nature.

 

[bloubul]

Hi PAB
Thanks Pal it works

BlouBul

 

[PAB]

Hi BlouBul,

Hi PAB,
Thanks pal it works.

You're welcome, I'm glad it works!

Regards,
PAB


-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
(1) Mathematics is the language of nature.
(2) Everything around us can be represented and understood through numbers.
(3) If you graph the numbers of any system, patterns emerge. Therefore, there are patterns, everywhere in nature.

 

[bloubul]

Hi PAB

I have several excel books with on sheet on with a macro. I want to create one book with all the sheets on it. How can I ensure that the macro's will still run at max and not at a snail's pace. I will be using ASAP Utilities 5 to import the sheets into one book.

BlouBul

 

[PAB]

Hi BlouBul,

I have several excel books with on sheet on with a macro.

Sorry but I don't understand what you are trying to say above!

I want to create one book with all the sheets on it. How can I ensure that the macro's will still run at max and not at a snail's pace. I will be using ASAP Utilities 5 to import the sheets into one book.

I have never used this utility so I can't comment on it's peformance or success rate.

Whenever I want to consolodate more than one WorkBook what I usually do is to open up the first one and use this as my Master WorkBook. I then load the second WorkBook and then just move the WorkSheet/WorkSheets to exactly where I want them to appear in the Master WorkBook. I do this for the rest of the WorkBooks until I finish up with the way I want the Master WorkBook to look.

There are benefits on doing this task the way I have described which I will briefly explain...

(1) Moving the WorkSheets from one WorkBook to another safeguards the existing WorkBook, if you don't save it when you close it of course. It can then be deleted when you are 100% sure everything is as you want it.

(2) You can move ALL the WorkSheets at once to either BEFORE or AFTER existing WorkSheets.

(3) You can move the individual WorkSheets and place them EXACTLY where you want to in the new WorkBook.

IMPORTANT

Doing the task by the method I have outlined above will NOT automatically move the Macro's. You will need to copy and paste them into the new WorkBook.

I have all my Macro's in a personal WorkBook (PERSONAL.XLSB), so copying the Macro's isn't an issue.

I hope this helps!

Regards,
PAB


-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
(1) Mathematics is the language of nature.
(2) Everything around us can be represented and understood through numbers.
(3) If you graph the numbers of any system, patterns emerge. Therefore, there are patterns, everywhere in nature.

 

[PAB]

Hi BlouBul,

How can I ensure that the Macro's will still run at max and not at a snail's pace.

I forgot to answer your question sorry!

The easiest answer is that because you have NOT changed the code then it will run at exactly the same speed as it did before, BUT, this is NOT the case, it depends how big your new WorkBook is going to be. I say this because the bigger the WorkBook the MORE resources Excel uses while it is open, and this has an impact on the time it takes to run a Macro. For relatively small WorkBooks though there will not be any significant difference.

I hope I have explained this clearly enough!

Regards,
PAB


-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
(1) Mathematics is the language of nature.
(2) Everything around us can be represented and understood through numbers.
(3) If you graph the numbers of any system, patterns emerge. Therefore, there are patterns, everywhere in nature.

 

[PAB]

Hi BlouBul,

Did you manage to merge ALL the WorkBooks & Macro's OK?
Was any of the information I gave you helpful?

Regards,
PAB


-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
(1) Mathematics is the language of nature.
(2) Everything around us can be represented and understood through numbers.
(3) If you graph the numbers of any system, patterns emerge. Therefore, there are patterns, everywhere in nature.

 

[bloubul]

Hi PAB

I'm not completly done with it, as I had to copy all macro's to a new file.
I have tested three macro's on the new book, but the macro's runs at a snails pace, it takes forever to finish, etc "Remove Duplicates" , "PowerBall Random". I had to press the Esc key to get them moving. How can I rectify the problem. Your info are allways on the mark, thank you.

BlouBul

 

[PAB]

Hi BlouBul,

I'm not completly done with it, as I had to copy all macro's to a new file.
I have tested three macro's on the new book, but the macro's runs at a snails pace, it takes forever to finish, etc "Remove Duplicates", "PowerBall Random". I had to press the Esc key to get them moving. How can I rectify the problem. Your info are always on the mark, thank you.

There are a few things that spring straight to mind.

(1) Is the new WorkBook showing that any of the WorkSheets are linked to external files?
(2) Are the Macro's in seperate Modules or consolodated into one?
(3) Are the Macro's all standard Macro's or are any of them WorkSheet Macro's?
(4) What is the Excel file size of the WorkBook?
(5) Did you think about setting up a PERSONAL.XLSB as I have previously suggested?
(6) Are the Macro's run from a button within the WorkSheet or are they run by referencing the WorkSheet within the code?

To be honest, without seeing the actual WorkBook it is going to be very difficult to figure out the reason why they run slower than they did before.
Perhaps if you uploaded the WorkBook I could take a look at it for you!

Regards,
PAB


-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
(1) Mathematics is the language of nature.
(2) Everything around us can be represented and understood through numbers.
(3) If you graph the numbers of any system, patterns emerge. Therefore, there are patterns, everywhere in nature.

 

[blitzed]

man that sounds like a lot of trouble...shoehorning tons of functionality into a spreadsheet

sometimes I think programming a spreadsheet, is actually more trouble than it would be to write a program in one of numerous languages to do the same thing

it would be interesting to compare resource usage between Excel & a local PHP/MySQL database solution.

cya,
blitzed

 

[PAB]

Hi Blitzed,

That sounds like a lot of trouble...shoehorning tons of functionality into a spreadsheet.

Sometimes I think programming a spreadsheet, is actually more trouble than it would be to write a program in one of numerous languages to do the same thing.

It would be interesting to compare resource usage between Excel & a local PHP/MySQL database solution.

I am not quite sure if I am following what you are saying!
Are you saying that a comparison between a SpreadSheet method such as Excel versus a DataBase method such as Access?

Regards,
PAB


-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
(1) Mathematics is the language of nature.
(2) Everything around us can be represented and understood through numbers.
(3) If you graph the numbers of any system, patterns emerge. Therefore, there are patterns, everywhere in nature.

 

[bloubul]

Hi PAB
Thanks for the offer to help with the macro's, luckely it’s only one or two macro’s so to press the “Esc” key twice is fine.

BlouBul

 

[PAB]

Hi BlouBul,

Thanks for the offer to help with the macro's, luckely it’s only one or two macro’s so to press the “Esc” key twice is fine.

OK, no problem!

I just thought that I if I took a look at them I might be able to see a way to speed them up as far as the processing time.

By the way, I don't see why you have to press the "Esc" key, is it because the program is not running correctly and stops at certain points within the execution of the Macro. If so, you need to add code to get it to exit the Sub if certain conditions are met, therefore you will NOT have to press the "Esc" key!!!

Regards,
PAB


-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
(1) Mathematics is the language of nature.
(2) Everything around us can be represented and understood through numbers.
(3) If you graph the numbers of any system, patterns emerge. Therefore, there are patterns, everywhere in nature.

 

[bloubul]

Hi PAB
I'm calling on your help.
I have uploaded for you the file with the macro's that's given me headaches.
The are the "RED" tabs. Just press the macro button.

http://www.mediafire.com/view/ma9tiumyipf9cry/Lotto-Selections.xlsm

Thank you in advance

BlouBul

 

[PAB]

Hi BlouBul,

I have uploaded for you the file with the macro's that's given me headaches.
The are the "RED" tabs. Just press the macro button.

http://www.mediafire.com/view/ma9tiumyipf9cry/Lotto-Selections.xlsm

I have downloaded the file and had a quick look at it, and I must stress, a quick look at it.

It seems to be quite involved and I will need to study it in more detail to fully understand what it is exactly that you are trying to do!

For example, on the WorkSheet Eliminations-6, there are formulas in columns T:Y that are not initially apparent.

Also, on the WorkSheet Selection_Lotto&Plus, there are formulas in column L that are not initially apparent.

Also, in the WorkSheet Ticket_Printing_Lotto column M, is there any reason why you have used the Space Bar to create blank spaces? It would have been better to structure the formula by using the Alt-Enter key combination to list each of the formulas under each other, this makes it easier to understand and if need be amend the formulas?

I have had a quick look at the coding and see that you are using the Scripting Dictionary etc.

Does the WorkBook take a long time to calculate by any chance???

Regards,
PAB


-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
(1) Mathematics is the language of nature.
(2) Everything around us can be represented and understood through numbers.
(3) If you graph the numbers of any system, patterns emerge. Therefore, there are patterns, everywhere in nature.

 

[bloubul]

Hi PAB
I hope that this will help, and thank you for looking at it.
The formula in Worksheet Eliminations-6 in columns T:Y is there to eliminate any combinations in columns N:S if there is 3 OR more numbers of the last drawing in the next combinations in columns H:M
WorkSheet Selection_Lotto&Plus, the formulas in column L is of none importance and can be ignored.
WorkSheet Ticket_Printing_Lotto, formulas, in column M, I'm no excel "GURU" and that was the way I could come up with a formula (way) to display what I want, I will be great full if you could give me a better way to do it.
As far as the coding is concerned it was trial and error with copy and paste from the internet, once again if you will
conceder to correct the coding for me.
No the formula’s does not takes long to calculate it is there instantly only the macro’s that’s the real problem.

BlouBul

 

[PAB]

Hi BlouBul,

Firstly, why are you using INDIRECT on the WorkSheet Wheeling?
Using INDIRECT slows down calculation for any data entered within that WorkSheet???
I am actually finding that your WorkBook is very slow to calculate anything really!!!

As for the Duplicates Macro, ignore the one that you are currently using and insert this code into a Module and run it.

Option Explicit
Option Base 1

Sub Remove_Duplicates_PAB()
' =======================================================================================
' Author - Algorithm written by PAB on the 18-08-2013.
' Objective - Remove duplicates from a range based on previous values.
' Recipient - BlouBul.
' Caveats - None.
' =======================================================================================
With Application
.ScreenUpdating = False: .Calculation = xlCalculationManual: .DisplayAlerts = False
End With
Range("R1:X" & Range("A" & Rows.Count).End(xlUp).Row).ClearContents
Range("R1").Select
Range("R1:X1").Value = Range("A1:G1").Value
Range("R2:X" & Range("A" & Rows.Count).End(xlUp).Row).Formula = _
"=IF(SUMPRODUCT(COUNTIF(A2,$A$1:$G1))>0,"""",A2)"
Range("R1:X" & Range("A" & Rows.Count).End(xlUp).Row).Value = _
Range("R1:X" & Range("A" & Rows.Count).End(xlUp).Row).Value
Range("E2").Select
With Application
.DisplayAlerts = True: .Calculation = xlCalculationAutomatic: .ScreenUpdating = True
End With
End Sub

Please let me know what you think!

Regards,
PAB


-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
(1) Mathematics is the language of nature.
(2) Everything around us can be represented and understood through numbers.
(3) If you graph the numbers of any system, patterns emerge. Therefore, there are patterns, everywhere in nature.

 

[PAB]

Hi BlouBul,

Firstly, please answer the questions I asked in the previous post before answering this post, thank you.

As for the Lotto Macro, ignore the one that you are currently using and insert this code into a Module and run it.

Option Explicit
Option Base 1

Sub Calculate_Concatenated_Lotto_String_PAB()
' =======================================================================================
' Author - Algorithm written by PAB on the 18-08-2013.
' Objective - Calculate [Concatenated] Lotto string.
' Recipient - BlouBul.
' Caveats - None.
' =======================================================================================
Dim p(5000, 49) As Integer
Dim i As Long, j As Long, k As Long
Dim s As String
Dim C As Variant
Dim rng1 As Range, rng2 As Range
With Application
.ScreenUpdating = False: .Calculation = xlCalculationManual: .DisplayAlerts = False
End With
Set rng1 = Range("C2:C50000")
Set rng2 = Range("J3:J51")
i = 0
For Each C In rng1
If C.Value <> Empty Then
i = i + 1
For j = 0 To 6
p(i, C.Offset(0, j).Value) = C.Offset(0, j).Value
Next
End If
Next
For Each C In rng2
k = 0
If C.Value <> Empty Then
s = ""
For j = 1 To i
If p(j, C.Value) = C.Value Then
If s = "" Then
s = k
Else
s = s & "," & k
End If
k = 0
End If
k = k + 1
Next
C.Offset(0, 1).Value = s
End If
Next
With Application
.DisplayAlerts = True: .Calculation = xlCalculationAutomatic: .ScreenUpdating = True
End With
End Sub

Please let me know what you think!

Regards,
PAB


-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
(1) Mathematics is the language of nature.
(2) Everything around us can be represented and understood through numbers.
(3) If you graph the numbers of any system, patterns emerge. Therefore, there are patterns, everywhere in nature.

 

[PAB]

Hi BlouBul,

Firstly, please answer the questions I asked in the two previous posts before answering this post, thank you.

As for the Sort Macro, ignore the one that you are currently using and insert this code into a Module and run it.

Option Explicit
Option Base 1

Sub Sort_Lotto_Numbers_Ascending_Left_To_Right_PAB()
' =======================================================================================
' Author - Algorithm written by PAB on the 18-08-2013.
' Objective - Sort Lotto numbers in ascending order from left to right.
' Recipient - BlouBul.
' Caveats - None.
' =======================================================================================
With Application
.ScreenUpdating = False: .Calculation = xlCalculationManual: .DisplayAlerts = False
End With
Dim i As Long
With Range("C2", Range("H" & Rows.Count).End(xlUp))
For i = 1 To .Rows.Count
.Rows(i).sort Key1:=.Rows(i).Range("A1"), Order1:=xlAscending, _
Orientation:=xlLeftToRight
Next
End With
With Application
.DisplayAlerts = True: .Calculation = xlCalculationAutomatic: .ScreenUpdating = True
End With
End Sub

Please let me know what you think!

Regards,
PAB


-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
(1) Mathematics is the language of nature.
(2) Everything around us can be represented and understood through numbers.
(3) If you graph the numbers of any system, patterns emerge. Therefore, there are patterns, everywhere in nature.