Inquiry: Converting Vertical Data to Horizontal Table

[YPJunk]

Hello!

I hope all is well. I have to upload some data into a new system. Currently the source data is in a "horizontal" format, but I need it to be in a "Vertical" format. That is, each account will need to be repeated five times; one amount per account per company. Attached is the source and example table created manually. Do you I need to do this via a pivot table? Thank you for your help.

Sincerely,

Yvette

 

[p45cal]

In the attached, cell P18 is your rearranged data.
How it was done:
Via a pivot table.
First a small addition to the existing data, I added a uniq column (column A) so that each line is uniquely identifiable. I discovered I needed this because you have for example company E which has more than one value for account 4800 (eg. on uniq nos. 11 and 12). A pivot table would summarise these by summing the values on one line.
Next, move the Company headers down one row.
Create a pivot table from range A4:G135. Then drag the fields like (the Values in the Column box will appear by itself):

Then, drag the Values (circled) to the Rows box.
Then in the pivotTable Tools of the ribbon, select the Design tab, and in the Report Layout section choose both of these:

The in the Grand Totals section choose Off for Rows and Columns:

Then in the Subtotals section choose Do Not Show Subtotals:

Now select the whole pivot table except for the uniq column, Copy it, then move across to where you want your actual table to be and Paste-Special|Values.
Where you've copied it to will still be selected, so press Ctrl+h on the keyboard to bring up the Find and Replace dialogue box:

Make sure there's a space after Sum of , make sure there's nothing in the Replace with box, click on Replace All.
Add a header to the rightmost column and you're done.
Delete the pivot table.

File attached in next message (limit reached).

 

[p45cal]

he file attached.

 

[azumi]

Another option using formula, cheers

 

[p45cal]

Careful azumi, you've got 4 values for company E account 4800, all at -75256, whereas the original data are:
4800 -75256
4800 0
4800 -75983
4800 -10480

 

[azumi]

@p45cal

You're right I'm less careful about duplicate data in there, and fix it

 

[AliGW]

Done in seconds with Power Query.

 

[p45cal]

@p45cal

You're right I'm less careful about duplicate data in there, and fix it

…but now there's only one entry for 4800, Company E!?

 

[YPJunk]

WOW! Thank you! I am going to give it a try now. I appreciate it.

 

[YPJunk]

Done in seconds with Power Query.

Do you mind sharing how you did this? I was able to find "Query" on excel. And duplicate the first table (to the left) in your attachment. I am sure how to get to the second table (on the right).

 

[Peter Bartholomew]

I agree with Ali, this is pretty much a one liner with PQ.

If you do not have PQ a formula is straightforward. I have used record numbers in the destination table to feed the INDEX formula used for each data item.

recordNumber: = ROW(FormulaTable[@]) - ROW(FormulaTable[#Headers])

This allows one to calculate the rowNumber and columnNumber of the data values to be looked up in the original cross-tab.

rowNumber = 1 + QUOTIENT( recordNumber - 1, 5 )
columnNumber = 1 + MOD(recordNumber -1, 5)

The worksheet formulae are then just

= INDEX( Account, rowNumber )
= INDEX( Company, columnNumber )
= INDEX( dataValue, rowNumber, columnNumber )

ps I have never used QUOTIENT before; it came up when I looked for integer divide!

 

[YPJunk]

I used the index formula and it worked. Thank you.

(Personal goal is to learn more about Power Query)

 

[AliGW]

Unless someone else steps in, I will do a Power Query walkthrough for you tomorrow - as Peter says, it's very easy, although PQ is a bit of a learning curve. It's one worth taking, though!

 

[AliGW]

As promised:

1. Starting with your original file attached to post #1, select the range B3 to G135.
2. With the range highlighted, go to the Data ribbon and click From Table/Range (4th icon from the left) - click OK in the small box that appears. This will launch the PQ editor.
3. Go to the Transform tab and find the Unpivot Columns drop-down - select Unpivot Other Columns.
4. Select the Value column (click on its header) and use its filter button to find and deselect the 0 value - this will filter out all of the zero values in the column.
5. Go back to the Home tab and from the Close & Load drop-down, select Close & Load To ... - choose Existing Worksheet and specify cell $L$3.

All done!

Now, when you add data to the source table on the left, all you need to do is go to the Data ribbon and click on Refresh All to update the output table.

 

[Peter Bartholomew]

Just a bit of icing on the cake, IF you want it …

4A. Staying with the transform tab, select Group.
Select Advanced and group by Account and by Company
Select SUM for the aggregation operation.
5. Return to Ali's list


Now there is only one entry for Account 4800, that being the sum of 3 company E transactions.