Array speed is slow

I have 3 tabs in excel
Tab A - "Pre"
Tab B - "Post"
Tab C - "Rec"
In Tab A & B I have the same column headers (15 dimensions and a value)
I create a unique lookup id (UID) based on a subset of the columns (and this can change run to run)
e.g. A|B|D|E|K

I move the UIDs from Tab A and Tab B to Tab C and filter this combined set of UIDs (and then sort) to get the unique UIDs from both tabs in Tab C.

I am then doing a sum if on both Tab A and Tab B and put the results in to Tab C in columns B & C (column B would be the Pre Sum and column C would be the Post Sum).

This gives the sums of the UIDs to allow me to check/compare the 'Pre' and 'Post' values to show the variances.

I have approx 56,000 lines in Pre and 85,000 lines in Post and 45,000 lines in UID tabs

Using Sumif, the runtime is about 7 minutes to do all these calculations.

It has been suggested I switch to using Arrays instead as it should be faster (thanks to the guys on Reddit r/VBA).

I have tried this (with my limited Array skills) and the runtime is still slower than when doing simple sumif's.

Can someone please advise what I am doing wrong with the array VBA code (shown below).
Thanks
Jon


Code for the Pre value sum is below.

Please Login or Register  to view this content.

Excel attached
It has the 3 tabs, with limited data in it
Tab Rec has examples of manual excel formula, VBA with SUMIF and VBA with Array,
When running across 50000 lines and 30000 UIDs it is slow in all 3 cases

Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context. We don't need zillions of rows, a representative sample will do.

Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.

To upload a file click the Go Advanced button at the foot of your post, look underneath the post area for the Manage Attachments section and take it from there.

Welcome to the forum

Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Before getting too deep unto this slowness issue are you saying this happens
AFTER you have created the long list of UIDs in column A of the Rec tab
AND then copy the Formula in C2 and D2 down as far as necessary?

I note that column R contains a formula to create the text string.
Have you tried first converting these to hard values before copying them to Rec?

Are you suggesting that if there were 50000 UIDs on the Rec tab, manually copying the SUMIF formula down all those rows is slow?

I've just created 100,000 UIDs and manually copied the SUMIF formula down, which then takes about 10 seconds. I wouldn't expect a macro doing the same job would be any different.

I'd also be interested to see how this performs.

Please Login or Register  to view this content.

Maybe try Power Query

Turn Pre and Post to Table tPre and tPost

Then at Ribbon data > Get data from Other sources > Blank Query
Query Ribbon home> advanced editor > paste code > close and load to table

Please Login or Register  to view this content.