Separate data onto multiple tabs based on partial cell matc

C

colinh69

New Member
Joined
Feb 25, 2011
Messages
8
Hi,

I could really use some help here. I have been working on this for a week now and have only gotten so far as to create a new worksheet for each unique cell in column A but I need to search column A and copy each row based on partial match because I have a few cells with the similar contents and I need these grouped together.

I have 5 rows A-F and in the real sheet there are 1000's of rows.

I have 6 tabs 1 main tab with all the info and then the following "palace" "amusement" "caravan" "house" "other"

I want to be able to press a button on the main tab and have it copy and paste based on partial match of a cell. For example if "palace" is mentioned anywhere in a cell in row A then copy that row into "palace" tab.

I have copied a sample of the sheet and info on it.

Thanks

Customer County Start Finish Notes
Arthur Data Data Data Data Data
James Data Data Data Data Data
Mark Data Data Data Data Data
amusement, Kilkenny Data Data Data Data Data
Andrew Data Data Data Data Data
George Data Data Data Data Data
Old One Data Data Data Data Data
Pub Data Data Data Data Data
house, Castlerea Data Data Data Data Data
amusement, Carlow Data Data Data Data Data
amusement, Gorey Data Data Data Data Data
amusement, Waterford Data Data Data Data Data
James Data Data Data Data Data
Pub Data Data Data Data Data
Pub Data Data Data Data Data
Pub Data Data Data Data Data
amusement, Cashel Data Data Data Data Data
amusement, Wicklow Data Data Data Data Data
Pub Data Data Data Data Data
amusement, Carlow Data Data Data Data Data
amusement, Cashel Data Data Data Data Data
amusement, Waterford Data Data Data Data Data
amusement, Wicklow Data Data Data Data Data
Conway, John Data Data Data Data Data
O'Kelly, Regina Data Data Data Data Data
amusement, Cashel Data Data Data Data Data
O'Kelly, Regina Data Data Data Data Data
amusement, Arklow Data Data Data Data Data
amusement, Waterford Data Data Data Data Data
amusement, Wexford Data Data Data Data Data
house, Castlerea Data Data Data Data Data
palace, Newcastle West Data Data Data Data Data
amusement, Clonmel Data Data Data Data Data
amusement, New Ross Data Data Data Data Data
amusement, Wexford Data Data Data Data Data
amusement, Wicklow Data Data Data Data Data
house, Castlerea Data Data Data Data Data
amusement, Carlow Data Data Data Data Data
amusement, Carlow Data Data Data Data Data
amusement, Clonmel Data Data Data Data Data
amusement, Gorey Data Data Data Data Data
amusement, Tipperary Data Data Data Data Data
amusement, Wicklow Data Data Data Data Data
amusement, Wicklow Data Data Data Data Data
house, Castlerea Data Data Data Data Data
house, Castlerea Data Data Data Data Data
caravan, Fermoy Data Data Data Data Data
caravan, Tralee Data Data Data Data Data
amusement, Carlow Data Data Data Data Data
amusement, Clonmel Data Data Data Data Data
amusement, Gorey Data Data Data Data Data
amusement, Gorey Data Data Data Data Data
amusement, Tipperary Data Data Data Data Data
amusement, Wicklow Data Data Data Data Data
caravan, Fermoy Data Data Data Data Data
amusement, Carlow Data Data Data Data Data
amusement, Cashel Data Data Data Data Data
amusement, Wicklow Data Data Data Data Data
palace, Hospital Data Data Data Data Data
caravan, Fermoy Data Data Data Data Data
amusement, Arklow Data Data Data Data Data
amusement, Carlow Data Data Data Data Data
amusement, Cashel Data Data Data Data Data
amusement, Wicklow Data Data Data Data Data
Ross Data Data Data Data Data
caravan, Fermoy Data Data Data Data Data
amusement, Carlow Data Data Data Data Data
amusement, Clonmel Data Data Data Data Data
amusement, Tipperary Data Data Data Data Data
amusement, Wicklow Data Data Data Data Data
palace, Newcastle West Data Data Data Data Data
caravan, Dingle Data Data Data Data Data
caravan, Fermoy Data Data Data Data Data
amusement, Arklow Data Data Data Data Data
amusement, Tipperary Data Data Data Data Data
house, Castlerea Data Data Data Data Data
palace, Newcastle West Data Data Data Data Data
amusement, Tipperary Data Data Data Data Data
amusement, Wicklow Data Data Data Data Data
house, Castlerea Data Data Data Data Data
amusement, Carlow Data Data Data Data Data
amusement, Clonmel Data Data Data Data Data
house, Castlerea Data Data Data Data Data
house, Castlerea Data Data Data Data Data
house, Mountjoy Data Data Data Data Data
caravan, Fermoy Data Data Data Data Data
amusement, Carlow Data Data Data Data Data
amusement, Clonmel Data Data Data Data Data
amusement, Gorey Data Data Data Data Data
amusement, Gorey Data Data Data Data Data
amusement, Wicklow Data Data Data Data Data
caravan, Fermoy Data Data Data Data Data
caravan, Tralee Data Data Data Data Data
amusement, Cashel Data Data Data Data Data
amusement, New Ross Data Data Data Data Data
amusement, Wicklow Data Data Data Data Data
caravan, Fermoy Data Data Data Data Data
amusement, Arklow Data Data Data Data Data
amusement, Carlow Data Data Data Data Data
amusement, Carlow Data Data Data Data Data
amusement, Carlow Data Data Data Data Data
amusement, Cashel Data Data Data Data Data
amusement, Clonmel Data Data Data Data Data
amusement, Dungarvan Data Data Data Data Data
amusement, Enniscorthy Data Data Data Data Data
amusement, New Ross Data Data Data Data Data
amusement, Waterford Data Data Data Data Data
amusement, Wicklow Data Data Data Data Data
house, Castlerea Data Data Data Data Data
house, Limerick Data Data Data Data Data
house, Midlands Data Data Data Data Data
caravan, Fermoy Data Data Data Data Data
caravan, Killarney Data Data Data Data Data
amusement, Carlow Data Data Data Data Data
amusement, Cashel Data Data Data Data Data
amusement, Wicklow Data Data Data Data Data
house, Portlaoise Data Data Data Data Data
caravan, Dingle Data Data Data Data Data
caravan, Fermoy Data Data Data Data Data
amusement, Arklow Data Data Data Data Data
amusement, Carlow Data Data Data Data Data
amusement, Carlow Data Data Data Data Data
amusement, Cashel Data Data Data Data Data
amusement, Tipperary Data Data Data Data Data
amusement, Wexford Data Data Data Data Data
house, Castlerea Data Data Data Data Data
palace, Newcastle West Data Data Data Data Data
caravan, Killarney Data Data Data Data Data
amusement, Carlow Data Data Data Data Data
amusement, Cashel Data Data Data Data Data
amusement, Gorey Data Data Data Data Data
amusement, Wexford Data Data Data Data Data
amusement, Wicklow Data Data Data Data Data
house, Castlerea Data Data Data Data Data
caravan, Killarney Data Data Data Data Data
amusement, Carlow Data Data Data Data Data
amusement, Clonmel Data Data Data Data Data
amusement, Wicklow Data Data Data Data Data
house, Castlerea Data Data Data Data Data
house, Castlerea Data Data Data Data Data
caravan, Fermoy Data Data Data Data Data
caravan, Killarney Data Data Data Data Data
amusement, Clonmel Data Data Data Data Data
amusement, Gorey Data Data Data Data Data
amusement, Waterford Data Data Data Data Data
amusement, Wicklow Data Data Data Data Data
caravan, Cork City car Data Data Data Data Data
caravan, Fermoy Data Data Data Data Data
caravan, Killarney Data Data Data Data Data
caravan, Tralee Data Data Data Data Data
amusement, Cashel Data Data Data Data Data
amusement, Clonmel Data Data Data Data Data
amusement, Waterford Data Data Data Data Data
amusement, Wicklow Data Data Data Data Data
caravan, Fermoy Data Data Data Data Data
caravan, Killarney Data Data Data Data Data
amusement, Cashel Data Data Data Data Data
amusement, Dungarvan Data Data Data Data Data
amusement, Wicklow Data Data Data Data Data
caravan, Fermoy Data Data Data Data Data
caravan, Killarney Data Data Data Data Data
amusement, Arklow Data Data Data Data Data
amusement, Cashel Data Data Data Data Data
amusement, Dungarvan Data Data Data Data Data
amusement, Dungarvan Data Data Data Data Data
amusement, Tipperary Data Data Data Data Data
amusement, Waterford Data Data Data Data Data
palace, Newcastle West Data Data Data Data Data
caravan, Dingle Data Data Data Data Data
caravan, Fermoy Data Data Data Data Data
amusement, Tipperary Data Data Data Data Data
amusement, Tipperary Data Data Data Data Data
house, Castlerea Data Data Data Data Data
palace, Newcastle West Data Data Data Data Data
caravan, Killarney Data Data Data Data Data
amusement, Gorey Data Data Data Data Data
amusement, New Ross Data Data Data Data Data
amusement, Tipperary Data Data Data Data Data
house, Castlerea Data Data Data Data Data
caravan, Killarney Data Data Data Data Data
amusement, Carlow Data Data Data Data Data
amusement, Clonmel Data Data Data Data Data
amusement, Gorey Data Data Data Data Data
amusement, Gorey Data Data Data Data Data
amusement, New Ross Data Data Data Data Data
amusement, Tipperary Data Data Data Data Data
amusement, Wexford Data Data Data Data Data
house, Castlerea Data Data Data Data Data
house, Castlerea Data Data Data Data Data
caravan, Fermoy Data Data Data Data Data
caravan, Killarney Data Data Data Data Data
amusement, Carlow Data Data Data Data Data
amusement, Clonmel Data Data Data Data Data
amusement, Gorey Data Data Data Data Data
amusement, Gorey Data Data Data Data Data
amusement, New Ross Data Data Data Data Data
amusement, Waterford Data Data Data Data Data
amusement, Wexford Data Data Data Data Data
caravan, Bandon Data Data Data Data Data
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Sort by date Sort by votes
colinh69 said:
Hi,

I could really use some help here. I have been working on this for a week now and have only gotten so far as to create a new worksheet for each unique cell in column A but I need to search column A and copy each row based on partial match because I have a few cells with the similar contents and I need these grouped together.

I have 5 rows A-F and in the real sheet there are 1000's of rows.

I have 6 tabs 1 main tab with all the info and then the following "palace" "amusement" "caravan" "house" "other"

I want to be able to press a button on the main tab and have it copy and paste based on partial match of a cell. For example if "palace" is mentioned anywhere in a cell in row A then copy that row into "palace" tab.

I have copied a sample of the sheet and info on it.

Thanks

Customer County Start Finish Notes
Arthur Data Data Data Data Data
James Data Data Data Data Data
Mark Data Data Data Data Data
amusement, Kilkenny Data Data Data Data Data
Andrew Data Data Data Data Data
George Data Data Data Data Data
Old One Data Data Data Data Data
Pub Data Data Data Data Data
house, Castlerea Data Data Data Data Data
amusement, Carlow Data Data Data Data Data
amusement, Gorey Data Data Data Data Data
amusement, Waterford Data Data Data Data Data
James Data Data Data Data Data
Pub Data Data Data Data Data
Pub Data Data Data Data Data
Pub Data Data Data Data Data
amusement, Cashel Data Data Data Data Data
amusement, Wicklow Data Data Data Data Data
Pub Data Data Data Data Data
amusement, Carlow Data Data Data Data Data
amusement, Cashel Data Data Data Data Data
amusement, Waterford Data Data Data Data Data
amusement, Wicklow Data Data Data Data Data
Conway, John Data Data Data Data Data
O'Kelly, Regina Data Data Data Data Data
amusement, Cashel Data Data Data Data Data
O'Kelly, Regina Data Data Data Data Data
amusement, Arklow Data Data Data Data Data
amusement, Waterford Data Data Data Data Data
amusement, Wexford Data Data Data Data Data
house, Castlerea Data Data Data Data Data
palace, Newcastle West Data Data Data Data Data
amusement, Clonmel Data Data Data Data Data
amusement, New Ross Data Data Data Data Data
amusement, Wexford Data Data Data Data Data
amusement, Wicklow Data Data Data Data Data
house, Castlerea Data Data Data Data Data
amusement, Carlow Data Data Data Data Data
amusement, Carlow Data Data Data Data Data
amusement, Clonmel Data Data Data Data Data
amusement, Gorey Data Data Data Data Data
amusement, Tipperary Data Data Data Data Data
amusement, Wicklow Data Data Data Data Data
amusement, Wicklow Data Data Data Data Data
house, Castlerea Data Data Data Data Data
house, Castlerea Data Data Data Data Data
caravan, Fermoy Data Data Data Data Data
caravan, Tralee Data Data Data Data Data
amusement, Carlow Data Data Data Data Data
amusement, Clonmel Data Data Data Data Data
amusement, Gorey Data Data Data Data Data
amusement, Gorey Data Data Data Data Data
amusement, Tipperary Data Data Data Data Data
amusement, Wicklow Data Data Data Data Data
caravan, Fermoy Data Data Data Data Data
amusement, Carlow Data Data Data Data Data
amusement, Cashel Data Data Data Data Data
amusement, Wicklow Data Data Data Data Data
palace, Hospital Data Data Data Data Data
caravan, Fermoy Data Data Data Data Data
amusement, Arklow Data Data Data Data Data
amusement, Carlow Data Data Data Data Data
amusement, Cashel Data Data Data Data Data
amusement, Wicklow Data Data Data Data Data
Ross Data Data Data Data Data
caravan, Fermoy Data Data Data Data Data
amusement, Carlow Data Data Data Data Data
amusement, Clonmel Data Data Data Data Data
amusement, Tipperary Data Data Data Data Data
amusement, Wicklow Data Data Data Data Data
palace, Newcastle West Data Data Data Data Data
caravan, Dingle Data Data Data Data Data
caravan, Fermoy Data Data Data Data Data
amusement, Arklow Data Data Data Data Data
amusement, Tipperary Data Data Data Data Data
house, Castlerea Data Data Data Data Data
palace, Newcastle West Data Data Data Data Data
amusement, Tipperary Data Data Data Data Data
amusement, Wicklow Data Data Data Data Data
house, Castlerea Data Data Data Data Data
amusement, Carlow Data Data Data Data Data
amusement, Clonmel Data Data Data Data Data
house, Castlerea Data Data Data Data Data
house, Castlerea Data Data Data Data Data
house, Mountjoy Data Data Data Data Data
caravan, Fermoy Data Data Data Data Data
amusement, Carlow Data Data Data Data Data
amusement, Clonmel Data Data Data Data Data
amusement, Gorey Data Data Data Data Data
amusement, Gorey Data Data Data Data Data
amusement, Wicklow Data Data Data Data Data
caravan, Fermoy Data Data Data Data Data
caravan, Tralee Data Data Data Data Data
amusement, Cashel Data Data Data Data Data
amusement, New Ross Data Data Data Data Data
amusement, Wicklow Data Data Data Data Data
caravan, Fermoy Data Data Data Data Data
amusement, Arklow Data Data Data Data Data
amusement, Carlow Data Data Data Data Data
amusement, Carlow Data Data Data Data Data
amusement, Carlow Data Data Data Data Data
amusement, Cashel Data Data Data Data Data
amusement, Clonmel Data Data Data Data Data
amusement, Dungarvan Data Data Data Data Data
amusement, Enniscorthy Data Data Data Data Data
amusement, New Ross Data Data Data Data Data
amusement, Waterford Data Data Data Data Data
amusement, Wicklow Data Data Data Data Data
house, Castlerea Data Data Data Data Data
house, Limerick Data Data Data Data Data
house, Midlands Data Data Data Data Data
caravan, Fermoy Data Data Data Data Data
caravan, Killarney Data Data Data Data Data
amusement, Carlow Data Data Data Data Data
amusement, Cashel Data Data Data Data Data
amusement, Wicklow Data Data Data Data Data
house, Portlaoise Data Data Data Data Data
caravan, Dingle Data Data Data Data Data
caravan, Fermoy Data Data Data Data Data
amusement, Arklow Data Data Data Data Data
amusement, Carlow Data Data Data Data Data
amusement, Carlow Data Data Data Data Data
amusement, Cashel Data Data Data Data Data
amusement, Tipperary Data Data Data Data Data
amusement, Wexford Data Data Data Data Data
house, Castlerea Data Data Data Data Data
palace, Newcastle West Data Data Data Data Data
caravan, Killarney Data Data Data Data Data
amusement, Carlow Data Data Data Data Data
amusement, Cashel Data Data Data Data Data
amusement, Gorey Data Data Data Data Data
amusement, Wexford Data Data Data Data Data
amusement, Wicklow Data Data Data Data Data
house, Castlerea Data Data Data Data Data
caravan, Killarney Data Data Data Data Data
amusement, Carlow Data Data Data Data Data
amusement, Clonmel Data Data Data Data Data
amusement, Wicklow Data Data Data Data Data
house, Castlerea Data Data Data Data Data
house, Castlerea Data Data Data Data Data
caravan, Fermoy Data Data Data Data Data
caravan, Killarney Data Data Data Data Data
amusement, Clonmel Data Data Data Data Data
amusement, Gorey Data Data Data Data Data
amusement, Waterford Data Data Data Data Data
amusement, Wicklow Data Data Data Data Data
caravan, Cork City car Data Data Data Data Data
caravan, Fermoy Data Data Data Data Data
caravan, Killarney Data Data Data Data Data
caravan, Tralee Data Data Data Data Data
amusement, Cashel Data Data Data Data Data
amusement, Clonmel Data Data Data Data Data
amusement, Waterford Data Data Data Data Data
amusement, Wicklow Data Data Data Data Data
caravan, Fermoy Data Data Data Data Data
caravan, Killarney Data Data Data Data Data
amusement, Cashel Data Data Data Data Data
amusement, Dungarvan Data Data Data Data Data
amusement, Wicklow Data Data Data Data Data
caravan, Fermoy Data Data Data Data Data
caravan, Killarney Data Data Data Data Data
amusement, Arklow Data Data Data Data Data
amusement, Cashel Data Data Data Data Data
amusement, Dungarvan Data Data Data Data Data
amusement, Dungarvan Data Data Data Data Data
amusement, Tipperary Data Data Data Data Data
amusement, Waterford Data Data Data Data Data
palace, Newcastle West Data Data Data Data Data
caravan, Dingle Data Data Data Data Data
caravan, Fermoy Data Data Data Data Data
amusement, Tipperary Data Data Data Data Data
amusement, Tipperary Data Data Data Data Data
house, Castlerea Data Data Data Data Data
palace, Newcastle West Data Data Data Data Data
caravan, Killarney Data Data Data Data Data
amusement, Gorey Data Data Data Data Data
amusement, New Ross Data Data Data Data Data
amusement, Tipperary Data Data Data Data Data
house, Castlerea Data Data Data Data Data
caravan, Killarney Data Data Data Data Data
amusement, Carlow Data Data Data Data Data
amusement, Clonmel Data Data Data Data Data
amusement, Gorey Data Data Data Data Data
amusement, Gorey Data Data Data Data Data
amusement, New Ross Data Data Data Data Data
amusement, Tipperary Data Data Data Data Data
amusement, Wexford Data Data Data Data Data
house, Castlerea Data Data Data Data Data
house, Castlerea Data Data Data Data Data
caravan, Fermoy Data Data Data Data Data
caravan, Killarney Data Data Data Data Data
amusement, Carlow Data Data Data Data Data
amusement, Clonmel Data Data Data Data Data
amusement, Gorey Data Data Data Data Data
amusement, Gorey Data Data Data Data Data
amusement, New Ross Data Data Data Data Data
amusement, Waterford Data Data Data Data Data
amusement, Wexford Data Data Data Data Data
caravan, Bandon Data Data Data Data Data
Click to expand...
Hi,
try the following:
Code: 
Sub myCompare()
Dim MyLine As String
Dim myCompare As String
Dim LR As Long
Dim a, p, c, h, o As Long
LR = Cells(Rows.Count, "A").End(xlUp).Row 'find the last row of data
a = 1 'start for sheets amusement
p = 1 'start for sheets palace
c = 1 'start for sheets caravan
h = 1 'start for sheets house
o = 1 'start for sheets other
For i = 1 To LR
    MyLine = Cells(i, 1)
    If MyLine Like ("*amusement*") Then
        Rows(i).Copy Destination:=Sheets("amusement").Rows(a)
        a = a + 1
    ElseIf MyLine Like ("*palace*") Then
        Rows(i).Copy Destination:=Sheets("palace").Rows(p)
        p = p + 1
    ElseIf MyLine Like ("*caravan*") Then
        Rows(i).Copy Destination:=Sheets("caravan").Rows(c)
        c = c + 1
    ElseIf MyLine Like ("*house*") Then
        Rows(i).Copy Destination:=Sheets("house").Rows(h)
        h = h + 1
    ElseIf MyLine Like ("*other*") Then
        Rows(i).Copy Destination:=Sheets("other").Rows(o)
        o = o + 1
    End If
Next i
End Sub
Hope that helps,
Cheers
 
Upvote 0
Hi Alan,

Sorry for my delayed reply, this works great thanks. Is there a way for it to also delete the lines from the main sheet that it has copied onto the separate sheet.
I cant just delete the whole sheet as not all of the information will be copied over.

Thanks again,
Colin
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
Separate data onto multiple tabs based on partial cell match
Replies
1
Views
306
Tinbendr
Tinbendr

Forum statistics

Threads
1,224,517
Messages
6,179,239
Members
452,898
Latest member
Capolavoro009

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top