Combine data from several worksheets into one with conditions

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?

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.

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

sample.xlsx

Chengo

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.

Please Login or Register  to view this content.

Any suggestions?