1. 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?

2. 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. [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. [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. [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. [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. 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. [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. 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. [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
•