Segmenting a continuously expanding list based on unique values.

[Duct]

Hello dear community,

I am brainstorming on a way to segment a continuously expanding list based on whether the row that is added is unique or not. The result I want to achieve is better understood in context, we have different projects, all of which have a main project. On some of those projects our client requests "additional work", which can sometimes be up to 10 additional little projects. The output comes from our calculation system and it gives me the following row:

Project#, Project Name, Additional Work title, Budget, Remaining Budget.

Every week I extract this information out of our calculation system and I paste new orders into this worksheet. This can be a completely new project or additional work on an existing project. I would like to come up with a formula that does the following. When pasted it has to identify whether the order is a new project or an additional works, based on the unique identifier of the project number, I am thinking along the lines of the countif function, if it's 1 it's a unique project, if it's more than one it is additional work.

If it is a new project it just has to be added on the two other sheets with no extra information. (see example Client 1 Project 9). If it's an additional work it should be added on the second sheet as extra information for the main project, and should be added as an extra line on the third sheet (but underneath the main project). Here I was thinking along the lines of the countif function and than expanding with the offset function to create the room for the additional work rows underneath the main project. Do you guys think I can get there with these formulas or is there a better way to approach this?

I attached an example sheet to visualize what I would like to achieve.

Many thanks!

Duct

[Bo_Ry]

Maybe this

A2
=IFERROR(INDEX(Inputfromsystem!$A$2:$A$99,SMALL(IF(FREQUENCY(IF(LEN(Inputfromsystem!$A$2:$A$99)=9,MATCH(Inputfromsystem!$A$2:$A$99,Inputfromsystem!$A$2:$A$99,)),ROW($A$1:$A$99)),ROW($A$1:$A$99)),ROWS(A$2:A2))),"")

B2:F2
=IF($A2="","",VLOOKUP($A2,Inputfromsystem!$A$2:$G$99,COLUMNS($A2:B2),))

D2
=COUNTIF(Inputfromsystem!$A$2:$A$99,$A2)-1
Custom format # to hide Zero

G2:H2
=IF($A2="","",SUMIFS(Inputfromsystem!E$2:E$99,Inputfromsystem!$A$2:$A$99,$A2)-E2)

Custom format 0,0.00;; to hide Zero