This is the first time I send the request to Office KB for the help in Excel.
My problem is to find out one formula which can figure out the result which
I want. There are two sets of data. The first set data is A, B, C, D, E, F,
G and H. The second set data is A, A, G, G, G, G, and H.


The result which I want is when the first data is exist in the second data,
the data will automatic appear in one cell.

For example, because there are 3 A in second set of data, therefore, only one
A appear in first cell. The second cell will have G and the third cell will
have H. I need an Excel formula to have this result!

Many thanks,
Wilchong.

One guess, one play ...

The 2 data sets are assumed running in A1 and in B1 down, viz.:
In A1 down is: A, B, C, D, E, F, G and H
In B1 down is: A, A, G, G, G, G, and H.

Place
In C1:
=IF(AND(COUNTIF(A:A,B1),COUNTIF(B$1:B1,B1)<2),ROW( ),"")

In D1:
=IF(ROW()COUNT(C:C),"",INDEX(B:B,SMALL(C:C,ROW()) ))
Copy C1:D1 down to cover the max expected extent of data in col B. Hide away
col C. Col D should return the results that you seek -- if my interp is
correct -- with all results neatly bunched at the top
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"wilchong" <u43231@uwe wrote in message news:835526aece2e1@uwe...
This is the first time I send the request to Office KB for the help in
Excel.
My problem is to find out one formula which can figure out the result
which
I want. There are two sets of data. The first set data is A, B, C, D, E,
F,
G and H. The second set data is A, A, G, G, G, G, and H.


The result which I want is when the first data is exist in the second
data,
the data will automatic appear in one cell.

For example, because there are 3 A in second set of data, therefore, only
one
A appear in first cell. The second cell will have G and the third cell
will
have H. I need an Excel formula to have this result!

Many thanks,
Wilchong.

Many thank Max!

Really appreciate your wisdom and time! The Excel formula in D1 which produce
the result is exactly what I want and I feel this formula is working so
perfect than what I imagine!

However, I have a small technical problem when I move the second set of data.
For example, when the second set of data is NOT starting from B1, let said it
starting from B10, the result in C1 and D1 has change totally UNLESS B1 and
C1 move together! Even B1 and C1 move together, the result in D1 also will
change. Max, my question is that how to deal with this situation? Many
thanks for your effort and time again!

Wilchong


Max wrote:
One guess, one play ...

The 2 data sets are assumed running in A1 and in B1 down, viz.:
In A1 down is: A, B, C, D, E, F, G and H
In B1 down is: A, A, G, G, G, G, and H.

Place
In C1:
=IF(AND(COUNTIF(A:A,B1),COUNTIF(B$1:B1,B1)<2),ROW (),"")

In D1:
=IF(ROW()COUNT(C:C),"",INDEX(B:B,SMALL(C:C,ROW() )))
Copy C1:D1 down to cover the max expected extent of data in col B. Hide away
col C. Col D should return the results that you seek -- if my interp is
correct -- with all results neatly bunched at the top
This is the first time I send the request to Office KB for the help in
Excel.
[quoted text clipped - 16 lines]
Many thanks,
Wilchong.

--
Message posted via http://www.officekb.com

If your data resides within rows 10 to 200 (say)

Use this revised set to frame it up

In C10:
=IF(AND(COUNTIF($A$10:$A$200,B10),COUNTIF(B$10:B10 ,B10)<2),ROWS($1:1),"")

In D10:
=IF(ROWS($1:1)COUNT($C$10:$C$200),"",INDEX($B$10: $B$200,SMALL($C$10:$C$200,ROWS($1:1))))
Copy C10:D10 down to D200
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"wilchong via OfficeKB.com" <u43231@uwe wrote in message
news:836190ff326be@uwe...
Many thank Max!

Really appreciate your wisdom and time! The Excel formula in D1 which
produce
the result is exactly what I want and I feel this formula is working so
perfect than what I imagine!

However, I have a small technical problem when I move the second set of
data.
For example, when the second set of data is NOT starting from B1, let said
it
starting from B10, the result in C1 and D1 has change totally UNLESS B1
and
C1 move together! Even B1 and C1 move together, the result in D1 also
will
change. Max, my question is that how to deal with this situation? Many
thanks for your effort and time again!

Wilchong

Dear Max,
Thanks for your help. The formula really work perfectly!

Thanks,
Wilchong

Max wrote:
If your data resides within rows 10 to 200 (say)

Use this revised set to frame it up

In C10:
=IF(AND(COUNTIF($A$10:$A$200,B10),COUNTIF(B$10:B1 0,B10)<2),ROWS($1:1),"")

In D10:
=IF(ROWS($1:1)COUNT($C$10:$C$200),"",INDEX($B$10 :$B$200,SMALL($C$10:$C$200,ROWS($1:1))))
Copy C10:D10 down to D200
Many thank Max!

[quoted text clipped - 15 lines]

Wilchong

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...l-new/200804/1

Welcome, good to hear that.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"wilchong via OfficeKB.com" <u43231@uwe wrote in message
news:8364862381c33@uwe...
Dear Max,
Thanks for your help. The formula really work perfectly!

Thanks,
Wilchong