Trying to create a new excel sheet which is a summary of another sheet of data with conditions

Question

I have a large data sheet 40 columns x 10000 rows which I want to abridge in a new sheet ready for export to a csv file.

The main data sheets relevant headings are:-

The new sheet wants to populate columns: segment, group, email, surname, first name and mobile number if "List" is equal to a value (in this case "Motorbike Sales"), "Unsubscribed" is NOT equal to -1 and "email" is NOT blank.

At first I tried vlookup but it appears that this only works if everything in the "List" column is a unique value. In my case their are many "Motorcycle Sales" records.

I also tried some sample VBA code which did populate the new sheet but it would take forever and often crashed excel. I also couldn't work out how to add the conditions I require.

My sample code is below. Could someone help please - I have already lost a day and a half looking at on-line examples, tutorials and books. I feel like I am missing something simple as I would expect this kind of sorting would be regular practice.

Thanks in advance

Private Sub Workbook_Open()
Dim i, LastRow
LastRow = Sheets("THS Contact Database").Range("A" & Rows.Count).End(xlUp).Row
Sheets("Motorbike Sales").Range("A2:AR10000").ClearContents
For i = 2 To LastRow
If Sheets("THS Contact Database").Cells(i, "A").Value = "Motorbike Sales" Then
Sheets("THS Contact Database").Cells(i, "A").EntireRow.Copy Destination:=Sheets("Motorbike Sales").Range("A" & Rows.Count).End(xlUp).Offset(1)
End If
Next i
End Sub

pnuts pnuts · Accepted Answer · 2015-03-19T04:34:50

Copy the entire sheet, filter on List to select and delete all rows not Motorbike, filter on Unsubscribed to select and delete all -1, filter on email to select and delete all (Blank).

Then delete columns List and Unsubscribed.

Trying to create a new excel sheet which is a summary of another sheet of data with conditions

1 Answers