Copy cells and put them into columns

[bessieunl]

in sheet1, I have a column A, where it contains several groups of data (in number format), and each group start with a cell that has this Group's name (text), I wanna to be able to copy groups (including group name and data) based on desirable group names. Each group has different number of rows. So for example, I want to have group 1 and group 3 copied and paste into column A and Column B in sheet2.

Column A
Group1
.
.
.
.
Group2
.
.
.
.
.
.
.
Group3
.
.
.
.
.
.
.
.

 

[AliMass]

I'm not sure I understand. Is it something like:

Row 1: Group 1
Row 2: 1
Row 3: 3
Row 4: 100
Row 5: Group 2
Row 6: 44
Row 7: 8
Row 8: Group 3
etc.

And you want to copy items under certain groups.

My main thought is to insert a column to the left and have that column contain the group headings for every row. Thus (with semicolon separating the columns)

Row 1: Group 1; Group 1
Row 2: Group 1; 1
Row 3: Group 1; 3
Row 4: Group 1; 100
Row 5: Group 2; Group 2
Row 6: Group 2; 44
Row 7: Group 2; 8
Row 8: Group 3; Group 3
etc.

Then you can have a formula column C such as =IF(OR(A1={"Group 1","Group 3"}),B1,""). There are more flexible ways than the OR statement, such as having a list of the group names you want in a separate sheet, and using ISERROR(MATCH combination.

So the question is how to get that first column to contain the name of the groups. For that, I'd first have your data starting on row 2. Then put this formula in column A, starting on cell A2: =IF(ISNUMBER(B2),A1,B2). This will check column B, and if it's not a number (i.e., a group name), bring it over to column A. If it is a number (data), then copy the cell above (which is the group name).

 

[bessieunl]

I'm not sure I understand. Is it something like:

Row 1: Group 1
Row 2: 1
Row 3: 3
Row 4: 100
Row 5: Group 2
Row 6: 44
Row 7: 8
Row 8: Group 3
etc.

And you want to copy items under certain groups.

My main thought is to insert a column to the left and have that column contain the group headings for every row. Thus (with semicolon separating the columns)

Row 1: Group 1; Group 1
Row 2: Group 1; 1
Row 3: Group 1; 3
Row 4: Group 1; 100
Row 5: Group 2; Group 2
Row 6: Group 2; 44
Row 7: Group 2; 8
Row 8: Group 3; Group 3
etc.

Then you can have a formula column C such as =IF(OR(A1={"Group 1","Group 3"}),B1,""). There are more flexible ways than the OR statement, such as having a list of the group names you want in a separate sheet, and using ISERROR(MATCH combination.

So the question is how to get that first column to contain the name of the groups. For that, I'd first have your data starting on row 2. Then put this formula in column A, starting on cell A2: =IF(ISNUMBER(B2),A1,B2). This will check column B, and if it's not a number (i.e., a group name), bring it over to column A. If it is a number (data), then copy the cell above (which is the group name).

Is there a way to use macro to do the copy paste part? Everything else works out just fine, Thanks!

 

[AliMass]

I was trying to give a solution without a macro, as this would be easier to maintain and more visible to others who use it.

But here is a code I created. The data started in cell A1, and this copies whatever is under Group1 or Group3. It might be better to have a true/false or something in the cell next to each group heading to flag whether it should be copied, otherwise there is higher maintenance on the Select Case statement.

Option Explicit

Sub separate_groups()


    Dim objCell As Object
    Dim blnOkaytoCopy As Boolean
    
    blnOkaytoCopy = False
    For Each objCell In Range(Cells(1, 1), Cells(65000, 1).End(xlUp))
        If Application.WorksheetFunction.IsNumber(objCell) Then
            'There is a number, so test if we're okay to copy
            If blnOkaytoCopy Then
                objCell.Offset(0, 1) = objCell
            End If
        Else
            'Not a number, so it's a heading. Is this heading one we can copy?
            Select Case objCell
                Case "Group1", "Group3"
                    blnOkaytoCopy = True
                Case Else
                    blnOkaytoCopy = False
            End Select
        End If
    Next objCell
                
End Sub