Column Data Transpose With Repeated Row Data

ncikusa · Mar 9th 2011

Hi guys,

I need to 'kind of' transpose a range of data and I have thousands of data cells so I guess a VBA macro would do the trick, however, I'm fairly n00b for that stuff, and I hoped one of you guys could help me out. You can see the attached example in regard to what I'd like to do with data; the input sheet obviously shows the data format I have right now, while the output sheet shows how I'd like data to be. It pretty much resembles a transpose of data, but with a twist though...

(Btw, numbers 1-15 represent my data points, thus they range arbitrarily between empty cells (de facto zero), zero's and several hundred thousands.. hence, don't mind the linearity of the range 1 to 15... that's simply because that was easier to write :-p)

In short, what I want to do is a transpose with a twist. I have something like:

a, 1, 2
b, 5, 6
c, 10,15

and I'd like:
a, 1
a, 2
b, 5
b, 6
c, 10
c, 15

So kind of like a transpose, but keeping the records next to each value - see transpose.xls (input -> output) for more

Hope anyone can help - fire away if you need more info!

Best regards,
Niko

**iwrk4dedpr** · Mar 9th 2011

Re: Help needed for advanced transposing (probably VBA code)

Niko....

Code

Option Explicit


Sub MakeOutputTable()


'   Local Variables
    Dim strInput            As String
        Dim strOutput       As String
    Dim cell                As Range
        Dim rngOut          As Range
        Dim rngData         As Range
        Dim rngHeader       As Range


'   Set sheet names
    strInput = "input"
    strOutput = "output"


'   Loop through input
    For Each cell In Worksheets(strInput).Range("A2", Worksheets(strInput).Range("A65536").End(xlUp))
        For Each rngHeader In Worksheets(strInput).Range("F1", Worksheets(strInput).Range("IV1").End(xlToLeft))
            Set rngData = Intersect(cell.EntireRow, rngHeader.EntireColumn)
            If rngData <> "" Then
                Set rngOut = Worksheets(strOutput).Range("A65536").End(xlUp).Offset(1, 0)
                rngOut.Range("A1:D1") = cell.Range("A1:D1").Value
                rngOut.Range("E1") = rngHeader
                rngOut.Range("F1") = rngData
            End If
        Next rngHeader
    Next cell


End Sub

Display More

ncikusa · Mar 9th 2011

Re: Help needed for advanced transposing (probably VBA code)

OMG. Yeah, that's right - a puberty-ridden answer is needed... That's so awesome iwrk! - Thank you very very much!

Stanley D. Grom · Mar 9th 2011

Re: Help needed for advanced transposing (probably VBA code)

ncikusa,

Welcome to the Ozgrid forum.

With your raw data in worksheet Sheet1, a new worksheet Results will be created with your data transposed per your request.

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Code

Option Explicit
Sub ReorgData()
' stanleydgromjr, 03/09/2011
' [URL]http://www.ozgrid.com/forum/showthread.php?t=151467[/URL]
Dim w1 As Worksheet, wR As Worksheet
Dim a As Long, aa As Long, LR As Long, LC As Long, NR As Long
Application.ScreenUpdating = False
Set w1 = Worksheets("Sheet1")
If Not Evaluate("ISREF(Results!A1)") Then Worksheets.Add(After:=w1).Name = "Results"
Set wR = Worksheets("Results")
wR.UsedRange.Clear
Set wR = Worksheets("Results")
LR = w1.Cells(Rows.Count, 1).End(xlUp).Row
NR = 0
LC = w1.Cells(1, Columns.Count).End(xlToLeft).Column
For a = 2 To LR Step 1
  For aa = 6 To LC Step 1
    If w1.Cells(a, aa) <> "" Then
      NR = NR + 1
      wR.Range("A" & NR).Resize(, 5).Value = w1.Range("A" & a).Resize(, 5).Value
      w1.Cells(1, aa).Copy wR.Range("F" & NR)
      wR.Range("G" & NR).Value = w1.Cells(a, aa)
    End If
  Next aa
Next a
wR.UsedRange.Columns.AutoFit
wR.Activate
Application.ScreenUpdating = True
End Sub

Display More

Then run the ReorgData macro.

Have a great day,
Stan

**S M C** · Mar 9th 2011

Re: Column Data Transpose With Repeated Row Data

Hi ncikusa,

Welcome to Ozgrid.

Please take note of the following regarding thread titles:

Thread titles are used in searching the forum, therefore, it is vital the be written to accurately describe your _thread content or overall objective_ using ONLY *search friendly key words*.

* The title must not use non-essential words such as:"Help needed", "Formula problem", "Please help", "urgent", "Code issue", "Need Advice", etc. Such words dilute the title/search results.
* The title should not contain VBA code or formula syntax or use abbreviations, jargon, delimiters
* The title should not assume or anticipate a solution as in referencing Excel functions or VBA methods - the actual solution is often quite different

ncikusa · Mar 9th 2011

Re: Help needed for advanced transposing (probably VBA code)

Hi guys,

Huge thanks go to both of you.

On another note, I actually forgot one column... the names (name1, name2, name3 etc.). Could either of you help adding this column to the output as well? I've added it in red in the attached output sheet (transpose_new.xls), so you know what I mean.

Also, Stan, when running your code version I get a 'runtime error '9' - subscript out of range' error?

iwrk, I have a whole bunch of lines and columns of this sort... hundreds.... (effectively giving thousands of rows) and when running your code, it seems to stop after some 10.400 records or so?

I really appreciate all your help, guys!

Regards,
Niko

**iwrk4dedpr** · Mar 9th 2011

Re: Column Data Transpose With Repeated Row Data

Niko...

You'll need to tell me WHY it's stopping... Is it filling 65536 rows of data?

Here's the code change

Code

Option Explicit


Sub MakeOutputTable()


'   Local Variables
    Dim strInput            As String
        Dim strOutput       As String
    Dim cell                As Range
        Dim rngOut          As Range
        Dim rngData         As Range
        Dim rngHeader       As Range


'   Set sheet names
    strInput = "input"
    strOutput = "output"


'   Loop through input
    For Each cell In Worksheets(strInput).Range("A2", Worksheets(strInput).Range("A65536").End(xlUp))
        For Each rngHeader In Worksheets(strInput).Range("F1", Worksheets(strInput).Range("IV1").End(xlToLeft))
            Set rngData = Intersect(cell.EntireRow, rngHeader.EntireColumn)
            If rngData <> "" Then
                Set rngOut = Worksheets(strOutput).Range("A65536").End(xlUp).Offset(1, 0)
                rngOut.Range("A1:E1") = cell.Range("A1:E1").Value
                rngOut.Range("F1") = rngHeader
                rngOut.Range("G1") = rngData
            End If
        Next rngHeader
    Next cell


End Sub

Display More

Stanley D. Grom · Mar 9th 2011

Re: Column Data Transpose With Repeated Row Data

ncikusa,

With your raw data in worksheet input, and worksheet output will contain the re-organized data.

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Code

Option Explicit
Sub ReorgDataV2()
' stanleydgromjr, 03/09/2011
' [URL]http://www.ozgrid.com/forum/showthread.php?t=151467[/URL]
Dim w1 As Worksheet, wR As Worksheet
Dim a As Long, aa As Long, LR As Long, LC As Long, NR As Long
Application.ScreenUpdating = False
Set w1 = Worksheets("input")
If Not Evaluate("ISREF(output!A1)") Then Worksheets.Add(After:=w1).Name = "output"
Set wR = Worksheets("output")
wR.UsedRange.Clear
Set wR = Worksheets("output")
LR = w1.Cells(Rows.Count, 1).End(xlUp).Row
NR = 0
LC = w1.Cells(1, Columns.Count).End(xlToLeft).Column
For a = 2 To LR Step 1
  For aa = 6 To LC Step 1
    If w1.Cells(a, aa) <> "" Then
      NR = NR + 1
      wR.Range("A" & NR).Resize(, 5).Value = w1.Range("A" & a).Resize(, 5).Value
      w1.Cells(1, aa).Copy wR.Range("F" & NR)
      wR.Range("G" & NR).Value = w1.Cells(a, aa)
    End If
  Next aa
Next a
wR.UsedRange.Columns.AutoFit
wR.Activate
Application.ScreenUpdating = True
End Sub

Display More

Then run the ReorgDataV2 macro.

ncikusa · Mar 9th 2011

Re: Column Data Transpose With Repeated Row Data

Hi guys,

Again, thanks for all your help... it's really appreciated!

Barry, I think your code is probably OK, however the months are made into values and I thought I only needed to format the values to months, but that simply shows the months looping within the year or so - and I have several months - for a couple of years.

Stan, I think your code is spot on... i'll check it out more thoroughly tomorrow

Anyway, both you guys, sincerely... thank you. I wish I culd do that geekish coding; I do know a little VBA, but we're talking pure intro level stuff

Best regards,

Niko

**iwrk4dedpr** · Mar 9th 2011

Re: Column Data Transpose With Repeated Row Data

Awwww Niko,

Ya breaking my heart here... My code wasn't good enough... Sniff Sniff

Stan cheated... he copy and pasted values.... All you had to do was format the column the same as you had formated those headers...

It's ok tho... I've got soft forgiving ego (lol)

I like my solution better tho.. it's cleaner and simpler (IMHO)

Although STAN I'm not sure we should take the "GEEK" thing laying down.

Stanley D. Grom · Mar 10th 2011

Re: Column Data Transpose With Repeated Row Data

ncikusa,

Quote

I wish I culd do that geekish coding; I do know a little VBA, but we're talking pure intro level stuff

Training / Books / Sites:

How to Learn to Write Macros
http://articles.excelyogi.com/playin...ba/2008/10/27/

How to use the macro recorder
http://articles.excelyogi.com/

Click here and scroll down to Getting Started with VBA.
http://www.datapigtechnologies.com/ExcelMain.htm

If you are serious about learning VBA try
http://www.add-ins.com/vbhelp.htm

Excel Tutorials and Tips - VBA - macros - training
http://www.mrexcel.com/articles.shtml

See David McRitchie's site if you just started with VBA
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Debra Dalgleish has some notes how to implement macros here:
Excel VBA -- Adding Code to a Workbook
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

Creating custom functions
http://office.microsoft.com/en-us/excel/HA011117011033.aspx

Writing Your First VBA Function in Excel
http://www.exceltip.com/st/Wri…unction_in_Excel/631.html
http://www.excel-vba.com/
http://www.mvps.org/dmcritchie/excel/getstarted.htm
http://www.exceltip.com/excel_links.html

(livelessons video)
Excel VBA and Macros with MrExcel
ISBN: 0-7897-3938-0
http://www.amazon.com/Excel-Macros-M...7936479&sr=1-1
http://www.youtube.com/user/ExcelIsFun#g/search
http://www.xl-central.com/index.html
http://www.datapigtechnologies.com/ExcelMain.htm

Dependent validation lists. Debra has a neat little tutorial here.
http://www.contextures.com/xlDataVal02.html

Data Validation > Drop-Down Lists - Dependent
http://www.bettersolutions.com/excel/EGH188/QE229212022.htm

Cascading queries
http://www.tushar-mehta.com/excel/ne...ing_dropdowns/
http://www.contextures.com/xlDataVal05.html

Excel Data Validation - Add New Items
http://www.contextures.com/excel-data-validation-add.html

Programming The VBA Editor - Created by Chip Pearson at Pearson Software Consulting LLC
This page describes how to write code that modifies or reads other VBA code.
http://www.cpearson.com/Excel/vbe.aspx

Locating files containing VBA
Searching Files in Subfolders for VBA code string:
http://www.dailydoseofexcel.com/arch...a-code-string/
http://www.pcreview.co.uk/forums/thread-978054.php

Excel 2003 Power Programming with VBA, by John Walkenbach

VBA and Macros for Microsoft Excel, by Bill Jelen "Mr.Excel" and Tracy Syrstad

Excel Hacks 100 Industrial-Strength Tips & Tools, by David & Traina Hawley

VBA and Macros for Microsoft Excel 2007, by Bill Jelen "Mr.Excel" and Tracy Syrstad
Excel 2007 Book: you can try this...there is a try before you buy ebook available at this link…
http://www.mrexcel.com/learnexcel2.shtml

DonkeyOte: My Recommended Reading:

Volatility
http://www.decisionmodels.com/calcsecretsi.htm

Sumproduct
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Introduction to Array Formulas
http://www.xtremevbtalk.com/showthread.php?t=296012

Using Pivot Tables and Pivot Charts in Microsoft Excel
http://peltiertech.com/Excel/Pivots/pivotstart.htm

A List of Pivot Table links at Jon Peltier's site (contributed by Debra Dalgleish) is here:
http://peltiertech.com/Excel/Pivots/pivotlinks.htm

Email from XL - VBA & Outlook VBA
http://www.rondebruin.nl/sendmail.htm
http://www.outlookcode.com/article.aspx?ID=40

Excel Function Dictionary
http://www.xlfdic.com/

Excel 2007 function name translations
http://www.piuha.fi/excel-function-name-translation/

Dynamic Named Ranges
http://www.contextures.com/xlNames01.html

Where to paste code in VBE VBA
Introducing the Excel VBA Editor
http://www.ask.com/web?qsrc=24…e+in+the+Excel+VBA+Editor

VBA for Excel (Macros)
http://www.excel-vba.com/excel-vba-contents.htm

VBA Lesson 11: VBA Code General Tips and General Vocabulary
http://www.excel-vba.com/vba-code-2-1-tips.htm

Excel VBA -- Adding Code to a Workbook
http://www.contextures.com/xlvba01.html

Basics of array formulas
http://www.youtube.com/view_play_lis...7E7E9CA63304D3

Array formula data extract formulas
http://www.youtube.com/watch?v=Tp7I5u1MqiM
http://www.youtube.com/watch?v=R5ZWAiNJLNo
http://www.youtube.com/watch?v=132ZdpxBm1U

How to create Excel Dashboards
http://www.contextures.com/excel-dashboards.html
http://chandoo.org/wp/excel-dashboards/

Unique Record Counting and Data Extract formulas
http://www.youtube.com/watch?v=uUrI8hoj8BA

ncikusa · Mar 10th 2011

Re: Column Data Transpose With Repeated Row Data

LOL - if that's not a lot of reading material... i don't know what is - thanks for the info!

Barry; nothing personal intended - I really am happy for all your help

Stan, in your code, would it be possible to make the 'transposing' of datapoints include formulas and comments - perhaps 'All', as Excel would say, instead of values?

See attachment (transpose_new2.xls) in which the top data is an example; both 'hardcoded' values and formulas and comments would be nice to have as in the output sheet, such that everything from the cell is transposed. If it is a hardcoded value then transpose the hardcoded value, if it is a formula, make the formula point to the right cells (which is always in the same row on the input sheet) etc.

Best regards,

Niko

Stanley D. Grom · Mar 14th 2011

Re: Column Data Transpose With Repeated Row Data

ncikusa,

Based on your latest request, and with your latest workbooks raw data, try this:

Detach/open workbook ReorgData - w1 w2 - ncikusa - OZ151467 - V3 - SDG15.xls and run macro ReorgDataV3.

If your data has more than three months (and, I am sure it does/will), the macro will not work correctly.

I am not sure how to adjust the latest macro for more than three months of data.

Have a great day,
Stan

Stanley D. Grom · Mar 14th 2011

Re: Column Data Transpose With Repeated Row Data

ncikusa,

The following macro will copy formatting, comments, and adjust formulae to values, and has been tested with more monthy columns to the right (see worksheet Instructions for other sample raw data).

Detach/open workbook ReorgData - w1 w2 - ncikusa - OZ151467 - V4 - SDG15.xls and run macro ReorgDataV4.

Have a great day,
Stan

ncikusa · Mar 15th 2011

Re: Column Data Transpose With Repeated Row Data

Hi Stan,

Looks very neat... thank you again for all your help! - I'll cope with the three months... I'll just run in over a few times, no biggie!

Best regards,
Niko

ncikusa · Mar 16th 2011

Re: Column Data Transpose With Repeated Row Data

Hi again Stan,

Actually, in your code, you assume that the formulas I put in the example was the formulas to be used again and again, however, those were merely examples. What I'd like was that formulas in the input sheet is simply 'transposed' as well with the data in the output sheet. For instance, when you transpose a row of, say, hardcoded values and formulas, what you get is a column of the same data in which the formulas have also been transposed, referencing to the correct cells in the new column (that was cells in rows before).

Is this possible to do in your code?

Best regards,
Niko

Stanley D. Grom · Mar 16th 2011

Re: Column Data Transpose With Repeated Row Data

ncikusa,

Detach/open workbook ReorgData - w1 w2 - ncikusa - OZ151467 - V5 - SDG15.xls and run macro ReorgDataV5.

Have a great day,
Stan

ncikusa · Mar 17th 2011

Re: Column Data Transpose With Repeated Row Data

Stan,

I'm astonished! - Looks like it does exactly what it should... and it seems it can take a lot of months!

I'm so forever grateful!

Best regards,
Niko

mslabbe · Jan 12th 2021

Hello -

I just joined today...but long long time user of this form with excel questions.

I realize that this thread is 10 years old, but it is very close to what I'm looking for. So, instead of posting a new thread, I'm hoping someone will see this.

I'll reference the 'Input' tab in the attached 'ReorgData - w1 w2 - ncikusa - OZ151467 - V5 - SDG15.xls' excel sheet. Disregard Columns A, B and C.

I'm trying to move info similar fashion, to create two different ordering sheets based on one of the columns...for this, let's say that Column D...so, we would have two new separate tabs, 'business1' and 'business2' to move data to instead of 'output'. So, in these two new sheets, the first column would be the dates that are currently in F1:H1, instead of the last column that is currently being moved to. The next column would be the Column E, placed to the correct sheet AND only if there are numbers present in F2:H6. So, for example, if G4 is 0 or blank, the Feb-11 date will not be moved, only the G6 line, to the 'business2' sheet.

Thanks for the help...I can create a sheet to what I'd like the final product to be IF that would be more helpful.

Matt

mslabbe · Jan 13th 2021

Oops!

I think I violated the rules by hi-jacking this thread...my apologies.

I've been in other forums that would prefer this.

New post coming.

Column Data Transpose With Repeated Row Data

Participate now!

Share