# Interesting filter question

#### Graebeard

##### New Member
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
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?

### 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
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
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
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

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
You're welcome. Glad we got there in the end. Replies
10
Views
110
Replies
3
Views
482
Replies
1
Views
249
Replies
27
Views
443
Replies
9
Views
97

### Forum statistics

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.

### Which adblocker are you using?    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

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