Interesting filter question

[Graebeard]

I have a strange request. I have a large table with many columns and I'd like to copy the selected filter value and copy it into another cell.

Let me clarify. As an example, assume a table that includes Column M for cities.

Step 1: Select "Toronto" as our filter in Column M, resulting in a large number of non-contiguous rows
Step 2: Take this value and display it in cell A1
Step 3: Then, in B1 write a formula that says something like =IF(A1 = "Toronto", {do something}, {do nothing})

So, steps 1 & 3 are trivial, But how can I carry out Step 2 ?

Without a filter, or some other filter setting, there would be a large number of city names which should set B1 to a blank. That's expected so it's fine
But, with the filter set so only one name appears in Column M, that value appears in A1, and then B1 can do its thing
Questions: - once the City filter is set to one city, the displayed column would contain only one value. Could this be used somehow?
- is there a way to scan the first few entries in the filtered list to see if they are equal ?


Any and all suggestions would be greatly appreciated

Grae

 

[Peter_SSs]

Thanks. Unfortunately copying your sheet, they all work correctly for me.
Does it make any difference if you select A1, press F2 then confirm the formula with Ctrl+Shift+Enter, not just Enter?

 

[Graebeard]

OMG it's an array formula. It never occured to me as the only time I have used those was for Sum's.

I knew it had to be something like formatting or a setting blooper at my end since the syntax was pretty standard.

Peter, thanks for your quick and patient assistance

 

[Graebeard]

Hi Peter,

I’ve fixed my table and it works fine except if a filter in a different column shortens the list. I can’t really understand the syntax used in the formula, but I gather it counts the number of occurrences of the filtered list.

If only Cities are filtered, we get 4 entries, but if we also filter on a different column, we get only 2 hits and A1 returns nothing.

My apologies and thanks for taking up your time.



Grae

xl2bbFilter.xlsm
	A											L	M	N
1													Cities	Product
2													Barrie	ABC
3													Toronto	ABC
4													Toronto	ABC
5													Misissauga	ABC
6													Misissauga	XYZ
7													Toronto	XYZ
8													Sidney	ABC
9													Toronto	XYZ
10													New York	ABC
11													Sidney	XYZ
12
Sheet1

Cell Formulas
Range	Formula
A1	A1	=IF(COUNTIF(M2:M99,INDEX(M2:M99,MATCH(1,SUBTOTAL(3,OFFSET(M2,ROW(M2:M99)-ROW(M2),,1)),0)))=SUM(SUBTOTAL(3,OFFSET(M2,ROW(M2:M99)-ROW(M2),,1))),INDEX(M2:M99,MATCH(1,SUBTOTAL(3,OFFSET(M2,ROW(M2:M99)-ROW(M2),,1)),0)),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

xl2bbFilter.xlsm
	A											L	M	N
1	Toronto												Cities	Product
3													Toronto	ABC
4													Toronto	ABC
7													Toronto	XYZ
9													Toronto	XYZ
12
Sheet1

Cell Formulas
Range	Formula
A1	A1	=IF(COUNTIF(M2:M99,INDEX(M2:M99,MATCH(1,SUBTOTAL(3,OFFSET(M2,ROW(M2:M99)-ROW(M2),,1)),0)))=SUM(SUBTOTAL(3,OFFSET(M2,ROW(M2:M99)-ROW(M2),,1))),INDEX(M2:M99,MATCH(1,SUBTOTAL(3,OFFSET(M2,ROW(M2:M99)-ROW(M2),,1)),0)),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

xl2bbFilter.xlsm
	A											L	M	N
1													Cities	Product
3													Toronto	ABC
4													Toronto	ABC
12
Sheet1

Cell Formulas
Range	Formula
A1	A1	=IF(COUNTIF(M2:M99,INDEX(M2:M99,MATCH(1,SUBTOTAL(3,OFFSET(M2,ROW(M2:M99)-ROW(M2),,1)),0)))=SUM(SUBTOTAL(3,OFFSET(M2,ROW(M2:M99)-ROW(M2),,1))),INDEX(M2:M99,MATCH(1,SUBTOTAL(3,OFFSET(M2,ROW(M2:M99)-ROW(M2),,1)),0)),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Peter_SSs]

I had a feeling that was coming.

Try either of these. Remember the array-entry.

Graebeard.xlsm
	A												M	N
1	London												Cities	Product
2													London	ABC
6													London	ABC
11													London	ABC
12
Sheet4

Cell Formulas
Range	Formula
A1	A1	=IF(SUM(SUBTOTAL(3,OFFSET(M2,ROW(M2:M100)-ROW(M2),,1))*(M2:M100=INDEX(M2:M100,MATCH(1,SUBTOTAL(3,OFFSET(M2,ROW(M2:M100)-ROW(M2),,1)),0))))=SUM(SUBTOTAL(3,OFFSET(M2,ROW(M2:M100)-ROW(M2),,1))),INDEX(M2:M100,MATCH(1,SUBTOTAL(3,OFFSET(M2,ROW(M2:M100)-ROW(M2),,1)),0)),"")

.. or with a helper cell to shorten the A1 formula

Graebeard.xlsm
	A												M	N	O	P
1	London												Cities	Product		London
2													London	ABC
6													London	ABC
11													London	ABC
12
Sheet5

Cell Formulas
Range	Formula
A1	A1	=IF(SUM(SUBTOTAL(3,OFFSET(M2,ROW(M2:M100)-ROW(M2),,1))*(M2:M100=P1))=SUM(SUBTOTAL(3,OFFSET(M2,ROW(M2:M100)-ROW(M2),,1))),P1,"")
P1	P1	=INDEX(M2:M100,MATCH(1,SUBTOTAL(3,OFFSET(M2,ROW(M2:M100)-ROW(M2),,1)),0))

 

[Graebeard]

I had a feeling that was coming.

Try either of these. Remember the array-entry.

Graebeard.xlsm
	A												M	N
1	London												Cities	Product
2													London	ABC
6													London	ABC
11													London	ABC
12
Sheet4

Cell Formulas
Range	Formula
A1	A1	=IF(SUM(SUBTOTAL(3,OFFSET(M2,ROW(M2:M100)-ROW(M2),,1))*(M2:M100=INDEX(M2:M100,MATCH(1,SUBTOTAL(3,OFFSET(M2,ROW(M2:M100)-ROW(M2),,1)),0))))=SUM(SUBTOTAL(3,OFFSET(M2,ROW(M2:M100)-ROW(M2),,1))),INDEX(M2:M100,MATCH(1,SUBTOTAL(3,OFFSET(M2,ROW(M2:M100)-ROW(M2),,1)),0)),"")

.. or with a helper cell to shorten the A1 formula

Graebeard.xlsm
	A												M	N	O	P
1	London												Cities	Product		London
2													London	ABC
6													London	ABC
11													London	ABC
12
Sheet5

Cell Formulas
Range	Formula
A1	A1	=IF(SUM(SUBTOTAL(3,OFFSET(M2,ROW(M2:M100)-ROW(M2),,1))*(M2:M100=P1))=SUM(SUBTOTAL(3,OFFSET(M2,ROW(M2:M100)-ROW(M2),,1))),P1,"")
P1	P1	=INDEX(M2:M100,MATCH(1,SUBTOTAL(3,OFFSET(M2,ROW(M2:M100)-ROW(M2),,1)),0))

That worked. Allelujah! Many thanks

 

[Peter_SSs]

You're welcome. Glad we got there in the end.