Copy data from one worksheet to another worksheet based on criteria

I am trying to track is when a Transaction Type is PO Receipt & PO Rcpt Adjust, that Transaction Type info is automatically pulled to separate sheet. The same thing for Transaction Type Intransit Receipt, Intransit Shipment & WIP component issue, that info will pull over to the another separate sheet. And for Transaction Type Miscellaneous receipt & Miscellaneous issue, that info will pull over to the another separate sheet.

The same thing i have done manually in sheet Pur,Cons & Misc respectively.

I am not sure if this can be done with a formula or if a macro is needed.

The status on the Proposal Log tab will be manually updated by each estimator as the project award status is made known. I am just trying to have a semi-simple place to track this info.

I am not sure if this can be done with a formula or if a macro is needed.

For Pur:
=IFERROR(INDEX('BG -1'!A:A,SMALL(INDEX((LEFT('BG -1'!$C$2:$C$47,2)<>"PO")*10^10+ROW('BG -1'!$C$2:$C$47),0),ROWS($1:1))),"")

For Cons:
=IFERROR(INDEX('BG -1'!A:A,SMALL(INDEX(((LEFT('BG -1'!$C$2:$C$47,2)="PO")+(LEFT('BG -1'!$C$2:$C$47,4)="misc"))*10^10+ROW('BG -1'!$C$2:$C$47),0),ROWS($1:1))),"")


For Misc:
=IFERROR(INDEX('BG -1'!A:A,SMALL(INDEX((LEFT('BG -1'!$C$2:$C$47,4)<>"Misc")*10^10+ROW('BG -1'!$C$2:$C$47),0),ROWS($1:1))),"")

in all cases, copy across and down as far as needed.

Thanks a lot Glenn. It works fine.
But can you please brief this formula so i can add or delete some more criteria.

For one criterion:

INDEX(Range wanted, SMALL((Criterion)*10^10+Row(rows containing the criterion)),a counter that returns 1, 2, 3, etc)

for multiple criteria, add another level of parentheses and use + between sets of criteria:
INDEX(Range wanted, SMALL(((Criterion)+(Next criterion))*10^10+Row(rows containing the criterion)),a counter that returns 1, 2, 3, etc)

Thanks for the Rep. Much appreciated!!

Is there any VBA code possible for the same.

Yes, but not from me. I'm no good with VBA. Best solution is to start a new post in the VBA sub-forum.

Oh. I didn't spot that. Apologies for wasting your time!!

Try this.

Please Login or Register  to view this content.

I see you marked this thread as solved.
Did you see the code I wrote in post #10?