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!!
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,129
Messages
5,857,537
Members
431,884
Latest member
Gcmoore63

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top