Reg replacing cell values based on a pattern

Hi All,

Sorry to ask this,but please point me in the right direction.I'm a total newbie on excel and trying to learn it.I have situation where in a i have to create a report from a master sheet which has the below format.
Order# Step# Assignee Status
OA1 Step1.Initiate New John Completed
OA1 Step2.Close New Missing In Progress
OA1 Step3.End - Pending for previous step
OA2 Step1.Initiate New Sean In Progress
OA2 Step2.Close New Missing Pending for previous step
OA2 Step3.End - Pending for previous step

I want to put a filter on step2 and step3 and should be able to see the assignee name.Can someone tell me how can i replace these(step2 and step3 value has to be overwritten based on step1 assignee) based on the step1 assignee? The master sheet has few hundreds of such entries.
Appreciate your help.

Hello sam9989 and Welcome to Excel Forum.
It might help us to help you if you would upload a sample spreadsheet. Show us a sample of what you have on the master sheet and what you want on the report by manually mocking up the expected outcome(s). We can attempt to automate your manually mocked up outcome(s) using either formula(s) or code.
To upload a sample spreadsheet click on the GO ADVANCED button below the Quick Reply window and then scroll down to Manage Attachments to open the upload window.
Let us know if you have any questions.

I have notice that, in your data "Order Item ID" duplicate for two different "step name". I think this not correct.
Secondly, explain about "[Resolver Assigned]"

This may not be as fully automatic as you want, but perhaps it will be a start.
I used three helper columns which may be moved for aesthetic purposes.
The first helper column (I) is populated using: =IF(OR(F2="",F2="Missing"),"",B2)
The second helper column (J) is populated using: =IF(I2="","",F2)
The third helper column (K) is populated using: =INDEX(J$2:J$17,MATCH(B2,I$2:I$17,0))
From this point you would need to:
1) select and copy cells K2:K17
2) select cell F2
3) use the paste option 'Paste Values'
These three steps might be something that could be written into a macro (I am pretty much VBA illiterate). If you would like to pursue that possibility you could mark this thread as 'Solved' and open a new thread on the 'Excel Programming/ VBA/ Macros' forum showing what you already have and explaining what you want (or include a link back to this thread).
Let us know if you have any questions.