Issue with blanks and spaces

  • Thread starter Thread starter L. Howard
  • Start date Start date
Hi Howard,

Am Thu, 20 Mar 2014 06:14:42 -0700 (PDT) schrieb L. Howard:
Sheets("Sheet2").Range("B2").Resize(lRowCount - 1) = myArr
Click to expand...

that is my bad
I did notice that Column AE2 is blank and the blank repeats every 20 rows. Does not affect the copy that your code does. All the blank rows are copied as 5 short phrases instead of 6. More troubleshooting to do.
Click to expand...

do you want to skip blank cells?
Can you send me the workbook?


Regards
Claus B.
 
Hi Howard,

Am Thu, 20 Mar 2014 06:14:42 -0700 (PDT) schrieb L. Howard:
I did notice that Column AE2 is blank and the blank repeats every 20 rows. Does not affect the copy that your code does. All the blank rows are copied as 5 short phrases instead of 6. More troubleshooting to do.
Click to expand...

is following code that what you want?

Sub CopyToA2()
Dim i As Long, j As Long
Dim myStr As String
Dim myArr As Variant

Application.ScreenUpdating = False
For i = 2 To 2001
myStr = ""
For j = 31 To 41 Step 2
If Len(Replace(Cells(i, j), " ", "")) > 0 Then
myStr = myStr & Cells(i, j)
End If
Next
Cells(i, 1) = myStr
Next
myArr = Range("A2:A2001")
Sheets("Sheet2").Range("A2").Resize(rowsize:=UBound(myArr)) = myArr
Application.ScreenUpdating = True
End Sub


Regards
Claus B.
 
Hi Howard,

Am Thu, 20 Mar 2014 15:00:24 +0100 schrieb Claus Busch:
is following code that what you want?
Click to expand...

if the result is what you want try following code. It is a bit faster.
If not please send me the workbook with the expected result.

Sub CopyToA2_2()
Dim i As Long, j As Long
Dim myStr As String
Dim myArr As Variant

Application.ScreenUpdating = False
myArr = Range("AE2:AO2001")
For i = LBound(myArr) To UBound(myArr)
myStr = ""
For j = 1 To 11 Step 2
If Len(Replace(myArr(i, j), " ", "")) > 0 Then
myStr = myStr & myArr(i, j)
End If
Next
Cells(i + 1, 1) = myStr
Next
myArr = Range("A2:A2001")
Sheets("Sheet2").Range("A2").Resize(rowsize:=UBound(myArr)) = myArr
Application.ScreenUpdating = True
End Sub


Regards
Claus B.
 
Hi Claus,

https://www.dropbox.com/s/w7swpuj776m8w0y/Copy of Title Builder Randomizer rev 2.2 Drop Box.xlsm

I think we are very close.

The sheets of concern at this time are Publish Data, Title Builder and Description Builder.

Title code needs to fill column A of Title sheet and column B of Publish.

Description code needs to fill column A of Description sheet and column E of publish.

Mostly it is doing that but the list seem to be correctly done but not identical.

That is: the list on Titles column A and the list on Publish column B are different from one another after each running of the code.

The same for Description.

Also need to be able to run Titles and Description code from Publish sheet. There is a button for each. This seems to somewhat of a problem.

I'll be dealing with the other sheets to do similar work, but hopefully I can use the correctly done code for Titles and Descriptions to guide me.

Thanks for taking a look.

Howard
 
Hi Howard,

Am Thu, 20 Mar 2014 11:00:00 -0700 (PDT) schrieb L. Howard:
The sheets of concern at this time are Publish Data, Title Builder and Description Builder.
Title code needs to fill column A of Title sheet and column B of Publish.
Description code needs to fill column A of Description sheet and column E of publish.
Mostly it is doing that but the list seem to be correctly done but not identical.
That is: the list on Titles column A and the list on Publish column B are different from one another after each running of the code.
The same for Description.
Also need to be able to run Titles and Description code from Publish sheet. There is a button for each. This seems to somewhat of a problem.
I'll be dealing with the other sheets to do similar work, but hopefully I can use the correctly done code for Titles and Descriptions to guide me.
Click to expand...

at the moment I am a little bit confused.

Should the list in column A or Title and column B of Publish be the
same?
Also for Description and Publish?
Or should be randomized between running the code?


Regards
Claus B.
 
On Thursday, March 20, 2014 11:00:00 AM UTC-7, L. Howard wrote:
Click to expand...


I believe this is another screw up on my part.

I have changed:
Sheets("Sheet2").Range("A2").Resize(rowsize:=UBound(myArr)) = myArr
To
Sheets("Sheet2").Range("B2").Resize(rowsize:=UBound(myArr)) = myArr
Thinking that was a destination...?

myArr = Range("A2:A2001")
Sheets("Sheet2").Range("A2").Resize(rowsize:=UBound(myArr)) = myArr
Application.ScreenUpdating = True
 
Hi Howard,



Am Thu, 20 Mar 2014 11:00:00 -0700 (PDT) schrieb L. Howard:













at the moment I am a little bit confused.



Should the list in column A or Title and column B of Publish be the

same?

Also for Description and Publish?

Or should be randomized between running the code?





Regards

Claus B.
Click to expand...

Each time the code is run for Titles both destinations should be identical lists.

Titles column A on Titles sheet and column B on Publish sheet.

Descriptions column A on Descriptions sheet and column E on Publish sheet.

Howard
 
Hi Howard,

Am Thu, 20 Mar 2014 11:38:22 -0700 (PDT) schrieb L. Howard:
Each time the code is run for Titles both destinations should be identical lists.

Titles column A on Titles sheet and column B on Publish sheet.

Descriptions column A on Descriptions sheet and column E on Publish sheet.
Click to expand...

I hope I understood you correctly.
Please have a look:
https://onedrive.live.com/?cid=9378...#cid=9378AAB6121822A3&id=9378AAB6121822A3!326
for workbook "Title Builder Randomizer rev 2.3.xlsm"


Regards
Claus B.
 
Hi Howard,



Am Thu, 20 Mar 2014 11:38:22 -0700 (PDT) schrieb L. Howard:






I hope I understood you correctly.

Please have a look:

https://onedrive.live.com/?cid=9378...#cid=9378AAB6121822A3&id=9378AAB6121822A3!326

for workbook "Title Builder Randomizer rev 2.3.xlsm"





Regards

Claus B.

--
Click to expand...

That is looking pretty good. I still need the Title portion to produce a single phrase instead of six phrases.

This link demonstrates what the Title output should look like.
Note column A entries are just one phrase.


https://www.dropbox.com/s/rftplrkmdwlaeh0/Title One Phrase Example.xlsm

Howard
 
Hi Howard,



Am Thu, 20 Mar 2014 13:42:20 -0700 (PDT) schrieb L. Howard:







I improved the code. Please have another look.





Regards

Claus B.

--
Click to expand...

Yes, that looks great!

I hope those codes will serve me well in finishing the workbook with the remaining sheets.

At this point I don't know what all info on the remaining sheet is the stuff to bring to Publish.

Terrific work in my eyes.

Thanks much.

Howard
 
Hi Claus,

Some advice please.

The "Title Builder" and "Description Builder" worksheets are my focus here.
I have found that if I run either Description or Title macros, they take about 190 seconds plus another 90 seconds to complete.

12,000 Titles and 2000 Descriptions to column A on their respective sheets.
The GOOD news is that duplicates are very very rare. Have yet to find one on the Description sheet and Titles I usually get from 0 to 3. These are very acceptable results.

The BAD news is the length of time to run the codes.

I have tried Calculation set to manual on both codes and the time is very acceptable, about 1 second.
The major BAD news with this is that the duplicates occur at a huge and unacceptable level. So I guess there are some necessary calculations not happening and the duplications occur.

I'm open to suggestions, perhaps do those Titles and Descriptions calculations in a separate workbook/s and transfer the data to the proper columns on the main workbook..??

This is a Demo main workbook if you need to see anything on it.

https://www.dropbox.com/s/vz387bfi02dimav/Demo Work Book rev 2.8 tester Drop Box.xlsm

Thanks.
Howard
 
This should say...

I have found that if I run either Description or Title macros, they take about 190 seconds plus another 90 seconds to complete the transfer to column A.

Howard
 
Hi Howard,

Am Sun, 23 Mar 2014 19:06:47 -0700 (PDT) schrieb L. Howard:
I have found that if I run either Description or Title macros, they take about 190 seconds plus another 90 seconds to complete the transfer to column A.
Click to expand...

if I would know what the workbook should do I could help better.
Do you need the columns AE:AO?
I thought in column A should only be 1 sentence?
Have a look:
https://onedrive.live.com/?cid=9378...#cid=9378AAB6121822A3&id=9378AAB6121822A3!326
for workbook "Demo Work Book rev2.9"
I changed the formulas in AQ and the code for
"Six_By_Six_Title_Randomizer" and "CopyToA2_2_Titles"

If you can work with this suggestion you can similiar change the rest of
the code.


Regards
Claus B.
 
I ran the Titles code in your revision and it worked pretty fast, but had about 100 duplicates. I used the "remove duplicates" feature on the Data tab and it found about 100 each time I ran the code and checked.

So, maybe the revised code with some more code at the end to remove duplicates is the answer. There would only be about 11,900 non duplicated entriesbut that might a workable solution.

The goal is for Titles to produce 12,000 single phrases in column A, with no duplicates.

Entries like this are considered a duplicate.

Greetings to you! Ordering a King Std Top? On Hand 65% savings!
Greetings to you! Ordering a King Std Top? On Hand 65% savings!

Entries like these are not duplicates.

BuenosDias - Fishing for a Contemporary Serta Queen Set? .> $below retail.
BuenosDias - Pursing a Current Beauty Rest-Bed? $$ under retail.
Buenos dias! Buying that King Comfort Set? Full Line $$ below retail,

The goal for Descriptions is to produce 2,000 entries in column A where each entry will be up to 6 individual phrases per entry.

No duplicates allowed with Descriptions also. And it seems duplicates in the Descriptions are very rare. Probably because of the multiple phrases ineach entry.

As far as need the columns AE:AO, I cannot say. Speed of the code and avoiding duplicates is paramount concern. If they can be eliminated that is okay.

I have tested the Titles in a one sheet workbook that does nothing but the 12,000 rows of single phrase entries, and the duplicates range between 0 and 4 on any given run of the code. The well written code spits the 12,000 entries out in about 1 second. That speed and that small amount of duplicates would be acceptable.

I did notice in the workbook I sent that some of the comments in the code are inaccurate, my bad and I am sorry if that caused confusion. Best to ignore them and the goals for the code is as I have stated above or as close as we can get to them.

Howard
 
Hi Howard,

Am Mon, 24 Mar 2014 06:46:51 -0700 (PDT) schrieb L. Howard:
I ran the Titles code in your revision and it worked pretty fast, but had about 100 duplicates. I used the "remove duplicates" feature on the Data tab and it found about 100 each time I ran the code and checked.
Click to expand...

I ran the Titles code also but I get a maximum of 4 duplicates. The
message of "RemoveDuplicates" is wrong.
Write in B2 (under "Len")
=--(Countif($A$2:$A$12001;A2) and copy down to B12001. After code is
through filter this column for 1.


Regards
Claus B.
 
Hi Howard,

Am Mon, 24 Mar 2014 15:57:57 +0100 schrieb Claus Busch:
=--(Countif($A$2:$A$12001;A2) and copy down to B12001. After code is
through filter this column for 1.
Click to expand...

here is a typo. The formula should be:
=--(Countif($A$2:$A$12001;A2)>1)


Regards
Claus B.
 
here is a typo. The formula should be:

=--(Countif($A$2:$A$12001;A2)>1)
Click to expand...

I re-downloaded you revised workbook and it works as you say. Using the Remove Duplicates feature agrees with the formula results in the test I did. About six tests.

And I too never got more than 4 dupes.

This is acceptable performance as far as I am concerned.

I have studied the code for Titles and Descriptions but I cannot figure out what to change in the Description code to get 2 second performance like Titles code does.

If I set the calc to manual, it seems to just repeat the first 20 entries until it reaches 2000 and quite fast. With cacl at automatic the code grinds along for about 4 minutes but I the entries are good, no dupes.

Howard
 
Back
Top