CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
Rosie Muir
Rosie Muir

Reputation: 37

How do I stop Filter retaining the order of source data?

I'm wondering how to get a FILTER function in Google Sheets to order itself by the data it is matching to in its conditions.

My example sheet is here.

In my example I have two sets of data in two sheets named "DS" and "SALESPIPELINE". DS contains a list of studios and dates, SALESPIPELINE also contains a list of studios and dates along with sales data such as leads, prospects, pre-quals, consults etc.

I want to bring the information from SALESPIPELINE to DS, but ordered correctly to match the studio and date columns in DS. I've tried a number of formulas, but currently the formula I am using is:

=FILTER(SALESPIPELINE!C2:I, MATCH(SALESPIPELINE!A2:A1000, A2:A, 0), MATCH(MONTH(SALESPIPELINE!B2:B), MONTH(B2:B), 0), MATCH(YEAR(SALESPIPELINE!B2:B), YEAR(B2:B), 0))

The filtered data is the correct data, but it retains the order of the data in SALESPIPELINE rather than matching to the columns in DS. I've tried wrapping the FILTER in the SORT function and sorting by SALESPIPELINE!A3:A14, but it doesn't seem to sort in order (this is also not a solution for me as the sort range needs to be dynamic as more data is added).

I've included a sheet that shows how the results would ideally come out. As you'll see, I'd like it to adopt the order of studios and dates in DS with the matching data from SALESPIPELINE.

Is FILTER the correct formula to be using, or would something like QUERY be more appropriate? (I did have a go at QUERY, but couldn't get it to work either)

Hope that makes sense - I would love some help figuring this out!

Upvotes: 0

Views: 205

Answers (2)

Mike Steelson
Mike Steelson

Reputation: 15328

Try this (you can put in another tab)

=sort(filter({arrayformula(if(iserror(match(SALESPIPELINE!A:A,DS!A:A,0)&"~"&match(SALESPIPELINE!B:B,DS!B:B,0)),9^9,match(SALESPIPELINE!A:A,DS!A:A,0))),SALESPIPELINE!A:I},match(SALESPIPELINE!A:A,DS!A:A,0),match(SALESPIPELINE!B:B,DS!B:B,0)))

enter image description here

Upvotes: 1

player0
player0

Reputation: 1

try:

=INDEX(IFNA(VLOOKUP(A3:A, 
 FILTER({SALESPIPELINE!A2:A, SALESPIPELINE!C2:I}, 
 MATCH(SALESPIPELINE!A2:A1000, A3:A, 0), MATCH(MONTH(SALESPIPELINE!B2:B), MONTH(B3:B), 0), 
 MATCH(YEAR(SALESPIPELINE!B2:B), YEAR(B3:B), 0)), {2,3,4,5,6,7,8}, 0)))

enter image description here

Upvotes: 1

Related Questions