Results 1 to 10 of 10

Thread: An Easy One

  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Column A of worksheet 1 contains customer names (several of which are duplicated because they have multiple contracts with multiple numbers and transactions). Column B of worksheet 1 contains contract numbers (as noted, one or more customers has more than one contract, and the same contract may be listed more than once depending on the number of transactions that occured durin the period in question). On worksheet 2, in column A, I have utilized the Morefunc function "Uniquevalues" to list the unique contract numbers. What I would like to do is "match" the customer name to the contract number in column B.
    Any ideas?
    Thanks in advance.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Something like the following formula in B2 on worksheet 2:

    =INDEX('Worksheet 1'!$A$2:$A$250,MATCH(A2,'Worksheet 1'!$B$2:$B$250,0))

    Substitute the "real" name of Worksheet 1, and adjust the ranges as needed. You can fill down this formula as far as needed.

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='782463' date='30-Jun-2009 10:57']Something like the following formula in B2 on worksheet 2:

    =INDEX('Worksheet 1'!$A$2:$A$250,MATCH(A2,'Worksheet 1'!$B$2:$B$250,0))

    Substitute the "real" name of Worksheet 1, and adjust the ranges as needed. You can fill down this formula as far as needed.[/quote]

    Thanks Hans

  4. #4
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='782463' date='30-Jun-2009 10:57']Something like the following formula in B2 on worksheet 2:

    =INDEX('Worksheet 1'!$A$2:$A$250,MATCH(A2,'Worksheet 1'!$B$2:$B$250,0))

    Substitute the "real" name of Worksheet 1, and adjust the ranges as needed. You can fill down this formula as far as needed.[/quote]

    Hans,
    Are you familiar with any function in Excel (be it a formula that is in Excel originally or as an add-in) that is equivalent to the MoreFunc "Equivalentvalues" function?
    Thanks.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='jlkirk' post='782481' date='30-Jun-2009 20:38']Are you familiar with any function in Excel (be it a formula that is in Excel originally or as an add-in) that is equivalent to the MoreFunc "Equivalentvalues" function?[/quote]
    Don't you mean the UniqueValues function? I've never heard of EquivalentValues...

  6. #6
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='782482' date='30-Jun-2009 13:50']Don't you mean the UniqueValues function? I've never heard of EquivalentValues...[/quote]

    Yes, I meant the "UniqueValues" function

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Well, Morefunc *is* an Excel add-in, so you already have such a function. If you don't want to rely on an add-in, see for example Duplicates or Unique Values.

  8. #8
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='782463' date='30-Jun-2009 10:57']Something like the following formula in B2 on worksheet 2:

    =INDEX('Worksheet 1'!$A$2:$A$250,MATCH(A2,'Worksheet 1'!$B$2:$B$250,0))

    Substitute the "real" name of Worksheet 1, and adjust the ranges as needed. You can fill down this formula as far as needed.[/quote]

    A slight variance to the original question: In column C on worksheet 1, is another data entry representing the customer type under the contract. What I would like to list on worksheet 2 in column A and B (or combined in column A) is the contract number and corresponding contract type, and in column B, the customer name as per the original query. Essentially, what I want to do is to have the UniqueValues function, or something equivalent, list the unique "combinations" of contract numbers and types. Any thoughts?

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You could create formulas in column D on Worksheet 1: in D2,

    =B2&"-"&C2

    and fill down. You can then use the UniqueValues function on Worksheet 2 to return the unique values from column D on Worksheet 1.

  10. #10
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='782506' date='30-Jun-2009 17:00']You could create formulas in column D on Worksheet 1: in D2,

    =B2&"-"&C2

    and fill down. You can then use the UniqueValues function on Worksheet 2 to return the unique values from column D on Worksheet 1.[/quote]
    Thanks, a very inciteful work-around!

Posting Permissions

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