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,COUNTIF( 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

oups.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.