Macro to Segregate the data based on Multiple Criteria's in Multiple Columns -Challenging!

Hi

I want to run a macro to Segregate the data based on Multiple Criteria's in Multiple Columns. The data I get is basically from Outlook Mail Fields, which I run daily and generate an excel sheet every day, for further processing.

I have data like
In Sheet 1

Please Login or Register  to view this content.

Which I want it Like this

In Sheet 2

Please Login or Register  to view this content.

In Sheet 3

Please Login or Register  to view this content.

I will sort the data by "Subject" manually and look for "Sender" = "CB@co.com" or "OD@co.com", if found, then copy all related Rows with "Same Subject Line" to "Sheet 2"
and Other rows to "Sheet 2" (where "CB@co.com" or "OD@co.com" is not there)

I have searched in internet for Segregating data, but couldn't find anything related to my problem. I'm attaching excel sheet for your reference

I'm wondering is it possible to do this through VBA? it would make my work life better!! Please someone please help me...

Thanks a million in advance...
Sample_Outlook_File_Processed_Manually.xls

Hi,

Add this into a blank macro - this will separate the data and add into the relevant sheets.

Sheets("MailBox").Select
Columns("B:B").Select
Selection.Replace What:="RE:", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="FW:", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("B1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Sheets("MailBox").Select
ActiveWorkbook.Worksheets("MailBox").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("MailBox").AutoFilter.Sort.SortFields.Add Key:= _
Range("C1:C18"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("MailBox").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("B1").Select
Selection.AutoFilter
ActiveSheet.Range("$B$1:$E$18").AutoFilter Field:=3, Criteria1:= _
"=Deb.Rob@company.com", Operator:=xlOr, Criteria2:= _
"=Karan.Gini@company.com"
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Copy
Sheets("Approved").Select
ActiveSheet.Paste
Columns("A:D").Select
Columns("A:D").EntireColumn.AutoFit
Sheets("MailBox").Select
Application.CutCopyMode = False
ActiveSheet.Range("$B$1:$E$18").AutoFilter Field:=3, Criteria1:= _
"<>Deb.Rob@company.com", Operator:=xlAnd, Criteria2:= _
"<>Karan.Gini@company.com"
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Copy
Sheets("InProcess").Select
ActiveSheet.Paste
Columns("A:D").Select
Columns("A:D").EntireColumn.AutoFit
Sheets("MailBox").Select
Application.CutCopyMode = False
ActiveSheet.Range("$B$1:$E$18").AutoFilter Field:=3
Range("B1").Select
End Sub

Thanks Collins

That was a nice try, which I had given a try, but, my requirement is a bit different, infact a bit more complicated. I think you missed the catch!

With your macro, only first criteria can be worked out, but not the second one.

What I'm looking for is, It should look for "Sender" = "CB@co.com" or "OD@co.com", if found,
then copy All Related Rows with "Same Subject Line" to "Sheet 2" (Not just that single Row where it find the string)
and Other rows to "Sheet 2" (where "CB@co.com" or "OD@co.com" is not there)

For instance, I'm looking for "CB@co.com" in Column D, its corresponding "Subject" in Column B is "Help". Then it should pick All Rows where it is = "Help" in Column B

Data

Please Login or Register  to view this content.

Result Sheet 2

Please Login or Register  to view this content.

Please check first post for more...

Let me introduce to the workflow I undergo:
I'm System Administrator who provides access to system resources. for which I need Approval from my managers (either Deb or Karan)

Hierarchy of work flow (I will get every mail)
1) Requester Sends a mail to his manager saying, he needs some access
2) If Manager Approves, it will be sent to General Managers Common Mail
3) At present we have Two General Managers (Deb & Karan), if any one of them approves
4) It comes to me for further process

I cannot process any request unless I have an Approval from my General Managers (Any one)

I do require to track that mails curent status, as I'm not just one System Admin who provides access, the Approval from Ded or Karan will trigger the action. So I dont want to waste my time while someone is already working on it, which would be redundant and no use!!

I get tons of mails every day, and it's been a great hassle in segregating the mails every day manually

May be I have given too many details, but i thought you will understand my problem a bit more clear!!!

Any suggestions will be great help

Thank you

Thanks Mike!! It did as needed

If possible, can you add comments on the code, so that i will know exactly what I'm doing. Then I can use your code in my future needs.

Thank you very much for help, that's really helped me a lot

Hi Mike

I'm getting "Run-time error '1004': Application-defined or object-defined error"

Please Login or Register  to view this content.

What I understood after experimenting, is when ever the Content of Body is Big (Cells in Column E), macro is getting this error. Otherwise it was fine!!

I'm wondering ,if there is a way to make it free size text, to accept entire cell, no matter how many lines of data is there
or Can we limit the Lines to consider to paste in two other sheets upto 10 Lines or something and ignore the rest of the lines?

Mike Please Reply. I Need your help in this

Thanks in advance

I mean, Few Cells in Column E has contents more than 200 Lines and 12000 characters, where I'm getting 1004 runtime error