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

Changing a matrix to a 2 column table

W

Wulluby

Member
Sorry if I have the terminology incorrect, I assume table 1 in the example at the following link to be a matrix;


https://skydrive.live.com/redir.aspx?cid=b81cfd1b7bf7bb2c&resid=B81CFD1B7BF7BB2C!217&parid=B81CFD1B7BF7BB2C!195&authkey=!AOSA4wp7N7K-Q50


What I would like to do is convert that to a 2 column table as can be seen in table2 in the example. Is this easily done with a formula?


Table 1 is a manipulation of an output of a report. The report just outputs '1's for where there is a match against a user. In order to feed another process the end result would be better if it was in 2 columns.


Thanks in advance.
 
Hi Chippy,


The link seems to break in the middle, might need a copy and paste


<https://skydrive.live.com/redir.aspx?cid=b81cfd1b7bf7bb2c&resid=B81CFD1B7BF7BB2C!217&parid=B81CFD1B7BF7BB2C!195&authkey=!AOSA4wp7N7K-Q50>


*Edit: yup, breaks at the ! and enclosing it in <> doesn't seem to fix that. Sorry about that but the copy and paste works at this end.
 
Wulluby

This will help you out


1. With Sheet 1 active, Press Alt-D P

2. Enable Multiple Consolidation Ranges, Select

3. Create a Single Page for Me, Select

4. Select Range A2:M5, Add, Next

5. Existing Worksheet Sheet1!F21, Finish

6. Select Cell G21 (Column) and Drag it to the Pivot Table Field List where it will show with a delete sign, release it and the Values will remove from teh pivot table

7. Drag the Value field from the Pivot Table Field List to the Column edge between the Row and Totals field of the pivot table, release

8. Select Anon 1 Subtotal, Right Click, Unselect Subtotal Row

9. Select any value in the Total Column, Right Click, Select Remove Count of Value.

10. Select Page 1, Right Click, Remove Page 1

11. Select Grand Total, Right Click, Remove Grand Total

12. Select the Value Dropdown, Unselect Blank

13. Select the Anon 1:Module 5 area Copy, Paste values where ever you want to use the data

14. Reformat to suit
 
You must log in or register to reply here.
Back
Top