Moving Grouped Rows Based on One Cell Value

Mastrius

New Member
Joined
Dec 12, 2016
Messages
2
Hello,

I wasn't sure how to phrase this but here is my data:

ACCOUNT#|CUSTOMER|BATCH|ORDER#|ITEM_NAME|
123______|Customer1|*Blank*|1234 |Nail |
123______|Customer1|FA216 |1234 |Screw |
123______|Customer1|FB315 |1234 |Bolt |
[Blank Color-Filled Row ]
222______|Customer2|FA980 |4567 |Nut |
222______|Customer2|FB287 |4567 |Pedal |
222______|Customer2|FB983 |4567 |Hinge |
[Blank Color-Filled Row]

If there is a blank cell under the "BATCH" column, I need all rows associated with that Customer/Order# to be moved to a separate worksheet, and deleted from the existing sheet. So for example above, since Customer1's order has a *Blank* "BATCH" cell, all three rows of data that are associated with Customer1 need to be removed from this sheet, and inserted into a new sheet.

The column headers are always the same and will never change, but the number of rows (so number of orders, customers, etc.) change every day.

Any help would be greatly appreciated.

Thank you,
-Ryan
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

mahhdy

Board Regular
Joined
Sep 15, 2016
Messages
86
I think this would be helpfull:
[url]http://www.mrexcel.com/forum/excel-questions/980097-copy-row-condition.html#post4702453 [/URL]

Code:
Sub FilterMini()
With Range("A1").CurrentRegion
.AutoFilter Field:=3, Criteria1:="", Operator:=xlFilterValues  'if it is exactly "*Blank*" put it between the "" area
.SpecialCells(xlCellTypeVisible).Copy Worksheets("Sheet2").Range("A1")   'Edit sheet name   
.EntireRow.Delete
End With
ActiveSheet.AutoFilterMode = False
End Sub
Maybe works ;)
Yours,
M
 

mahhdy

Board Regular
Joined
Sep 15, 2016
Messages
86
I checked it, It works perfect, But it will also copy the header row and cut it to the next page.
maybe one offset command can resolve it, I will try and let you know.
Just be mindful the code originally belongs to My Aswer Is This I just made a simple edit.
Yours,
M
 

Mastrius

New Member
Joined
Dec 12, 2016
Messages
2
Hello M,

Thank you for the response. I tried the code, and it copied the header row, as well as the single row that included the blank "Batch" cell. The only problem is that it did not copy the other two rows that were associated with that customer. So for the above example, I need it to copy all three rows associated with "Customer1" but it only copied the one row that had the blank batch when I tried it.

I also need this to scale with my orders, so that if there are multiple orders on the sheet that have this issue, it will copy all of them to the new sheet. But if I can get it working right with just one of the orders, I'm confident I can figure out the scalability issue myself.

Thanks,

-Ryan
 

Watch MrExcel Video

Forum statistics

Threads
1,123,481
Messages
5,601,918
Members
414,482
Latest member
morkar

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
Top