I am trying to delete duplicate child rows for a parent row. I tried using count if but it removes all the duplicate child even from other parent item

Question

I am trying to delete duplicate child rows for a parent row. I tried using a helper cell with the count if formula "=(COUNTIF($B$1:$B1,$B1)=1)+0", but it removes all the duplicate children, even from the other parent item.

Current Dataset Sample:

Level	Item Number
0	Parent-01
2	Child-01
2	Child-02
2	Child-03
2	Child-01
2	Child-02
2	Child-03
2	Child-01
2	Child-02
2	Child-03
0	Parent-02
2	Child-01
2	Child-02
2	Child-03

Expected Output:

Level	Item Number
0	Parent-01
2	Child-01
2	Child-02
2	Child-03
0	Parent-02
2	Child-01
2	Child-02
2	Child-03

I am trying to delete duplicate child rows for a parent row. I tried using helper cell with the count if formula "=(COUNTIF($B$1:$B1,$B1)=1)+0" but it removes all the duplicate child even from other parent item.

Output of the count if:

Level	Item Number	Count
0	Parent-01	1
2	Child-01	1
2	Child-02	1
2	Child-03	1
2	Child-01	0
2	Child-02	0
2	Child-03	0
2	Child-01	0
2	Child-02	0
2	Child-03	0
0	Parent-02	1
2	Child-01	0
2	Child-02	0
2	Child-03	0

Expected Output:

Level	Item Number	Count
0	Parent-01	1
2	Child-01	1
2	Child-02	1
2	Child-03	1
0	Parent-02	1
2	Child-01	1
2	Child-02	1
2	Child-03	1

Answer 1

Here is one way to accomplish the desired output:

=LET(
     a, SCAN(0,A2:A15=0,LAMBDA(x,y,IF(y,x+1,x))),
     b, XLOOKUP(a,a,B2:B15),
     VSTACK(A1:B1,TAKE(UNIQUE(HSTACK(A2:B15,b)),,2)))

---

Here is an explanation:

• Using LET() function it helps to define variables and reduce redundant calculations.

• Variable a is defines the use of a LAMBDA() helper function called SCAN() which helps in returning an array with the help of the custom formula applied to the LAMBDA() which iterates through each value in the array and returns an array of outputs for each intermediate element in the array. So, it checks for the range A2:A15 if the range is equal to 0 and then it creates a series of array like 1,1,1,1,2,2,2,etc, its like we are generating a group id based on zero values in the range !

• Next, using the above and help of XLOOKUP() function we are returning the Parent which again does creates and aligns with the Grouping.

• In the final step we are performing and wrapping the variable b within HSTACK() along with the original source range that is A2:B15 to return a combined merged data. Wrapping within UNIQUE() to return the requisite values, and using TAKE() excluding the variable b which gives us the final output, to make things fancy , just adding the VSTACK() so that output appends with the output.

---