Allocate ID to another ID without duplicating

[leabe18]

Hi, 
I want to be able to "Allocate" an ID to another ID based on a condition. 
My data is currently: 

Parent	ID	Type	Status
P1	ID1	A	Installed
P1	ID2	A	Planned
P1	ID3	A	Planned
P1	ID4	A	Planned
P1	ID5	F	Remove
P1	ID6	F	Remove
P1	ID7	F	Remove
P1	ID8	F	Remove
P1	ID9	F	Planned
P1	ID10	F	Planned
P1	ID11	F	Planned

I want to be able to "Allocate" the ID's of Type F where they match the status of a type A ID. Ensuring that a type F ID is not used more than once. 
The end result should look like this: 

Parent	ID	Type	Status	Allocated F ID
P1	ID1	A	Installed
P1	ID2	A	Planned	ID9
P1	ID3	A	Planned	ID10
P1	ID4	A	Planned	ID11

The part I am struggling with is ensuring that the F type ID is not used more than once. I end up with the same F Type ID "allocated" to all of the matching A type ID's.... (like the below - which is wrong)

Parent	ID	Type	Status	Allocated ID
P1	ID1	A	Installed
P1	ID2	A	Planned	ID9
P1	ID3	A	Planned	ID9
P1	ID4	A	Planned	ID9

Any advice please?

[rubenmarin · Accepted solution]

Hi, I get that table with this script:

Orig:
LOAD * INLINE [
    Parent, ID, Type, Status
    P1, ID1, A, Installed
    P1, ID2, A, Planned
    P1, ID3, A, Planned
    P1, ID4, A, Planned
    P1, ID5, F, Remove
    P1, ID6, F, Remove
    P1, ID7, F, Remove
    P1, ID8, F, Remove
    P1, ID9, F, Planned
    P1, ID10, F, Planned
    P1, ID11, F, Planned
];

Combined:
NoConcatenate LOAD * Resident Orig Where Type='A';
Left Join LOAD Status, ID as AllocatedID Resident Orig Where Type='F';

DROP Table Orig;

Final:
NoConcatenate LOAD *, ID as UsedID, AllocatedID as UsedAllocatedID 
Resident Combined Where not Exists('UsedID',ID) and not Exists('UsedAllocatedID',AllocatedID)
Order By ID, AllocatedID;

DROP Table Combined;

[leabe18]

that works, thank you so much