• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

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.

80445
 

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))),"")

80447
 

Attachments

  • TEST_123 (BY).xlsx
    12.5 KB · Views: 4
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.
 
Back
Top