T
tkt_tang
Re : Excel Experts E-letter (No. 19) ; POWER FORMULA TECHNIQUES,
Re-Visit No. 2
by Leo Heuser.
A. How can you get a list of unique entries in an n * m array by using
a worksheet formula?
B. Example:
C. Assuming data in B5:GR10, enter this array formula in e.g. G12. G11
must be empty or, if it has a value, this value must not occur in
B5:GR10.
D. =OFFSET($B$5,
MIN(IF(COUNTIF($G$11:G11,$B$5:$GR$10)=0,ROW($B$5:$GR$10)-ROW($B$5))),
MOD(
MIN(IF(COUNTIF($G$11:G11,$B$5:$GR$10)=0,
ROW($B$5:$GR$10)-ROW($B$5)+(COLUMN($B$5:$GR$10)-COLUMN($B$5))/1000)),1)*1000)
E. Drag down until the value in G12 begins repeating.
1. Had attempted to replicate the above example.
2. And now, how is it possible to modify the given formula such that
the ensuing wish list would be met ?
3. Wish to eliminate the restriction imposed on cell G11 that it must
be empty or, if it has a value, this value must not occur in the range
B5:GR10. Refer to Clause C as given above.
4. Wish to display the listing of unique entries (extracted from the
range B5:GR10) in an array dimensionally sized similar to the range
B5:GR10.
5. Last but not least, wish to display the listing of unique entries
(extracted from the range B5:GR10) according to an alphabetical order
from left to right row-by-row in an array dimensionally sized similar
to the range B5:GR10.
6. Please share your experience and comments. Regards.
Re-Visit No. 2
by Leo Heuser.
A. How can you get a list of unique entries in an n * m array by using
a worksheet formula?
B. Example:
C. Assuming data in B5:GR10, enter this array formula in e.g. G12. G11
must be empty or, if it has a value, this value must not occur in
B5:GR10.
D. =OFFSET($B$5,
MIN(IF(COUNTIF($G$11:G11,$B$5:$GR$10)=0,ROW($B$5:$GR$10)-ROW($B$5))),
MOD(
MIN(IF(COUNTIF($G$11:G11,$B$5:$GR$10)=0,
ROW($B$5:$GR$10)-ROW($B$5)+(COLUMN($B$5:$GR$10)-COLUMN($B$5))/1000)),1)*1000)
E. Drag down until the value in G12 begins repeating.
1. Had attempted to replicate the above example.
2. And now, how is it possible to modify the given formula such that
the ensuing wish list would be met ?
3. Wish to eliminate the restriction imposed on cell G11 that it must
be empty or, if it has a value, this value must not occur in the range
B5:GR10. Refer to Clause C as given above.
4. Wish to display the listing of unique entries (extracted from the
range B5:GR10) in an array dimensionally sized similar to the range
B5:GR10.
5. Last but not least, wish to display the listing of unique entries
(extracted from the range B5:GR10) according to an alphabetical order
from left to right row-by-row in an array dimensionally sized similar
to the range B5:GR10.
6. Please share your experience and comments. Regards.