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
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,167
Office Version
  1. 365
Platform
  1. Windows
  1. I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

  2. Are you looking for a vba solution or a formula solution?

is there a way to scan the first few entries in the filtered list to see if they are equal ?
That would not be robust as the first 50 values could be the same but the 51st could be different.
 

Graebeard

New Member
Joined
Mar 21, 2015
Messages
23
Office Version
  1. 2019
Platform
  1. Windows
  1. I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

  2. Are you looking for a vba solution or a formula solution?


That would not be robust as the first 50 values could be the same but the 51st could be different.
I would prefer a formula solution, but I think what's at the heart of my question is whether there is a function that takes the selected filter item (Toronto in the example) and uses that as the search criteria for the job of filtering the table.

Btw, thanks for the reminder. I have updated my personal page.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,167
Office Version
  1. 365
Platform
  1. Windows
Thanks for updating your profile. (y)

Give this a try

With multiple cities in the filter, A1 is blank

Graebeard.xlsm
AM
1 Cities
3Toronto
4Toronto
7Toronto
8Sydney
9Sydney
10Toronto
12
13
Sheet1
Cell Formulas
RangeFormula
A1A1=IF(COUNTIF(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)),"")


With only one city filtered that city is shown in A1

Graebeard.xlsm
AM
1TorontoCities
3Toronto
4Toronto
7Toronto
10Toronto
12
13
Sheet1
Cell Formulas
RangeFormula
A1A1=IF(COUNTIF(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)),"")
 

Graebeard

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

ADVERTISEMENT

Perfect! That's exactly what I was looking for. Gotta love Excel. In the right hands it can do just about everything. Really appreciate the knowledge base and helpfulness of the forum members.

Thanks very much

Grae
 

Graebeard

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

I'm a bit fuzzy, actually quite fuzzy, with the Index/match as I grew up on VBlookup, so struggling with the understanding the concept here. At any rate, I duplicated your example exactly as you show except I added entries for the unfiltered table. When I run the filter, I get an #N/A error in A1. I tried setting the whole table format to Text and General, with no difference. If I select the first city in my list, I get a Blank. I copy/pasted your displayed text into my own A1 and did eyeball both formulae and they do appear to be identical.

Am I missing a setting?

Thanks,

Grae
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,167
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Are you able to provide your sample sheet that is failing with XL2BB so that I can compare directly?
 

Graebeard

New Member
Joined
Mar 21, 2015
Messages
23
Office Version
  1. 2019
Platform
  1. Windows
Are you able to provide your sample sheet that is failing with XL2BB so that I can compare directly?
Let's try this

xl2bbFilter.xlsm
ABLM
1 Cities
2Barrie
3Toronto
4Toronto
5Misissauga
6Misissauga
7Toronto
8Sidney
9Sidney
10Toronto
11
12
13
14
15
Sheet1
Cell Formulas
RangeFormula
A1A1=IF(COUNTIF(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)),"")
 

Graebeard

New Member
Joined
Mar 21, 2015
Messages
23
Office Version
  1. 2019
Platform
  1. Windows
First Set shows all Cities. Second set picks a single city which is the first item in the range, the third picks another single city from further in the range. The last set shows multiple occurrences of a single city.



xl2bbFilter.xlsm
ABLM
1 Cities
2Barrie
3Toronto
4Toronto
5Misissauga
6Misissauga
7Toronto
8Sidney
9Toronto
10New York
11Sidney
12
13
14
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)),"")




xl2bbFilter.xlsm
ABLM
1 Cities
2Barrie
12
13
14
15
16
17
18
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)),"")


xl2bbFilter.xlsm
ABLM
1#N/ACities
10New York
12
13
14
15
16
17
18
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)),"")




xl2bbFilter.xlsm
ABLM
1#N/ACities
3Toronto
4Toronto
7Toronto
9Toronto
12
13
14
15
16
17
18
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)),"")
 

Forum statistics

Threads
1,144,394
Messages
5,724,084
Members
422,536
Latest member
Zeeshan53

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