# Formula to change vertical items to horizontal items - Repeated values - New

#### Attachments

• 11.1 KB Views: 1

#### bosco_yip

##### Excel Ninja
Try,

1] Output 1 [P4] CSE formula copied across and down:

=IFERROR(INDEX(\$C\$3:\$D\$9,SMALL(IF(\$O4=\$B\$3:\$B\$9,ROW(\$B\$3:\$B\$9)-ROW(\$B\$3)+1),CEILING(COLUMNS(\$P4: P4)/2,1)),MOD(COLUMN(A\$1)+1,2)+1),"")

2] Output 2 [P11] CSE formula copied across and down:

=IF(COLUMN(A\$1)<=COUNTIF(\$B\$3:\$B\$8,\$O4),INDEX(\$C\$3:\$C\$9,SMALL(IF(\$O4=\$B\$3:\$B\$9,ROW(\$B\$3:\$B\$9)-ROW(\$B\$3)+1),COLUMN(A\$1))),"")

3] Output 3 [P18] CSE formula copied across and down:

=IF(COLUMN(A\$1)<=COUNTIF(\$B\$3:\$B\$8,\$O4),INDEX(\$D\$3:\$D\$9,SMALL(IF(\$O4=\$B\$3:\$B\$9,ROW(\$B\$3:\$B\$9)-ROW(\$B\$3)+1),COLUMN(A\$1))),"") #### Attachments

• 12.5 KB Views: 4
• Vignesh Veerasamy

#### Vignesh Veerasamy

##### Member
Try,

1] Output 1 [P4] CSE formula copied across and down:

=IFERROR(INDEX(\$C\$3:\$D\$9,SMALL(IF(\$O4=\$B\$3:\$B\$9,ROW(\$B\$3:\$B\$9)-ROW(\$B\$3)+1),CEILING(COLUMNS(\$P4: P4)/2,1)),MOD(COLUMN(A\$1)+1,2)+1),"")

2] Output 2 [P11] CSE formula copied across and down:

=IF(COLUMN(A\$1)<=COUNTIF(\$B\$3:\$B\$8,\$O4),INDEX(\$C\$3:\$C\$9,SMALL(IF(\$O4=\$B\$3:\$B\$9,ROW(\$B\$3:\$B\$9)-ROW(\$B\$3)+1),COLUMN(A\$1))),"")

3] Output 3 [P18] CSE formula copied across and down:

=IF(COLUMN(A\$1)<=COUNTIF(\$B\$3:\$B\$8,\$O4),INDEX(\$D\$3:\$D\$9,SMALL(IF(\$O4=\$B\$3:\$B\$9,ROW(\$B\$3:\$B\$9)-ROW(\$B\$3)+1),COLUMN(A\$1))),"")

View attachment 80447
Thank you. Issue able to visualize as expected this worked as the data is large processing of the formula itself is taking large amount of time.
Posted one more question in separate thread with same title.

• bosco_yip