Data horizontal to vertical

[sureshpunna]

Dear All,

Can it possible as below either in Pivot or any formula

Thanks in advance.

[Fotis1991]

“A picture may paint a thousand words but it's pretty useless when answering Excel questions, please post a sample workbook"

[sureshpunna]

attached the above

[e.isayev]

I simulated your problem on excel file to understand the array formula

=IFERROR(INDEX($B$2:$B$17,MATCH(SMALL(($A$2:$A$17=E$1)*(ROW($A$2:$A$17)-1),ROW(A1)+SUM(--(($A$2:$A$17=E$1)*(ROW($A$2:$A$17)-1)=0))),($A$2:$A$17=E$1)*(ROW($A$2:$A$17)-1),0)),"")

[Fotis1991]

In I2 copy this Array(control+shift+enter-not just enter formula) formula

=IFERROR(INDEX($A$3:$A$20,MATCH(0,COUNTIF($H$2:H2,$A$3:$A$20),0)),"")

Copy across.

In I3 this also array and copy down and across.

=IFERROR(INDEX($B$3:$B$20,SMALL(IF($A$3:$A$20=I$2,ROW($B$3:$B$20)-2),ROW(A1))),"")

[:) Sixthsense :)]

Select E2 to G2 and press = and copy and paste the below formula and Press Ctrl+Shift+Enter

Please Login or Register  to view this content.

In E3 cell- Array formula requires Ctrl+Shift+Enter

Please Login or Register  to view this content.

Drag it down and right

[sureshpunna]

Thank you all

Can we get onlt unique values in items list. vertical 2 hor.xlsx

[Fotis1991]

Yes we can.

Try.

In M2

Please Login or Register  to view this content.

In M3

Please Login or Register  to view this content.

In M4

Please Login or Register  to view this content.

Array formulas. This will be a helper and hidden column.

Then in I2 and copy down this array formula. Same for the other columns.

Please Login or Register  to view this content.

You have to change ALL the semi colons in my formulas to comma.