search string & delete entire rows; then reassign the task - Bulk ticket reassignment

sudarkp · Jun 11th 2016

Hi,
I have attached the test data herewith for your reference. Requesting this solution to make the bulk reassingment of InProgress tasks to new resources easily

Please provide excel VBA code for below condition:

1. Find a word like "Apple","Oranges", "pineapple" and delete entire rows wherever this word is present; here i would like to find strings one by one automcatically using vba code; that is vba code will find apples first delete entire rows of apple ; then find oranges delete entire rows of orange etc.
2. After that, for the remaining lines, i need to fill the last column new resource reassingment based on project name like "apples", "oranges" etc.
One resource can have tasks from multiple random project names; like one resource will have one task from apple and one task from oranges etc.

Thanks for the time and support

Best Regards

mrmmickle1 · Jun 11th 2016

Re: search string & delete entire rows; then reassign the task - Bulk ticket reassign

This code will quickly delete items that meet your criteria:

Code

Sub Test()


Dim lr As Long
Dim ArrLp As Integer
Dim myDelArr As Variant


lr = Sheets("Table").Cells(Rows.Count, "A").End(xlUp).Row
MyDelArr = Array("apple", "orange", "pineapple")


For ArrLp = 0 To UBound(MyDelArr)
    With Sheets("Table").Range("A1:M" & lr)
        .AutoFilter 4, "=" & MyDelArr(ArrLp) 'Filter Column 4 for deletion based on Criteria
        .Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete 'Filter for all delete matches
        .AutoFilter 'Unfilter
    End With
Next ArrLp


End Sub

Display More

I don't really understand what you mean in number 2....can you elaborate or show a before and after?

sudarkp · Jun 11th 2016

Re: search string & delete entire rows; then reassign the task - Bulk ticket reassign

Thank you mrmmickle1

Point No. 2 - Remaining data in the excel sheet after performing point no.1, should be re-assigned to new resources (in the previously attached excel sheet, there is a column name "resourcealias", these are the ids of various resources to whom the last column "NewResourcelias" has to be filled with
But the challenge here is one new resourcealias will have multiple rows (tasks) assigned randomnly based on project name like one new resource alias will have one apple, one pineapple, one butterfruit etc.

"Newresourcelias" will have dynamic re-assingment of rows(tasks) based on availability and capacity

For example:
"Newresourcealias" - here test1 should be assigned with one apple row, one orange row, one pineapple row etc., then test2 only with butterfruit rows; then test3 with apple and pineappl rows; so its combination of dynamic row assignment; how could we achieve it through VBA code. thanks again for your time and support

"Newresourcealias"
test1
test2
test3
test4
test5

mrmmickle1 · Jun 11th 2016

Re: search string & delete entire rows; then reassign the task - Bulk ticket reassign

If you want to reassign values like in your example:

Quote

"Newresourcealias"
test1
test2
test3
test4
test5

Display More

Use this:

Code

Sub Test()




    Dim lr As Long
    Dim LngLp As Long
    
    lr = Sheets("Table").Cells(Rows.Count, "A").End(xlUp).Row 'Define LastRow




    For LngLp = 2 To lr
        Sheets("Table").Cells(LngLp, "M") = "Test" & LngLp - 1
    Next LngLp
    
End Sub

Display More

sudarkp · Jun 12th 2016

Re: search string & delete entire rows; then reassign the task - Bulk ticket reassign

Thanks again mrmmickle1

It works.. But i am looking something like below. Its dynamic assignment of resources, test1 can have one in each project name; test2 will have only oranges; test3 will have dynamic assignment one in each project name etc.

[TABLE="width: 214"]

[tr]

[td]

project name

[/td]

[td]

NewResourceAlias

[/td]

[/tr]

[tr]

[td]

grapes

[/td]

[td]

Test1

[/td]

[/tr]

[tr]

[td]

grapes

[/td]

[td]

Test1

[/td]

[/tr]

[tr]

[td]

Butter fruit

[/td]

[td]

Test1

[/td]

[/tr]

[tr]

[td]

Mango

[/td]

[td]

Test1

[/td]

[/tr]

[tr]

[td]

Orange

[/td]

[td]

Test2

[/td]

[/tr]

[tr]

[td]

Orange

[/td]

[td]

Test2

[/td]

[/tr]

[tr]

[td]

Apples

[/td]

[td]

Test3

[/td]

[/tr]

[tr]

[td]

Butter fruit

[/td]

[td]

test3

[/td]

[/tr]

[tr]

[td]

Mango

[/td]

[td]

test3

[/td]

[/tr]

[tr]

[td]

Banana

[/td]

[td]

Test4

[/td]

[/tr]

[tr]

[td]

Banana

[/td]

[td]

Test4

[/td]

[/tr]

[tr]

[td]

Banana

[/td]

[td]

test4

[/td]

[/tr]

[tr]

[td]

Guava

[/td]

[td]

Test5

[/td]

[/tr]

[tr]

[td]

Guava

[/td]

[td]

test5

[/td]

[/tr]

[tr]

[td]

Guava

[/td]

[td]

test5

[/td]

[/tr]

[/TABLE]

Best Regards

mrmmickle1 · Jun 12th 2016

Re: search string & delete entire rows; then reassign the task - Bulk ticket reassign

Perhaps something like this will work for your needs:

Code

Sub Test()
         
    Dim lr As Long
    Dim LngLp As Long
    Dim ResrceArr As Variant
     
    lr = Sheets("Table").Cells(Rows.Count, "A").End(xlUp).Row 'Define LastRow
    ResrceArr = Array("Test1", "Test2", "Test3", "Test 4", "Test 5")
    
    For LngLp = 2 To lr
    
        If Sheets("Table").Cells(LngLp, "D") = "orange" Then
            'Assignment of Test1 for orange
            Sheets("Table").Cells(LngLp, "M") = "Test1"
        Else
            'Random Assignment
            Sheets("Table").Cells(LngLp, "M") = ResrceArr(Int((4 - 0 + 1) * Rnd + 0))
        End If
        
    Next LngLp
     
End Sub

Display More

sudarkp · Jun 12th 2016

Re: search string & delete entire rows; then reassign the task - Bulk ticket reassign

Thanks very much mrmmickle1

this is very helpful !!

I tried to use your code further to assign test 1 with one apple, one pineapple, one guava like below and go on the same with test 2
[TABLE="class: cms_table, width: 214"]

[tr]

[td]

project name

[/td]

[td]

NewResourceAlias

[/td]

[/tr]

[tr]

[td]

grapes

[/td]

[td]

Test1

[/td]

[/tr]

[tr]

[td]

grapes

[/td]

[td]

Test1

[/td]

[/tr]

[tr]

[td]

Butter fruit

[/td]

[td]

Test1

[/td]

[/tr]

[tr]

[td]

Mango

[/td]

[td]

Test1

[/td]

[/tr]

[tr]

[td]

Orange

[/td]

[td]

Test2

[/td]

[/tr]

[tr]

[td]

Orange

[/td]

[td]

Test2

[/td]

[/tr]

[tr]

[td]

Apples

[/td]

[td]

Test3

[/td]

[/tr]

[tr]

[td]

Butter fruit

[/td]

[td]

test3

[/td]

[/tr]

[/TABLE]

modified your code like this below:- , could not succeed, could you provide a solution to achieve above results, thank you

Sub Test2()

Dim lr As Long
Dim LngLp As Long
Dim ResrceArr As Variant

lr = Sheets("Table").Cells(Rows.Count, "A").End(xlUp).Row 'Define LastRow
ResrceArr = Array("Test1", "Test2", "Test3", "Test 4", "Test 5")

For LngLp = 2 To lr

If Sheets("Table").Cells(LngLp, "D") = "grapes" Then
'Assignment of Test1 for orange
Sheets("Table").Cells(LngLp, "M") = "Test1"
End If
If Sheets("Table").Cells(LngLp, "D") = "pineapple" Then
'Assignment of Test1 for pineapple

Sheets("Table").Cells(LngLp, "M") = "Test2"
End If
Next LngLp

End Sub

mrmmickle1 · Jun 13th 2016

Re: search string & delete entire rows; then reassign the task - Bulk ticket reassign

Maybe try using something like this. It may be simpler for you to manipulate:

Code

Sub Test2()
    
    Dim lr As Long
    Dim LngLp As Long
    Dim ws As Worksheet
    
    Set ws = Sheets("Table") 'Set ws variable
    lr = Sheets("Table").Cells(Rows.Count, "A").End(xlUp).Row 'Define LastRow
    
    For LngLp = 2 To lr
    
        Select Case ws.Cells(LngLp, "D")
            Case "apple", "pineapple", "guava" 'If one of these then assign Test1
                  ws.Cells(LngLp, "M") = "Test1"
            Case "butterfruit"
                  ws.Cells(LngLp, "M") = "Test2"
            Case Else
                  ws.Cells(LngLp, "M") = "Test3"
        End Select
    
    Next LngLp




End Sub

Display More

sudarkp · Jun 13th 2016

Re: search string & delete entire rows; then reassign the task - Bulk ticket reassign

Thanks so much mrmmickle1; this solutions was simple and worked wonderfully!!

If you are free, could you please help with my other thread
"Compare sheet1 & sheet 2 for matches, then update sheet1 for validation status"
Kindly provide vba code as much as posible; i will explain more, if something is not clear

Thanks again

mrmmickle1 · Jun 14th 2016

Re: search string & delete entire rows; then reassign the task - Bulk ticket reassign

Your quite welcome. Glad you were able to get your issue sorted out.

search string & delete entire rows; then reassign the task - Bulk ticket reassignment

Participate now!

Share

Users Viewing This Thread