LinkBack URL
About LinkBacks
Please Login or Register to view this content.
Please Login or Register to view this content.
I will let the other experienced folks answer but options below perhaps:
1. Save the file as a binary workbook (.xlsb)
2. Include a progress indicator so folks would know it is processing
Already tried. 4-5 hours min. So, if anybody can help with that macro.. optimize the speed, or may be change something...Originally Posted by chin67326
Real workbook more than 300K rows.
Strange. Mb something with the macro. 75k rows - ok. 76k - error here...
If in VBA press ctrl+break then macro works, but works without end...Please Login or Register to view this content.
Mb anybody can inspect and catch all erros, which I can.. Thanx in advance.
Strange. Mb something with the macro. 75k rows - ok. 76k - error here...
If in VBA press ctrl+break then macro works, but works without end...Please Login or Register to view this content.
Mb anybody can inspect and catch all erros, which I can.. Thanx in advance.
Hi Remphan,
try to describe in words, what macro should make
On example in the attached file:
Type 1 in column D have empty values in E:J range. E2:J2 and E7:J7 in the file. Original table may contain many rows and columns in table. Here only useful data for macro.
We must compare values for "Type 1" in column "C" with all other values in column "C" for any Type (exclude "Type 1"). If we found 1 or more full matches in "C" for "Type 1" in "D" column with another values in "C" for any other type, we must choose from them only the one, which have max number in column "K".
My macro is working properly (that what i need), but since some strings somehow hangs. Maybe somewhere not quite correct error checking. At least I think so. Because the handles 75 thousand rows in a couple of seconds, and 76 000 - hangs and works endlessly.
Last edited by Remphan; 12-28-2015 at 09:13 AM.
6635558v2.xlsm
And now I can attach file with macro. But how already said
Probably, some bug in the vba.On example in the attached file:
Type 1 in column D have empty values in E:J range. E2:J2 and E7:J7 in the file. Original table may contain many rows and columns in table. Here only useful data for macro.
We must compare values for "Type 1" in column "C" with all other values in column "C" for any Type (exclude "Type 1"). If we found 1 or more full matches in "C" for "Type 1" in "D" column with another values in "C" for any other type, we must choose from them only the one, which have max number in column "K".
My macro is working properly (that what i need), but since some strings somehow hangs. Maybe somewhere not quite correct error checking. At least I think so. Because the handles 75 thousand rows in a couple of seconds, and 76 000 - hangs and works endlessly.
Hi Remphan,
try this
Please Login or Register to view this content.
6635558v2.xlsm Thanks for a prompt reply. Almost properly, but not quite
D2 "Type 1". Ok, then look through C column excepting values, opposite to which in the D column "Type 1".
Found. C9 and C10. K10 biggest, than K9. So E10:J10 copy into E2:J2.
Next, C7 for Type 1 in D7 match with C8. No any matches. Ok, E8:J8 copy into E7:J8.
If 0 will be in K column, it also by default biggest than negative values like -5. And unfortunately in a column K can be either numerical values only (like -1 or 0 or 0,15 or 15 etc) or a symbol "-" (dash symbol instead number)
perhaps I did not quite understand your task
but K10=-5 K9=10?K10 biggest, than K9
and K1=10.23 > k10 & >k9
but K7 more than K8?Ok, E8:J8 copy into E7:J8
Pls excuse me, K9>K10, right.K10 biggest, than K9
K10 & K 9 only. K1 is not involved in the calculations , as is the value in Type 1 row (D2 = Type 1).but K10=-5 K9=10?
and K1=10.23 > k10 & >k9
So, K9>K10 then E9:J10 copy to E2:J2.
True, but we're looking for a matches in column B for "Type 1" in other types of column B (=exept "Type 1', as example we exclude such coincidences as the C2=C7, only C(n) value for "Type 1" with all posiibles C(x) values for all Type<>Type 1 in range C) and when we found any matches, we choose only from its rows with biggest in K. Pls excuse my English(but K7 more than K8?
K8=1. No any matches for C7, only C8. So E8:J8 copy into E7:J7
And in column K data may be: numeric values or have dash (="-") symbol/or simple empthy. If K9 will be "-" and K10 "-5", so -5 biggest than dash symbol.
If K9 = "-" and K10="-" than found and copy first match. In this virtual case from E9:K10 to E2:J2.
I apologize that misled above.
And the original array much more. Can have many rows and columns. In this table only the useful data for macro.
Last edited by Remphan; 12-28-2015 at 04:48 PM.
Ok, let's try so
Please Login or Register to view this content.
Hi, I was just looking at Excel posts in general and saw this one.
I am wondering that maybe the problem lies in the fact the AutoFilter does have problems when the filtered list is too long. I'm not sure.
Have you tried to AutoFilter manually and see if you get results?
You could also start recording a macro and make tour choices and see what that gives you.
You can then always change the macro accordingly.
I hope I am making sense and my explanation helps ( a little)
---
Hans
"IT" Always crosses your path!
May the (vba) code be with you... if it isn't; start debugging!
If you like my answer, Click the * below to say thank-you
Yeah, right, found that problem. Macro by Nilem works much better than mine and very quickly.
Last question: if table 200000 rows, does it mean that i need change in u macroandPlease Login or Register to view this content.values?Please Login or Register to view this content.
And little more: some rows for Type marked as "-" in E:J ater macro stops, some rows filled by macro. But some rows for Type 1 still have an empty values in E:J? (raws in example 86312, 86313, 86314, 88615)
Example. A| B | C | D | E:J | K
86314 row: City | 768594856 | 768594856City | Type 1 | x y z f n a | 300,20
86312 row: City | | City | Type 1 | | 112
86313 row: City | | City | Type 1 | | 0
86314 row: City | | City | Type 1 | | 0
88125 row: City | 568389265 | 568389265City | Type 1 | - - - - - - | 300,20
88169 row: City | ,, | ,,City | Type 1 | - - - - - - | 367,45
88615 row: City | 568389265 | 568389265City | Type 1 | | 198,613
Just an idea.
You're talking about 200000 rows or more.
I would suggest a macro that copies the selections you require to a temporary sheet or a temporary workbook instead of trying to filter such a large number of records in the same sheet.
I would even start suggesting to use MS Access instead of Excel, even if Excel support up to 1400000+ rows per worksheet.
Just an idea.
You're talking about 200000 rows or more.
I would suggest a macro that copies the selections you require to a temporary sheet or a temporary workbook instead of trying to filter such a large number of records in the same sheet.
I would even start suggesting to use MS Access instead of Excel, even if Excel support up to 1400000+ rows per worksheet.
I wrote some code which required two hours and longer and reduced it to 2 minutes changing my strategy
Unfortunately somethimes can't use Access, only Excel.
Macro by Nilem #13 works very fast (150 rows/5-10 seconds at my PC), just some clarifying questions a bit higher #15
Will apperciate for any working solution 4 Excel
it takes a 2 hrs and may not be freeI wrote some code which required two hours and longer and reduced it to 2 minutes changing my strategy
It's not a problem to use Excel, I use Excel for everything too, it was just an idea and I understand that many do not use MS Access since it it's not always included in the Office suite.
Do you have a sample file you can attach with a larger number of rows?
I'd like to take a fresh look at it and maybe I can find a way to speedup the processing.
Really want to attach, but unfortunately I can't (PC at work)
41 column it all (in original file). And 200+ rows.
Its no difficult for me manually copy from the original table useful data to working example with macro and back.
A:J in example may contain any data or can have empty rows/cells/duplicate data.
K column contains only numeric values, but sometimes may include not numeric value (made by mistake). If found 2 matches, 1st with value in K = "-5" and the second with wrong non numeric value "-" we choose K with -5. Some detailed explanation here #12
There may be situations where multiple identical rows till A:K for any type exclude Type 1. In that case shoose 1st match among identical.
Can't attach file, but Ill try detalize what must be.Ok, let's try so
Please Login or Register to view this content.
Sum = not only numeric, so if column Sum = 1 or Sum = "-", then 1>"-" (dash or any text/symbol)
Before macro
City Number CONCATENATE (B:A) Type 1 2 3 4 5 6 Sum Default 545343545 545343545Default Type2 test test test test test test 112827 Default 545343545 545343545Default Type2 asd asd asd asd asd asd 1227,81 Default Default Type 1 Default 545343545 545343545Default Type 1 Default 545343545 545343545Default Type 1 Default Default Type 1 Default Default Type 1
After macro
City Number CONCATENATE (B:A) Type 1 2 3 4 5 6 Sum Default 545343545 545343545Default Type2 test test test test test test 112827 Default 545343545 545343545Default Type2 asd asd asd asd asd asd 1227,81 Default Default Type 1 Default 545343545 545343545Default Type 1 Default 545343545 545343545Default Type 1 test test test test test test Default Default Type 1 Default Default Type 1 - - - - - -
Must be
City Number CONCATENATE (B:A) Type 1 2 3 4 5 6 Sum Default 545343545 545343545Default Type2 test test test test test test 112827 Default 545343545 545343545Default Type2 asd asd asd asd asd asd 1227,81 Default Default Type 1 - - - - - - Default 545343545 545343545Default Type 1 test test test test test test Default 545343545 545343545Default Type 1 test test test test test test Default Default Type 1 - - - - - - Default Default Type 1 - - - - - -
Last edited by Remphan; 12-29-2015 at 09:10 AM.
Hi Remphan,
please try again
*it is not necessary to quote the entire messagePlease Login or Register to view this content.
Thank u, Sir! Now macro solves the problem posed.
You're welcome, Sir
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks