Try something like this:
Note: ALL of the below formulas are ARRAY FORMULAS. Commit them by holding
down the [ctrl] and [shift] keys when you press [enter].
With your list in A1:B10
E1: Name
E2:
=IF(SUMPRODUCT(($A$2:$A$10<>"")*ISERROR(MATCH($A$2:$A$10,$E$1:E1,0)))<>0,INDEX($A$2:$A$10,MATCH(TRUE,ISERROR(IF(ISBLANK($A$2:$A$10),FALSE,MATCH($A$2:$A$10,$E$1:$E1,0))),0),1),"")
F1 through J1: Fav_1, Fav_1....to Fav_5
F2:
=IF($E2="","",IF(COUNTIF($A$1:$A$10,$E2)<COLUMNS($F:F),"",INDEX($A$1:$B$10,SMALL(IF($A$1:$A$10=$E2,ROW($A$1:$A$10 )),COLUMNS($F:F)),2)))
>>follow these next steps carefully<<<
Copy from F2 and Paste into G2 through J2
Copy from E2:J2 and Paste DOWN into E3 through E10
Is that something you can work with?
***********
Regards,
Ron
XL2002, WinXP-Pro
"Yan" wrote:
> Hi,
>
> I would like to know the advanced Excel function or formula on how to
> display a list of data from vertical listing and be able to identify and
> display the next occurence of the same text, to be displayed horizontally for
> the return column. Here is an example:
>
> Sample data:
> Name Favourite colour
> John Smith Black
> John Smith Blue
> John Smith Orange
> Mary Singh Purple
> Sally Hansen Red
> Sally Hansen Green
>
> The layout I want to display is as follows:
> Name Favourite colour1 Favourite colour2
> Favourite colour3
> John Smith Black Blue
> Orange
> Mary Singh Purple
> Sally Hansen Red Green
>
> Much appreciate your help.
>
> --
> Yan
|
Similar Threads
