Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #41   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 852
Default Issue with blanks and spaces


here is a typo. The formula should be:

=--(Countif($A$2:$A$12001;A2)1)


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
  #42   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default Issue with blanks and spaces

Hi Howard,

Am Mon, 24 Mar 2014 09:43:56 -0700 (PDT) schrieb L. Howard:

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.


have another look for the workbook.


Regards
Claus B.
--
Vista Ultimate SP2 / Windows7 SP1
Office 2007 Ultimate SP3 / 2010 Prodessional
  #43   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 852
Default Issue with blanks and spaces


have another look for the workbook.





Regards

Claus B.

--


Well, that certainly looks like a winner.

Code run time and duplicates are very workable.

I don't know you do it!!

A ton of thanks.

Howard
  #44   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 852
Default Issue with blanks and spaces

On Monday, March 24, 2014 12:33:42 PM UTC-7, L. Howard wrote:
have another look for the workbook.












Regards




Claus B.




--




Well, that certainly looks like a winner.



Code run time and duplicates are very workable.



I don't know you do it!!



A ton of thanks.



Howard


Two bugs have popped up

The first is with the Description Builder sheet out put to column A.
Each entry is made up of 5 or 6 short phrases and I have noticed the there is no space between the second and third phrase. Seems consistent in each row.

Here are a few easier to fine examples.

In A2 in the formula bar arrow to the right until you find "...select Matteresses.Most of our...".

Should be a space between "...Matteresses. Most of our..."

In A14 find arrow over to "...outlets.Much of our..."
In A22 "...completion.A huge selection..."

I cannot find in the data where or what would make that happen or with the code, whichever one it is that fails to put the space in.

The other glitch has occurred with the code for CL PVA's sheet where it moves the top row to the bottom and then shifts all upward.

TopRow = Rows(FirstRow) this line of code errors out.

Here is a link if needed and you have the time to take a look.

https://www.dropbox.com/s/ebd1f1ao4u... p%20Box.xlsm

Thanks.
Howard
  #45   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default Issue with blanks and spaces

Hi Howard,

Am Thu, 27 Mar 2014 18:58:23 -0700 (PDT) schrieb L. Howard:


The first is with the Description Builder sheet out put to column A.
Each entry is made up of 5 or 6 short phrases and I have noticed the there is no space between the second and third phrase. Seems consistent in each row.


I changed the code and RTrim the values

TopRow = Rows(FirstRow) this line of code errors out.


have a look:
https://onedrive.live.com/?cid=9378A...121822A3%21326
for the workbook "Client Data Work Book _1 rev 3.1_C.xlsm"
In Module2 is the code "Move". It is easier and shorter.


Regards
Claus B.
--
Vista Ultimate SP2 / Windows7 SP1
Office 2007 Ultimate SP3 / 2010 Professional


  #46   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 852
Default Issue with blanks and spaces

On Friday, March 28, 2014 1:24:32 AM UTC-7, Claus Busch wrote:
Hi Howard,



Am Thu, 27 Mar 2014 18:58:23 -0700 (PDT) schrieb L. Howard:





The first is with the Description Builder sheet out put to column A.


Each entry is made up of 5 or 6 short phrases and I have noticed the there is no space between the second and third phrase. Seems consistent in each row.




I changed the code and RTrim the values



TopRow = Rows(FirstRow) this line of code errors out.




have a look:

https://onedrive.live.com/?cid=9378A...121822A3%21326

for the workbook "Client Data Work Book _1 rev 3.1_C.xlsm"

In Module2 is the code "Move". It is easier and shorter.





Regards

Claus B.



The Description Builder is looking real fine.

The Move code in Module 2 errors out on this line:

Rows("3:" & LRow).Cut

The code makes no mention to a sheet name, should I incorporate it in the code in Module 1? When the line errors out and you mouse over the line it refers to row 21 which looks correct on the CL PVA's sheet as it is the last row.

Scratching my head on this.

Howard
  #47   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 852
Default Issue with blanks and spaces



Whoa! Hold everything! I saved and closed the workbook and then reopened it and everything seems to be working fine.

Before I had a couple errors both in Titles code, clear line. and Description, clear line.

The Move code was erring before, but now it seems to be just fine. Don't know what was going on.

I am still puzzled how the Move code in Module 2 knows to do it job on CL PVA's sheet.

It might just be the late hour here clouding my mind...

Howard


  #48   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default Issue with blanks and spaces

Hi Howard,

Am Fri, 28 Mar 2014 02:53:17 -0700 (PDT) schrieb L. Howard:

I am still puzzled how the Move code in Module 2 knows to do it job on CL PVA's sheet.


sorry, my bad. I didn't refer correctly.
Change the "Move" code to:

Sub Move()
Dim LRow As Long

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

With Sheets("CL PVA's")
LRow = .Cells(Rows.Count, 1).End(xlUp).Row
.Rows("3:" & LRow).Cut
.Rows("2:" & LRow - 1).Insert Shift:=xlDown
End With

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub


Regards
Claus B.
--
Vista Ultimate SP2 / Windows7 SP1
Office 2007 Ultimate SP3 / 2010 Professional
  #49   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default Issue with blanks and spaces

Hi again,

Am Fri, 28 Mar 2014 02:53:17 -0700 (PDT) schrieb L. Howard:

I am still puzzled how the Move code in Module 2 knows to do it job on CL PVA's sheet.


if the macro is bound to the button on the sheet the sheet must be the
active sheet to press the button. And code without refering always works
on the active sheet.


Regards
Claus B.
--
Vista Ultimate SP2 / Windows7 SP1
Office 2007 Ultimate SP3 / 2010 Professional
  #50   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 852
Default Issue with blanks and spaces

On Friday, March 28, 2014 3:10:44 AM UTC-7, Claus Busch wrote:
Hi again,



Am Fri, 28 Mar 2014 02:53:17 -0700 (PDT) schrieb L. Howard:



I am still puzzled how the Move code in Module 2 knows to do it job on CL PVA's sheet.




if the macro is bound to the button on the sheet the sheet must be the

active sheet to press the button. And code without refering always works

on the active sheet.





Regards

Claus B.

--

Vista Ultimate SP2 / Windows7 SP1

Office 2007 Ultimate SP3 / 2010 Professional


Okay, Got it. Sure do thank you for the little clean ups of the code.

Howard


  #51   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 852
Default Issue with blanks and spaces

Hi Claus,

A revisit to Description Builder if and when you have time please.

First, all the code work you have offered works fine and does what was expected.

The problem I'm looking at now may not have a practical fix.

Duplicate returns were an issue to begin with, but after some usage that seems to be a non issue now.

What is at issue is "identical like phrase elements" in close proximity to each other in the Column A list.

The link should open to "Description Builder" sheet and that is the only sheet at issue.

To illustrate what the problem is copy any one of the phrase elements in the orange range, and do a "Find All" on column A. With the box expanded you can see the cells that hold the chosen phrase element. Usually there are around 95 to 130 cells listed.

The problem is that they are often only 15 to 30 rows apart.

Crazy as it may seem, that causes a pretty serious problem/penalty further down the road with the use of the long 5 and 6 phrase row entries.

So the question is from a practical stand point, can the code or the on sheet methods, be change to broaden the proximity of these phrase elements?

I'm calling a 'phrase element' any of the text in the orange and other colored ranges like it. And these are the ones that wind up too close to each other.

The long phrases are the five and six short phrases combined in a column A row entry.

So if you would copy and find all for the entry of D6 "Come Visit Us!" that would be what I'm talking about. The first two cells with "Come Visit Us!" are in A13 and A26. That's too close.


https://www.dropbox.com/s/9x9ma0o2yz...01.0%20DB.xlsm

Thanks.
Howard
  #52   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default Issue with blanks and spaces

Hi Howard,

Am Wed, 9 Apr 2014 03:43:16 -0700 (PDT) schrieb L. Howard:

A revisit to Description Builder if and when you have time please.


can you further explain what should be done in this sheet?
You build in AQ out of 720 phrases 120 (6x20 if all cells are filled in
D:N) and these phrases should be copied in AE:AO. So each column should
only have 20 phrases. But in the header is written 200 and in the column
are 2000.
So what is right? And out of 120 phrases you can't make 2000 values in
column A.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional
  #53   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default Issue with blanks and spaces

Hi Howard,

Am Wed, 9 Apr 2014 13:27:35 +0200 schrieb Claus Busch:

can you further explain what should be done in this sheet?
You build in AQ out of 720 phrases 120 (6x20 if all cells are filled in
D:N) and these phrases should be copied in AE:AO. So each column should
only have 20 phrases. But in the header is written 200 and in the column
are 2000.


in one loop you only get 20 phrases in columns AE:AO. So you have to
loop 100 times and get some duplicates.
What about the time the macro needs for running? You could do more
loops, write it all in one column and then create with
Scripting.Dictionary unique values. I will check this later when I have
time. But I don't know if that will reduce the duplicates. So you loop
100 times every value from D:N will occure 100 times. With more loops
and creating unique values the occurence will not be reduced but there
will be no same complete phrases.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional
  #54   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 852
Default Issue with blanks and spaces



can you further explain what should be done in this sheet?

You build in AQ out of 720 phrases 120 (6x20 if all cells are filled in

D:N) and these phrases should be copied in AE:AO. So each column should

only have 20 phrases. But in the header is written 200 and in the column

are 2000.

So what is right? And out of 120 phrases you can't make 2000 values in

column A.





Regards

Claus B.


The Headers are incorrect, I should have cleaned that up before sending. Sorry, that is bad info.

Howard

  #55   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 852
Default Issue with blanks and spaces

in one loop you only get 20 phrases in columns AE:AO. So you have to

loop 100 times and get some duplicates.

What about the time the macro needs for running? You could do more

loops, write it all in one column and then create with

Scripting.Dictionary unique values. I will check this later when I have

time. But I don't know if that will reduce the duplicates. So you loop

100 times every value from D:N will occure 100 times. With more loops

and creating unique values the occurence will not be reduced but there

will be no same complete phrases.

Regards

Claus B.


The main issue is the occurrence of those "text elements" in the orange (and other colored ranges like the orange one) in rows too close together in column A.

Not sure if it is relevant, but the problem does not happen on the "Titles Builder" sheet where it uses the same data to generate 12,000 rows. But Titles only uses a single phrase per row in its column A.

The code to transfer phrases to column A have some differences of course, transferring one single phrase for Titles vs. five or six for Descriptions, but I was wondering if that could be part of the solution.

The Scripting Dictionary would be worth trying.

If that or any other ideas fall short then that may just have to be the brutal reality of it all.

What you've done so far is pretty darned remarkable to me, and trying to hit a moving target with code has to have its limits.

Howard



  #56   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default Issue with blanks and spaces

Hi Howard,

Am Wed, 9 Apr 2014 06:30:18 -0700 (PDT) schrieb L. Howard:

The main issue is the occurrence of those "text elements" in the orange (and other colored ranges like the orange one) in rows too close together in column A.


there is no chance to fit this behaviour. The dictance between those
phrases should be greater if all colored fields in D:N were filled.
Now you have only 220 phrases to create the descriptions. If all fields
are filled you would have 720. Four colors have no values and yellow has
only 100 instead of 120


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional
  #57   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 852
Default Issue with blanks and spaces

On Wednesday, April 9, 2014 7:07:07 AM UTC-7, Claus Busch wrote:
Hi Howard,



Am Wed, 9 Apr 2014 06:30:18 -0700 (PDT) schrieb L. Howard:



The main issue is the occurrence of those "text elements" in the orange (and other colored ranges like the orange one) in rows too close together in column A.




there is no chance to fit this behaviour. The dictance between those

phrases should be greater if all colored fields in D:N were filled.

Now you have only 220 phrases to create the descriptions. If all fields

are filled you would have 720. Four colors have no values and yellow has

only 100 instead of 120





Regards

Claus B.


Okay, thanks Claus. Appreciate you taking a look and all you have done to get the project to this point.

Regards,
Howard


  #58   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default Issue with blanks and spaces

Hi Howard,

Am Wed, 9 Apr 2014 07:33:18 -0700 (PDT) schrieb L. Howard:

Okay, thanks Claus. Appreciate you taking a look and all you have done to get the project to this point.


to copy the values to A try following macro instead of
CopyToA2_2_Descript:

Sub CopyToDiscript()

Dim i As Long, j As Long
Dim myArr As Variant

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

With Sheets("Description Builder")

For i = 2 To 1902 Step 100
For j = 31 To 41 Step 2
If WorksheetFunction.CountA(.Range(.Cells(i, j), _
.Cells(i + 99, j))) = 100 Then
myArr = .Range(.Cells(i, j), .Cells(i + 99, j))
.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0) _
.Resize(100) = myArr
End If
Next
Next
myArr = .Range("A2:A2001")
End With

Sheets("Publish Data").Range("E2").Resize(rowsize:=UBound(myArr)) =
myArr

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Charting blanks as spaces Todd Charts and Charting in Excel 2 June 30th 09 10:10 PM
Issue removing leading and lagging spaces robs3131 Excel Programming 10 February 14th 08 03:43 PM
How to count blanks and spaces that look like blanks Ben Excel Programming 1 July 10th 07 06:34 PM
how do I remove leading spaces and leave the remianing spaces w Debi Excel Worksheet Functions 6 February 28th 07 04:29 PM
Paste Special Skip Blanks not skipping blanks, but overwriting... gsrosin Excel Discussion (Misc queries) 0 February 22nd 05 04:33 AM


All times are GMT +1. The time now is 05:40 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"