Need Macro help

Seanzies

Board Regular
Joined
Nov 19, 2005
Messages
212
I need a macro for the following info
I have a 5 column list with a long row list that I want to take some of the info and but it into rows based on this criteria
This is all in Sheet1

Here is an example of items in the list....
A.................B
B12DST30....32
B12DST30....34
B12DST30....36
B12DST30....38
B74BRN30....30
B74BRN30....32
B74BRN32....30
B74BRN32....32
B74BRN32....34
C19BRNREG..SML
C19BRNREG..MED
C19BRNREG..LRG


I want to take all of the column A's items that are the same style and take Column B info and take the data and put it in a row format on Sheet2 starting with cell A1. Then do the same for the next item. Heres and example of what it should look like on Sheet2
A...................B.......C......D......E.......F
B12DST30......32.....34.....36....38
B74BRN30......30.....32
B74BRN32......30.....32.....34
C19BRNREG...SML...MED...LRG

Any Suggestions, there's got to be a way!!
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Try:

Code:
Sub Test()
    Dim Sh As Worksheet
    Dim Rng As Range
    Dim ShNew As Worksheet
    Dim r As Long
    Dim c As Integer
    Dim x As Range
    Set Sh = Worksheets("Sheet1")
    Set Rng = Sh.Range("A1:A" & Sh.Range("A65536").End(xlUp).Row)
    Set ShNew = Worksheets.Add
    r = 1
    c = 2
    For Each x In Rng
        If x.Value = x.Offset(1, 0) Then
            If c = 2 Then
                ShNew.Cells(r, 1) = x.Value
                ShNew.Cells(r, c) = x.Offset(0, 1).Value
            Else
                ShNew.Cells(r, c) = x.Offset(0, 1).Value
            End If
            c = c + 1
        Else
            ShNew.Cells(r, c) = x.Offset(0, 1).Value
            r = r + 1
            c = 2
        End If
    Next x
    ShNew.Range("A1").CurrentRegion.EntireColumn.AutoFit
End Sub
 

Seanzies

Board Regular
Joined
Nov 19, 2005
Messages
212
Andrew, first, Thank you for the reply.
I tried it and when I run it all it does is do the first item and paste what's in Sheet1 cell B1 on to the new sheet cell B1.
Any Suggestions?
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Seanzies said:
Andrew, first, Thank you for the reply.
I tried it and when I run it all it does is do the first item and paste what's in Sheet1 cell B1 on to the new sheet cell B1.
Any Suggestions?

With the sample data you posted in the range A1:B12 on Sheet1, the macro produced what you required on a new worksheet.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,018
Messages
5,569,652
Members
412,286
Latest member
kychemist00
Top