On 17 jun, 03:58, "T. Valko" wrote:
This handles TEXT only.

A1:An = rng1
B1:Bn = rng2

Assuming you want to extract the uniques and sort ascending.

Enter this formula in D1:

=LOOKUP(2,1/(COUNTIF(rng1,"<"&rng1)=0)/ISTEXT(rng1),rng1)

Enter this array formula** in D2:

=IF(ROWS($1:2)<=SUM((rng1<"")/COUNTIF(rng1,rng1&"")),INDEX(rng1,MATCH(0,CO*UNTIF (rng1,"<"&rng1)-SUM(COUNTIF(rng1,D$1:D1)),0)),"")

Copy down until you get blanks.

Enter this formula in E1:

=IF(D1="","",INDEX(rng2,MATCH(D1,rng1,0)))

Copy down until you get blanks.

Based on your sample data the result will be:

...........D.............E
1.......cup........water
2.......light........team
3.......table.......cup
4.......water......wine
5............................

Biff

"vsoler" wrote in message

oups.com...



On 16 jun, 22:41, "T. Valko" wrote:
When you say to remove duplicate rows does that mean you want to retain
the
first instance and exclude all other instances? For example, based on
your
data you want to keep row 1 and exclude row 4. Is this possible:

...........A.............B
1........light........team
2........light........ice
3........light........water
4........light........team
5........water.....wine

Biff

"vsoler" wrote in message

groups.com...

My sheet contains in A1:B500 lots of pairs, some of which are repeated

...........A.............B
1........light........team
2........table.........cup
3........cup.........water
4.........light........team
5.........water......wine

I am interested in removing the duplicated rows (in the case above
light -- team), pushing the rest of the pairs up.

If possible, although it can complicate things, I want to sort the
pairs by column A.

Is it possible to do all this only with formulas? (I don't want to use
advanced filter)

It would help a lot.

Thank you in advance.- Ocultar texto de la cita -

- Mostrar texto de la cita -

T. Valko,

Yes, I want to retain the first instance and delete all the rest.

The example you have given is not possible.- Ocultar texto de la cita -

- Mostrar texto de la cita -

T. Valko,

I just discovered one important problem with your formula: it finds
unique texts in column A and then joins the corresponding element in
column B.

However, I am trying to find unique pairs, which is something a bit
different.

Say, for example, that my list contained

table .... cup
table.....chair

In this case, I'd like the 2 pairs to appear in the final list,
because each one is unique. I am also trying to avoid, as much as
possible, concatenating A&B in order to distingish between, say

some.......how
so..........mehow

which are 2 different combinations of elements.

I appreciate your help and effort