+ Reply to Thread
Results 1 to 15 of 15

Matching unique column combinations of data with VBA

  1. #1
    Registered User
    Join Date
    08-30-2014
    Location
    Lincoln, England
    MS-Off Ver
    Office 365
    Posts
    74

    Matching unique column combinations of data with VBA

    In an array, one column contains strings which are not unique - some are repeated. Similarly, another column contains repeated strings. However, any single row combination from column 1 and column 2 IS unique. I need to find this row when a VBA function is sent the two strings, one for each column. Any ideas? I'm getting desperate.
    Thank you.

  2. #2
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,677

    Re: Matching unique column combinations of data with VBA

    If you sort by col 1 and col 2, then you only need to compare adjacent rows to know if a given row is unique:

    Row\Col
    A
    B
    C
    D
    1
    Hdr1
    Hdr2
    Unique?
    2
    A A
    TRUE
    C2: =((A2<>A1)+(B2<>B1))*((A2<>A3)+(B2<>B3)) > 0
    3
    A C
    FALSE
    4
    A C
    FALSE
    5
    A C
    FALSE
    6
    A D
    FALSE
    7
    A D
    FALSE
    8
    A D
    FALSE
    9
    A E
    FALSE
    10
    A E
    FALSE
    11
    B A
    FALSE
    12
    B A
    FALSE
    13
    B B
    TRUE
    14
    B C
    TRUE
    15
    B D
    FALSE
    16
    B D
    FALSE
    Last edited by shg; 05-02-2015 at 01:49 PM.
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    08-30-2014
    Location
    Lincoln, England
    MS-Off Ver
    Office 365
    Posts
    74

    Re: Matching unique column combinations of data with VBA

    Many thanks for the reply - any chance of starting me off with some code or a little more detail? Still not sure how to approach it.

  4. #4
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,677

    Re: Matching unique column combinations of data with VBA

    The posted example?

  5. #5
    Registered User
    Join Date
    08-30-2014
    Location
    Lincoln, England
    MS-Off Ver
    Office 365
    Posts
    74

    Re: Matching unique column combinations of data with VBA

    That should help, thank you so much. I need to construct this as a vba function so I will try it out and post back soon...

  6. #6
    Registered User
    Join Date
    08-30-2014
    Location
    Lincoln, England
    MS-Off Ver
    Office 365
    Posts
    74

    Re: Matching unique column combinations of data with VBA

    SHG, thank you again. I have to leave this for a few hours but PLEASE could you keep the thread open for a while? I'll be working at it later tonight (UK time).

  7. #7
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,677

    Re: Matching unique column combinations of data with VBA

    You could also just select both columns and do Data > Remove Duplicates.

  8. #8
    Registered User
    Join Date
    08-30-2014
    Location
    Lincoln, England
    MS-Off Ver
    Office 365
    Posts
    74

    Re: Matching unique column combinations of data with VBA

    I may have misunderstood, but I don't think I can use the formula or options given. The data in the two columns is different and can never match.
    The pairing of data is unique, but not a match. A function should send two parameters (strings) which must match two cells in the same row.
    The only way I can see of doing this is by looping down each column until the parameters match the cells, but I thought there might be a more elegant method.
    I have to leave this for tonight as I'm beyond thinking clearly but I may try tomorrow, or use the paid service if I can't sort it.
    Goodnight from the Great State of Confusion, UK.

  9. #9
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,677

    Re: Matching unique column combinations of data with VBA

    Paying for a solution isn't going to avoid the necessity of explaining clearly what you're trying to do. Maybe this:

    Row\Col
    A
    B
    C
    D
    E
    F
    G
    1
    Bob Ben Bob Joe
    5
    F1: =MATCH(1, INDEX((A1:A10=D1)*(B1:B10=E1), 0), 0)
    2
    Bob Anne
    3
    Bob Bill
    4
    Ben Joe
    5
    Bob Joe
    6
    Bob Beth
    Last edited by shg; 05-02-2015 at 07:39 PM.

  10. #10
    Registered User
    Join Date
    08-30-2014
    Location
    Lincoln, England
    MS-Off Ver
    Office 365
    Posts
    74

    Re: Matching unique column combinations of data with VBA

    The spreadsheet lists book titles in one column and formats in another. There are three formats, so a title appears three times, each with a different format.
    Here is a screen shot:
    Screenshot 2015-05-03 07.56.25.jpg
    I tried converting the last formula you gave me to vba code but still got the same error message 1004, "Unable to get the index property..."
    This is the test line I used:
    Please Login or Register  to view this content.
    So I am feeding through the title and want the function to return the row number where, in this case, it is a "Paperback", e.g Robinson Crusoe would return 5.
    Thanks!

  11. #11
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,677

    Re: Matching unique column combinations of data with VBA

    Time to post a workbook.

  12. #12
    Registered User
    Join Date
    08-30-2014
    Location
    Lincoln, England
    MS-Off Ver
    Office 365
    Posts
    74

    Re: Matching unique column combinations of data with VBA

    It's 9 years of work, overly complicated and too big to attach. I tried to distill the problem down previously and I have a clumsy looping function which works, but I know there's something more elegant out there. If you see it, let me know... if not,thanks for trying, and apologies for not explaining it clearly first time.

  13. #13
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,677

    Re: Matching unique column combinations of data with VBA

    Row\Col
    B
    C
    D
    E
    F
    G
    H
    I
    2
    Title
    Media
    Title
    Media
    Row
    3
    Alien DVD Alien Hardcover
    4
    H3: =TheRow($B$2:$D$8, "Title", F3, "Media", G3)
    4
    Alien Hardcover Aliens DVD
    6
    5
    Alien Paperback Aliens CD
    #VALUE!
    6
    Aliens DVD
    7
    Aliens Hardcover
    8
    Aliens Paperback


    Please Login or Register  to view this content.

  14. #14
    Registered User
    Join Date
    08-30-2014
    Location
    Lincoln, England
    MS-Off Ver
    Office 365
    Posts
    74

    Re: Matching unique column combinations of data with VBA

    Thank you so much for helping out with this problem and for being so patient. It's perfect. At least, I'm sure it will be once incorporated into the workbook. My function wasn't too far away, so that cheered me up, but this is very neat.

  15. #15
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,677

    Re: Matching unique column combinations of data with VBA

    You're welcome.

    A little clean-up:

    Please Login or Register  to view this content.

+ 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] Pull unique two column combinations and skip particular value
    By volfied in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-30-2013, 11:02 AM
  2. How to populate multiple column listbox with unique combinations only
    By shuvajit in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-06-2012, 12:37 AM
  3. Replies: 2
    Last Post: 07-25-2011, 11:47 AM
  4. Generating combinations from a column of unique numbers
    By mistermat in forum Excel General
    Replies: 1
    Last Post: 08-31-2008, 06:54 AM
  5. Finding unique combinations of data
    By LAF in forum Excel General
    Replies: 3
    Last Post: 11-07-2006, 05:39 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