Improve company productivity with a Business Account.Sign Up

x
?
Solved

Excel macro to transpose columns

Posted on 2016-11-02
14
Medium Priority
?
100 Views
Last Modified: 2016-11-08
Hi All,
I have received an complex excel file format which contains data into various different columns and required to get these data into rows.
Please find attached sample file which contains input at the top and required output in new excel file.
Can you please provide any reference or sample code to achieve this. I find this quite complex to code this in vb macro.

Thanks in advance.
Shail
sample.xlsx
0
Comment
Question by:Shailesh Shinde
  • 7
  • 6
14 Comments
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 41870589
Where would you report the following values?
D4: "ans trans text" (tied to "text3" and "A")
E4: "trans ans text" (tied to "text3" and "T_A")

What defines a "type source" and what iteration it is (i.e., "type" vs. "type1")?
0
 
LVL 3

Author Comment

by:Shailesh Shinde
ID: 41870637
Hi,

D4 and E4 needs to be ignored.
type source and type 1 source is the column heading and text in that row.

please do let me know if you have any query.

Thanks,
Shail
0
 
LVL 2

Expert Comment

by:Alan Varga
ID: 41873005
I put this together just for fun and practice.  It is macro-enabled, so I always recommend getting in the habit of scanning such files with your antivirus software before opening them.

My assumptions were that the column titles are hardcoded, not examples, and that you might have a large number of rows to process, so I refer to row numbers as long integers (>32767 possible rows).

There are liberal comments, so I hope you can follow what I did.  If not, and you have any questions, just post back.

Cheers!
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
LVL 2

Expert Comment

by:Alan Varga
ID: 41873006
I'm not sure the attachment made it the first time, so here it is again.
sample-28980540.xlsm
0
 
LVL 3

Author Comment

by:Shailesh Shinde
ID: 41873424
Hi VieleFragen,

Safely received macro-enabled file and tested on sample file itself. I do have one query.
1. Can it be possible to get rid of user to input first ID cell and output cell ID. The actual input file is always with this fixed columns and new output file will always starts with A1 as column heading ID and so on.
2. Can it be possible to output in different worksheet or new excel file?

Thanks,
Shail
0
 
LVL 3

Author Comment

by:Shailesh Shinde
ID: 41873425
Hi VieleFragen,,

Please find attached the required output file sample for your reference.

Thanks,
Shail
output-sample-28980540.xlsx
0
 
LVL 3

Author Comment

by:Shailesh Shinde
ID: 41873780
Hi VieleFragen,,
When I ran this code on actual production file which contains more columns in input file. I found that in output format last two columns for particular rows gets overwritten and and add last column with data.

Thanks,
Shail
0
 
LVL 2

Expert Comment

by:Alan Varga
ID: 41875072
Hi Shail,

I will have time to look at your new sample on the weekend.  To answer your first post, yes, output can go to either a new sheet or a new workbook, whichever you prefer.  The prompts were for demonstration purposes, but those can certainly be removed.

What I typically do is put the macros in their own workbook so they are independent of the input and output data.  Once both the macro and data workbooks are open, it's a simple matter of choosing and running the macro either from the View/Macros menu (Excel 2010) or using <Alt>+<F8> from the keyboard.

I will send you an updated macro file which you can test against your own data.
0
 
LVL 2

Expert Comment

by:Alan Varga
ID: 41876202
Hi Shail,

I have attached a macro workbook.  It will work with any input file formatted as your original example, and will create an output file per your latest output sample.  All three files should be in the same Windows folder.  The output file will be titled with the current date, but you can rename it to anything you like.

If any adjustments are needed, let me know.  Happy reporting!
macros-28980540.xlsm
0
 
LVL 3

Author Comment

by:Shailesh Shinde
ID: 41876729
Hi VieleFragen,
I have tried this update code on production file and found it is placing the contents into next column instead of first.

Please find attached production file with first row heading and second row with data for your reference.

Thanks,
Shail
28980540.xlsm
0
 
LVL 2

Expert Comment

by:Alan Varga
ID: 41877267
Ah, with more data, I see a different pattern, based on columns grouped in 4's.  I will send you an updated macro file later today.

Alan
0
 
LVL 2

Expert Comment

by:Alan Varga
ID: 41878892
Shail, try this updated macro file.  I ran it against a combination of your original input and the new input with more columns, and it handles both successfully.  Column groups with no data are skipped.  Please let me know if this will meet your needs.

Thanks, Alan
0
 
LVL 2

Accepted Solution

by:
Alan Varga earned 2000 total points
ID: 41878893
Once again, the upload did not take the first time.
macros-28980540.xlsm
0
 
LVL 3

Author Closing Comment

by:Shailesh Shinde
ID: 41879911
Thanks a lot! This works as per requirement.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
Excel allows various different methods to link Excel files to each other. This includes relative paths, mapped drives (or the local drive) and UNC paths. UNC paths are the least robust of the three.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

584 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