• 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.

Macro required for Find and paste values based on criteria matched

N

Nitesh Mathur

Member
HI,

Im using MS Excel 2013 and seeking a macro for find the values based on criteria matched in columns. I did this formulas on around 90K rows but file become 12MB size and taking too much time in processing. So for reducing this size and time I required a macro.

Sample file attached for reference.

Query:

I have to sheets (1.) Data Sheet , (2.) Compiled_Sheet

- In "Data" sheet data is available for compilation.
- In "Compiled_Sheet", I need to populate data, based on conditions matched from column A to F. If all conditions / criteria are matched than populate SUM of values in Q1, Q2, Q3, Q4


Make a note: This is a sample data , actual data is around 90K rows.

Thanks
NItesh
 

Attachments

  • Sample1.xlsm
    11.7 KB · Views: 10
Have you considered making a pivot? Pivot can lend you a lot of flexibility. I am attaching your sample file for your reference.
 

Attachments

  • Sample1.xlsm
    16.8 KB · Views: 1
shrivallabha said:
Have you considered making a pivot? Pivot can lend you a lot of flexibility. I am attaching your sample file for your reference.
Click to expand...

Yes, but no of rows are 90K and I want to generate more reports based on Complied_Sheets arranged data .
So Pivot table is not a solution for me.

Thanks for your response.

NItesh
 
Hui said:
Nitesh,

Have you tried using a Pivot Table?
Click to expand...

Yes, but no of rows are 90K and I want to generate more reports based on Complied_Sheets arranged data .

So Pivot table is not a solution for me. I need a macro to arrange data as per my requirement.

Thanks

NItesh
 
Not the sample file, the real data?

Any VBA code will be so much slower than a Pivot table I'd recommend against it
PT's will also give you flexibility in being able to re-configure the report easily, where as in VBA it is locked in.
 
Hui said:
Not the sample file, the real data?

Any VBA code will be so much slower than a Pivot table I'd recommend against it
PT's will also give you flexibility in being able to re-configure the report easily, where as in VBA it is locked in.
Click to expand...

Sorry Hui..... because of our organization policy, Im not able to share original file to any externals.

I request you to please write macro on sample file, if it is possible.

Thanks

Nitesh
 
Hi !

For the size, formulas can be removed after calculation …

Via running a code, differents ways mixing or not an helper column and
formulas but all will be take more or less time, a long time …

With this kind of poor attachment (without a result worksheet well filled !)
any way is fast enough and we have no time to test at your place !

But you can send some directives to follow and maybe we can post
a dirty code, maybe not the best one without a real workbook …

Code lasts more than a pivot table : +90% !
​
 
Nitesh

Not that I am a non believer, but I just am

I didn't believe your claim about the PT Handling 90,000 records

So I copied the data you supplied and filled in 90,000 records
I put random values into the Value Fields
I made up other values in the other fields

I added a PT and linked it to the data

upload_2016-3-4_23-10-11.png

It works wonderfully

I even added a count of Q4 to show that there is 90,000 records

Now I am using Excel 2016

And I suspect that in order of speed, 2010 will be fastest then 2013 then 2016.

This exercise took less than 5 mins to setup the data and the PT
Setting up a VBA Sample will be much slower and not nearly as flexible

So as a final test
I copied the 90,000 records and pasted it below the existing data
Now I had 180,000 Records
upload_2016-3-4_23-14-18.png

I updated the PT Data Range and it took a whole 5 seconds to recalculate
 
Hi Hui,

I did not look further after initial exchange. For posterity and curiosity, could you test how does VBA fare with your data please?

This code doesn't sort the results but posts them as they are. Sorting could be added of course.

Code: 
Option Base 1
Public Sub ProcessData()
Dim varData As Variant, varCalc()
Dim objDict As Object
Dim lngCnt As Long, i As Long, j As Long, lngPos As Long: lngCnt = 0
Dim strKey As String

varData = Application.Trim(Sheets("Data").Range("A4:J" & Sheets("Data").Range("A" & Rows.Count).End(xlUp).Row).Value)
ReDim varCalc(1 To UBound(varData), 1 To 10)
Set objDict = CreateObject("Scripting.Dictionary")
objDict.CompareMode = TextCompare

For i = LBound(varData) To UBound(varData)
    strKey = varData(i, 1) & varData(i, 2) & varData(i, 3) & varData(i, 4) & varData(i, 5) & varData(i, 6)
    If objDict.Exists(strKey) Then
        lngPos = objDict(strKey)
        For j = 7 To 10
            varCalc(lngPos, j) = CDbl(varCalc(lngPos, j)) + CDbl(varData(i, j))
        Next
    Else
        lngCnt = lngCnt + 1
        objDict.Add strKey, lngCnt
        varCalc(lngCnt, 1) = varData(i, 2)
        varCalc(lngCnt, 2) = varData(i, 1)
        For j = 3 To 10
            varCalc(lngCnt, j) = varData(i, j)
        Next
    End If
Next i

Sheets("Compiled_Sheet").Range("A4:A" & Rows.Count).EntireRow.Delete
Sheets("Compiled_Sheet").Range("A4").Resize(UBound(varCalc), 10).Value = varCalc

End Sub
 

Yes, Array variable combined with Dictionary object is a very fast way !
But faster in Binary mode than Text one …

And you start from data worksheet instead of compiled worksheet …
​
 
Hi Marc,

Didn't go with binary since the first 6 columns are text and people may not adhere to uppercase.

I did not choose compiled as starting point as it may miss few entries from source (unless someone built them first accurately enough) and wanted to build a summary in a sequence similar to a Pivot.
 

From initial post, I understood to populate data for conditions
of Compiled worksheet, not for all data but maybe I'm wrong …​
 
I'll second Hui. I've routinely worked with data over 500k rows using pivot and had no issues.

It's fast and very convenient method to slice and dice data.
 
Shrivallabha

That's a remarkable piece of code on 180,000 records

It took about 4 seconds the process

I can see I'm going to have to learn Dictionary Objects
 
Hui said:
Shrivallabha

That's a remarkable piece of code on 180,000 records

It took about 4 seconds the process

I can see I'm going to have to learn Dictionary Objects
Click to expand...
Thank you Hui for testing. Use of Arrays and Dictionary object keep interaction with Sheet data to minimum (at input and output we deal with Sheet).
 
You must log in or register to reply here.
Back
Top