Working with pairs of cells

[vsoler]

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.

 

[T. Valko]

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]

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










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

 

[Teethless mama]

=IF(ISERR(SMALL(IF(FREQUENCY(MATCH(rngA&rngB,rngA&rngB,0),MATCH(rngA&rngB,rngA&rngB,0))>0,ROW(INDIRECT("1:"&ROWS(rngA)))),ROWS($1:1))),"",INDEX(rngA&"
"&rngB,SMALL(IF(FREQUENCY(MATCH(rngA&rngB,rngA&rngB,0),MATCH(rngA&rngB,rngA&rngB,0))>0,ROW(INDIRECT("1:"&ROWS(rngA)))),ROWS($1:1))))

ctrl+shift+enter, not just enter
copy down

 

[T. Valko]

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$11)),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

 

[Max]

Another tinker to try, using non-array formulas ..

Source data assumed in cols A and B, from row1 down

In C1:
=IF(COUNTA(A1:B1)<2,"",IF(SUMPRODUCT(($A$1:A1=A1)*($B$1:B1=B1))>1,"",CODE(LEFT(A1))+ROW()/10^10))

In D1:
=IF(ROW()>COUNT($C:$C),"",INDEX(A:A,MATCH(SMALL($C:$C,ROW()),$C:$C,0)))
Copy D1 to E1. Select C1:E1, copy down to cover the max expected extent of
data in cols A and B, say down to E100? Hide away col C. Cols D and E will
return the required results all neatly bunched at the top, viz. for the
posted sample source data, you'd get:

cup water
light team
table cup
water wine

 

[T. Valko]

That might be good enough but it doesn't do a real sort.

Biff

 

[vsoler]

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$11)),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









- Mostrar texto de la cita -

T. Valko,

Your solution is super! excellent!

It assumes that both members of the pair are non null, but it is far
more of what I would have expected to get in this newsgroup.

It will take me a while to study how it works, and also trying to
adapt it to a combination of text and numbers.

Again, congratulations

 

[vsoler]

Another tinker to try, using non-array formulas ..

Source data assumed in cols A and B, from row1 down

In C1:
=IF(COUNTA(A1:B1)<2,"",IF(SUMPRODUCT(($A$1:A1=A1)*($B$1:B1=B1))>1,"",CODE(L­EFT(A1))+ROW()/10^10))

In D1:
=IF(ROW()>COUNT($C:$C),"",INDEX(A:A,MATCH(SMALL($C:$C,ROW()),$C:$C,0)))
Copy D1 to E1. Select C1:E1, copy down to cover the max expected extent of
data in cols A and B, say down to E100? Hide away col C. Cols D and E will
return the required results all neatly bunched at the top, viz. for the
posted sample source data, you'd get:

cup water
light team
table cup
water wine

--
Max
Singaporehttp://savefile.com/projects/236895
xdemechanik
---

Hello Max,

Thank you very much for your solution. It works very well. I am going
to study in detail how it works

 

[vsoler]

Another tinker to try, using non-array formulas ..

Source data assumed in cols A and B, from row1 down

In C1:
=IF(COUNTA(A1:B1)<2,"",IF(SUMPRODUCT(($A$1:A1=A1)*($B$1:B1=B1))>1,"",CODE(L­EFT(A1))+ROW()/10^10))

In D1:
=IF(ROW()>COUNT($C:$C),"",INDEX(A:A,MATCH(SMALL($C:$C,ROW()),$C:$C,0)))
Copy D1 to E1. Select C1:E1, copy down to cover the max expected extent of
data in cols A and B, say down to E100? Hide away col C. Cols D and E will
return the required results all neatly bunched at the top, viz. for the
posted sample source data, you'd get:

cup water
light team
table cup
water wine

--
Max
Singaporehttp://savefile.com/projects/236895
xdemechanik
---

Max,

I've just realized it does not do a real sort, it works only on first
letter.

 

[vsoler]

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$11)),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









- 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

 

[T. Valko]

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$11)),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









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

Say, for example, that my list contained

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

Well, I asked you if that was a possibility:

And you said:

So now *it is possible* ?

About the only way to do this is to concatenate, find and sort the uniques,
then unconcatenate.

Let me see what I can come up with. No guarantees!

Don't change the "rules" anymore!!! <BG>

Or better yet, let us know *ALL* the rules before we attempt a solution.

Biff

 

[Max]

Yes, that's the limitation of the criteria formula suggested in col C. The
set-up will return only & all the unique pairs that you seek from cols A and
B, arranged in an ascending sort order based on the 1st character in col A.
I'm not sure whether it's possible to emulate via formulas a "real" sort
order as per Excel's native Data > Sort functionality. Perhaps it's easier
to consider bolting-on a recorded macro to copy n paste as values the
results returned in cols D and E into cols F and G, and then sort cols F and
G using Data > Sort?

 

[Max]

Yes, it doesn't do a "real" sort, only an approx sort based on the 1st
character in col A. And I guess this wasn't good enough for the OP <g>.

 

[T. Valko]

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



Well, I asked you if that was a possibility:


And you said:


So now *it is possible* ?

About the only way to do this is to concatenate, find and sort the
uniques, then unconcatenate.

Let me see what I can come up with. No guarantees!

Don't change the "rules" anymore!!! <BG>

Or better yet, let us know *ALL* the rules before we attempt a solution.

Biff

Ok, this is about the best I can come up with. It *requires* that you
concatenate column A and column B. It will *not* handle empty cells.

A1:Bn = rng1
C1:Cn = rng2

C1: =A1&" "&B1

Copied down as needed

E1 copied across to F1:

=INDEX(rng1,MATCH(LOOKUP(2,1/(COUNTIF(rng2,"<"&rng2)=0)/ISTEXT(rng2),rng2),rng2,0),COLUMNS($A:A))

Array formula entered in E2 and copied across to F2:

=IF(ROWS($1:2)<=SUM((rng2<>"")/COUNTIF(rng2,rng2&"")),INDEX(rng1,MATCH(0,COUNTIF(rng2,"<"&rng2)-SUM(COUNTIF(rng2,$E$1:$E1&"
"&$F$1:$F1)),0),COLUMNS($A:A)),"")

Select both E2 and F2 and copy down until you get blanks.

You can hide column C if you'd like.

Biff

 

[vsoler]

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










- Mostrar texto de la cita -

T. Valko,

I apologize I there has been a misunderstanding although I still
believe I have not changed the specs.

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

My answer to this example was that it was not possible, because row 1
is exactly the same as row 4; I want to retain row 1 and delete row 4;
however, rows 2, 3 and 5 are different pairs, and I would like to keep
them all. After the deletion of row 4, row 5 will move up to become
the new row 4.

I hope this time there no doubt about the definition of the problem.

Thank you