Identifying Couples who pay by different means.

[Carton Card]

Hi everyone

My first post, I hope you can help.
I maintain a membership list, some are singles and some are couples (i.e. live at the same address). Most of the couples pay their membership fee by Standing Order or by cheque. However there is a significant number where one member of the couple pays by SO and the other by cheque. I need to be able to identify all the couples that pay by different means. Can anyone help?

Thanks
Carton Card

[sourabhg98]

Hi
Welcome o the forum!!
Before giving out any answers we would like to have a look at your data..

Please attach a sample workbook. Make sure there is just enough data to demonstrate your need. Make sure your desired results are shown, mock them up manually if necessary.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

http://www.excelforum.com/members/da...ch-a-file.html

[Carton Card]

Hi sourabhg98

Here, I hope is a file that will demonstrate what I want.

Identifying Couples who pay by different means.xlsx

[gmr4evr1]

Removed because it isn't at all what you were asking for

[sourabhg98]

Hello
Use this in C2
=IF(OR(AND(B2=B1,C2<>C1),AND(B2=B3,C2<>C3)),"x","")
and copy down

If your addresses are not continuous ....like in your example the same addresses were one below the other...but if they are spread and not continuous then the above formula won't work
So use this in C2 and copy down
=IF(OR(IFERROR(VLOOKUP(B2,B3:$C$12,2,FALSE),C2)<>C2,IFERROR(VLOOKUP(B2,$B$1:C1,2,FALSE),C2)<>C2),"x","")
Change $C$12 to your own data range....

Hope this helps!!

[Carton Card]

Hi sourabhg98

Works a treat although I think you meant "D2" and not "C2". I do sort into addresses first. Many thanks for your help.

regards
Carton Card

[sourabhg98]

Glad to know it worked!!
Yes it was D2 ...sorry for that!

If your query is solved then please mark this thread as solved and you can click on add reputation below to say thanks!!
Regards