+ Reply to Thread
Results 1 to 18 of 18

Finding a unique combination of values

  1. #1
    Registered User
    Join Date
    05-24-2013
    Location
    rome
    MS-Off Ver
    Excel 2007
    Posts
    9

    Finding a unique combination of values

    Hi everyone,
    my problem is this:
    I got some sheets with some numbers in each row and I want to find the right combination between them which use all of the numbers only one time each. I give you an example just to clarify:

    Sheet 1
    1,2,3,4
    1,2,3,5
    1,2,4,6
    1,3,4,5

    Sheet 2
    1,2,5,6
    5,6,7
    6,4,7,8
    1,6,7,8
    2,5,6,8

    Sheet 3
    1,9,10,11
    8,9,10,11
    8,9,10,11,12


    and I want in the result sheet

    Sheet 1 - 1,2,3,4
    Sheet 2 - 5,6,7
    Sheet 3 - 8,9,10,11,12

    that are one row from each sheet that use one number one time each and uses all the 12 numbers.
    The constrict are that not in each row we have the same number of "numbers" and not in each sheet we have the same number of used rows.


    Any help will be VERY appreciated!!

  2. #2
    Forum Expert millz's Avatar
    Join Date
    08-14-2013
    Location
    Singapore
    MS-Off Ver
    Excel, Access 2016
    Posts
    1,694

    Re: Finding a unique combination of values

    Hi bassiere, it's a little unclear about you want.
    1)
    Sheet 1
    1,2,3,4 <- are these values all in 1 cell? or does the commas represent a column separator?
    1,2,3,5
    1,2,4,6
    1,3,4,5

    2)
    at a glance, I can see there are 6 unique numbers 1 - 6 in Sheet 1, but your expected results to be shown is Sheet 1 - 1,2,3,4. How should that be determined?

    3)
    in Sheet 2, I can see there is 1, 2, 4, 5, 6, 7, 8.
    1, 2, 4, 8 are omitted from the expected results. Again, how should that be determined? I can guess 1, 2, 4 already exists in Sheet 1, and is to be excluded, but 5 and 6 also exists in Sheet 1, but not pulled to results for Sheet 1, and is shown in results for Sheet 2 instead.
    Last edited by millz; 08-22-2013 at 09:54 AM.

  3. #3
    Registered User
    Join Date
    05-24-2013
    Location
    rome
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Finding a unique combination of values

    hi millz,
    thanks for your reply

    1) yes the values are wrote in one cell separated with commas
    2) the values come froma a macro that I found that find each combination of a given list of number to match a sum, each sheet separate the list of sums I have. Obviosly the numbers I gave you are not the sums but are placeholder for the ivoices codes that are placed in each sheet.

    to clarify a give you an example of how the macro work untill now:

    Please Login or Register  to view this content.
    the result of the macro I want to write should be:

    Please Login or Register  to view this content.
    hope it clarify it.

  4. #4
    Registered User
    Join Date
    08-15-2013
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: Finding a unique combination of values

    You could use a series of NESTED for next loops

    For example

    Please Login or Register  to view this content.
    Thoughts?

  5. #5
    Registered User
    Join Date
    05-24-2013
    Location
    rome
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Finding a unique combination of values

    Hi tung,
    is not what I'm looking for but could be a starting point. I'm trying to find a way to make a macro that find (in short words) the row in each sheet that has codes that are not used in the other sheets, but the codes are not int any order, they are strings that refer to payments.

  6. #6
    Forum Expert millz's Avatar
    Join Date
    08-14-2013
    Location
    Singapore
    MS-Off Ver
    Excel, Access 2016
    Posts
    1,694

    Re: Finding a unique combination of values

    Is it possible to post a sample workbook (with expected results layout as well)? with sensitive information removed/replaced. E.g. Invoice number 10012412 -> inv111. I sort of figured out the objective now, need a clearer picture on how the worksheetsare displayed.

  7. #7
    Registered User
    Join Date
    05-24-2013
    Location
    rome
    MS-Off Ver
    Excel 2007
    Posts
    9

    Wink Re: Finding a unique combination of values

    hi,I cannot upload files.
    I will paste here the sheets divided:
    "DATA"
    Please Login or Register  to view this content.
    "0000000001 € 11049,71"
    Please Login or Register  to view this content.
    "0000000002 € 13507,87"
    Please Login or Register  to view this content.
    "0000000003 € 11235,48"
    Please Login or Register  to view this content.
    "0000000004 € 5886,64"
    Please Login or Register  to view this content.
    "Cobinations"
    Please Login or Register  to view this content.

  8. #8
    Forum Expert millz's Avatar
    Join Date
    08-14-2013
    Location
    Singapore
    MS-Off Ver
    Excel, Access 2016
    Posts
    1,694

    Re: Finding a unique combination of values

    Hi bassiere,
    I spent quite some time playing around. First I thought maybe I could sum up all the amount per code and get the one that matches the payment amount, but I found out that actually all those rows of 'code' sum up to the same amount.
    So yeah, we must move on to find the unique combination.

    But, from my code, it seems there are more than 1 'unique' combination.
    Your result:
    Sheet1 - 6, 7, 18
    Sheet2 - 12, 14, 16, 21, 22, 23
    Sheet3 - 19, 20, 25, 26, 27
    Sheet4 - 1, 2, 3, 4, 5, 8

    My Results:
    Sheet1 - 18, 25, 26
    Sheet2 - 12, 14, 16, 22, 23, 27
    Sheet3 - 6, 7, 19, 20, 21
    Sheet4 - 1, 2, 3, 4, 5, 8

    So question now. Any way to determine the correct unique combination?

  9. #9
    Registered User
    Join Date
    05-24-2013
    Location
    rome
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Finding a unique combination of values

    well,
    for my company purposes I just have to present both and let then choose wich one. So to my POW there's no need to determine wich one is correct.

  10. #10
    Forum Expert millz's Avatar
    Join Date
    08-14-2013
    Location
    Singapore
    MS-Off Ver
    Excel, Access 2016
    Posts
    1,694

    Re: Finding a unique combination of values

    Problem is, I didn't have much time last night when I was playing with it, so I stopped the code after finding the first 'unique' combination. I suspect there would be at least 10 more unique combinations. So, if that's the case, or let's say if there are just 2 unique combinations, how should they be shown?

  11. #11
    Registered User
    Join Date
    05-24-2013
    Location
    rome
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Finding a unique combination of values

    In the way I shown you on the "Cobinations" tabs. Can be one per sheet with counter on the name (EG Comb_1, Comb_2... etc), or the way down on the same sheet separated in some way (maybe a line of "-")... is the same for their point of view, the ask just for this way of presentation for each combination, then they don't care how the various combination are provided.

  12. #12
    Forum Expert millz's Avatar
    Join Date
    08-14-2013
    Location
    Singapore
    MS-Off Ver
    Excel, Access 2016
    Posts
    1,694

    Re: Finding a unique combination of values

    I've made it to display them all in a worksheet, with 5 rows separating each unique combination. Since there are so many combinations to check through, it takes my computer about 10 seconds to complete the macro, and depending on yours, it may take longer.

    Please Login or Register  to view this content.
    Last edited by millz; 08-26-2013 at 07:30 AM. Reason: add some explanations/comments to the code

  13. #13
    Registered User
    Join Date
    05-24-2013
    Location
    rome
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Finding a unique combination of values

    Hi Millz and thank you!! The code works perfectly but there's a way to avoid the combinations where's an "N/A" for a payment? I mean, in each combination every payment should be filled.
    And also, can you explain me your code? I'm willing to raise my developing knowledge!

  14. #14
    Forum Expert millz's Avatar
    Join Date
    08-14-2013
    Location
    Singapore
    MS-Off Ver
    Excel, Access 2016
    Posts
    1,694

    Re: Finding a unique combination of values

    I don't understand what do you mean by N/A for a payment. I've worked the code based on the sample data in different worksheets you have given me above. The only thing that would give a N/A that I can think of, is the VLOOKUP formula, which is the Invoice amounts. Would return N/A if the Invoice ref. is not found within the list in "DATA" worksheet.

    I don't have much time to do the explanation of the code now, will post again with some comments added to the code when I get back home.

  15. #15
    Registered User
    Join Date
    05-24-2013
    Location
    rome
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Finding a unique combination of values

    Sorry for the previous message. The N/A was due to some crap data that were on the DATA sheet. My fault.

  16. #16
    Forum Expert millz's Avatar
    Join Date
    08-14-2013
    Location
    Singapore
    MS-Off Ver
    Excel, Access 2016
    Posts
    1,694

    Re: Finding a unique combination of values

    Hi bassiere, you can take a look at the post above, I've edited to add in comments/explanations. Also, I found that I made a mistake in the code, it was missing the first row of the first worksheet in the first loop.

    Mistake here:
    Please Login or Register  to view this content.

  17. #17
    Registered User
    Join Date
    05-24-2013
    Location
    rome
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Finding a unique combination of values

    Hi millz and thanks again,
    I've found a combination that is not working with your code.
    I got a combination that have 1 combination in the first page, 3 in the second, 1 in the third and fourth and 2 in the fifth.
    It's like that if the first page having only one row is skipping every time and for this reason it gives out 0 combination found.
    here's the combinations:

    Please Login or Register  to view this content.
    € 4.562,32 0000000001
    Please Login or Register  to view this content.
    € 3.935,11 0000000002
    Please Login or Register  to view this content.
    € 381,30 0000000003
    Please Login or Register  to view this content.
    € 3.616,61 0000000004
    Please Login or Register  to view this content.
    € 389,25 0000000005
    Please Login or Register  to view this content.
    you got any suggestion to fix this?

  18. #18
    Forum Expert millz's Avatar
    Join Date
    08-14-2013
    Location
    Singapore
    MS-Off Ver
    Excel, Access 2016
    Posts
    1,694

    Re: Finding a unique combination of values

    Ahhh, sorry I did not foresee that happening - having only 1 row per sheet. Try this revised code.
    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. Macro for the fetching values on basic of unique combination of more than one column value
    By manish_nichante in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 06-06-2013, 09:35 PM
  2. [SOLVED] Finding largest combination of cell values
    By omni72 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-22-2012, 04:13 PM
  3. Finding Unique Values
    By davidimurray in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-09-2007, 05:02 AM
  4. Finding all unique values
    By Shawn in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-30-2005, 09:05 AM
  5. Finding unique values with Criterias
    By dolpphinv4 in forum Excel General
    Replies: 1
    Last Post: 04-13-2005, 10:06 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