Macro to delete rows

[bloubul]

PAB

Will you please help me again.

I'm looking for a macro to delete rows which contains 2 and more numbers from the previous draw. E.g.

Sheet1:
In columns A1 to F1 lies the 6 numbers from previous draw.
In columns H1 to M4000 lies my wheel.
In columns O1 to T4000 is my formula to tell me which rows in the wheel matches A1 to F1. (THe formula is the one that you did for me on 28 October 2005, repeating numbers).There are plenty of rows that contains 2 and more numbers of the previous draw, we know that it is very unlikely that more than 2 of the previous draws numbers will appear in the next draw. By deleting some rows the wheel will be more playable.

Thanks

BlouBul

 

[GillesD]

Deleting rows

I am sure PAB will come up with a solution or myself will do it, time permitting. But I would recommend one small change to your sheet.

All data in cells H1 to T4000 should be placed in cells H2 to T4001; that way any row could easily be deleted. Otherwise if there are two matches in row 1, that row cannot be deleted since if would remove data from cells A1 to F1.

Yes, deleting data only in columns H to T on a given row and moving up data in rows under it is a feasible option but I beleive this would result in a slower macro than just deleting rows.

 

[bloubul]

GillesD

Thanks for the correction, will place data as describe.


BlouBul

 

[GillesD]

Deleting rows

I may have some time for developing the macro but I would need some information. What are the values your formulas give in columns O to T so you can evaluate how many matches between the previous draw (A1 to F1) and your wheel results in columns H to M?

I could probably figure it out based on the previous post from PAB but it would be faster if you post the information here.

 

[bloubul]

Hi GillesD

I have uploaded the file for you.

http://mytempdir.com/293874

My English is not to good to explain it in writing. You can return the file in the same manner.

Thanks for your help.

BlouBul

 

[PAB]

Hi BlouBul,

Rather than Creating a Macro to Achieve this you could Use Formulas.
Place the Formula ...

=IF(COUNT($O2:$T2)>=2,"",H2)

... in Cell "V2".
Copy it Across to Cell "AA2".
Copy Cells "V2:AA2" Down to Cell "AA4001".
Save the File.
Copy Cells "V2:AA4001" & Paste Special as Values.
You could then SORT Columns "V:AA" to get Rid of ALL the Blanks.
DON'T Save the File.
Out of Interest, Why are your Combinations NOT in Ascending Order from Left to Right?. If you want this Corrected Please let me Know.

Hope this Helps.

Nice to See you Back GillesD .

All the Best.
PAB

 

[PAB]

Hi GillesD,

I Hope ALL is Well with You.
If you get Time, I have Been Trying to Create a Macro that Produces the "Covered" Combinations ( Only the "Covered" Combinations as the "Tested" Combinations can be Achieved with Excel Formulas ) for an Abbreviated Wheel, But Without ANY Success. Perhaps you would Kindly have a Look at the Thread ...

http://www.lotto649.ws/showthread.php?s=&threadid=6157

... and give me Any Ideas that you Might have Please.

Thanks in Advance.
All the Best.
PAB

 

[bloubul]

Hi PAB


A big thanks for the help.

"Why are your Combinations NOT in Ascending Order" I use two DN numbers, but on my tickets they are in Ascending order.


BlouBul

 

[PAB]

Hi BlouBul,

I am Glad it Worked.

All the Best.
PAB

 

[SRM]

Hi PAB,
I noticed that you mention sorting combinations in ascending order using Excel.
I have a problem doing this.
I can get Excel to sort a single row but not the entire set of combinations. [rows]

Column sorting works as expected but not for rows.

Any suggestions most welcome.
Thx.
SRM.

 

[PAB]

Hi SRM,

If for Example you have 6 Numbers in Cells "A1:F1" that are NOT Sorted from Left to Right, Enter the Following Formulas in Cells "H1:M1" ...

=SMALL(A1:F1,1) in Cell "H1"
=SMALL(A1:F1,2) in Cell "I1"
=SMALL(A1:F1,3) in Cell "J1"
=SMALL(A1:F1,4) in Cell "K1"
=SMALL(A1:F1,5) in Cell "L1"
=SMALL(A1:F1,6) in Cell "M1"

... and then Copy Down.

Hope this Helps.
All the Best.
PAB

 

[PAB]

Hi SRM,

Did the Formulas do what you Wanted?.

All the Best.
PAB

 

[SRM]

Hi PAB,
Thanks for the suggestions.
I am working on large systems - v30,k13,t8,m8 etc so have decided to write something in basic to load in - swap - produce a new file.
I will almost certainly try your suggestions to see if I can produce a simple excel sheet.

Cheers,

SRM.

 

[PAB]

Hi GillesD,

Have you had Any Time to have a Look at ...

I have Been Trying to Create a Macro that Produces the "Covered" Combinations ( Only the "Covered" Combinations as the "Tested" Combinations can be Achieved with Excel Formulas ) for an Abbreviated Wheel, But Without ANY Success. Perhaps you would Kindly have a Look at the Thread ...

http://www.lotto649.ws/showthread.php?s=&threadid=6157

... and give me Any Ideas that you Might have Please.

... Yet, If so, have you Any Comments or Suggestions Please.

Thanks in Advance.
All the Best.
PAB