+ Reply to Thread
Results 1 to 9 of 9

Marking if one of a pair included in list

  1. #1
    Forum Contributor
    Join Date
    06-24-2009
    Location
    Germany, MG
    MS-Off Ver
    Excel 2010
    Posts
    131

    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?

    Thanks a lot in advance!
    Attached Files Attached Files

  2. #2
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,745

    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
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  3. #3
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,771

    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.


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  4. #4
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often 365, but still time to time use also older versions
    Posts
    8,350

    Re: Marking if one of a pair included in list

    Or no sorting needed: array formula (Ctrl+Shift+Enter comitted)
    Please Login or Register  to view this content.
    see attachment
    Attached Files Attached Files
    Best Regards,

    Kaper

  5. #5
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,771

    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. #6
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,745

    Re: Marking if one of a pair included in list

    @Sixth

    OP states 2003

  7. #7
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,771

    Re: Marking if one of a pair included in list

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

  8. #8
    Forum Contributor
    Join Date
    06-24-2009
    Location
    Germany, MG
    MS-Off Ver
    Excel 2010
    Posts
    131

    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!
    Last edited by Cunner; 04-09-2014 at 03:23 AM.

  9. #9
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often 365, but still time to time use also older versions
    Posts
    8,350

    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. #10
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,771

    Re: Marking if one of a pair included in list

    Quote Originally Posted by Kaper View Post
    your private mesages has exceeded quota so I cannot send answer
    Oopss.. Sorry for that... now it's having more space since I deleted the old messages

  11. #11
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often 365, but still time to time use also older versions
    Posts
    8,350

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Dependant DV List Includes an Extra Row that should NOT be Included
    By BeachRock in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-13-2013, 03:44 PM
  2. List of standard variables already included in excel
    By tmox1130 in forum Excel General
    Replies: 1
    Last Post: 11-30-2012, 02:10 PM
  3. Replies: 0
    Last Post: 12-14-2011, 09:29 AM
  4. Countif included within a list?
    By di22y in forum Excel General
    Replies: 3
    Last Post: 09-04-2009, 08:44 AM
  5. Randomly Pair Names in a list
    By Gos-C in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-29-2006, 01:14 PM

Bookmarks

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