PDA

View Full Version : Combine data from several worksheets into one with conditions



Chengo
03-27-2012, 06:46 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.

I have included a sample file for your reference.

Any help is most welcomed.

7738

CatDaddy
03-27-2012, 10:58 AM
are you trying to remove rows with SP0 in the third column?

CatDaddy
03-27-2012, 11:54 AM
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

'Delete SP0
Sheets(4).Activate
For Each cell In Range("C2:C" & Range("A65536").End(xlUp).Row)
If InStr(cell.Text, "SP0") Then
cell.EntireRow.Delete shift:=xlUp
End If
Next cell
End Sub

Almost works, for some reason it is not deleting one line with SP0 in it...not sure why

Chengo
03-27-2012, 04:41 PM
Hi CatDaddy,

This is exactly what I'm looking for, if only this one line gets sorted as well. What do you think keeps this one instance of SP0 still in the list?

CatDaddy
03-29-2012, 09:15 AM
no clue, sorry mate

Chengo
03-29-2012, 04:53 PM
Hi,

The code 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.

Also I've decided to hide the rows with SP0 in them so I've changed the code to relflect the same.

'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?