How to sort data according two matching cells?

[Guest]

Hi,

I have this problem with sorting my data. I have data that is in two lists
like

col a---col b col d---col e
001 a 002 k
020 f 041 l
041 i 554 p

How can I sort all this data so that both column a and d would macth? And if
there is no match then it would leave the other columns empty

Like this

col a---col b col d---col e
001 a
002 k
020 f
041 i 041 l
554 p

I need your help very much!
-Dave

 

[vezerid]

Hi,

I have this problem with sorting my data. I have data that is in two lists
like

col a---col b col d---col e
001 a 002 k
020 f 041 l
041 i 554 p

How can I sort all this data so that both column a and d would macth? And if
there is no match then it would leave the other columns empty

Like this

col a---col b col d---col e
001 a
002 k
020 f
041 i 041 l
554 p

I need your help very much!
-Dave

Dave,

A quick and dirty one: Assuming list1 is in A1:B5, list2 in D1:E4.

Use an aux column (say starting from F1):

=SMALL(($A$1:$B$5,$D$1:$E$4),ROW()-ROW($F$1)+1)

Copy down until you get #NUM!

In H1:
=IF(COUNTIF($A$1:$B$5,F1),F1,"")
In I1:
=IF(H1,VLOOKUP(H1,$A$1:$B$5,2,0),"")

In K1:
=IF(COUNTIF($D$1:$E$4),F1),F1,"")
In L1:
=IF(K1,VLOOKUP(K1,$D$1:$E$4,2,0),"")

HTH
Kostis Vezerides

 

[Guest]

Thank you very much for your help Vezerid! I'll try the solution immediately

-Dave