Define excel formula or vba code

[MSchädler]

Hello there and I definitely need some help from someone to find a formula or a vba code for my column D.
Situation: I'm building a Excel sheet to act as a database and I want some automation.
In column A I have a formula for an incremental counter (starting from 1 counting up to 63655 when using, copy last row+paste below).
In column B I have different project names eg. Toronto, Montreal and so on.
In column C I have the adjacent opening date of each project.
In column D I want to insert the specific project-ID when several same project names occur. When it is a new project name I want to continue the sequence in Column A.
The database is similar to this

A (counter)	B (project name)	C (opening date)	D (project ID)
1	Toronto	03.15.2017	5
2	Montreal	15.01.2017	1
3	Toronto	20.04.2017	5
4	Halifax	10.06.2017	2
5	Toronto	01.01.2017	5
6	XXX	04.04.2016	3
7	YYY	08.04.2017	4

<tbody>
</tbody>

I'm now looking for a formula or a vba code to input in column D the appropriate project-ID.
The formula or vba code should do the following: Find in Column B every exact same project name (eg. Toronto), detect the oldest date of the project (column C), and then enter that row count from Column A in all cells with that same project name in column D.
Additional when opening a new project, the project ID (Column D) will be the next unused sequence of column A.

Can someone give me some suggestions on how to go about it?
Thanks in advance for your help.

 

[Marcelo Branco]

Maybe this...


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Counter​

[/td][td]

Project Name​

[/td][td]

Opening Date​

[/td][td]

Project ID​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

1​

[/td][td]

Toronto​

[/td][td]

15/03/2017​

[/td][td]

5​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

2​

[/td][td]

Montreal​

[/td][td]

15/01/2017​

[/td][td]

1​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

3​

[/td][td]

Toronto​

[/td][td]

20/04/2017​

[/td][td]

5​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

4​

[/td][td]

Halifax​

[/td][td]

10/06/2017​

[/td][td]

2​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

5​

[/td][td]

Toronto​

[/td][td]

01/01/2017​

[/td][td]

5​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td]

6​

[/td][td]

XXX​

[/td][td]

04/04/2016​

[/td][td]

3​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

8​

[/td][td]

7​

[/td][td]

YYY​

[/td][td]

08/04/2017​

[/td][td]

4​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

9​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

Formula in A2 copied down
=IF(B2<>"",N(A1)+1,"")

Array formula in D2 copied down
=IF(A2="","",IF(COUNTIF(B$2:B$100,B2)=1,SMALL(IF(A$2:A$100<>"",IF(ISNA(MATCH(A$2:$A$100,D$1:D1,0)),A$2:A$100)),1),INDEX(A$2:A$100,MATCH(MIN(IF(B$2:B$100=B2,C$2:C$100)),IF(B$2:B$100=B2,C$2:C$100),0))))
Ctrl+Shift+Enter

Hope this helps

M.

 

[Marcelo Branco]

It seems the forum software inserted an extraneous blank in the formula
MATCH(MIN(IF(B$2:B$100=B2,C$2:C$100)),IF(B$2:B$100=B 2,C$2:C$100),0))))

Delete it - the correct is
MATCH(MIN(IF(B$2:B$100=B2,C$2:C$100)),IF(B$2:B$100=B2,C$2:C$100),0))))

M.

 

[MSchädler]

Hello Marcelo

Many thanks for your input and I have used the formula in my trial sheet. It works wonderfully. Thanks for this.
I now have to adapt it to my sheet and I'm sure it will work, otherwise I'll allow myself to ask you again.
In the meantime many thanks again.
Marc

 

[Marcelo Branco]

You are welcome. Glad to help

M.

 

[MSchädler]

Hello Marcelo
Sorry, I have to ask you if you can give me the array formula for the following setting. The project ID and the date are before the project name. I tried it myself but somehow I cant get it to work.
This is the setting of the database

[TABLE="width: 343"]
<colgroup><col span="2"><col><col></colgroup><tbody>[TR]
[TD]Counter[/TD]
[TD]Project ID[/TD]
[TD]Opening Date[/TD]
[TD]Project Name[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]15.03.2017[/TD]
[TD]Toronto[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]15.01.2017[/TD]
[TD]Montreal[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]20.04.2017[/TD]
[TD]Toronto[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]10.06.2017[/TD]
[TD]Halifax[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]01.01.2017[/TD]
[TD]Toronto[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]04.04.2016[/TD]
[TD]XXX[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]08.04.2017[/TD]
[TD]YYY[/TD]
[/TR]
</tbody>[/TABLE]
Thanks again for your help.
M;

 

[Marcelo Branco]

Be sure that the dates in column C are real dates (numbers), not text.

Formula in A2 copied down
=IF(D2<>"",N(A1)+1,"")

Array formula in B2 copied down
=IF(A2="","",IF(COUNTIF(D$2:D$100,D2)=1,SMALL(IF(A$2:A$100<>"",IF(ISNA(MATCH(A$2:$A$100,B$1:B1,0)),A$2:A$100)),1),INDEX(A$2:A$100,MATCH(MIN(IF(D$2:D$1000=D2,C$2:C$100)),IF(D$2:D$100=D2,C$2:C$100),0))))
Ctrl+Shift+Enter

M.

 

[MSchädler]

Hello Marcelo
Thanks to your effort everything in my sheet works the way I expected. Many thanks again!

Now, because I'm using up to 63655 rows the time for the Array to calculate is high. Do you know of a way to speed up this calculation?
I'm starting to think towards a VBA code (macro). Do you have any idea? Thanks for your view.
Regards
M.

 

[Marcelo Branco]

I've tried to work out a solution to your problem, but with a large database the formula may not perform well. Maybe a macro can be solution - hope someone can help you.

On the other hand, i think you should seriously consider a less complex way to create the ID of each project.

Regards

M.

 

[MSchädler]

No problem Marcelo.
For the moment your input works well but with time and growth of the database I will encounter problems with speed.
I will see if someone can provide some help with a macro.

Thanks again for your help and have a great time.
Marc