# 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

• TEST_123.xlsx
11.1 KB · Views: 1
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

• TEST_123 (BY).xlsx
12.5 KB · Views: 4
Office365?
Only green cells have formulae:

#### Attachments

• Chandoo48596.xlsx
16.3 KB · Views: 5
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.