Unique couples of entries
 

I have the following model:

............A........B
1.........1........blank
2.........2........a
3.........3........blank
4.........1........1
5.........2........2
6.........3........blank
7.........1........1
8.........2........a
9.........3........blank


I need to show, in D1:E1 and below, the unique combinations (couples)
of entries.

In the exemple above:

............D........E
1.........1........blank
2.........2........a
3.........1........1
4.........2........2
5.........3........blank

Any ideas?

Unique couples of entries
 

Add headers to your data if you don't have them already.

Select A1:Bxxx
Data|Filter|advanced filter
Check copy to another location
Type D1 in that "Copy to:" box
Check "Unique records only"

remove row 1 if you don't want the headers

And sort the data in D1:E### if you want.


vsoler wrote:

I have the following model:

...........A........B
1.........1........blank
2.........2........a
3.........3........blank
4.........1........1
5.........2........2
6.........3........blank
7.........1........1
8.........2........a
9.........3........blank

I need to show, in D1:E1 and below, the unique combinations (couples)
of entries.

In the exemple above:

...........D........E
1.........1........blank
2.........2........a
3.........1........1
4.........2........2
5.........3........blank

Any ideas?

--

Dave Peterson

Unique couples of entries
 

Leave row row #1 for labels

In column C enter:
=A2 & B2 and copy down

In column D enter:
=COUNTIF($C$2:C2,C2)-1 and copy down

v1 v2 v3 v4
1 blank 1blank 0
2 a 2a 0
3 blank 3blank 0
1 1 11 0
2 2 22 0
3 blank 3blank 1
1 1 11 1
2 a 2a 1
3 blank 3blank 2


Then switch on AutoFilter on column D to show only 0:

v1 v2 v3 v4
1 blank 1blank 0
2 a 2a 0
3 blank 3blank 0
1 1 11 0
2 2 22 0

--
Gary''s Student - gsnu200725


"vsoler" wrote:

I have the following model:

............A........B
1.........1........blank
2.........2........a
3.........3........blank
4.........1........1
5.........2........2
6.........3........blank
7.........1........1
8.........2........a
9.........3........blank


I need to show, in D1:E1 and below, the unique combinations (couples)
of entries.

In the exemple above:

............D........E
1.........1........blank
2.........2........a
3.........1........1
4.........2........2
5.........3........blank

Any ideas?

Unique couples of entries
 

Try this:

rng1 is a define name range in column A
rng2 is a define name range in column B

D1
=IF(ISERR(SMALL(IF(FREQUENCY(MATCH(rng1&rng2,rng1& rng2,0),MATCH(rng1&rng2,rng1&rng2,0))0,ROW(INDIRE CT("1:"&ROWS(rng1)))),ROWS($1:1))),"",INDEX(rng1,S MALL(IF(FREQUENCY(MATCH(rng1&rng2,rng1&rng2,0),MAT CH(rng1&rng2,rng1&rng2,0))0,ROW(INDIRECT("1:"&ROW S(rng1)))),ROWS($1:1))))

ctrl+shift+enter, not just enter
copy down

E1
=IF(D1="","",INDEX(rng2&"",SMALL(IF(FREQUENCY(MATC H(rng1&rng2,rng1&rng2,0),MATCH(rng1&rng2,rng1&rng2 ,0))0,ROW(INDIRECT("1:"&ROWS(rng1)))),ROWS($1:1)) ))

ctrl+shift+enter, not just enter
copy down



"vsoler" wrote:

I have the following model:

............A........B
1.........1........blank
2.........2........a
3.........3........blank
4.........1........1
5.........2........2
6.........3........blank
7.........1........1
8.........2........a
9.........3........blank


I need to show, in D1:E1 and below, the unique combinations (couples)
of entries.

In the exemple above:

............D........E
1.........1........blank
2.........2........a
3.........1........1
4.........2........2
5.........3........blank

Any ideas?