Solved

Excel-Copy columns to rows

Posted on 2013-06-25
15
268 Views
Last Modified: 2013-06-27
Hi, I have a worksheet that has 150 companies accross my worksheet with four account descriptions. The start cell is A1 and there are no spaces accross.  How can I copy the companies, amounts, and descriptions down every 16 companies? See what I want below. I want to be able to print this so that my first 48 companies (3 sets of 16) print on one page.

Have:

Description            Co1     Co2      Co3.............Co150
Cash                        10        80        50                    90
Acct Rcvble                2          1         6                       3
Inventory                   0          0         0                      5
Assets                      20          30       6                     55

Want

Description            Co1     Co2      Co16
Cash                        10        80        50        
Acct Rcvble                2          1         6                      
Inventory                   0          0         0                    
Assets                      20          30       6    

Description            Co17     Co18....Co32
Cash                        10        80        50        
Acct Rcvble                2          1         6                      
Inventory                   0          0         0                    
Assets                      20          30       6  

Description            Co33     Co34....Co48
Cash                        10        80        50        
Acct Rcvble                2          1         6                      
Inventory                   0          0         0                    
Assets                      20          30       6    

etc.
0
Comment
Question by:Conernesto
  • 6
  • 6
  • 2
  • +1
15 Comments
 
LVL 12

Expert Comment

by:telyni19
ID: 39276382
This isn't a transpose, so there's no simple formula to do this kind of thing as far as I know. Are you looking for a VBA script to transform your data arrangement? Since there are only 150 columns (less than 10 sets of 16), I'd consider just selecting the data in its chunks of 16 columns and dragging it around the sheet to rearrange it. It would probably be faster than trying to write a script.
0
 

Expert Comment

by:Ahype
ID: 39276402
Well I think the best thing to do would be to use Excel's built in "columns to repeat at left" in the page setup on the sheet tab. Select $A:$A as columns to repeat. If all the company names are simple numbers as posted, you can adjust the scaling to 75% normal size to and 16 companies fit on a page. You also have to make sure you setup the page size orientation to landscape. See if this spreadsheet helps.
Excel-Columns-to-Rows.xlsx
0
 
LVL 12

Expert Comment

by:telyni19
ID: 39276458
Ahype: You still only get one set of data per page that way though. I think he wants to stack the data so that he can have multiple sets of 16 companies per page. It didn't work that way when I tried your file. On the other hand, in the print options, you could select 2 pages per sheet to stack two sets of data. But the standard print options don't allow parallel stacking of more than that, and it still wouldn't be a very effective use of space because it would just be resizing the existing layout of one set per page to fit two on a page. Still not ideal.

In page layout mode, it's pretty easy to take one page-worth at a time and rearrange it manually. I just rearranged the attached in about two minutes.
Excel-Columns-to-Rows-Rearranged.xlsx
0
 

Author Comment

by:Conernesto
ID: 39276478
I guess I am looking for VBA script to do this as the range could be more than 150 columns accross.
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 39281825
As you have a consistent set of data for each block, 6 rows by 17 columns (including headers and account titles), you could probably do it with formulas such as INDEX and MATCH based on column number and row number.

This could be on a separate sheet to keep the original data intact.

I will see what I can put together but running a bit late at the minute so may take some time.

Cheers
Rob H
0
 
LVL 31

Accepted Solution

by:
Rob Henson earned 500 total points
ID: 39281978
See attached.

Random data generated on Data sheet for 150 columns.

Report sheet has formula in column A to repeat account headings, change on Data and they will change on Report.

Columns B to Q have formula pulling in the relevant data for each account for the relevant column.

The formula in B1 has carriage returns embedded so that you can see the steps more easily.

I have only copied this down as far as row 59 to show multiple pages but you can copy down as far as you need and the data will change.

Thanks
Rob H
Transpose-Blocks.xls
0
 

Author Comment

by:Conernesto
ID: 39282689
Rob,

This is great! How would I modify the formula if have four more rows (Accounts 5-8). In Cell A1 the formula includes <10 and <18 what do these mean?

Description      Co1
Cash                        10
Acct Rcvble        20
Inventory                        30
Assets                        40
Account 5                        50
Account 6                        60
Account 7                        70
Account 8                        80
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 31

Expert Comment

by:Rob Henson
ID: 39282914
To allow for accounts 5 - 8 all you would have to do is change the section of the formula that refers to Data!$A$1:$EU$5 to Data!$A$1:$EU$8

Likewise if you have more than 150 companies to look at the EU would have to be changed to allow for the additional columns.

The <10 section is looking at the row number of the row containing the formula and checking if it is the first block of results as the formula is slightly different for that section, subsequent sections are calculating which group of data it is in; 1st, 2nd etc.

The MOD(ROW()),10) looks at the row number and divides it by 10 but only takes the remainder, hence row 14 becomes 1.4 but it only takes the 4 so returns the 4th row of data.

The 18 does similar with the column number.

Looking at it, I could have changed the column A formula as I applied some slightly different logic in the data columns and it made the formula shorter. I didn't then revisit the header column to apply the same logic.

Also, which version of Excel are you using, I was working on Excel 2003 earlier but now have access to Excel 2007 which has different error functions which will make it shorter again.

Thanks
Rob
0
 

Author Comment

by:Conernesto
ID: 39282930
I am using Excel 2007.

Thank you.

conernesto
0
 

Author Closing Comment

by:Conernesto
ID: 39282939
Great feedback.
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 39282965
The amended formula for column A would be:

=IFERROR(IF(ROW()<10,INDEX(Data!$A$1:$EU$5,ROW(),1),INDEX(Data!$A$1:$EU$5,MOD(ROW(),10)+1,1)),"")

Also simpler formula for columns B to Q:

=IF($A1="","",INDEX(Data!$A$1:$EU$5,MATCH($A1,Data!$A$1:$A$5,0),IF(ROW()<10,COLUMN(),COLUMN()+(16*INT(ROW()/10)))))

I was a little stressed after work earlier but am thinking a bit straighter so able to apply a bit more logic.

Thanks
Rob H
0
 

Author Comment

by:Conernesto
ID: 39282974
Thank you Rob.

Conernesto
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 39282999
FYI, it would get more complicated if you went to more than 8 accounts; 9 would be the same but no gap between blocks. 10 or more would need a serious rethink as the MOD and INT functions to get the right block number wouldn't work.

For those two new formulas above, I left the data range to row 5; you would have to change two ranges:

In the INDEX functionsin both formulas:
A1:EU5 to A1:EU8

In the MATCH function in the second formula:
A1:A5 to A1:A8

Thanks
Rob H
0
 

Author Comment

by:Conernesto
ID: 39283007
Noted. I will keep these with my notes.

Conernesto
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 39283078
My last comment got me thinking, what if the number of accounts does go over 10???

My suggestion would be to force the report sheet into blocks of units of ten.

So, the formula would be:

Account headers:

=IFERROR(IF(ROW()<$T$1,INDEX(Data!$A$1:$EU$11,ROW(),1),INDEX(Data!$A$1:$EU$11,MOD(ROW(),$T$1)+1,1)),"")

Data:

=IF($A1="","",INDEX(Data!$A$1:$EU$11,MATCH($A1,Data!$A$1:$A$11,0),IF(ROW()<$T$1,COLUMN(),COLUMN()+(16*INT(ROW()/$T$1)))))

You will notice that these now refer to $T$1 rather than the 10 as before. In T1 of the report sheet I have the formula:

=CEILING(COUNTA(Data!$A:$A),10)

This counts the rows of data (account headers) in the data sheet and the CEILING function rounds that count up to the next factor of 10.

You could, if so desired, do away with the helper cell and include the count formula in the header and data formulas instead of referring to the helper cell.

Thanks
Rob H
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now