Define excel formula or vba code

MSchädler

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

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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;
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,177
Members
453,021
Latest member
Justyna P

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