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

Savage702

New Member
Joined
Sep 28, 2009
Messages
8
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
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Try:
Code:
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
 
Last edited:
Upvote 0
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:

Code:
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
 
Upvote 0
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.

Code:
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
 
Upvote 0
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)),"")
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,224,820
Messages
6,181,159
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top