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)
<Data<Filter<Advanced Filter
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, WinXP


"Steve-in-austin" wrote:

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

I need to do this automatically if possible; the results will be linked to
another worksheet.

"Ron Coderre" wrote:

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)
<Data<Filter<Advanced Filter
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, WinXP


"Steve-in-austin" wrote:

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

I forgot to mention, without a macro. Thx

"Ron Coderre" wrote:

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)
<Data<Filter<Advanced Filter
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, WinXP


"Steve-in-austin" wrote:

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

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$10&$B$2:$B$10<"")*ISERROR (MATCH($A$2:$A$10&$B$2:$B$10,$D$1:D1,0)))<0,INDEX ($A$2:$A$10&$B$2:$B$10,MATCH(TRUE,ISERROR(IF(ISBLA NK($A$2:$A$10),FALSE,MATCH($A$2:$A$10&$B$2:$B$10,$ D$1:$D1,0))),0),1),"")

*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, WinXP


"Steve-in-austin" wrote:

I forgot to mention, without a macro. Thx

"Ron Coderre" wrote:

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)
<Data<Filter<Advanced Filter
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, WinXP


"Steve-in-austin" wrote:

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

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.

"Ron Coderre" wrote:

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$10&$B$2:$B$10<"")*ISERROR (MATCH($A$2:$A$10&$B$2:$B$10,$D$1:D1,0)))<0,INDEX ($A$2:$A$10&$B$2:$B$10,MATCH(TRUE,ISERROR(IF(ISBLA NK($A$2:$A$10),FALSE,MATCH($A$2:$A$10&$B$2:$B$10,$ D$1:$D1,0))),0),1),"")

*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, WinXP


"Steve-in-austin" wrote:

I forgot to mention, without a macro. Thx

"Ron Coderre" wrote:

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)
<Data<Filter<Advanced Filter
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, WinXP


"Steve-in-austin" wrote:

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

Thanks for the feedback


I would add that the various column ranges i.e. B2:B10 etc, must be extended to 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, WinXP


"Steve-in-austin" wrote:

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.

"Ron Coderre" wrote:

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$10&$B$2:$B$10<"")*ISERROR (MATCH($A$2:$A$10&$B$2:$B$10,$D$1:D1,0)))<0,INDEX ($A$2:$A$10&$B$2:$B$10,MATCH(TRUE,ISERROR(IF(ISBLA NK($A$2:$A$10),FALSE,MATCH($A$2:$A$10&$B$2:$B$10,$ D$1:$D1,0))),0),1),"")

*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, WinXP


"Steve-in-austin" wrote:

I forgot to mention, without a macro. Thx

"Ron Coderre" wrote:

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)
<Data<Filter<Advanced Filter
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, WinXP


"Steve-in-austin" wrote:

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