Excel Formula

[wilchong]

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.

 

[Max]

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

 

[wilchong via OfficeKB.com]

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

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

 

[Max]

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 C1010 down to D200

 

[wilchong via OfficeKB.com]

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

Thanks,
Wilchong

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 C1010 down to D200

Many thank Max!

[quoted text clipped - 15 lines]

Wilchong

 

[Max]

Welcome, good to hear that.