Rearranging Data

**uberathlete** · 09-05-2014, 07:06 PM

Hi everyone

Please see attached.

In the Initial sheet, I have data where column A lists products and columns B onwards lists serial numbers for those products.

So, Widget A (cell A1) has serial numbers listed in B1 to D1
Widget B (cell A4) has serial numbers listed in B4 to H4
And vice versa

I would like to order the data such that the serial numbers are listed in one column (col B) and in column A it lists the product. The desired result is in the Result sheet.

Is there a way to do this via macro? Any assistance would be very much appreciated.

**JBeaucaire** · 09-05-2014, 07:32 PM

Here's a published macro that pretty much does exactly that:

'ROWS TO COLUMNS
Here's a macro for expanding rows of data to multiple rows parsing out the values in the row while duplicating the header row as categories. There's a sample workbook too you could drop your data into and test it out.

Rows to Columns - Part 1a

**kalak** · 09-06-2014, 05:16 AM

Originally Posted by uberathlete

Any assistance would be very much appreciated.

here's a bit more

Sub two_cols()
Dim a, u(), c As Long, i As Long, j As Long
a = Cells(1).CurrentRegion
ReDim u(1 To UBound(a, 1) * UBound(a, 2), 1 To 2)
For i = 1 To UBound(a, 1)
    For j = 2 To UBound(a, 2)
        If Len(a(i, j)) > 0 Then
            c = c + 1
            u(c, 1) = a(i, 1)
            u(c, 2) = a(i, j)
        End If
    Next j
Next i
Cells(UBound(a, 1) + 2, 1).Resize(c, 2) = u
End Sub

**uberathlete** · 09-06-2014, 12:07 PM

Thank you so much JBeaucaire and kalak! These are exactly what I was hoping for.

Rearranging Data

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Rearranging Data

Re: Rearranging Data

Re: Rearranging Data

Re: Rearranging Data

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] rearranging data using VBA

rearranging data

Rearranging data

need: Rearranging data

Need help on rearranging data

Bookmarks

Bookmarks

Posting Permissions