How to use FILTER on two columns?

[rubsley]

Hi, I'm looking to solve the following:

sample.JPG

I've searched around a bunch but I haven't found what will do this.

Any ideas?

Thanks!

[rubsley]

PS I realise I could use SUMPRODUCT or SUMIF in each cell - but since it's going to cover over a thousand rows and up to six hundred columns I figure that would be a bit much.

[JohnTopley]

Please see yellow banner at top of page on how to post a workbook. Please make sure it is a good representative sample,

Are you open to a VBA solution?

And I suspect you are not on Excel 2003 so please update your profile

[FlameRetired]

Since you clearly have the sample data there please upload an Excel workbook here. It saves retyping your data.

Instructions are in the gold banner titled HOW TO ATTACH YOUR SAMPLE WORKBOOK:

[rubsley]

Apologies. Here's the sample.

[JohnTopley]

You stated there were several hundred columns: what is data format over these columns as your sample gives no indication of this : are there many pairs of names ???

[rubsley]

Yep, it just carries on as it is: pairs of names and pairs of numbers.

[JeteMc]

Try the following in G3 (then copy over and down):

Please Login or Register  to view this content.

Let us know if you have any questions.

[rubsley]

Strangely, I just got blank cells from that.

I think I've figured another way to go about what I needed.

Thanks for looking though!

[AliGW]

Please share your solution for the benefit of others.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

[JeteMc]

To demonstrate that the formula works.

[rubsley]

Many thanks for the help, all good now.

(PS The solution I mentioned was to do with what the next stage of what I wanted to do with the data, but I arrived at that step in a different way.)

As ever, all help much appreciated.

[AliGW]

And what was your solution? Please share it here for the benefit of the whole community. Thanks.

[rubsley]

=IF(AND(A2<>"", IF(A2="", , IF(MAX(MAX(ArrayFormula(IF((Data!B:B=A2)*(Data!C:C>Data!D:D), ROW(Data!B:B)))), MAX(ArrayFormula(IF((Data!E:E=A2)*(Data!D:D>Data!C:C), ROW(Data!B:B)))))="", FALSE, IF(MAX(MAX(ArrayFormula(IF((Data!B:B=A2)*(Data!C:C>Data!D:D), ROW(Data!B:B)))), MAX(ArrayFormula(IF((Data!E:E=A2)*(Data!D:D>Data!C:C), ROW(Data!B:B)))))>MAX(MAX(ArrayFormula(IF((Data!B:B=A2)*(Data!D:D>Data!C:C), ROW(Data!B:B)))), MAX(ArrayFormula(IF((Data!E:E=A2)*(Data!C:C>Data!D:D), ROW(Data!B:B))))), TRUE, FALSE)))), COUNTIF(INDIRECT(ADDRESS(MAX(MAX(ArrayFormula(IF((Data!B:B=A2)*(Data!D:D>Data!C:C), ROW(Data!B:B)))), MAX(ArrayFormula(IF((Data!E:E=A2)*(Data!C:C>Data!D:D), ROW(Data!B:B)))))+1, 2, 1, TRUE, "Data")):INDIRECT(ADDRESS(MAX(MAX(ArrayFormula(IF((Data!B:B=A2)*(Data!C:C>Data!D:D), ROW(Data!B:B)))), MAX(ArrayFormula(IF((Data!E:E=A2)*(Data!D:D>Data!C:C), ROW(Data!B:B))))), 5, 1, TRUE, "Data")), A2) - COUNTA(IFNA(FILTER(INDIRECT(ADDRESS(MAX(MAX(ArrayFormula(IF((Data!B:B=A2)*(Data!D:D>Data!C:C), ROW(Data!B:B)))), MAX(ArrayFormula(IF((Data!E:E=A2)*(Data!C:C>Data!D:D), ROW(Data!B:B)))))+1, 2, 1, TRUE, "Data")):INDIRECT(ADDRESS(MAX(MAX(ArrayFormula(IF((Data!B:B=A2)*(Data!C:C>Data!D:D), ROW(Data!B:B)))), MAX(ArrayFormula(IF((Data!E:E=A2)*(Data!D:D>Data!C:C), ROW(Data!B:B))))), 2, 1, TRUE, "Data")), INDIRECT(ADDRESS(MAX(MAX(ArrayFormula(IF((Data!B:B=A2)*(Data!D:D>Data!C:C), ROW(Data!B:B)))), MAX(ArrayFormula(IF((Data!E:E=A2)*(Data!C:C>Data!D:D), ROW(Data!B:B)))))+1, 2, 1, TRUE, "Data")):INDIRECT(ADDRESS(MAX(MAX(ArrayFormula(IF((Data!B:B=A2)*(Data!C:C>Data!D:D), ROW(Data!B:B)))), MAX(ArrayFormula(IF((Data!E:E=A2)*(Data!D:D>Data!C:C), ROW(Data!B:B))))), 2, 1, TRUE, "Data"))=A2, INDIRECT(ADDRESS(MAX(MAX(ArrayFormula(IF((Data!B:B=A2)*(Data!D:D>Data!C:C), ROW(Data!B:B)))), MAX(ArrayFormula(IF((Data!E:E=A2)*(Data!C:C>Data!D:D), ROW(Data!B:B)))))+1, 6, 1, TRUE, "Data")):INDIRECT(ADDRESS(MAX(MAX(ArrayFormula(IF((Data!B:B=A2)*(Data!C:C>Data!D:D), ROW(Data!B:B)))), MAX(ArrayFormula(IF((Data!E:E=A2)*(Data!D:D>Data!C:C), ROW(Data!B:B))))), 6, 1, TRUE, "Data"))=0)))+COUNTA(IFNA(FILTER(INDIRECT(ADDRESS(MAX(MAX(ArrayFormula(IF((Data!B:B=A2)*(Data!D:D>Data!C:C), ROW(Data!B:B)))), MAX(ArrayFormula(IF((Data!E:E=A2)*(Data!C:C>Data!D:D), ROW(Data!B:B)))))+1, 5, 1, TRUE, "Data")):INDIRECT(ADDRESS(MAX(MAX(ArrayFormula(IF((Data!B:B=A2)*(Data!C:C>Data!D:D), ROW(Data!B:B)))), MAX(ArrayFormula(IF((Data!E:E=A2)*(Data!D:D>Data!C:C), ROW(Data!B:B))))), 5, 1, TRUE, "Data")), INDIRECT(ADDRESS(MAX(MAX(ArrayFormula(IF((Data!B:B=A2)*(Data!C:C>Data!D:D), ROW(Data!B:B)))), MAX(ArrayFormula(IF((Data!E:E=A2)*(Data!D:D>Data!C:C), ROW(Data!B:B)))))+1, 5, 1, TRUE, "Data")):INDIRECT(ADDRESS(MAX(MAX(ArrayFormula(IF((Data!B:B=A2)*(Data!C:C>Data!D:D), ROW(Data!B:B)))), MAX(ArrayFormula(IF((Data!E:E=A2)*(Data!D:D>Data!C:C), ROW(Data!B:B))))), 5, 1, TRUE, "Data"))=A2, INDIRECT(ADDRESS(MAX(MAX(ArrayFormula(IF((Data!B:B=A2)*(Data!D:D>Data!C:C), ROW(Data!B:B)))), MAX(ArrayFormula(IF((Data!E:E=A2)*(Data!C:C>Data!D:D), ROW(Data!B:B)))))+1, 6, 1, TRUE, "Data")):INDIRECT(ADDRESS(MAX(MAX(ArrayFormula(IF((Data!B:B=A2)*(Data!C:C>Data!D:D), ROW(Data!B:B)))), MAX(ArrayFormula(IF((Data!E:E=A2)*(Data!D:D>Data!C:C), ROW(Data!B:B))))), 6, 1, TRUE, "Data"))=0))), )

The ultimate aim was to find the length of an ongoing sequence of 1s (wins) for each name.

Data!B:B is name 1
Data!C:C is score 1
Data!D:D is score 2
Data!E:E is name 2
A2 is name required

It's a long formula and is actually spread over 4 cells - much more sensible like that - but that's the "one cell" version.

[AliGW]

Monstrous! And it's not an Excel formula, is it? So are you using Google Sheets? If so, you should say so when you open threads and post to the Other Platforms section.

[rubsley]

Yeah, I got a collaborator in and we ended up doing it on Sheets for easy sharing.

[AliGW]

OK. Any further issues will need to be raise in the Other Platforms section, then.