# Marking if one of a pair included in list

1. ## Marking if one of a pair included in list

Hi everyone,

first of all sorry for the clumsy title...
I think it's easiest to look at the example sheet when I explain my problem.

I have a list of pairs (triples or even more) in column A. All the units that belong together have the same ID. So in the example A and J are a pair.
Now I have a list (column F).
What I want is to add a "1" in column C for all the units that belong to pairs where at least one unit is on the list. I have hard coded the desired result in column D.

Does anyone have a simple formula (the sheet is huge in reality) to solve this?

2. ## Re: Marking if one of a pair included in list

Hi,

Assuming your IDs will always be sorted, in D2 and copy down:

=IF(SUMPRODUCT(0+(\$A\$2:\$A\$15=A2),0+(ISNUMBER(MATCH(\$B\$2:\$B\$15,\$G\$2:\$G\$4,0)))),1,"")

Regards

3. ## Re: Marking if one of a pair included in list

In C2 Cell - Array Formula - Requires CTRL+SHIFT+ENTER

=IF(SUM(COUNTIFS(\$A\$2:\$A\$15,A2,\$B\$2:\$B\$15,TRANSPOSE(\$F\$2:\$F\$4))),1,"")

Drag it down...

Array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.

4. ## Re: Marking if one of a pair included in list

Or no sorting needed: array formula (Ctrl+Shift+Enter comitted)
see attachment

5. ## Re: Marking if one of a pair included in list

Oops... Here is the revised sotluion

Non Array Formula

=IF(SUMPRODUCT(COUNTIFS(\$A\$2:\$A\$15,A2,\$B\$2:\$B\$15,\$F\$2:\$F\$4)),1,"")

6. ## Re: Marking if one of a pair included in list

@Sixth

OP states 2003

7. ## Re: Marking if one of a pair included in list

Originally Posted by XOR LX
@Sixth
OP states 2003
But the attached sample file is 2007 lol

8. ## Re: Marking if one of a pair included in list

Yeah, I guess I had to update the Excel Info :p...

Thanks for the solutions!

9. ## Re: Marking if one of a pair included in list

Yeh, I was also thinking it is for Ex2003 while obviously file extension was xlsx - blind me lol

@Sixthsense: We have an open conversation on PM, but your private mesages has exceeded quota so I cannot send answer

10. ## Re: Marking if one of a pair included in list

Originally Posted by Kaper
Oopss.. Sorry for that... now it's having more space since I deleted the old messages

11. ## Re: Marking if one of a pair included in list

Hope moderators will be tollerant (because it gets really OT), but: nope, it didn't work. Still getting:
The following errors occurred with your submission
Sixthsense has exceeded their stored private messages quota and cannot accept further messages until they clear some space.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1