I need a formula to find the actual combinations in a 2-column, 50-row range.
Both columns are constrained to 10 text characters if that makes it easier.
e.g.
Data: (may be in random order)
A A
A B
B A
B B
B C
B C
B B
J A
J A
Looking for this result:
A A
A B
B A
B B
B C
J A
Thanks
Try this:
With your list is in A1:B100, with A1 and B1 as the Heading: eg MyCol_A,
MyCol_B, respectively.
D1: MyCol_A (the same col heading as A1)
E1: MyCol_B (the same col heading as B1)
Select your list (A1:B100)
lt;Datagt;lt;Filtergt;lt;Advanced Filtergt;
List Range: (already selected $A$1:$B$100)
Check: Copy to another location
Copy to: $D$1:$E$1 (which contains the col headings)
Check: Unique Records
Click the [OK] button
That will create a list, under D1:E1, of the unique combinations in A:B
Is that something you can work with?
***********
Regards,
Ron
XL2002, WinXPquot;Steve-in-austinquot; wrote:
gt; I need a formula to find the actual combinations in a 2-column, 50-row range.
gt; Both columns are constrained to 10 text characters if that makes it easier.
gt; e.g.
gt; Data: (may be in random order)
gt; A A
gt; A B
gt; B A
gt; B B
gt; B C
gt; B C
gt; B B
gt; J A
gt; J A
gt;
gt; Looking for this result:
gt; A A
gt; A B
gt; B A
gt; B B
gt; B C
gt; J A
gt;
gt; Thanks
I need to do this automatically if possible; the results will be linked to
another worksheet.
quot;Ron Coderrequot; wrote:
gt; Try this:
gt;
gt; With your list is in A1:B100, with A1 and B1 as the Heading: eg MyCol_A,
gt; MyCol_B, respectively.
gt;
gt; D1: MyCol_A (the same col heading as A1)
gt; E1: MyCol_B (the same col heading as B1)
gt;
gt; Select your list (A1:B100)
gt; lt;Datagt;lt;Filtergt;lt;Advanced Filtergt;
gt; List Range: (already selected $A$1:$B$100)
gt; Check: Copy to another location
gt; Copy to: $D$1:$E$1 (which contains the col headings)
gt; Check: Unique Records
gt; Click the [OK] button
gt;
gt; That will create a list, under D1:E1, of the unique combinations in A:B
gt;
gt; Is that something you can work with?
gt; ***********
gt; Regards,
gt; Ron
gt;
gt; XL2002, WinXP
gt;
gt;
gt; quot;Steve-in-austinquot; wrote:
gt;
gt; gt; I need a formula to find the actual combinations in a 2-column, 50-row range.
gt; gt; Both columns are constrained to 10 text characters if that makes it easier.
gt; gt; e.g.
gt; gt; Data: (may be in random order)
gt; gt; A A
gt; gt; A B
gt; gt; B A
gt; gt; B B
gt; gt; B C
gt; gt; B C
gt; gt; B B
gt; gt; J A
gt; gt; J A
gt; gt;
gt; gt; Looking for this result:
gt; gt; A A
gt; gt; A B
gt; gt; B A
gt; gt; B B
gt; gt; B C
gt; gt; J A
gt; gt;
gt; gt; Thanks
I forgot to mention, without a macro. Thx
quot;Ron Coderrequot; wrote:
gt; Try this:
gt;
gt; With your list is in A1:B100, with A1 and B1 as the Heading: eg MyCol_A,
gt; MyCol_B, respectively.
gt;
gt; D1: MyCol_A (the same col heading as A1)
gt; E1: MyCol_B (the same col heading as B1)
gt;
gt; Select your list (A1:B100)
gt; lt;Datagt;lt;Filtergt;lt;Advanced Filtergt;
gt; List Range: (already selected $A$1:$B$100)
gt; Check: Copy to another location
gt; Copy to: $D$1:$E$1 (which contains the col headings)
gt; Check: Unique Records
gt; Click the [OK] button
gt;
gt; That will create a list, under D1:E1, of the unique combinations in A:B
gt;
gt; Is that something you can work with?
gt; ***********
gt; Regards,
gt; Ron
gt;
gt; XL2002, WinXP
gt;
gt;
gt; quot;Steve-in-austinquot; wrote:
gt;
gt; gt; I need a formula to find the actual combinations in a 2-column, 50-row range.
gt; gt; Both columns are constrained to 10 text characters if that makes it easier.
gt; gt; e.g.
gt; gt; Data: (may be in random order)
gt; gt; A A
gt; gt; A B
gt; gt; B A
gt; gt; B B
gt; gt; B C
gt; gt; B C
gt; gt; B B
gt; gt; J A
gt; gt; J A
gt; gt;
gt; gt; Looking for this result:
gt; gt; A A
gt; gt; A B
gt; gt; B A
gt; gt; B B
gt; gt; B C
gt; gt; J A
gt; gt;
gt; gt; Thanks
Ok...see if this ARRAY FORMULA* works for you....
Same rules as my previous post:
With your list is in A1:B100, with A1 and B1 as the Heading: eg MyCol_A,
MyCol_B, respectively.
D1: Unique Combos
D2:
=IF(SUMPRODUCT(($A$2:$A$10amp;$B$2:$B$10lt;gt;quot;quot;)*ISERROR (MATCH($A$2:$A$10amp;$B$2:$B$10,$D$11,0)))lt;gt;0,INDEX ($A$2:$A$10amp;$B$2:$B$10,MATCH(TRUE,ISERROR(IF(ISBLA NK($A$2:$A$10),FALSE,MATCH($A$2:$A$10amp;$B$2:$B$10,$ D$1:$D1,0))),0),1),quot;quot;)
*Note: For that array formula, hold down [Ctrl] and [Shift] when you press
[Enter], instead of just pressing [Enter].
Copy from D2 into D3 and down as far as you need.
Is that something you can work with?
***********
Regards,
Ron
XL2002, WinXPquot;Steve-in-austinquot; wrote:
gt; I forgot to mention, without a macro. Thx
gt;
gt; quot;Ron Coderrequot; wrote:
gt;
gt; gt; Try this:
gt; gt;
gt; gt; With your list is in A1:B100, with A1 and B1 as the Heading: eg MyCol_A,
gt; gt; MyCol_B, respectively.
gt; gt;
gt; gt; D1: MyCol_A (the same col heading as A1)
gt; gt; E1: MyCol_B (the same col heading as B1)
gt; gt;
gt; gt; Select your list (A1:B100)
gt; gt; lt;Datagt;lt;Filtergt;lt;Advanced Filtergt;
gt; gt; List Range: (already selected $A$1:$B$100)
gt; gt; Check: Copy to another location
gt; gt; Copy to: $D$1:$E$1 (which contains the col headings)
gt; gt; Check: Unique Records
gt; gt; Click the [OK] button
gt; gt;
gt; gt; That will create a list, under D1:E1, of the unique combinations in A:B
gt; gt;
gt; gt; Is that something you can work with?
gt; gt; ***********
gt; gt; Regards,
gt; gt; Ron
gt; gt;
gt; gt; XL2002, WinXP
gt; gt;
gt; gt;
gt; gt; quot;Steve-in-austinquot; wrote:
gt; gt;
gt; gt; gt; I need a formula to find the actual combinations in a 2-column, 50-row range.
gt; gt; gt; Both columns are constrained to 10 text characters if that makes it easier.
gt; gt; gt; e.g.
gt; gt; gt; Data: (may be in random order)
gt; gt; gt; A A
gt; gt; gt; A B
gt; gt; gt; B A
gt; gt; gt; B B
gt; gt; gt; B C
gt; gt; gt; B C
gt; gt; gt; B B
gt; gt; gt; J A
gt; gt; gt; J A
gt; gt; gt;
gt; gt; gt; Looking for this result:
gt; gt; gt; A A
gt; gt; gt; A B
gt; gt; gt; B A
gt; gt; gt; B B
gt; gt; gt; B C
gt; gt; gt; J A
gt; gt; gt;
gt; gt; gt; Thanks
This is pretty spiffy--maybe better! I would add that the various column
ranges i.e. B2:B10 etc, must be extended to the bottom of the array.
quot;Ron Coderrequot; wrote:
gt; Ok...see if this ARRAY FORMULA* works for you....
gt;
gt; Same rules as my previous post:
gt; With your list is in A1:B100, with A1 and B1 as the Heading: eg MyCol_A,
gt; MyCol_B, respectively.
gt;
gt; D1: Unique Combos
gt; D2:
gt; =IF(SUMPRODUCT(($A$2:$A$10amp;$B$2:$B$10lt;gt;quot;quot;)*ISERROR (MATCH($A$2:$A$10amp;$B$2:$B$10,$D$11,0)))lt;gt;0,INDEX ($A$2:$A$10amp;$B$2:$B$10,MATCH(TRUE,ISERROR(IF(ISBLA NK($A$2:$A$10),FALSE,MATCH($A$2:$A$10amp;$B$2:$B$10,$ D$1:$D1,0))),0),1),quot;quot;)
gt;
gt; *Note: For that array formula, hold down [Ctrl] and [Shift] when you press
gt; [Enter], instead of just pressing [Enter].
gt;
gt; Copy from D2 into D3 and down as far as you need.
gt;
gt; Is that something you can work with?
gt; ***********
gt; Regards,
gt; Ron
gt;
gt; XL2002, WinXP
gt;
gt;
gt; quot;Steve-in-austinquot; wrote:
gt;
gt; gt; I forgot to mention, without a macro. Thx
gt; gt;
gt; gt; quot;Ron Coderrequot; wrote:
gt; gt;
gt; gt; gt; Try this:
gt; gt; gt;
gt; gt; gt; With your list is in A1:B100, with A1 and B1 as the Heading: eg MyCol_A,
gt; gt; gt; MyCol_B, respectively.
gt; gt; gt;
gt; gt; gt; D1: MyCol_A (the same col heading as A1)
gt; gt; gt; E1: MyCol_B (the same col heading as B1)
gt; gt; gt;
gt; gt; gt; Select your list (A1:B100)
gt; gt; gt; lt;Datagt;lt;Filtergt;lt;Advanced Filtergt;
gt; gt; gt; List Range: (already selected $A$1:$B$100)
gt; gt; gt; Check: Copy to another location
gt; gt; gt; Copy to: $D$1:$E$1 (which contains the col headings)
gt; gt; gt; Check: Unique Records
gt; gt; gt; Click the [OK] button
gt; gt; gt;
gt; gt; gt; That will create a list, under D1:E1, of the unique combinations in A:B
gt; gt; gt;
gt; gt; gt; Is that something you can work with?
gt; gt; gt; ***********
gt; gt; gt; Regards,
gt; gt; gt; Ron
gt; gt; gt;
gt; gt; gt; XL2002, WinXP
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; quot;Steve-in-austinquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; I need a formula to find the actual combinations in a 2-column, 50-row range.
gt; gt; gt; gt; Both columns are constrained to 10 text characters if that makes it easier.
gt; gt; gt; gt; e.g.
gt; gt; gt; gt; Data: (may be in random order)
gt; gt; gt; gt; A A
gt; gt; gt; gt; A B
gt; gt; gt; gt; B A
gt; gt; gt; gt; B B
gt; gt; gt; gt; B C
gt; gt; gt; gt; B C
gt; gt; gt; gt; B B
gt; gt; gt; gt; J A
gt; gt; gt; gt; J A
gt; gt; gt; gt;
gt; gt; gt; gt; Looking for this result:
gt; gt; gt; gt; A A
gt; gt; gt; gt; A B
gt; gt; gt; gt; B A
gt; gt; gt; gt; B B
gt; gt; gt; gt; B C
gt; gt; gt; gt; J A
gt; gt; gt; gt;
gt; gt; gt; gt; Thanks
Thanks for the feedbackgt;I would add that the various column ranges i.e. B2:B10 etc, must be extended to gt;the bottom of the array.
I played with a shrinky-dink version of the formula on my PC and forgot to
extend the ranges to row 100 when I posted to the newsgroup.....I was sure
you'd figure that out right away, though...and you did!.
***********
Regards,
Ron
XL2002, WinXPquot;Steve-in-austinquot; wrote:
gt; This is pretty spiffy--maybe better! I would add that the various column
gt; ranges i.e. B2:B10 etc, must be extended to the bottom of the array.
gt;
gt; quot;Ron Coderrequot; wrote:
gt;
gt; gt; Ok...see if this ARRAY FORMULA* works for you....
gt; gt;
gt; gt; Same rules as my previous post:
gt; gt; With your list is in A1:B100, with A1 and B1 as the Heading: eg MyCol_A,
gt; gt; MyCol_B, respectively.
gt; gt;
gt; gt; D1: Unique Combos
gt; gt; D2:
gt; gt; =IF(SUMPRODUCT(($A$2:$A$10amp;$B$2:$B$10lt;gt;quot;quot;)*ISERROR (MATCH($A$2:$A$10amp;$B$2:$B$10,$D$11,0)))lt;gt;0,INDEX ($A$2:$A$10amp;$B$2:$B$10,MATCH(TRUE,ISERROR(IF(ISBLA NK($A$2:$A$10),FALSE,MATCH($A$2:$A$10amp;$B$2:$B$10,$ D$1:$D1,0))),0),1),quot;quot;)
gt; gt;
gt; gt; *Note: For that array formula, hold down [Ctrl] and [Shift] when you press
gt; gt; [Enter], instead of just pressing [Enter].
gt; gt;
gt; gt; Copy from D2 into D3 and down as far as you need.
gt; gt;
gt; gt; Is that something you can work with?
gt; gt; ***********
gt; gt; Regards,
gt; gt; Ron
gt; gt;
gt; gt; XL2002, WinXP
gt; gt;
gt; gt;
gt; gt; quot;Steve-in-austinquot; wrote:
gt; gt;
gt; gt; gt; I forgot to mention, without a macro. Thx
gt; gt; gt;
gt; gt; gt; quot;Ron Coderrequot; wrote:
gt; gt; gt;
gt; gt; gt; gt; Try this:
gt; gt; gt; gt;
gt; gt; gt; gt; With your list is in A1:B100, with A1 and B1 as the Heading: eg MyCol_A,
gt; gt; gt; gt; MyCol_B, respectively.
gt; gt; gt; gt;
gt; gt; gt; gt; D1: MyCol_A (the same col heading as A1)
gt; gt; gt; gt; E1: MyCol_B (the same col heading as B1)
gt; gt; gt; gt;
gt; gt; gt; gt; Select your list (A1:B100)
gt; gt; gt; gt; lt;Datagt;lt;Filtergt;lt;Advanced Filtergt;
gt; gt; gt; gt; List Range: (already selected $A$1:$B$100)
gt; gt; gt; gt; Check: Copy to another location
gt; gt; gt; gt; Copy to: $D$1:$E$1 (which contains the col headings)
gt; gt; gt; gt; Check: Unique Records
gt; gt; gt; gt; Click the [OK] button
gt; gt; gt; gt;
gt; gt; gt; gt; That will create a list, under D1:E1, of the unique combinations in A:B
gt; gt; gt; gt;
gt; gt; gt; gt; Is that something you can work with?
gt; gt; gt; gt; ***********
gt; gt; gt; gt; Regards,
gt; gt; gt; gt; Ron
gt; gt; gt; gt;
gt; gt; gt; gt; XL2002, WinXP
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; quot;Steve-in-austinquot; wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; gt; I need a formula to find the actual combinations in a 2-column, 50-row range.
gt; gt; gt; gt; gt; Both columns are constrained to 10 text characters if that makes it easier.
gt; gt; gt; gt; gt; e.g.
gt; gt; gt; gt; gt; Data: (may be in random order)
gt; gt; gt; gt; gt; A A
gt; gt; gt; gt; gt; A B
gt; gt; gt; gt; gt; B A
gt; gt; gt; gt; gt; B B
gt; gt; gt; gt; gt; B C
gt; gt; gt; gt; gt; B C
gt; gt; gt; gt; gt; B B
gt; gt; gt; gt; gt; J A
gt; gt; gt; gt; gt; J A
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Looking for this result:
gt; gt; gt; gt; gt; A A
gt; gt; gt; gt; gt; A B
gt; gt; gt; gt; gt; B A
gt; gt; gt; gt; gt; B B
gt; gt; gt; gt; gt; B C
gt; gt; gt; gt; gt; J A
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Thanks
留言列表
