Combine data from several worksheets into one with conditions

**Chengo** · 03-26-2012, 08:45 AM

Hi all,

I'm looking for help with my data sorting.

I have 3 different Worksheets with the following data and I would like to combine them into the 4th Worksheet. But there are some requirements:

1. I’d like to have rows with same serial number under each other (row from Worksheet 1, then from Worksheet 2, and then from Worksheet 3) but so that the order from Worksheet 1 stays unchanged.
2. Then I’d like to remove any row that contains SP0 in it.

Sheet1
CZ32089DM9 U1→NIC1 SwA→SP24
CZ32089DMD U3→NIC1 SwA→SP23
CZ32089DMB U6→NIC1 SwA→SP0
CZ32089D7D U4→NIC1 SwA→SP18

Sheet2
CZ32089DM9 U1→NIC2 SwB→SP22
CZ32089DMD U3→NIC2 SwB→SP0
CZ32089DMB U6→NIC2 SwB→SP0
CZ32089D7D U4→NIC2 SwB→SP0

Sheet3
CZ32089DM9 U1→ILO SwB→SP41
CZ32089DMD U3→ILO SwB→SP43
CZ32089DMB U6→ILO SwB→SP43
CZ32089D7D U4→ILO SwB→SP39

And the result would be like this, except at the end I'd like to have rows with SP0 in it deleted.

Sheet4
CZ32089DM9 U1→NIC1 SwA→SP24
CZ32089DM9 U1→NIC2 SwB→SP22
CZ32089DM9 U1→ILO SwB→SP41
CZ32089DMD U3→NIC1 SwA→SP23
CZ32089DMD U3→NIC2 SwB→SP0
CZ32089DMD U3→ILO SwB→SP43
CZ32089DMB U6→NIC1 SwA→SP0
CZ32089DMB U6→NIC2 SwB→SP0
CZ32089DMB U6→ILO SwB→SP43
CZ32089D7D U4→NIC1 SwA→SP18
CZ32089D7D U4→NIC2 SwB→SP0
CZ32089D7D U4→ILO SwB→SP39

Is there a formula or macro I could run to get the result I'm looking for?

**arlu1201** · 03-26-2012, 08:53 AM

Sheet1
CZ32089DM9 U1→NIC1 SwA→SP24
CZ32089DMD U3→NIC1 SwA→SP23

Are the above entries in 1 column or multiple? Do you have a sample file that you could upload with some dummy data so we can understand your data layout?

**firefly2k8** · 03-26-2012, 08:57 AM

Have you tried copying and pasting all from sheet1,2,3 into sheet 4. Then selecting and doing a data sort by the column with the serial.

To delete the rows with SP0, add a data filter (data>filter>autofilter), then custom filter (from the drop down, select custom), 'contains' (from the drop down select 'contains'), type in "SP0". Then highlight the filtered rows and right-click, delete.

**Chengo** · 03-26-2012, 11:41 AM

I've included a sample file.
Data is in 3 columns: Serial, Location, Port

sample.xlsx

Chengo

**Chengo** · 03-29-2012, 07:36 PM

I have a piece of code that works on my sample data but I need some help to make it work in project spreadsheet.
Code currently calls out Sheets 1, 2 and 3 and put the results in Sheet 4.
I'd like it to call out data from renamed sheets - NIC1, NIC2 and ILO and put the results in sheet named ORDER.
I need to change it because I have more sheets in use and some of them are hidden. Also sheets NIC1, NIC2 and ILO are not first 3 sheets in the workbook.

Sub sort_delete() 
    Dim cell As Range, cell2 As Range 
    Dim r As Long, r2 As Long, a As Long 
    Dim f As Integer, sh As Integer 
    ActiveWorkbook.Sheets(1).Activate 
     'In Order Copy/Paste
    Range("A1:C" & Range("A65536").End(xlUp).Row).Copy _ 
    Destination:=Sheets(4).Range("A1") 
    For sh = 2 To 3 
        Sheets(sh).Activate 
        For Each cell In Range("A2:A" & Range("A65536").End(xlUp).Row) 
            r = cell.Row 
            f = 0 
             
            For a = Sheets(4).Range("A65536").End(xlUp).Row To 2 Step -1 
                 
                If StrComp(cell.Text, Sheets(4).Range("A" & a).Text, vbTextCompare) = 0 Then 
                     
                    Sheets(sh).Select 
                    Rows(r).Select 
                    Application.CutCopyMode = False 
                    Selection.Copy 
                    Sheets(4).Select 
                    Rows(a + 1).Select 
                    Selection.Insert shift:=xlDown 
                    f = 1 
                    Exit For 
                     
                End If 
            Next a 
             
            If f = 0 Then 
                cell.EntireRow.Copy Destination:=Sheets(4).Range("A" & Range("A65536").End(xlUp).Offset(1, 0).Row) 
            End If 
        Next cell 
    Next sh 
     
     'Hide SP0
    Sheets(4).Activate 
    For Each cell In Range("C2:C" & Range("A65536").End(xlUp).Row) 
        If InStr(cell.Text, "SP0") Then 
            cell.EntireRow.Hidden = True
        End If 
    Next cell 
End Sub

Any suggestions?

**arlu1201** · 04-03-2012, 09:27 AM

Try this code instead -

Option Explicit

Sub cons_data()
Dim i As Long
Dim lrow As Long

Worksheets.Add(after:=Worksheets(Worksheets.Count)).Name = "Summary"
With Worksheets("Summary")
    .Range("A1").Value = "Serial"
    .Range("B1").Value = "Location"
    .Range("C1").Value = "Port"
End With

For i = 1 To Worksheets.Count
    With Worksheets(i)
        If .Name <> "Summary" Then
            lrow = .Range("A" & .Rows.Count).End(xlUp).Row
            .Range("A2:C" & lrow).Copy Worksheets("Summary").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
        End If
    End With
Next i

With Worksheets("Summary")
    .Cells.EntireColumn.AutoFit
   .Sort.SortFields.Clear
   .Sort.SortFields.Add Key:=Range("A:A") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    .Sort
        .SetRange Range("A:C")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
       
    lrow = .Range("A" & .Rows.Count).End(xlUp).Row
    For i = lrow To 2 Step -1
        If Right(.Range("C" & i).Value, 3) = "SP0" Then
            .Rows(i & ":" & i).Delete
            lrow = lrow - 1
        End If
    Next i
End With
End Sub

**shg** · 04-03-2012, 11:10 AM

Arlu, you might want to relook at some of your code:

    With Worksheets("Summary")
        .Cells.EntireColumn.AutoFit
        ActiveWorkbook.Worksheets("Summary").Sort.SortFields.Clear
        ActiveWorkbook.Worksheets("Summary").Sort.SortFields.Add _
            Key:=Range("A:A"),_
            SortOn:=xlSortOnValues, _
            Order:=xlAscending, _
            DataOption:=xlSortNormal
        With ActiveWorkbook.Worksheets("Summary").Sort
            .SetRange Range("A:C")
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With

**arlu1201** · 04-03-2012, 11:39 AM

Thanks shg. Got it corrected.

**snb** · 04-03-2012, 12:25 PM

Sub snb()
  With Sheets("Sheet4")
    For Each sh In Sheets
      If sh.Name <> .Name Then
        sh.Cells(1).CurrentRegion.Offset(iif(.cells(1)="",0,1)).Copy .Cells(Rows.Count, 1).End(xlUp).Offset(iif(.cells(1)="",0,1))
      End If
    Next

    With .Cells(1).CurrentRegion
      .AutoFilter 3, "*SP0*"
      .Offset(1).SpecialCells(12).EntireRow.Delete
      .AutoFilter
   End With
        
    Application.AddCustomList Application.Transpose(Sheets("Sheet1").Cells(1).CurrentRegion.Columns(1))
    .Cells(1).CurrentRegion.Sort .Cells(1), Header=:xlYes,Ordercustom:=Application.CustomListCount
  End With
End Sub