Parent-Child - Unique Parent count based on Children Type and Counts

[albertd9]

Hello,

I have been trying to solve this query for the past few days without any luck.

I have a simple table that contains 2 columns: Parent Column and Children Type. The query that I am trying to create is to extract how many parents contain the same children type and the same children type count.

Parent Column	Children Type
uvw	123
uvw	234
wxy	345
xyz	456

<tbody>
</tbody>

(e.g.) parents columns contains data that is duplicate because the same unique parent number contains multiple children data. Some parents may contain the same children type but not the same children type count.

The goal is to extract from the whole table all different parents "recipe"

e.g. there are 100 parents similar to parent "uvw" that contain the same children types "123" and "234" with the exact count for each children type).

 

[Steve_R]

Something like the following?

Sheet1

*	A	B	C	D	E
1	Parent Column	Children Type	Parent Count	Child Count	Family Duplicate Count
2	uvw	123	3	1	1
3	uvw	234	3	1	1
4	wxy	345	2	3	2
5	xyz	456	1	1	1
6	uvw	345	3	3	1
7	wxy	345	2	3	2

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:100px;"><col style="width:94px;"><col style="width:88px;"><col style="width:79px;"><col style="width:153px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula C2 =COUNTIF(A:A,A2) D2 =COUNTIF(B:B,B2) E2 =COUNTIFS(A:A,A2,B:B,B2) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[albertd9]

Hello Steve,

Thank you for the idea I believe that is the right direction. But for example in your chart parent "wxy" is the same parent that contains 2 "345" children type. Since there are no other parents containing 2 "345" then there are unique.

Maybe my explanation was not very clear and please accept my apologies this query gets me all confused.

Another problem i am seeing with this formula is how to identified that the "family duplicates" were already counted (e.g. parent "xyz" with children "123 = 1", "234 = 2" and "345 = 1" is 1 family and we are counting how many more parents in the database exist like him. So imagine it found 100 more parents like him, maybe I need to introduce another field to identify that particular type of family in order to count unique "family recipe".

wow this thing is confused, I hope you guys enjoy the challenge.

Thank you in advance for the ideas.

 

[Steve_R]

So the child 345 is unique no matter how many repetitions with the same parent? It becomes non-unique if found under another parent? If that's not the problem you are trying to solve, perhaps it will help if you construct an example showing the result you need to get. Samples generally help people understand when they are on the right track. Just make sure you cover the scenarios that makes something a duplicate and not a duplicate. Also, whether you want the result in a separate location or new column.

 

[Steve_R]

Still slightly unsure about what it is you need but, if a count in a separate table of each parent/Child combination, the following VBA will set up a table of unique records on a separate sheet with a formula to count.

Sub GetUniqueList()
    Dim FromSheet
    Dim ToSheet
    Dim MyRange
    FromSheet = "Sheet1" 'change
    ToSheet = "Sheet2" 'change
    
    
    lRow = Sheets(FromSheet).UsedRange.Rows.Count
    
    MyRange = "A1:B" & lRow 'if parent/child not in columns A & B
                            'code will need significant change
    
    Sheets(ToSheet).Cells.Clear
    Sheets(ToSheet).Range(MyRange).Value = Sheets(FromSheet).Range(MyRange).Value
    
    Sheets(ToSheet).Range(MyRange).RemoveDuplicates Columns:=Array(1, 2), Header _
        :=xlYes
        MyFormula = "=COUNTIFS(" & FromSheet & "!A:A," & ToSheet & "!A2," & FromSheet & "!B:B," & ToSheet & "!B2)"
        
    Sheets(ToSheet).Range("C2").Formula = MyFormula
    lRowSh2 = Sheets(ToSheet).UsedRange.Rows.Count
    Sheets(ToSheet).Range("C2").AutoFill Destination:=Range("C2:C" & lRowSh2)
    
End Sub

Excel 2010

	A	B
1	Parent Column	Children Type
2	uvw	123
3	uvw	234
4	wxy	345
5	xyz	456
6	uvw	345
7	wxy	345

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Excel 2010

	A	B	C
1	Parent Column	Children Type
2	uvw	123	1
3	uvw	234	1
4	wxy	345	2
5	xyz	456	1
6	uvw	345	1

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet2​

Worksheet Formulas Cell Formula C2 =COUNTIFS(Sheet1!A:A,Sheet2!A2,Sheet1!B:B,Sheet2!B2) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

NOTE:Code above generated by VBA

 

[FDibbins]

Is this an excel or access question? It is posted in the access forum

 

[Steve_R]

Is this an excel or access question? It is posted in the access forum

Insighful question