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]

Marc,

You are very welcome. I'm glad i could help in some way.

I've suggested a new way of identifying projects because adding new entries can change the already defined IDs. For example, considering the data sample from post 6, assume a new Halifax entry with an earlier date. This will happen:


[TABLE="class: grid"]
<tbody>[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 ID​

[/TD]
[TD]

Opening Date​

[/TD]
[TD]

Project Name​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

1​

[/TD]
[TD]

5​

[/TD]
[TD]

15/03/2017​

[/TD]
[TD]

Toronto​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

2​

[/TD]
[TD]

1​

[/TD]
[TD]

15/01/2017​

[/TD]
[TD]

Montreal​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

3​

[/TD]
[TD]

5​

[/TD]
[TD]

20/04/2017​

[/TD]
[TD]

Toronto​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

4​

[/TD]
[TD]

8​

[/TD]
[TD]

10/06/2017​

[/TD]
[TD]

Halifax​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

5​

[/TD]
[TD]

5​

[/TD]
[TD]

01/01/2017​

[/TD]
[TD]

Toronto​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

6​

[/TD]
[TD]

2​

[/TD]
[TD]

04/04/2016​

[/TD]
[TD]

XXX​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD]

7​

[/TD]
[TD]

3​

[/TD]
[TD]

08/04/2017​

[/TD]
[TD]

YYY​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

9​

[/TD]
[TD]

8​

[/TD]
[TD]

8​

[/TD]
[TD]

02/01/2017​

[/TD]
[TD]

Halifax​

[/TD]
[/TR]
</tbody>[/TABLE]


Some projects would have a new ID. I am afraid that this may be confusing and does not seem to be a good practice.

Anyway, i hope you find a good solution.

Good luck!

M.

 

[Marcelo Branco]

Marc

It seems i misunderstood what you really want

Try this new version

[TABLE="class: grid"]
<tbody>[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 ID​

[/TD]
[TD]

Opening Date​

[/TD]
[TD]

Project Name​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

1​

[/TD]
[TD]

5​

[/TD]
[TD]

15/03/2017​

[/TD]
[TD]

Toronto​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

2​

[/TD]
[TD]

2​

[/TD]
[TD]

15/01/2017​

[/TD]
[TD]

Montreal​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

3​

[/TD]
[TD]

5​

[/TD]
[TD]

20/04/2017​

[/TD]
[TD]

Toronto​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

4​

[/TD]
[TD]

4​

[/TD]
[TD]

10/06/2017​

[/TD]
[TD]

Halifax​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

5​

[/TD]
[TD]

5​

[/TD]
[TD]

01/01/2017​

[/TD]
[TD]

Toronto​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

6​

[/TD]
[TD]

6​

[/TD]
[TD]

04/04/2016​

[/TD]
[TD]

XXX​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD]

7​

[/TD]
[TD]

7​

[/TD]
[TD]

08/04/2017​

[/TD]
[TD]

YYY​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

9​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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

Formula in B2 copied down
=IF(A2="","",LOOKUP(1,0/FREQUENCY(1,(D$2:D$1000=D2)*(C$2:C$1000)),A$2:A$1000))

The formula assumes that every time a value (project) is entered in column D also a date is entered in column C and that dates in column C are real dates (numbers), not text.

M.

 

[MSchädler]

Hello Marcelo
Many thanks for this additional Input.
I now have all the options that I need from you and from Peter to build my sheet and test it in real.
Again many thanks for your help!
Marc