Two similar questions: (1) Can I count how many rows contain certain text in any column? (2) Can you filter to only rows that have the selected text in any of 5 columns. Similar solutions, although the second requires a bit of VBA macro code.
Transcript of the video:
The MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel, Episode 1826: Is Selection in Any Column?
Hey.
Welcome back to the MrExcel netcast.
I’m Bill Jelen.
This is two for today for Veterans Day, and hey, if you happen to be watching this on Veterans Day, check that overlay there on the screen.
I got a free offer for all veterans today.
Today, I have two almost identical questions sent in.
First one, is there a simple formula that can count how many rows contain a string or text in any of several columns but only count the row once?
So, I have a whole bunch of different values here – three different columns – and we want to know if the selected item is in any of those columns.
So, I’ll just call this count.
So here's what we're going to do.
We're going to use the =COUNTIF function, COUNTIF.
We’re going to look at all of these values over here in Column A and see if they're equal to the selected item in F1.
I'll press F4 there to put the dollar signs in and say, is that greater than 0, greater than 0.
Then, what we're going to get there is a series of trues and falses.
Now, we could come up with some sort of a formula that counts the trues at the bottom, but let's just change this formula a little bit and say =IF that's true, then put a 1, otherwise a 0.
Copy that down, and then, to get the count, we'll just SUM, and so how many of these rows have Flower?
It is 2.
Choose something else.
Ike, 2.
Krill, 6.
Alright.
So, it's counting the number of rows.
And then I also had a question about doing the same thing with auto-filter, okay?
So, here's an area of expertise and the people can choose up to five areas of expertise.
They can choose them in any order and so we need to figure out, we want to filter this data set, to show which people have eggplant in any of these columns.
So, out here, I did a COUNTIF, C through G, to see if it's equal to C and then greater than zero, and now I'm getting either trues and falses, and what we'd like to have happen automatically is for the filter to automatically show us just the true values, okay, and, you know, if it was you or I that were doing this, we could turn on filter and then choose true, click OK, or if you had added the auto-filter icon to your toolbar, you could just choose one of the true items and click the auto-filter icon and that will filter by selection, but the question said, “Look, I'm giving these to people who are not good at Excel and that's far too many clicks for them to have to go through.” So, VBA is the answer.
I'm going to switch over.
AltF11.
A little bit of code here.
This is not in a regular module.
It is on the worksheet code pane, so double click there on Sheet 1.
From the top left drop-down, choose Worksheet.
From the top right drop-down, choose Change.
They will give us Private Sub Worksheet_Change with this variable called Target that tells us what just changed.
Check and see if C1 – and in my case, C1 is where that drop-down is – so if they change the drop-down in C1, then turn off the filters, go back to the headings A3 to H3 – that’s where your headings are – filter Field 8 – that’s column 8 – and Criteria is equal to True.
So watch how this works.
If we come here and choose, for example, Cherry, the formula identifies which rows have Cherry and then the filter works.
Now, we can even, if we wanted to, hide Column H once that's all working and then, as far as the people know, they are just kind of magically auto-filtering to something that's in any of these five columns instead of filtering to just one specific column.
So – kind of funny there – within a couple of days, the same type of question came in, whether we want to just count the number of rows that have the text or filter to the rows that have the text in any of those five columns.
And again, thanks to all of our veterans out there on Veterans Day.
I appreciate your service.
See you next time for another netcast from MrExcel.
Learn Excel from MrExcel, Episode 1826: Is Selection in Any Column?
Hey.
Welcome back to the MrExcel netcast.
I’m Bill Jelen.
This is two for today for Veterans Day, and hey, if you happen to be watching this on Veterans Day, check that overlay there on the screen.
I got a free offer for all veterans today.
Today, I have two almost identical questions sent in.
First one, is there a simple formula that can count how many rows contain a string or text in any of several columns but only count the row once?
So, I have a whole bunch of different values here – three different columns – and we want to know if the selected item is in any of those columns.
So, I’ll just call this count.
So here's what we're going to do.
We're going to use the =COUNTIF function, COUNTIF.
We’re going to look at all of these values over here in Column A and see if they're equal to the selected item in F1.
I'll press F4 there to put the dollar signs in and say, is that greater than 0, greater than 0.
Then, what we're going to get there is a series of trues and falses.
Now, we could come up with some sort of a formula that counts the trues at the bottom, but let's just change this formula a little bit and say =IF that's true, then put a 1, otherwise a 0.
Copy that down, and then, to get the count, we'll just SUM, and so how many of these rows have Flower?
It is 2.
Choose something else.
Ike, 2.
Krill, 6.
Alright.
So, it's counting the number of rows.
And then I also had a question about doing the same thing with auto-filter, okay?
So, here's an area of expertise and the people can choose up to five areas of expertise.
They can choose them in any order and so we need to figure out, we want to filter this data set, to show which people have eggplant in any of these columns.
So, out here, I did a COUNTIF, C through G, to see if it's equal to C and then greater than zero, and now I'm getting either trues and falses, and what we'd like to have happen automatically is for the filter to automatically show us just the true values, okay, and, you know, if it was you or I that were doing this, we could turn on filter and then choose true, click OK, or if you had added the auto-filter icon to your toolbar, you could just choose one of the true items and click the auto-filter icon and that will filter by selection, but the question said, “Look, I'm giving these to people who are not good at Excel and that's far too many clicks for them to have to go through.” So, VBA is the answer.
I'm going to switch over.
AltF11.
A little bit of code here.
This is not in a regular module.
It is on the worksheet code pane, so double click there on Sheet 1.
From the top left drop-down, choose Worksheet.
From the top right drop-down, choose Change.
They will give us Private Sub Worksheet_Change with this variable called Target that tells us what just changed.
Check and see if C1 – and in my case, C1 is where that drop-down is – so if they change the drop-down in C1, then turn off the filters, go back to the headings A3 to H3 – that’s where your headings are – filter Field 8 – that’s column 8 – and Criteria is equal to True.
So watch how this works.
If we come here and choose, for example, Cherry, the formula identifies which rows have Cherry and then the filter works.
Now, we can even, if we wanted to, hide Column H once that's all working and then, as far as the people know, they are just kind of magically auto-filtering to something that's in any of these five columns instead of filtering to just one specific column.
So – kind of funny there – within a couple of days, the same type of question came in, whether we want to just count the number of rows that have the text or filter to the rows that have the text in any of those five columns.
And again, thanks to all of our veterans out there on Veterans Day.
I appreciate your service.
See you next time for another netcast from MrExcel.
