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

#### Vignesh Veerasamy

##### Member
Hi Gurus

I would like to have the formula in which two set of data is needed.

One is duplicate of data should be removed and data should me published.
Second is Formula comparing Part no & PO columns & other with formula comparing column Part no & Price columns.
Sample output shown below.

Thank you.

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

#### p45cal

##### Well-Known Member
Office365?
Only green cells have formulae:

#### Attachments

• 16.3 KB Views: 4

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