# Data horizontal to vertical

1. ## Data horizontal to vertical

Dear All,

Can it possible as below either in Pivot or any formula

2. ## Re: Data horizontal to vertical

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

3. ## Re: Data horizontal to vertical

attached the above

4. ## Re: Data horizontal to vertical

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

5. ## Re: Data horizontal to vertical

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

6. ## Re: Data horizontal to vertical

Select E2 to G2 and press = and copy and paste the below formula and Press Ctrl+Shift+Enter
Formula:
`Please Login or Register  to view this content.`

In E3 cell- Array formula requires Ctrl+Shift+Enter
Formula:
`Please Login or Register  to view this content.`

Drag it down and right

7. ## Re: Data horizontal to vertical

Thank you all

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

8. ## Re: Data horizontal to vertical

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.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1