• 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

  • ListTest.xlsx
    12.1 KB · Views: 15
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.
 
Having trouble uploading the document, but it looks like the information you provided works. Thank you for your time!
 
You must log in or register to reply here.
Back
Top