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

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,R OW($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