• 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.

Excel Column and Row data

A

autoguy57

New Member
I have a report that has data in both column and row format, and am trying to identify a way to configure the information for a Pivot Table. Here is the way the information is organized now:
Audit: Process-1 Audit Type: Process
Question: LP12

Date Completed Time Performed Auditor Nonconform Attachments
01/01/1900 9:00 PM Super Dave (None)
​
I need the top areas (Audit/Question/Audit Type) in their own individual columns next to the "Date Completed." Any ideas? Thanks in advance!
 
Last edited:
Can you please upload some sample sheet with desire output

autoguy57 said:
I have a report that has data in both column and row format, and am trying to identify a way to configure the information for a Pivot Table. Here is the way the information is organized now:
Audit: Process-1 Audit Type: Process
Question: LP12

Date Completed Time Performed Auditor Nonconform Attachments
01/01/1900 9:00 PM Super Dave (None)
​
I need the top areas (Audit/Question/Audit Type) in their own individual columns next to the "Date Completed." Any ideas? Thanks in advance!
Click to expand...
 
Maybe…………..

In B10, array formula (confirm enter with CTRL+SHIFT+ENTER) copy across :

=INDIRECT(TEXT(SMALL(IF(($B$2:$J$6<>"")*(RIGHT($B$2:$J$6)<>":"),ROW($2:$6)/1%+COLUMN($B$2:$J$2),4^8),COLUMNS($A1:A1)),"R0C00"),)&""

Regards
Bosco
 

Attachments

bosco_yip said:
Maybe…………..

In B10, array formula (confirm enter with CTRL+SHIFT+ENTER) copy across :

=INDIRECT(TEXT(SMALL(IF(($B$2:$J$6<>"")*(RIGHT($B$2:$J$6)<>":"),ROW($2:$6)/1%+COLUMN($B$2:$J$2),4^8),COLUMNS($A1:A1)),"R0C00"),)&""

Regards
Bosco
Click to expand...

The formula works in the example I sent you, but when I place it into the Worksheet it doesn't work. Not sure if I'm missing a "switch" or not that is turned off.
 
I appreciate your time, sorry I couldn't get this to you sooner. I have attached an example, and would like to go from a Row and Column to just Rows of information.

I tried to upload a file, but it won't allow me to upload it. Can't figure that out. What I need is to take the Row and Column format and move the data to all Row format. Fields 1, 2, &3 should all be in alignment with the other Fields in the previous document.
 
You must log in or register to reply here.
Back
Top