Define excel formula or vba code

MSchädler

Board Regular
Joined
Apr 27, 2017
Messages
87
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)
1Toronto03.15.20175
2Montreal15.01.20171
3Toronto20.04.20175
4Halifax10.06.20172
5Toronto01.01.20175
6XXX04.04.20163
7YYY08.04.20174

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

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,371
Maybe this...


A
B
C
D
1
Counter​
Project Name​
Opening Date​
Project ID​
2
1​
Toronto​
15/03/2017​
5​
3
2​
Montreal​
15/01/2017​
1​
4
3​
Toronto​
20/04/2017​
5​
5
4​
Halifax​
10/06/2017​
2​
6
5​
Toronto​
01/01/2017​
5​
7
6​
XXX​
04/04/2016​
3​
8
7​
YYY​
08/04/2017​
4​
9

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

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,371
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

Board Regular
Joined
Apr 27, 2017
Messages
87
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
 

MSchädler

Board Regular
Joined
Apr 27, 2017
Messages
87
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

CounterProject IDOpening DateProject Name
1515.03.2017Toronto
2115.01.2017Montreal
3520.04.2017Toronto
4210.06.2017Halifax
5501.01.2017Toronto
6304.04.2016XXX
7408.04.2017YYY

<colgroup><col span="2"><col><col></colgroup><tbody>
</tbody>
Thanks again for your help.
M;
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,371
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

Board Regular
Joined
Apr 27, 2017
Messages
87
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

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,371
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

Board Regular
Joined
Apr 27, 2017
Messages
87
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:cool:.
Marc
 

Watch MrExcel Video

Forum statistics

Threads
1,095,816
Messages
5,446,669
Members
405,413
Latest member
AlainCar

This Week's Hot Topics

Top