• 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

Hi Excel gurus

This is related to one formula used to pull the vertical values to horizontal values.
Challenge is able to get the data but it is duplicated. I need to remove the duplicate and show only on value.

Formula : =IFERROR(INDEX($H$7:$H$66611,SMALL(IF($J2687=$G$7:$G$66611,ROW($G$7:$G$66611)-ROW($G$7)+1),CEILING(COLUMNS($K2687:K2687)/2,1))),"")

Thanks for support.

80439
 

Attachments

bosco_yip

Excel Ninja
The formula herein is based on your level 1 snapshot source table

So,

In H3, formula copied across and down :

=IFERROR(INDEX($C$3:$D$9,AGGREGATE(15,6,ROW($B$3:$B$9)-ROW($B$2)/($G3=$B$3:$B$9)/($C$3:$C$9<>$C$4:$C$10),CEILING(COLUMNS($H3:H3)/2,1)),MOD(COLUMN(A1)+1,2)+1),"")

80442
 
Last edited:
The formula herein is based on your level 1 snapshot source table

So,

In H3, formula copied across and down :
=IFERROR(INDEX($C$3:$D$9,AGGREGATE(15,6,ROW($B$3:$B$9)-ROW($B$2)/($G3=$B$3:$B$9)/($C$3:$C$9<>$C$4:$C$10),CEILING(COLUMNS($H3:H3)/2,1)),MOD(COLUMN(A1)+1,2)+1),"")

View attachment 80442
Thank you.
Can I have formula only considering column B & C scenario alone ? without price column [column D].
 

bosco_yip

Excel Ninja
Thank you.
Can I have formula only considering column B & C scenario alone ? without price column [column D].
Hi,

Should you have a new question other than the level 1 original question.

Based on the forum rule, one post one question.

You could open a new thread for your new question.

Thank you for your support

Remark : you could used my formula in #5 in change of the cell range only, and take note to this formula part ($C$3:$C$9<>$C$4:$C$10) to suit with your new requirement to remove the duplicate

bosco_yip
 
Last edited:
Hi,

Should you have a new question other than the level 1 original question.

Based on the forum rule, one post one question.

You could open a new thread for your new question.

Thank you for your support

Remark : you could used my formula in #5 in change of the cell range only, and take note to this formula part ($C$3:$C$9<>$C$4:$C$10) to suit with your new requirement to remove the duplicate

bosco_yip
Thank you.
Hope the formula you mentioned in #5 is as below
Old : =IFERROR(INDEX($C$3:$D$9,AGGREGATE(15,6,ROW($B$3:$B$9)-ROW($B$2)/($G3=$B$3:$B$9)/($C$3:$C$9<>$C$4:$C$10),CEILING(COLUMNS($H3:H3)/2,1)),MOD(COLUMN(A1)+1,2)+1),"")

New
=IFERROR(INDEX($C$3:$C$9<>$C$4:$C$10),AGGREGATE(15,6,ROW($B$3:$B$9)-ROW($B$2)/($G3=$B$3:$B$9)/($C$3:$C$9<>$C$4:$C$10),CEILING(COLUMNS($H3:H3)/2,1)),MOD(COLUMN(A1)+1,2)+1),"")

Thanks.
 
Top