A week ago Tarun asked a question on the Chandoo.org Forums.
“I have got multiple names in each row and would like to have what name is repeated maximum number of times and how many times?
Eg. Ram, Amita, Obama, Ram, Willi, Ram, Amita, Chandoo, Ram, Willi
Ans: Ram (4 times)”
(The list and answers are edited)
Chandoo responded with a neat Array Formula:
=INDEX(B2:K2,MATCH(MAX(COUNTIF(B2:K2,B2:K2)), COUNTIF(B2:K2,B2:K2),0)) &
” (“&MAX(COUNTIF(B2:K2,B2:K2))&” times)”
Lets take a look inside this and see how it works
THE EXAMINATION
The formula has two parts separated by a &
=INDEX(B2:K2,MATCH(MAX(COUNTIF(B2:K2,B2:K2)), COUNTIF(B2:K2,B2:K2),0))
and
&
and
” (“&MAX(COUNTIF(B2:K2,B2:K2))&” times)”
Each part is separate and can be used independently, the & character simply joins the two parts together to make a single string which answers Tarun’s question, Ram (4 times).
Now, lets look at each part.
You can follow along with this forensic examination by downloading the Sample Data File.
=INDEX(B2:K2,MATCH(MAX(COUNTIF(B2:K2,B2:K2)), COUNTIF(B2:K2,B2:K2),0))
This is a single Index Function with 2 components, being:
a Range B2:K2 and
a Count MATCH(MAX(COUNTIF(B2:K2,B2:K2)), COUNTIF(B2:K2,B2:K2),0)
Typically an Index Function uses 3 components
=Index(Array, Row Number,[Column Number])
In this example the Range is a single Row, B2:K2
And so using the Counter in the Row spot has the effect of counting down the first Column and then continuing at the top of the second Column etc
So the formula used:
=INDEX(B2:K2,MATCH(MAX(COUNTIF(B2:K2,B2:K2)), COUNTIF(B2:K2,B2:K2),0))
Is equivalent to:
=INDEX(B2:K2,1,MATCH(MAX(COUNTIF(B2:K2,B2:K2)), COUNTIF(B2:K2,B2:K2),0))
Now lets jump ahead to the COUNTIF(B2:K2,B2:K2) bit
If you copy =COUNTIF(B2:K2,B2:K2) to a cell, Press F2 and then evaluate the Formula using F9
You will see that it returns an array. The array is highlighted by the squiggly brackets { } ‘s
={4,2,1,4,2,4,2,1,4,2}
This is the heart of the solution.
What this is showing us is that for each position in the range B2:K2, the count of how many times that cells value occurs in the range B2:K2
So the formula
=INDEX(B2:K2,MATCH(MAX(COUNTIF(B2:K2,B2:K2)), COUNTIF(B2:K2,B2:K2),0))
Is equivalent to
=INDEX(B2:K2,MATCH(MAX({4,2,1,4,2,4,2,1,4,2}), {4,2,1,4,2,4,2,1,4,2},0))
Looking at the MAX({4,2,1,4,2,4,2,1,4,2}) part, this simplifies to 4, the Maximum value of the array (Remember this line, we’ll come back to it later).
So our simplified formula is now: =INDEX(B2:K2,MATCH(4, {4,2,1,4,2,4,2,1,4,2},0))
Now looking at the MATCH(4, {4,2,1,4,2,4,2,1,4,2},0) part of the equation
You can see that Match is looking for the value 4, in the array {4,2,1,4,2,4,2,1,4,2}, which is the First value , Position 1, the 0 requesting that an exact match is found.
So that MATCH(4, {4,2,1,4,2,4,2,1,4,2},0) is equivalent to 1
So our equation =INDEX(B2:K2,MATCH(4, {4,2,1,4,2,4,2,1,4,2},0))
Is now simplified even more to =INDEX(B2:K2, 1)
Index will then look in B2:K2 and will return the first cell or “Ram” in this example.
& “(” & MAX(COUNTIF(B2:K2,B2:K2)) & ” times)”
The second part of the equation is responsible for counting the number of Times Ram occurs and displaying it with some text.
& “(” & MAX(COUNTIF(B2:K2,B2:K2)) & ” times)”
The parts displayed in Red above add the text ( and times) to the Count
Remember the section MAX(COUNTIF(B2:K2,B2:K2)) which was explained above and evaluates to 4 in this case
So the & “(” & MAX(COUNTIF(B2:K2,B2:K2)) & ” times)”
Part evaluates to: ( 4 times)
With the initial & adding it to the text of the first part Ram for the final result – Ram ( 4 times)
LEARN MORE ABOUT ARRAY FORMULAS
You can learn more about Array Formulas at the following links:
http://www.cpearson.com/excel/ArrayFormulas.aspx
http://www.databison.com/index.php/excel-array-formulas-excel-array-formula-syntax-array-constants/
http://office.microsoft.com/en-us/excel-help/introducing-array-formulas-in-excel-HA001087290.aspx
Chandoo.org has several articles on Array Formulas
http://chandoo.org/wp/tag/array-formulas/
FORENSIC FORMULAS
Would you like to see more “Forensic” examination of complex formulas ?
Let us know in the comments below and it may become a regular section at Chandoo.org.
21 Responses to “How to Filter Odd or Even Rows only? [Quick Tips]”
Infact, instead of using =ISEVEN(B3), how about to use =ISEVEN(ROW())
So it takes away any chance of wrong referencing.
I like Daily Dose of Excel
I like it.
Just a heads up, you do need to have the Analysis ToolPak add-in activated to use the ISEVEN / ISODD functions. An alternative to ISEVEN would be:
=MOD(ROW(),2)=0
rather than use a formula, couldn't you enter "true" in first cell and "false" in the second and drag it down and than filter on true or false.
Just for clarification, is Ashish looking to filter by even or odd Characters or rows?
so many functions to learn!
Nice support by chandoo and team as a helpdesk. Give us more to learn and make us awesome. Always be helpful.......
In case you want to delete instead of filter,
IF your data is in Sheet1 column A
Put this in Sheet2 column A and drag down
=OFFSET(Sheet1!A$1,(ROWS($1:1)-1)*2,,)
(This is to delete even rows)
To delete odd rows :
=OFFSET(Sheet1!A$2,(ROWS($1:1)-1)*2,,)
If your numbered cells did not correspond to rows, the answer would be even simpler:
=MOD([cell address],2), then filter by 0 to see evens or 1 to see odds.
I sometimes do this using an even simpler method. I add a new column called "Sign" and put the value of 1 in the first row, say cell C2 if C1 contains the header. Then in C3 I put the formula =-1 * C2, which I copy and paste into the rest of the rows (so C4 has =-1 * C3 and so forth). Now I can just apply a filter and pick either +1 or -1 to see half the rows.
Another way, which works if I want three possibilities: in C2 I put the value 1, in C3 I put the value 2, in C4 I put the value 3, then in C5 I put the formula =C2 then I copy C5 and paste into all the remaining rows (so C6 gets =C3, C7 gets =C4, etc.). Now I can apply a filter and pick the value 1, 2, or 3 to see a third of the rows.
Extending this approach to more than 3 cases is left as an exercise for the reader.
Another way =MOD(ROW();2). In this case, must to choose betwen 1 and 0.
[...] How to Filter Even or Odd rows only [...]
very different style Odd or Even Rows very easy way to visit this site
http://www.handycss.com/tips/odd-or-even-rows/
Thanks for the tip, it worked like magic, saved having to delete row by row in my database.
Thanks!
Thankssssssssssssssss
Hi Chandoo- First of all thanks for the trick. It helped me a lot. Here I have one more challenge. Having filtered the data based on odd. I want to paste data in another sheet adjacent to it. How can I do that?
For Example-
A 1 odd
B 3 odd
C 4 even
D 6 even
I have fileted the above data for odd and want to copy the "This is odd number" text in adjacent/next sheet here. How can I do that. After doing this my data should look like this
A 1 odd This is odd number
B 3 odd This is odd number
C 4 even
D 6 even
Hi! Could you please help me find a formula to filter by language?
Thank you!
Chandoo SIR,
I HAVE A DATA IN EXCEL ROWS LIKE BELOW IS THERE ANY FORMULA OR A WAY WHERE I CAN INSTRUCT I CAN MAKE CHANGES , MEANS I WANT TO WRITE ONLY , THE FIG IS FRESH, BUT IN BELOW ROW IT WILL AUTOMATICALLY TAKE THE SOME WORDS FROM FIGS AND MAKE IN PLURAL FORM , WHILE USING '' ARE'' LIKE BELOW
The fig is fresh - row 1
Figs are fresh - row 2
The Pomegranate is red - row 3
Pomegranates are red - row 4
=IF(EVEN(A1)=A1,"EVEN - do something","ODD - do something else") with iferron (for blank Cell)