Excel Formula: Insert a Specific Text After the Group of Texts

[MrRajKumar]

Hi,

I have the following data in A1:B7, The values in A2:B7 are in a single cell spill formula (dynamic array fill). The range will always be sorted by Group, so they return here in ascending order.

Group	Names
Group 1	John
Group 1	Steve
Group 1	Boe
Group 2	Jose
Group 2	Lily
Group 2	Robert

I would like to insert "Completed" after the every group. Output has shown below. Its OK, if I get the text 'Completed' either starting or ending of a group.
So whenever a new Group comes, should be added the text 'Completed'.

I can add VSTACK({"Group 1","Completed"},A2#) if it is only one group. I am stuck since more than one group.

Thank you all for your valuable time.

Group	Names
Group 1	John
Group 1	Steve
Group 1	Boe
Group 1	Completed
Group 2	Jose
Group 2	Lily
Group 2	Robert
Group 2	Completed

 

[Fluff]

How about

=LET(u,UNIQUE(INDEX(A2#,,1)),SORTBY(VSTACK(A2#,CHOOSE({1,2},u,"Completed")),VSTACK(SEQUENCE(ROWS(A2#)),XMATCH(u,INDEX(A2#,,1),,-1)+0.5)))

 

[Cubist]

One more option:

=DROP(REDUCE("",UNIQUE(INDEX(A1#,,1)),LAMBDA(a,b,VSTACK(a,FILTER(A1#,INDEX(A1#,,1)=b),HSTACK(b,"Completed")))),3)

 

[MrRajKumar]

How about

=LET(u,UNIQUE(INDEX(A2#,,1)),SORTBY(VSTACK(A2#,CHOOSE({1,2},u,"Completed")),VSTACK(SEQUENCE(ROWS(A2#)),XMATCH(u,INDEX(A2#,,1),,-1)+0.5)))

Thank you. It works. I used the following version,

=SORT(VSTACK(A2#,CHOOSE({1,2},UNIQUE(INDEX(A2#,,1)),"Completed")),{1,2})

 

[MrRajKumar]

One more option:

=DROP(REDUCE("",UNIQUE(INDEX(A1#,,1)),LAMBDA(a,b,VSTACK(a,FILTER(A1#,INDEX(A1#,,1)=b),HSTACK(b,"Completed")))),3)

Thank you for your reply Cubist. This version doesn't give me correct result. Below are the result I got with this formula.

Group 1	Boe
Group 1	Completed
Group 2	Jose
Group 2	Lily
Group 2	Robert
Group 2	Completed

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Cubist]

Thank you for your reply Cubist. This version doesn't give me correct result. Below are the result I got with this formula.

Group 1	Boe
Group 1	Completed
Group 2	Jose
Group 2	Lily
Group 2	Robert
Group 2	Completed

Change the 3 to 1. I assumed the headers were part of the array.