VBA code (macro) to replace array formula

MSchädler

Board Regular
Joined
Apr 27, 2017
Messages
95
I use this array formula in an excel sheet. The formula works fine in a small data base.
The database is currently 600 rows long and growing and now it takes time to calculate.
I think that a macro instead of the array formula could help to speed up the calculation.
Can somebody help me finding the macro code?

This is the general setup of my sheet:

Row/columnA to PQRST to WX
1DatesProject nameArray formula
220.09.2017Paris[=(xxx)]

<tbody>
</tbody>

This is the array formula in column X, limited to 1000 rows for speed

english:
[=IF(A2="","",IF(COUNTIF(S$2:S$1000,S2)=1,SMALL(IF(A$2:A$1000<>"",IF(ISNA(MATCH(A$2:$A$1000,X$1:X1,0)),A$2:A$1000)),1),INDEX(A$2:A$1000,MATCH(MIN(IF(S$2:S$1000=S2,Q$2:Q$1000)),IF(S$2:S$1000=S2,Q$2:Q$1000),0))))]
german:
[=WENN(A2="";"";WENN(ZÄHLENWENN(S$2:S$1000;S2)=1;KKLEINSTE(WENN(A$2:A$1000<>"";WENN(ISTNV(VERGLEICH(A$2:$A$1000;X$1:X1;0));A$2:A$1000));1);INDEX(A$2:A$1000;VERGLEICH(MIN(WENN(S$2:S$1000=S2;Q$2:Q$1000));WENN(S$2:S$1000=S2;Q$2:Q$1000);0))))]


Thanks for any suggestions.
M.
 
Last edited:
Re: Help needed to find a VBA code (macro)

Just for the records:
Surely this is related to this other thread
https://www.mrexcel.com/forum/excel...p-needed-define-excel-formula-vba-code-2.html

As a solution i suggested a not very efficient formula because i did not quite understand what you wanted, or because i wasn't on a very good day ;).

The solution with macro should be the best, but only for testing purposes follows another formula.

X2 copied down
=IF(A2="","",INDEX(A$2:A$1000,AGGREGATE(15,6,(ROW(A$2:A$1000)-ROW(A$2)+1)/(Q$2:Q$1000=AGGREGATE(15,6,1/(1/((S$2:S$1000=S2)*(Q$2:Q$1000))),1)),1)))

confirmed with just Enter (no need of Ctrl+Shift+Enter)

M.
 
Upvote 0

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Re: Help needed to find a VBA code (macro)

Another formula for testing purposes

X2 copied down
=IF(A2="","",LOOKUP(1,0/FREQUENCY(1,(S$2:S$1000=S2)*(Q$2:Q$1000)),A$2:A$1000))

confirmed with just Enter

M.
 
Upvote 0
Re: Help needed to find a VBA code (macro)

Good day Marcelo
Thanks for this new formula option. Here my feedback!
The formula works well in my test sheet, but not in my active sheet.
I believe that its due to the fact that I have not everywhere dates in column Q. I'll fill them with dummy dates. and test again.

Thanks for your effort.
Marc
 
Upvote 0
Re: Help needed to find a VBA code (macro)

Just checking: Your original question was how to move from a formula approach to a macro approach. Are you now looking to go back to a formula solution?
 
Upvote 0
Re: Help needed to find a VBA code (macro)

Hi Peter.
No, for the moment the macro approach seems to be the fastest way to get the results in the sheet.
It is just my interest in understanding that formula.
Thanks for asking.
Marc
 
Upvote 0
Upvote 0
Hello Marcelo
Now I really feel stupid.
Sorry I didn't see that (Q371) but it makes sense why it didn't work! And yes I'll make sure Q contains dates.

Thanks again and yes the sheet works now.
Marc
 
Upvote 0
Re: Help needed to find a VBA code (macro)

Just checking: Your original question was how to move from a formula approach to a macro approach. Are you now looking to go back to a formula solution?

Hi Peter.
No, for the moment the macro approach seems to be the fastest way to get the results in the sheet.
It is just my interest in understanding that formula.
Thanks for asking.
Marc


Peter and Marc

I apologise.

It seems i've caused some confusion. I should have posted the alternative formula in the mentioned other thread. I'm doing this just now.

M.
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,089
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top