How to isolate one to one relationships?

Hi, hoping someone can help me with this?

For every record in column A there can me many records in column B. For every record in column B there can be many different records in column C. I only want to look at records in column A that only have one unique record in column B.

Hope my explanation makes sense? Be gentle with me.

It would help if you attached a sample Excel workbook, then we can see exactly what you have.

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

It strikes me that you can use a formula with COUNTIFS, but it would be easy to check this out if we can see your data.

Pete

Thanks for the advice Pete. Here's the attachment.

I am only interested in records in column A that only have one unique record in column B.

Hope the attachment worked.

Try this in cell D2:

=IF(COUNTIF(A:A,A2)=1,"here","")

then copy down. Is that the kind of thing that you mean?

Hope this helps.

Pete

Thanks Pete. Almost what I wanted. I don't think I am explaining it very well. I want the rows with a one to one relationship even if there are multiple rows. Like in the first four rows of data, there is only one unique number in column B that correlates to column A, so I want those rows isolated as well.

Rows 8-21 have more than one unique record in column B for every unique record in Column A, so I want them filtered out.

I appreciate your help on this.

Winner winner chicken dinner.

Thanks so much for your help!

May the force be with you.

Originally Posted by shg

Like this?

	A	B	C	D	E
1	Purch.Doc.	Delivery	Article
2	4501879764	368177104	1122697	0	D2: =COUNTIFS($A$2:$A$21, A2, $B$2:$B$21, "<>" & B2)
3	4501879764	368177104	1129457	0
4	4501879764	368177104	1129633	0
5	4501879764	368177104	1129636	0
6	4501887045	371040471	1097146	0
7	4501900610	369255024	1106098	0
8	4501910018	368307623	1050078001	2
9	4501910018	368307623	1050078002	2
10	4501910018	368307623	1050078003	2
11	4501910018	368307623	1050078004	2
12	4501910018	368307623	1050078005	2
13	4501910018	368905681	1050123002	5
14	4501910018	368905681	1050123005	5
15	4501910021	369241847	1060658001	3
16	4501910021	369241847	1078665003	3
17	4501910021	369241847	1078665004	3
18	4501910021	369241847	1078665005	3
19	4501910021	369535643	1101849005	4
20	4501910021	369535643	1101849006	4
21	4501910021	369535643	1101849007	4

The zero means there are no other values in col A that have a different value in col B.

Winner winner chicken dinner.

Thanks so much for your help!

May the force be with you.

Originally Posted by Glenn Kennedy

Who are you talking to??

Sorry for the confusion. I did not try your solution as shg's solution seemed to work.