• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Excel File size

Hi, Monty!
Just because of the 800k+ rows, with formulas, I'd suggest you to move to Access or any version of SQL database.
Regards!


Hello Sir...We do not have option as i said we were planned for SQL server so that every body can validate there template rows with tables stored in SQL server...but unfortunately that's rulled out now...and need to live with EXcel.
 
Thanks NARAYANK for the explanation..

The idea of using Access would be really great but unfortunately each indvidual has to validate his template rows with the tables stored in Access..and data would be changing in access everyday..and this cannot be stored in Sharedrive due to police..Am blocked from every corner and need to have the dump in separate sheet and validate it.


Hi !

.xlsb file format is just to reduce size on disk,
nothing with how Excel "works" !

Excel needs to load all in memory : application, add-ins, ribbon,
workbooks & worksheets, formulas, data, …
As it is just how Excel works so there is almost nothing to do
except to increase the RAM of computer !

Excel can create and manage some Access database.
But for big data, Access is far away the best choice.

On a recent thread on my local forum, a guy asked for a help to end
its project. An helper warned him again like several weeks ago
for this kind of database project Excel is under level, better is to move
to Access. The guy did'nt want that way again as it was so closed to ending.
After some help, two days later the guy was happy to finish but
complained about a long time execution for a main procedure.
Helper tested on his side : 53 minutes to finish the process.
The guy said : « Ok it's normal, almost same time on my side. »

Few hours later, the helper tried on Access : less than a minute !
The guy was surprised and disappointed o_O and wrote
« Ok but 'cause your are an expert too on Access. »
The helper answered his code was like a beginner but yes,
as an expert he optimized his code and his new version lasts
around 20 seconds ! Versus 53 minutes under Excel … :eek:

Access is the right choice for big data, safety of data,
shared work between users, work on a lan, …

Often database and maintenance procedures are under Access
but some data can be exported to Excel
(or Excel can directly grab Access data via SQL)
for analysis for example …
 
Thanks Chihiro...code really works well..But am extremely sorry for just providing the sample data with two columns as wanted to see if this works..but your code works like magic...Preparing my excel file now which is just replica of my actual project...hope you will extend your help in modifying the code...am trying level best to do the same...Thanks a million.

Con-cat can be done for 800K rows in about 3 sec. I initially tested with code and formula. Didn't make much difference so I pasted them as values for second test.

Checking if the matching record exist in database will be the bottle neck here.

Code:
Sub Test()
Dim TempArr
Dim cel As Range, i
Dim sTime As Single, eTime As Single

sTime = Timer
myArray = Sheets("DataBase").Range("A1").CurrentRegion

For Each cel In Range("A1:A8")
    i = Application.Match(cel.Value & "|" & cel.Offset(, 1).Value, Application.Index(myArray, , 5), 0)
    If IsError(i) Then
        cel.Resize(, 2).Interior.Color = vbRed
        cel.Resize(, 2).Font.Color = vbWhite
        GoTo Skip
    End If
    If myArray(i, 3) = "" Or myArray(i, 4) = "" Then
        cel.Resize(, 2).Interior.Color = vbRed
        cel.Resize(, 2).Font.Color = vbWhite
    ElseIf myArray(i, 3) = "Testing" Or myArray(i, 4) = "Testing" Then
        cel.Resize(, 2).Interior.Color = vbRed
        cel.Resize(, 2).Font.Color = vbWhite
    ElseIf myArray(i, 3) = "InProgress" Or myArray(i, 4) = "InProgress" Then
        cel.Resize(, 2).Interior.Color = vbRed
        cel.Resize(, 2).Font.Color = vbWhite
    End If
Skip:
Next
eTime = Timer
Debug.Print eTime - sTime
End Sub

It really depends on how many record needs to be checked from Template sheet.

But added issue is when Database needs any change. Updating for all 150 users is going to be a pain.

If using DB, you can ensure everyone is using same database table and perform checking operation using temp table in under 1 sec.
 
Hello Chihiro

Code works fantastic...As discussed my columns are totally 9 which needs to be validated against the database sheet need little modification to the code provided.


TEMPLATE SHEET

Columns to be consider to concatenation
V, AD, AE, AG, AL, AN,AM AK ,AJ total nine columns.


DATABASE SHEET

1) Combo existing in column "N"
2) Approver 1 existing in column "J"
3) Approve 2 existing in column "M"

Attached the file with clear notes for understanding...just a screenshot for quick look

Tempsheet

upload_2017-5-13_0-51-54.png

Database Sheet

upload_2017-5-13_0-52-33.png


Hope this helps...Initially taken a simple example to see if it works..But your code works charm..

Waiting for your response!..Thanks always.

@Monty

Did bit of test. Using approx 800k rows, takes about 8.4 sec to just find matching row# for 8 items from template.

There may be faster way... but I wouldn't pursue this avenue if I were you.
Code:
Sub Test()
Dim TempArr
Dim cel As Range, x
Dim sTime As Single, eTime As Single

sTime = Timer
myArray = Sheets("DataBase").Range("A1").CurrentRegion

ReDim TempArr(1 To UBound(myArray, 1), 1 To 3)

For i = 1 To UBound(myArray, 1)
    TempArr(i, 1) = myArray(i, 1) & "|" & myArray(i, 2)
    TempArr(i, 2) = myArray(i, 3)
    TempArr(i, 3) = myArray(i, 4)
Next

For Each cel In Range("A1:A8")
    x = Application.Match(cel.Value & "|" & cel.Offset(, 1).Value, Application.Index(TempArr, , 1), 0)
    Debug.Print x
Next
eTime = Timer

Debug.Print eTime - sTime
End Sub

Edit: Also did test with Scripting.Dictionary, but just adding 800k unique keys took about 28 sec.
Concat can be done for 800K rows in about 3 sec. I initially tested with code and formula. Didn't make much difference so I pasted them as values for second test.

Checking if the matching record exist in database will be the bottle neck here.

Code:
Sub Test()
Dim TempArr
Dim cel As Range, i
Dim sTime As Single, eTime As Single

sTime = Timer
myArray = Sheets("DataBase").Range("A1").CurrentRegion

For Each cel In Range("A1:A8")
    i = Application.Match(cel.Value & "|" & cel.Offset(, 1).Value, Application.Index(myArray, , 5), 0)
    If IsError(i) Then
        cel.Resize(, 2).Interior.Color = vbRed
        cel.Resize(, 2).Font.Color = vbWhite
        GoTo Skip
    End If
    If myArray(i, 3) = "" Or myArray(i, 4) = "" Then
        cel.Resize(, 2).Interior.Color = vbRed
        cel.Resize(, 2).Font.Color = vbWhite
    ElseIf myArray(i, 3) = "Testing" Or myArray(i, 4) = "Testing" Then
        cel.Resize(, 2).Interior.Color = vbRed
        cel.Resize(, 2).Font.Color = vbWhite
    ElseIf myArray(i, 3) = "InProgress" Or myArray(i, 4) = "InProgress" Then
        cel.Resize(, 2).Interior.Color = vbRed
        cel.Resize(, 2).Font.Color = vbWhite
    End If
Skip:
Next
eTime = Timer
Debug.Print eTime - sTime
End Sub

It really depends on how many record needs to be checked from Template sheet.

But added issue is when Database needs any change. Updating for all 150 users is going to be a pain.

If using DB, you can ensure everyone is using same database table and perform checking operation using temp table in under 1 sec.
 

Attachments

  • Test.xlsb
    23.6 KB · Views: 4
Hello Chihiro Sir.

Below things made it has comments trying to modify your code...but still finding a problem.

Code:
Sub Test()
Dim TempArr
Dim cel As Range, x
Dim sTime As Single, eTime As Single

sTime = Timer
myArray = Sheets("DataBase").Range("A1").CurrentRegion ' I think we can not use Current region as data sibe may go to 5laksh rows.

ReDim TempArr(1 To UBound(myArray, 1), 1 To 3)

For i = 1 To UBound(myArray, 1)
    TempArr(i, 1) = myArray(i, 1) & "|" & myArray(i, 2)
    TempArr(i, 2) = myArray(i, 3)
    TempArr(i, 3) = myArray(i, 4)
Next

For Each cel In Range("A1:A8") 'Array is only for a1 to a8 but this trying to make dynamic
    x = Application.Match(cel.Value & "|" & cel.Offset(, 1).Value,  'This need to change as per requirent as we have many rows
Application.Index(TempArr, , 1), 0)
    Debug.Print x
Next
eTime = Timer

Debug.Print eTime - sTime
End Sub
 
I don't have time to look at it this weekend. Friend's birthday and all. Will look at it next week. But use the other code as bases.
 
Hello Chihiro...thanks for responding waiting for the whole night for your response. Have a blast at your friends birthday!

Can you please suggest on this! Am trying my best taking base your code but....

ForEach cel In Range("A1:A8") 'Array is only for a1 to a8 but this trying to make dynamic x = Application.Match(cel.Value & "|" & cel.Offset(, 1).Value, 'This need to change as per requirent as we have many rowsApplication.Index(TempArr, , 1), 0)
Debug.Print x
Next

As per my requirment is for the below columns and sheets

TEMPLATE SHEET

Columns to be consider to concatenation
V, AD, AE, AG, AL, AN,AM AK ,AJ total nine columns.


DATABASE SHEET

1) Combo existing in column "N"
2) Approver 1 existing in column "J"
3) Approve 2 existing in column "M"

can you please suggest on below line...tired trying it

I don't have time to look at it this weekend. Friend's birthday and all. Will look at it next week. But use the other code as bases.
 
Hello Chihirio/SirBj /Narayana

My weekend just gone in just trying what Marc code in modifications....Still no luck...Marc is busy this weekend...Any body want to give a try in modifying the code for me please..

Thanks in Advance...Marc waiting for u.
 
Try this.
Code:
Sub Test()
Dim TempArr, i
Dim jStr As String
Dim cel As Range
Dim sTime As Single, eTime As Single

sTime = Timer
myArray = Sheets("DataBase").Range("A2:N" & Sheets("DataBase").Cells(Rows.Count, 1).End(xlUp).Row)
For Each cel In Range("V20:V" & Cells(Rows.Count, "V").End(xlUp).Row)
    jStr = Join(Array(cel, cel.Offset(, 8), cel.Offset(, 9), cel.Offset(, 11), cel.Offset(, 14), _
            cel.Offset(, 15), cel.Offset(, 16), cel.Offset(, 17), cel.Offset(, 18)), "|")
    i = Application.Match(jStr, Application.Index(myArray, , 14), 0)
    If IsError(i) Then
        cel.Resize(, 19).Interior.Color = vbRed
        cel.Resize(, 19).Font.Color = vbWhite
        GoTo Skip
    End If
    If myArray(i, 10) = "" Or myArray(i, 13) = "" Then
        cel.Resize(, 19).Interior.Color = vbRed
        cel.Resize(, 19).Font.Color = vbWhite
    ElseIf myArray(i, 10) = "Testing" Or myArray(i, 13) = "Testing" Then
        cel.Resize(, 19).Interior.Color = vbRed
        cel.Resize(, 19).Font.Color = vbWhite
    ElseIf myArray(i, 10) = "Inprogress" Or myArray(i, 13) = "Inprogress" Then
        cel.Resize(, 19).Interior.Color = vbRed
        cel.Resize(, 19).Font.Color = vbWhite
    End If
Skip:
Next
eTime = Timer
Debug.Print eTime - sTime
End Sub

NOTE: VBA text comparison using "=" sign is case sensitive. If you want to ignore case use other methods to compare strings (such as Instr etc).
 
Last edited:
Hello Chihirio....Thank u so very much taking out time for me and amend code...I will give a try now...And sure this should work for me....Thanks Ton
 
Hello Chihiro.

Onces again thank you so very much for the time.

it works very well as per the requirment.

But when you put 100,000 rows in Database sheet and 500 rows in Template sheets am getting error at

" i = Application.Match(jStr, Application.Index(myArray, , 14), 0)"

otherwise works with out any issue..

Not really sure why debug at the line when the same working with few lines working..


upload_2017-5-16_0-37-36.png
Thanks.
Monty!
 
Last edited:
Hello Chihiro

May be myArray is not declared need to declare as variant so that it can hold millions of rows..
 
Last edited:
Chihiro...It's not issue with even declaration cross checked...But no luck..Able to handle up-to one thousand rows or so...Problem with laksh of rows in database sheet and 1000 rows in template sheet...Please advise..
 
Last edited:
? Am I not seeing Marc's post? Or is the post directed at me?

At any rate, what's the exact error message you get?

Code does take few minutes to run through. But ran fine on my end with about 500 records in Template and 100k records in Database.
 
Sorry Chihiro...Am really sorry to refer as Marc...

Please excuse me this time!

Error Details
upload_2017-5-16_1-8-32.png
 
It shouldn't give type mismatch as "i" is declared as variant type for that purpose (to deal with Error when no match is found as well as long type returned by Application.Match).

Use Debug.Print or step through the code and find out what variables holds when the error is returned and report back.

Or...

If needed, you can upload the workbook which gives error to DropBox (or similar service) and I can take a look at it.
 
Hello Chihiro.

Unable to upload file because of the size and same with Dropbox some issue...just upload same file with Template sheet some 600 and database with 6 rows the same rows just copy and paste for 100000 and need to test where we can see error...Sorry to trouble you..am not finding any option.
 

Attachments

  • Test.xlsb
    29.7 KB · Views: 1
Tested on my machine. Code ran without any error.

By the way, I'd turn off ScreenUpdating at start of the code if I were you (I didn't do it for testing).

Sorry, without your actual workbook that produces error. Can't trouble shoot further.
 
Back
Top