Interesting filter question

Graebeard

New Member
Joined
Mar 21, 2015
Messages
23
Office Version
  1. 2019
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
50,498
Office Version
  1. 365
Platform
  1. Windows
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?
 
Solution

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Graebeard

New Member
Joined
Mar 21, 2015
Messages
23
Office Version
  1. 2019
Platform
  1. Windows
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

New Member
Joined
Mar 21, 2015
Messages
23
Office Version
  1. 2019
Platform
  1. Windows
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
ALMN
1 CitiesProduct
2BarrieABC
3TorontoABC
4TorontoABC
5MisissaugaABC
6MisissaugaXYZ
7TorontoXYZ
8SidneyABC
9TorontoXYZ
10New YorkABC
11SidneyXYZ
12
Sheet1
Cell Formulas
RangeFormula
A1A1=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
ALMN
1TorontoCitiesProduct
3TorontoABC
4TorontoABC
7TorontoXYZ
9TorontoXYZ
12
Sheet1
Cell Formulas
RangeFormula
A1A1=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
ALMN
1 CitiesProduct
3TorontoABC
4TorontoABC
12
Sheet1
Cell Formulas
RangeFormula
A1A1=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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
50,498
Office Version
  1. 365
Platform
  1. Windows
I had a feeling that was coming. :)

Try either of these. Remember the array-entry.

Graebeard.xlsm
AMN
1LondonCitiesProduct
2LondonABC
6LondonABC
11LondonABC
12
Sheet4
Cell Formulas
RangeFormula
A1A1=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
AMNOP
1LondonCitiesProductLondon
2LondonABC
6LondonABC
11LondonABC
12
Sheet5
Cell Formulas
RangeFormula
A1A1=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,"")
P1P1=INDEX(M2:M100,MATCH(1,SUBTOTAL(3,OFFSET(M2,ROW(M2:M100)-ROW(M2),,1)),0))
 

Graebeard

New Member
Joined
Mar 21, 2015
Messages
23
Office Version
  1. 2019
Platform
  1. Windows

I had a feeling that was coming. :)

Try either of these. Remember the array-entry.

Graebeard.xlsm
AMN
1LondonCitiesProduct
2LondonABC
6LondonABC
11LondonABC
12
Sheet4
Cell Formulas
RangeFormula
A1A1=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
AMNOP
1LondonCitiesProductLondon
2LondonABC
6LondonABC
11LondonABC
12
Sheet5
Cell Formulas
RangeFormula
A1A1=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,"")
P1P1=INDEX(M2:M100,MATCH(1,SUBTOTAL(3,OFFSET(M2,ROW(M2:M100)-ROW(M2),,1)),0))
That worked. Allelujah! Many thanks
 
Last edited by a moderator:

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
50,498
Office Version
  1. 365
Platform
  1. Windows
You're welcome. Glad we got there in the end. :)
 

Forum statistics

Threads
1,140,924
Messages
5,703,182
Members
421,280
Latest member
Jaycee01

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
Top