VBA code (macro) to replace array formula

M

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:

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Sort by date Sort by votes
Re: Help needed to find a VBA code (macro)

From that post I cannot tell what you have, exactly where it is or what you are trying to do. Any chance of a small set of actual data (in a form we can see what & where it is - see my signature block below), the expected results & an explanation of how the expected results would be obtained manually?
 
Upvote 0
Re: Help needed to find a VBA code (macro)

Hello Peter

Sorry for my unclear question.
I will send you the short version of my excel file this evening. And thanks in advance for your interest and help.
Marc
 
Upvote 0
Re: Help needed to find a VBA code (macro)

Hi Peter
Sorry, somehow I'm not capable to send you the information as you requested. I don't know how to send you my work file, so that you see what I'm trying to achieve.
Do you have some more information for me on how to send you (signature block) my excel test file? I have not done this before.
Marc
 
Upvote 0
Re: Help needed to find a VBA code (macro)

Did you click the "Look here" link in my signature block?
 
Upvote 0
Re: Help needed to find a VBA code (macro)

Hello Peter

I hope that this is the way to send you part of the excel table with the contents.
The excel table below automatically counts in colomn A the row line. This is needed so that the "array formula" (X) finds what row number to give to a re-occurring project name (S) when the date entry (Q) is the oldest in that column.

My goal. I would like to have a macro to replace the array formula (X) and execute as soon as there is an entry in column S - Rollout Projekt. I believe that this would speed up the calculation time, especially when you have more than 1000 rows.
Guideline to the sheet: once an entry is made, approved and saved, that entry will never be deleted or changed again. Changes will only be made by copy/paste or new entry. The date is always =today().

I hope this makes my question understandable.
Thank you for your help
M.


xyz
Search Value5
a13197
Count3
b1555
Values At:RowColc984

byd11515

bz

DB NummerProzess

(drop down)		Project Typ (kann)
(drop down)		Project ID
(aut.)
(drop down)		ISP
(muss ab FS7)		Datum
Eintrag (aut.)		Cluster
(kann)		Rollout-Projekt
(muss)		GSM-R Strecken
(aut.)		Bahn-Linie(n)
(kann)		Funnel-Status (FS)




(muss und drop down)		Prozess

(drop down)		Project Typ (kann)
(drop down)		Project ID
(aut.)
(drop down)		ISPDate
entry (aut.)		ClusterRollout-ProjectG-Strecken
(aut.)		LinesFunnel-Status (FS)most recent entryProject ID
(aut.)
Array
105.02.2016Paris15
205.10.2016Rome#2
323.02.2017New York17
401.02.2016Toronto14
505.02.2015Paris#5
605.10.2017Rome12
723.02.2014New York#7
801.02.2016Toronto14
905.02.2016Paris15
1005.10.2016Rome#2
1123.02.2017New York17
1201.02.2016Toronto14

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col span="12"></colgroup><tbody>
</tbody>





dy
 
Last edited by a moderator:
Upvote 0
Re: Help needed to find a VBA code (macro)

MSchädler said:
I hope that this is the way to send you part of the excel table with the contents.
Click to expand...
It is better, but not the best. Compare how easy it is to tell what is where in what you have posted, compared to what I have posted below. Note also that I have hidden some irrelevant columns to make the screen shot even simpler - yet another advantage of the methods I am trying to direct you to, but you aren't even saying if you have tried any of them and if so what went wrong. :(

I am not familiar with exactly what you are trying to do, so I have some more questions.

1. Do I have the correct layout?

2. Are they actual dates (numbers) in column Q or are they text that look like dates? (Below, I have converted them to actual dates in my local format)

3. As an example, how do you (manually) get 5 for the first value in column X? (If I apply your formula from post 1 to row 2, it returns #N/A for me)

If I don't have the correct layout and/or the results for this sample data are incorrect, or you could demonstrate better with another small set of dummy sample data, please do so - preferably with one of the HTML Makers!


Excel 2016 (Windows) 32 bit
ABPQRSTUVWX
1DB NummerProzessISPDateClusterRollout-ProjectG-StreckenLinesFunnel-Status (FS)most recent entryProject ID
2105/02/2016Paris15
3205/10/2016Rome#2
4323/02/2017New York17
5401/02/2016Toronto14
6505/02/2015Paris#5
7605/10/2017Rome12
8723/02/2014New York#7
9801/02/2016Toronto14
10905/02/2016Paris15
111005/10/2016Rome#2
121123/02/2017New York17
131201/02/2016Toronto14
Sheet6
[/SIZE]
 
Last edited:
Upvote 0
Re: Help needed to find a VBA code (macro)

Peter_SSs said:
It is better, but not the best. Compare how easy it is to tell what is where in what you have posted, compared to what I have posted below. Note also that I have hidden some irrelevant columns to make the screen shot even simpler - yet another advantage of the methods I am trying to direct you to, but you aren't even saying if you have tried any of them and if so what went wrong. :(

I am not familiar with exactly what you are trying to do, so I have some more questions.

1. Do I have the correct layout?

2. Are they actual dates (numbers) in column Q or are they text that look like dates? (Below, I have converted them to actual dates in my local format)

3. As an example, how do you (manually) get 5 for the first value in column X? (If I apply your formula from post 1 to row 2, it returns #N/A for me)

If I don't have the correct layout and/or the results for this sample data are incorrect, or you could demonstrate better with another small set of dummy sample data, please do so - preferably with one of the HTML Makers!

Excel 2016 (Windows) 32 bit
ABPQRSTUVWX
DB NummerProzessISPDateClusterRollout-ProjectG-StreckenLinesFunnel-Status (FS)most recent entryProject ID
05/02/2016Paris
05/10/2016Rome#
23/02/2017New York
01/02/2016Toronto
05/02/2015Paris#
05/10/2017Rome
23/02/2014New York#
01/02/2016Toronto
05/02/2016Paris
05/10/2016Rome#
23/02/2017New York
01/02/2016Toronto

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]2[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]7[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]5[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]7[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]2[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]7[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]

</tbody>
Sheet6​
Click to expand...

Hello Peter
Since I'm a newcomer to MrExcel I have some difficulties with posting actuel files.
To your questions:
1. Yes the layout is correct.
2. Yes I have real dates (format)
3. The result is 5 which is correct. The array formula looks at column S in respect to the dates (Q) and takes the oldest date. Then it takes the number in column A.
In this example it is Paris with the oldest date 05/02/2015.
In Column A I use the following formula; =if(S2<>"";N(A1)+1;"")
The array formula has to be entered with; ctrl-shift-enter
I hope this helps.
M
 
Last edited by a moderator:
Upvote 0
Re: Help needed to find a VBA code (macro)

MSchädler said:
Since I'm a newcomer to MrExcel I have some difficulties with posting actuel files.
Click to expand...
I am trying to help with that too. :)
Which of the HTML Makers did you try, and what went wrong?



MSchädler said:
3. The result is 5 which is correct. The array formula looks at column S in respect to the dates (Q) and takes the oldest date. Then it takes the number in column A.
Click to expand...
OK, got it at last. :)

Try this in a copy of your workbook.

Code: 
Sub ProjectID()
  Dim d As Object
  Dim a As Variant, aRws As Variant, aCols As Variant, b As Variant
  Dim lr As Long, r As Long
  
  Set d = CreateObject("Scripting.Dictionary")
  d.CompareMode = 1
  lr = Cells(Rows.Count, "Q").End(xlUp).Row
  aRws = Evaluate("row(2:" & lr & ")")
  aCols = Array(1, 17, 19) [COLOR="#008000"]'<-Columns A, Q & S containing the 'data of interest'[/COLOR]
  a = Application.Index(Cells, aRws, aCols)
  ReDim b(1 To UBound(a), 1 To 1)
  For r = 1 To UBound(a)
    If d.exists(a(r, 3)) Then
      If Split(d(a(r, 3)))(0) > a(r, 2) Then d(a(r, 3)) = a(r, 2) & " " & a(r, 1)
    Else
      d(a(r, 3)) = a(r, 2) & " " & a(r, 1)
    End If
  Next r
  For r = 1 To UBound(a)
    b(r, 1) = Split(d(a(r, 3)))(1)
  Next r
  Range("X2").Resize(UBound(b)).Value = b
End Sub
 
Upvote 0
Re: Help needed to find a VBA code (macro)

Good day Peter
Thanks a lot for your much appreciated input.
I will test this vba it in my sheet tomorrow try to understand the codes and give you a feedback.
In the meantime enjoy your day and thanks again.
Marc
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Solved
VBA Code to Enter Formula in the Cell as Array Formula
Replies
5
Views
79
MARK858
MARK858
K
  • Solved
Using VBA to select a color coded cell
2
Replies
11
Views
960
Alex Blakenburg
A
Y
  • Question Question
Macro to find & replace from source table - need to match whole source table string to partial string in workbook
Replies
1
Views
427
Kaper
Kaper
M
  • Question Question
VBA for different formulas to reduce the excel file
Replies
4
Views
212
MMM_84
M
I
  • Solved
VBA to remove formulas with values causing wrong calculations
2
Replies
12
Views
903
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,224,823
Messages
6,181,182
Members
453,021
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

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