Return values from another sheet based on matched results with no duplicates

[Savage702]

Morning All,

Have been trying to figure out how to do this, but failing miserably. Hoping someone can help/offer advice.
Including a sample sheet to download.

Completed worksheet is a selection of tickets worked by a team, a description and the customer name.
On the Order Processing sheet, I want to pull anything starting with "Order Processing*" from Completed!C and list all the customers from Completed!D, but with no duplicates. So once Cassy C is in the list, she should not repeat, and the same for anyone with "Order Processing*" listed.

It should ignore anything that doesn't have "Order Processing*"

I have tried a number of array formulas, some I have got to work only with the EXACT string from C, no wildcards, which apparently are issues in Array Formulas, I've also tried adding LEFT into the formula to use the effect of a wildcard, but looking only for text on the left starting with "Order Processing".


https://www.dropbox.com/s/vt421k8cemnpp2p/TestBook.xlsx?dl=0

 

[mumps]

Try:

Sub ReturnVals()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    With Sheets("Completed").Cells(1).CurrentRegion
        .AutoFilter Field:=3, Criteria1:="=Order Processing*"
        Sheets("Completed").Range("D2:D" & LastRow).SpecialCells(xlCellTypeVisible).Copy Sheets("Order Processing").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
    End With
    Sheets("Order Processing").Cells.RemoveDuplicates Columns:=Array(1), Header:=xlNo
    Sheets("Completed").Cells(1).AutoFilter
    Application.ScreenUpdating = True
End Sub

 

[Savage702]

Thanks Mumps, I was hoping to do this without VBA, but guessing that's not going to be the case. I'd found some similar (but way different) options when searching around, but kept pounding.

I opened the editor, added module on the "Order Processing" sheet, saved as a Macro Enabled sheet.

Can you tell me if/what I'm doing wrong? I've only used VBA in a sheet once before. When I run it, I'm getting an error on this row:

    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

 

[mumps]

do the following: Hold down the ALT key and press the F11 key. This will open the Visual Basic Editor. In the menu at the top click 'Insert' and then click 'Module'. Copy and paste the macro below into the empty code window that opens up. Press the F5 key to run the macro. Close the code module window to return to your sheet. There are other quicker ways to run the macro such as assigning it to a button that you would click on your sheet or assigning it to a short cut key.

Sub ReturnVals()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Sheets("Completed").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    With Sheets("Completed").Cells(1).CurrentRegion
        .AutoFilter Field:=3, Criteria1:="=Order Processing*"
        Sheets("Completed").Range("D2:D" & LastRow).SpecialCells(xlCellTypeVisible).Copy Sheets("Order Processing").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
    End With
    Sheets("Order Processing").Cells.RemoveDuplicates Columns:=Array(1), Header:=xlNo
    Sheets("Completed").Cells(1).AutoFilter
    Application.ScreenUpdating = True
End Sub

 

[Fluff]

You can do this with formulae
=IFERROR(INDEX(Completed!$D$2:$D$37,AGGREGATE(15,6,(ROW(Completed!$D$2:$D$37)-ROW(Completed!$D$2)+1)/((LEFT(Completed!$C$2:$C$37,16)="Order Processing")),ROWS($A$1:$A1))),"")
=IFERROR(INDEX(A$2:A$37,MATCH(0,INDEX(COUNTIF(B$1:B1,A$2:A$37),0),0)),"")

 

[Savage702]

Thank you Mumps, I got it! I appreciate your patience with me. lol

 

[Savage702]

You can do this with formulae
=IFERROR(INDEX(Completed!$D$2:$D$37,AGGREGATE(15,6,(ROW(Completed!$D$2:$D$37)-ROW(Completed!$D$2)+1)/((LEFT(Completed!$C$2:$C$37,16)="Order Processing")),ROWS($A$1:$A1))),"")
=IFERROR(INDEX(A$2:A$37,MATCH(0,INDEX(COUNTIF(B$1:B1,A$2:A$37),0),0)),"")

Thank you, I'll check this out on Monday when I have a new report to run against. Mumps worked a charm, but I'll see how this one works too.
Thanks again.

 

[mumps]

Glad top help.