VBA code (macro) to replace array formula

[MSchädler]

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/column	A to P	Q	R	S	T to W	X
1		Dates		Project name		Array formula
2		20.09.2017		Paris		[=(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.

 

[Marcelo Branco]

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.

 

[Marcelo Branco]

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.

 

[MSchädler]

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

 

[MSchädler]

Re: Help needed to find a VBA code (macro)

Hello Marcelo
I have played arround with my sheet and came across the following.
If my Range is above the 379 the result is all "0".
The Range from 2 to 278 seem to work.
Any idea?
Here is the link to my DropBox where you see my file:
https://www.dropbox.com/s/fq88tzdpyd9j0za/Test Array_formula1.xlsm?dl=0

Looking forward to your answer.
Marc

 

[Peter_SSs]

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?

 

[MSchädler]

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

 

[Marcelo Branco]

Re: Help needed to find a VBA code (macro)

Hello Marcelo
I have played arround with my sheet and came across the following.
If my Range is above the 379 the result is all "0".
The Range from 2 to 278 seem to work.
Any idea?
Here is the link to my DropBox where you see my file:
https://www.dropbox.com/s/fq88tzdpyd9j0za/Test Array_formula1.xlsm?dl=0

Looking forward to your answer.
Marc

Cell Q371 contains ???
Delete it and the formula should work

M.

 

[Marcelo Branco]

Also be sure that column Q contains dates (numbers), not text

M.

 

[MSchädler]

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

 

[Marcelo Branco]

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.