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:

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
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)

I hope that this is the way to send you part of the excel table with the contents.
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)

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

<tbody>
</tbody>
Sheet6

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)

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



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

Forum statistics

Threads
1,213,529
Messages
6,114,155
Members
448,554
Latest member
Gleisner2

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