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]

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]

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]

Thanks for updating your profile.

Give this a try

With multiple cities in the filter, A1 is blank

Graebeard.xlsm
	A												M
1													Cities
3													Toronto
4													Toronto
7													Toronto
8													Sydney
9													Sydney
10													Toronto
12
13
Sheet1

Cell Formulas
Range	Formula
A1	A1	=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
	A												M
1	Toronto												Cities
3													Toronto
4													Toronto
7													Toronto
10													Toronto
12
13
Sheet1

Cell Formulas
Range	Formula
A1	A1	=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]

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]

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]

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

 

[Graebeard]

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
	A	B										L	M
1													Cities
2													Barrie
3													Toronto
4													Toronto
5													Misissauga
6													Misissauga
7													Toronto
8													Sidney
9													Sidney
10													Toronto
11
12
13
14
15
Sheet1

Cell Formulas
Range	Formula
A1	A1	=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)),"")

 

[Peter_SSs]

Let's try this

Well that is working as expected. Can you filter that same sheet for a single city and post it again to demonstrate the error?

 

[Graebeard]

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
	A	B										L	M
1													Cities
2													Barrie
3													Toronto
4													Toronto
5													Misissauga
6													Misissauga
7													Toronto
8													Sidney
9													Toronto
10													New York
11													Sidney
12
13
14
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)),"")

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

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

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