Array Formula to Remove Zero Value Rows

R

Risk

Board Regular
Joined
Jul 27, 2006
Messages
71
I'm trying to create an array formula to remove zero value rows from a two column dataset, but thus far, I'm only able to accomplish this with a single column dataset which starts in row 1.
Code: 
{=IF(SUM(IF($B$1:$B$25<>0,1,0))>=ROW(),INDEX($B$1:$B$25,SMALL(IF(($B$1:$B$25)<>0,ROW($B$1:$B$25),""),ROW())),"")}

I'd like to expand this formula to include both columns A & B. Also I think my usage of ROW() is incorrect since if I insert a row above my data set, the array skips my first value "660.75".


Below is what I'm trying to accomplish. Thanks

2W3Ed.jpg
 
Sort by date Sort by votes

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Hello,

I have data from cells A1:A76 that I am trying to calculate the most frequent occurrence. The problem I am running into is that the data I have is copied from other sheets and all blank values show up as zero values. I really just need to find the most text occurring in cells A1:A76 and exclude the numbers. Here is the formula I currently have:

=INDEX(A1:A76,MATCH(MAX(COUNTIF($A$1:$A$76,A1:A76)),COUNTIF($A$1:$A$76,A1:A76)))

It is populating 0 as the most frequent occurrence but I want to take those out of the equation and just include the cells that have text.

Any ideas?

Thanks!
 
Upvote 0
nidk27 said:
Hello,

I have data from cells A1:A76 that I am trying to calculate the most frequent occurrence. The problem I am running into is that the data I have is copied from other sheets and all blank values show up as zero values. I really just need to find the most text occurring in cells A1:A76 and exclude the numbers. Here is the formula I currently have:

=INDEX(A1:A76,MATCH(MAX(COUNTIF($A$1:$A$76,A1:A76)),COUNTIF($A$1:$A$76,A1:A76)))

It is populating 0 as the most frequent occurrence but I want to take those out of the equation and just include the cells that have text.

Any ideas?

Thanks!
Click to expand...


[TABLE="width: 240"]
<COLGROUP><COL style="WIDTH: 83pt; mso-width-source: userset; mso-width-alt: 3925" width=110><COL style="WIDTH: 21pt; mso-width-source: userset; mso-width-alt: 995" width=28><COL style="WIDTH: 78pt; mso-width-source: userset; mso-width-alt: 3697" width=104><COL style="WIDTH: 58pt; mso-width-source: userset; mso-width-alt: 2759" width=78><TBODY>[TR]
[TD="class: xl64, width: 110, bgcolor: transparent"]NAD[/TD]
[TD="class: xl63, width: 28, bgcolor: transparent"] [/TD]
[TD="class: xl63, width: 104, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl63, width: 78, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]JAD[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]NAD[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"]MOST FREQ[/TD]
[TD="class: xl67, bgcolor: transparent"]COUNT[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]NAD[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: #daeef3"]JAD[/TD]
[TD="class: xl65, bgcolor: #daeef3, align: right"]4[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]KAD[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: #daeef3"]VAD[/TD]
[TD="class: xl65, bgcolor: #daeef3, align: right"]4[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]VAD[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: #daeef3"] [/TD]
[TD="class: xl65, bgcolor: #daeef3"] [/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]CAD[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: #daeef3"] [/TD]
[TD="class: xl65, bgcolor: #daeef3"] [/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]VAD[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]XAD[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]WAD[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]VAD[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]JAD[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]LAD[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]JAD[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]VAD[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]FAD[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]0[/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]0[/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]QAD[/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]JAD[/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]0[/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]0[/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[/TR]
</TBODY>[/TABLE]

C1: 1 (Adaptable parameter; 1 means the first modus)

C2, control+shift+enter (CSE), not just enter:
Rich (BB code): 
=SUM(IF(FREQUENCY(IF($A$2:$A$76<>"",IF(1-ISNUMBER($A$2:$A$76+0),
  MATCH($A$2:$A$76,$A$2:$A$76,0))),ROW($A$2:$A$76)-ROW($A$2)+1)>=
  LARGE(FREQUENCY(IF($A$2:$A$76<>"",MATCH($A$2:$A$76,$A$2:$A$76,0)),
  ROW($A$2:$A$76)-ROW($A$2)+1),C1),1))
Rich (BB code):

This counts in the ties of the first modus if any.

C4, CSE and copy down:
Rich (BB code): 
=IF($D4="","",INDEX($A$2:$A$76,SMALL(IF(FREQUENCY(IF($A$2:$A$76<>"",
  IF(1-ISNUMBER($A$2:$A$76+0),MATCH($A$2:$A$76,$A$2:$A$76,0))),
  ROW($A$2:$A$76)-ROW($A$2)+1)=$D4,ROW($A$2:$A$76)-ROW($A$2)+1),
  COUNTIF($D$4:D4,D4))))

D4, CSE and copy down:
Rich (BB code): 
=IF(ROWS($D$4:D4)<=$C$2,LARGE(FREQUENCY(IF($A$2:$A$76<>"",
  IF(1-ISNUMBER($A$2:$A$76+0),MATCH($A$2:$A$76,$A$2:$A$76,0))),
  ROW($A$2:$A$76)-ROW($A$2)+1),ROWS($D$4:D4)),"")
 
Upvote 0
I'm stuck with a similar problem as the original one.

I have "Boolean" data in all the cells in $A$1:$E$200. I'm looking to create a one-dimensional array in column J (or later) where all the cells would have the addresses of the cells in range $A$1:$E$200 which have the value as "TRUE".
 
Upvote 0
KolGuyXcel said:
I'm stuck with a similar problem as the original one.

I have "Boolean" data in all the cells in $A$1:$E$200. I'm looking to create a one-dimensional array in column J (or later) where all the cells would have the addresses of the cells in range $A$1:$E$200 which have the value as "TRUE".
Click to expand...

Try to provide a small input sample along with the output which must obtain from that sample.
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Solved
Counting cells when Values are Zero or None
Replies
6
Views
144
TheTiredEngineer
T
R
  • Question Question
Spill array that only shows rows that have value in certain cells
Replies
1
Views
83
Alex Blakenburg
A
I
  • Solved
Formula to count consecutive zeros at the end of a row
Replies
7
Views
276
Peter_SSs
Peter_SSs
BradleyS
  • Solved
Issue passing two parameters from sub to function to get back result
Replies
5
Views
120
RoryA
RoryA
T
  • Solved
Integer programming with binary variables in Solver - issue with constraint?
Replies
3
Views
279
StephenCrump
S

Forum statistics

Threads
1,223,246
Messages
6,170,999
Members
452,373
Latest member
TimReeks

Share this page

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
Back
Top