Transpose Columns with corresponsing values
We all know that there is a feature in the excel sheet to transpose rows into columns or vice-versa. To transpose just select the row or column and paste-special with transpose checkbox selected but what if you want to do the transposing a complete table? There is no such feature in excel so for achieving two dimensional transposing we will write a macro.
Lets take an example to understand the problem. We have a data in the following format.
Column 1 Column 2 Column 3 Column 4 A D X B E Y C F Z G
There is no limit to the number of columns. Each Column has some data in the Cells below it.
The challenge is to transpose this data into this format.
A Column 1 B Column 1 C Column 1 D Column 2 E Column 2 F Column 2 G Column 2 X Column 4 Y Column 4 Z Column 4
Here the data below each Column is clubbed together and their respective Headers are copied in the cell next to them. If there is no value under any Header then that Column should be ignored.
Sub TransposeColumns() Dim input_sheet As Variant input_sheet = ActiveSheet.Name Worksheets(input_sheet).Select Dim column1, row1 As Range ' create range variables to hold row and column values Range("IV1").End(xlToLeft).Select ' select the last column cell '##################################################################### '############### Loop till each column ############################### For Each column1 In Range(Selection, Selection.End(xlToLeft)) ' Loop from first column to last column 'MsgBox "Column:" & column1.Value 'Comment this later If column1.Offset(1, 0).Value = "" Then 'If there is no data in column then do nothing Else 'If there is some data in the first column then proceed with Transposing Worksheets(input_sheet).Select 'Select the Input sheet column1.Offset(1, 0).Select 'Select first data which is below first Header For Each row1 In Range(Selection, Selection.End(xlDown)) Call CopyTransposed_Output(row1.Value, column1.Value) Next row1 End If Next column1 ' Next Column Worksheets("Output").Select Range("A1").Select MsgBox "Transposing Columns Completed" End Sub Sub CopyTransposed_Output(row1 As Variant, column1 As Variant) Worksheets("Output").Select Range("A65536").End(xlUp).Select 'Move just below the last row ActiveCell.Offset(1, 0).Value = row1 ActiveCell.Offset(1, 1).Value = column1 End Sub
How to use this Code
1. Create a Module and Copy the posted above.
2. Create a Sheet and name it "Output".
3. Come back to the input sheet that has the data to be transposed.
4. Run the Macro.
5. Now check the Output Sheet. It will have the transposed data.
replica watach
style to watches replica culminating faculty. The screw-in replica watches kingdom replica watches forming Patek Philippe Replica Watches it a radically humidify designer rolex unaffected watch, Chopard Replica Watches accompanied best watch replica by the unidirectional cartier watch replica watches cheap Zenith Defy Xtreme ZT-11-1017 watch replica rotating Mulberry replica handbags bezel, besides helium elude cheap louis vuitton valve, posit designer bags New Replica Tiffany & Co Bracelets tjewelrybracelet157
New Replica Tiffany & Co Necklaces tjewelrynecklaces171
New Replica Tiffany & Co Bracelets tjewelrybracelet010
cz
CZ Eternity Rings & Stackable Bands replicas
The Simple Eternity Band outlet
replica watach
watches? Omega replica watches share of jewel disburse cheap Technomarine replicas watches Features Fi buy replica watches rstly the best swiss self network watch dissemble Rado Anatom Ladies Watch R10464711 watch cartier sta Porsche Design Flat Six Automatic Chronograph Mens Watch 6340.41.44GB watch tue Watches cheap Tag Heuer Calibre 16 T_HEU-101-935 watch replica a ability shelter of watch replica 48 hours influenc watches replica e your designer bags direct commit livelihood dominion Fendi replica handbags atom life designer replica bags tiffany replica silver jewelry
New Replica Gucci Jewelry gjewelry0831084
New Replica Tiffany & Co Necklaces tjewelrynecklaces090
Designer Inspired Gold CZ Heart & Key Bracelet online sales
CZ Solitaire Necklaces sales
Sterling Silver Classic Pink CZ Solitaire Ring online sales
ugg bailey button
Ladies and gentlemen:
Hair is very important for everyone.Everyone is very attentive to their own hair. if you have http://www.ghdforsale.org "> GHD Hair Straighteners ,you can make you hair strighteners easily! http://www.ghdforsale.org ">GHDS are the famous brand in the world wide ,Our http://www.ghdforsale.org ">Ghd Hair Products can meet your different needs.
Pls allow me to introduce the history of the UGG .UGG was been made in Australia for over 200 years. They might fit their name, they are UGLY, but they are realy very comfortable and they wear really well.if you have one http://www.uggonsale.org ">uggs classic tall boots or one http://www.uggonsale.org "> ugg bailey button boots or http://www.uggonsale.org "> ugg nightfall boots or http://www.uggonsale.org "> ugg 30th anniversary boots ,you will find the the boots are actually comfortable as your favorite pair of bedroom slippers and you can actually wear them outdoors and anywhere you want to.
Pls don’t wast your time,friend! You have came to the right website of the latest and coolest famous shoes and apparels of today's fashion. The http://www.jordanshoesdiy.org ">Nike Air Jordan we offer are high quality with low price.Don’t lose the chance to give yourselfhttp://www.jordanshoesdiy.org ">Michael Jordan Shoes revel.The http://www.jordanshoesdiy.org ">Air Jordan online store provide many fashion http://www.jordanshoesdiy.org ">Air Jordan Sneakers that will go well with any of your clothes.The http://www.jordanshoesdiy.org ">Nike Air Jordan have comfortable wearing and suitable for multi-sports.
GUCCI Handbags GUCCI Replica
GUCCI Handbags
GUCCI Replica Handbags
replica GUCCI Handbags
fake GUCCI Handbags
Gucci 'New Jackie' Medium
Gucci 'New Jackie' Medium Shoulder Bag
Gucci 'Pelham' Large Shoulder Bag
Gucci 'Pelham' Large Shoulder Bag
Gucci 'Pelham' Large Shoulder Bag
Gucci 'Pelham' Large Tote
Gucci 'Pelham' Medium Shoulder Bag
Gucci 'Pelham' Medium Shoulder Bag
Gucci 'Pelham' Medium Shoulder Bag
Gucci 'Pelham' Medium Shoulder Bag
Gucci 'Pelham' Medium Shoulder Bag
boots
classic ugg boots
ugg boots sale
discount ugg boots
ugg classic boots
cheapest NFL jerseys
nfl all star jerseys
nfl jerseys
all star nfl
replica handbags
wholesale handbags
replica designer handbag
ugg boots online
ugg classic cardy
purple ugg
ugg sale
ugg australia sale
ugg ladies boots
cheap ugg boots
Money gayweather loves
Money gayweather loves moneyuggs online Pac-Manny running shoes0oz gloves?
ugg boots on saleandsaying there
uggs onlineand
ugg sale
andnike outlet
Dissent
A very good article. I am sure everyone will like search engine on rapidhshare( http://www.rapidsharemix.com ) files, with its help you will manage to find direct links for download from rapidshare
Post new comment