Getting multiple columns into a Single Row

[squeakums]

Here is what I am having a problem with:

Example of excel sheet:

Test # po code 1 po code 2 po code 3

12345 p0098343 po123243 po4325435
12654 po121424



I'm trying to change this to be Test # and just have all PO codes listed in column B. Is there a macro or a formula that I can write to pull it to do as so?

Test # po codes
12345 p0098334
12345 po123243
12345 po4325435
12654 po121424

Therefore, lining all up even if duplicated test #'s with different po codes in column b? I am trying to create it this way to run pivots off of as well.

Any help would be appreciated.

Thanks!

 

[Aaron Blood]

Are the starting values for po codes in separate columns?
...or are we trying to parse a space-delimited string?

 

[squeakums]

Are the starting values for po codes in separate columns?
...or are we trying to parse a space-delimited string?

They showed up in separate columns when a text to columns was used. They were originally in one column all by theirselves with comas. Not all had 3 ..some had 1 value. So, now I am trying to get it to only be in two columns. Frustrating.

 

[Aaron Blood]

I see...

To be safe, let's assume we want to process the data without destroying it. So if you select the entire range you want to process and run this code, it would post the results to another worksheet tab named "Output".

Sub Table_to_Records()

    Dim RecRange As Range
    Set RecRange = Selection.Resize(Selection.Rows.Count, 1)

    Dim ProcRange As Range
    Set ProcRange = Selection.Offset(0, 1)
    Set ProcRange = ProcRange.Resize(ProcRange.Rows.Count, ProcRange.Columns.Count - 1)
    Set ProcRange = ProcRange.SpecialCells(xlCellTypeConstants)

    
    Dim ShtOut As Worksheet
    Set ShtOut = Sheets("Output")

    
    Dim cell As Range, TargCell As Range
    For Each cell In ProcRange
        Set TargCell = ShtOut.Cells(ShtOut.Rows.Count, 1).End(xlUp).Offset(1, 0)
        TargCell.Value = Intersect(cell.EntireRow, RecRange.EntireColumn).Value
        TargCell.Offset(0, 1).Value = cell.Value
    Next cell


End Sub

 

[SpillerBD]

Check Hikers code on this thread... http://www.mrexcel.com/forum/excel-questions/852326-converting-multiple-columns-into-rows-same-column.html

There are two other methods of Unpivoting data. If available the one in PowerQuery is the most flexible and easiest to use. There is a method using a trick with PivotTables and Multiple consolidation ranges; its flexible but a little more work. The macro is easiest, once set up for the correct dimensions of the table being unpivoted.

 

[Aaron Blood]

I think in this rare case... I like my code better.

 

[squeakums]

Could you please give me an example of how this macro would be filled in assuming that test # is in column A1:A500 and PO #'s are in columns D1:G500?

Thank you.

 

[Aaron Blood]

Sure...

Sub Table_to_Records()

    Dim RecRange As Range
    Set RecRange = Range("A:A")

    Dim ProcRange As Range
    Set ProcRange = Range("D1:G500").SpecialCells(xlCellTypeConstants)

    
    Dim ShtOut As Worksheet
    Set ShtOut = Sheets("Output")

    
    Dim cell As Range, TargCell As Range
    For Each cell In ProcRange
        Set TargCell = ShtOut.Cells(ShtOut.Rows.Count, 1).End(xlUp).Offset(1, 0)
        TargCell.Value = Intersect(cell.EntireRow, RecRange.EntireColumn).Value
        TargCell.Offset(0, 1).Value = cell.Value
    Next cell


End Sub

 

[SpillerBD]

Could you please give me an example of how this macro would be filled in assuming that test # is in column A1:A500 and PO #'s are in columns D1:G500?

Thank you.

Aaron, and then the OP changes the conditions....
Your original code is sweet because it works on a selected range and not a specific range (specific case.) Great flexibility that way!

 

[Aaron Blood]

It happens...