• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Search for Rows that match a criteria in another column

wnll

New Member
Hi, my data has two columns named Child and Parent. The child values are duplicated and span multiple rows. Each child has multiple parents. I'd like to use a formula to search for those child that has the same parents in column B (in this example, 1022 and 1069).

Thanks so much in advance!
Wnll

Child Parent
1012 C28102
1012 C7057
1022 C20187
1022 C20187
1022 C20187
1041 C13018
1041 C3367
1065 C83482
1065 C7057
1065 C89680
1065 C6487
1069 C13018
1069 C13018
 
Assumed your table in ranges A1:B14 (Include Table Header):

Put in D2:
=IFERROR(INDEX($A$2:$A$14,SMALL(IF((COUNTIFS($B$2:$B$14,$B$2:$B$14,$A$2:$A$14,$A$2:$A$14)>1)*(COUNTIF(D1:$D$1,$A$2:$A$14)=0),ROW($A$2:$A$14)-ROW($A$2)+1),ROWS(A1:$A$1))),"")

or see the file attach
 

Attachments

  • Example.xlsx
    10.4 KB · Views: 9
Hi, my data has two columns named Child and Parent. The child values are duplicated and span multiple rows. Each child has multiple parents. I'd like to use a formula to search for those child that has the same parents in column B (in this example, 1022 and 1069).

Thanks so much in advance!
Wnll

Child Parent
1012 C28102
1012 C7057
1022 C20187
1022 C20187
1022 C20187
1041 C13018
1041 C3367
1065 C83482
1065 C7057
1065 C89680
1065 C6487
1069 C13018
1069 C13018

Thank you so much!
 
Hi,

Thank you again for your smart answer!

I tried your formula by extending 2 more rows from 14 rows to 16 rows (see sheet3 in the attached book). However, the answer I got is 0. Also, if I put your formula into another cell F8, I got a blank output. Why is that?

Could you also explain how you got this formula, what is the purpose of countif and countifs here?

wnll
 

Attachments

  • Example1_chandoo.xlsx
    10.8 KB · Views: 5
I think I missed something, the formula change to:

=IFERROR(INDEX($A$2:$A$16,MATCH(MIN(IF((COUNTIF($D$1:D1,$A$2:$A$16)=0)*(COUNTIFS($B$2:$B$16,$B$2:$B$16,$A$2:$A$16,$A$2:$A$16)>1),1,MAX((COUNTIF($A$2:$A$16,"<"&$A$2:$A$16)+1)*2))*(COUNTIF($A$2:$A$16,"<"&$A$2:$A$16)+1)),COUNTIF($A$2:$A$16,"<"&$A$2:$A$16)+1,0)),"")

or see the file attach

Thanks
 

Attachments

  • Example1_chandoo.xlsx
    11.4 KB · Views: 3
I think I missed something, the formula change to:

=IFERROR(INDEX($A$2:$A$16,MATCH(MIN(IF((COUNTIF($D$1:D1,$A$2:$A$16)=0)*(COUNTIFS($B$2:$B$16,$B$2:$B$16,$A$2:$A$16,$A$2:$A$16)>1),1,MAX((COUNTIF($A$2:$A$16,"<"&$A$2:$A$16)+1)*2))*(COUNTIF($A$2:$A$16,"<"&$A$2:$A$16)+1)),COUNTIF($A$2:$A$16,"<"&$A$2:$A$16)+1,0)),"")

or see the file attach

Thanks


Hi Azumi,

Thank you so much, again!

So, If I need to extend the number of rows from 18 to 100, can I simply change all the $A$18 to $A$100 and $B$18 to $B$100 in your formula?

I changed the row number from 18 to 20. How come the answer is blank? Please see sheet "test" in the attached file.

BR,
wnll
 

Attachments

  • Example2_chandoo.xlsx
    11.9 KB · Views: 3
Hi Azumi,

Thank you so much, again!

So, If I need to extend the number of rows from 18 to 100, can I simply change all the $A$18 to $A$100 and $B$18 to $B$100 in your formula?

I changed the row number from 18 to 20. How come the answer is blank? Please see sheet "test" in the attached file.

BR,
wnll

Azumi,

Forget my last email. It works fine - I should do cntrl-sh-ft-enter since it a array.

Thanks
wnll
 
Azumi,

Forget my last email. It works fine - I should do cntrl-sh-ft-enter since it a array.

Thanks
wnll

Hi,

I have one more question and really appreciate if you can help.

How would you modify the formula if the number of child in column A is not always 2, but can be any number?

In the attached excel file, "example2" tab, The answer should be 2240026 and 3008275. But the answer using your formula is 2955385.

Thanks again!
wnll
 

Attachments

  • Example2_chandoo.xlsx
    12.3 KB · Views: 2
Nor sure about what you need, pls find the file attach, perhaps something like this?
 

Attachments

  • Example_chandoo.xlsx
    14.4 KB · Views: 5
Nor sure about what you need, pls find the file attach, perhaps something like this?

Thanks!

Sorry I didn't make it clear. What I like to find is the same as my original post. i.e., only look for those child whoes corresponding parents in several rows are the same (the parents all have the same value). You had solved the problem for me. Really appreciated!

The only change now is the data itself. In the new data, the number of duplicated child are not always 2 like in the original data. The the number of dplicated child could be 2, 3, 4, 5.etc. How to modify your original formular for get the answer?

The anser should only be 2240026 and 3008293. These two child only have one unique parent: C46126|C27993 and C16956.

BR and appreciate your help,

wnll
 
Put in D2:

=IFERROR(INDEX($A$2:$A$24,SMALL(IF((COUNTIF($D$1:D1,$A$2:$A$24)=0)*(COUNTIFS($B$2:$B$24,$B$2:$B$24,$A$2:$A$24,$A$2:$A$24)>2),ROW($A$2:$A$24)-ROW($A$2)+1),1)),"")

Change the highlited in the formula to change the results
Hope it works
 
Put in D2:

=IFERROR(INDEX($A$2:$A$24,SMALL(IF((COUNTIF($D$1:D1,$A$2:$A$24)=0)*(COUNTIFS($B$2:$B$24,$B$2:$B$24,$A$2:$A$24,$A$2:$A$24)>2),ROW($A$2:$A$24)-ROW($A$2)+1),1)),"")

Change the highlited in the formula to change the results
Hope it works
Thanks!
 
Back
Top