Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issue with blanks and spaces
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 entries but 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 in each 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issue with blanks and spaces
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. 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. -- Vista Ultimate SP2 / Windows7 SP1 Office 2007 Ultimate SP3 / 2010 Prodessional |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issue with blanks and spaces
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. here is a typo. The formula should be: =--(Countif($A$2:$A$12001;A2)1) Regards Claus B. -- Vista Ultimate SP2 / Windows7 SP1 Office 2007 Ultimate SP3 / 2010 Prodessional |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Charting blanks as spaces | Charts and Charting in Excel | |||
Issue removing leading and lagging spaces | Excel Programming | |||
How to count blanks and spaces that look like blanks | Excel Programming | |||
how do I remove leading spaces and leave the remianing spaces w | Excel Worksheet Functions | |||
Paste Special Skip Blanks not skipping blanks, but overwriting... | Excel Discussion (Misc queries) |