/docs/community?hl=en
/docs/community?hl=en
10/19/13
Original Poster
Julia Potter

Array formula to remove blanks

I am trying to avoid using macros in a spreadsheet and I want to consolidate a list of data that contains blanks.  The array formula I am trying to use is this:

=ArrayFormula(IF(ROW()-ROW(noblanks)+1>ROWS(blanks)-COUNTBLANK(blanks),"",INDIRECT(ADDRESS(SMALL((IF(blanks<>"",ROW(blanks),ROW()+ROWS(blanks))),ROW()-ROW(noblanks)+1),COLUMN(blanks),4))))

I select my "noblanks" array, enter the formula in the formula bar, and press CTRL+SHIFT+ENTER.

However, the formula returns the first real value for every cell instead of going on to list the other values.  When I try to make changes, I can briefly see the correct next value before something automatically adjusts.

Example:

(blank)
(blank)
value1
(blank) 
value2

will show up in the noblanks range as

value1
value1
value1
value1
value1

What can I do to fix this?

Community content may not be verified or up-to-date. Learn more.
Recommended Answer
Was this answer helpful?
How can we improve it?
All Replies (12)
Yogi Anand
10/19/13
Yogi Anand
Hi Julia:

how about if you use the FILTER function to pull only non-blank values ...
post back if you need to discuss it further.

Make It A Great One
Cheers!
Yogi
Cloud Computing -- Google Docs Way
yogi--anand-consulting.blogspot.com
John3791
10/19/13
John3791
Are you just trying to list value from a column without blank values?

=filter(A:A; A:A<>"")
10/20/13
Original Poster
Julia Potter
I have tried the filter.  My data range that contains blanks automatically populates based on other data entries.  If I filter to remove blanks, the data range doesn't update to add/remove new values anymore.
John3791
10/20/13
John3791
Is it possible to share your spreadsheet? Or can you recreate the behavior in another sheet that you can share? In every scenario I have tested, the filter function works as expected.

In my testing, the SMALL function in your formula is what is causing the first value to be repeated, since that is always the smallest row number.
10/20/13
Original Poster
Julia Potter
6 MORE
--Hyde
6/3/14
--Hyde
Hi Heather,

To replace zeros in your results with blank values, try something like this:

=arrayformula( if(O2:O * P2:P; O2:O * P2:P; iferror(1/0) ) )

To filter out rows where the result is zero or not a number, try something like this:

=query( arrayformula( if(O2:O * P2:P; O2:O * P2:P; iferror(1/0) ) ); "select Col1 where Col1 is not null"; 0 )

If you need more help, please share a sample spreadsheet.

Cheers --Hyde

 
This question is locked and replying has been disabled. Still have questions? Ask the Help Community.

Badges

Some community members might have badges that indicate their identity or level of participation in a community.

 
Google Employee — Google product team members and community managers
 
Community Specialist — Google partners who help ensure the quality of community content
 
Platinum Product Expert — Community members with advanced product knowledge who help other Google users and Product Experts
 
Gold Product Expert — Community members with in-depth product knowledge who help other Google users by answering questions
 
Silver Product Expert — Community members with intermediate product knowledge who help other Google users by answering questions
 
Product Expert Alumni — Former Product Experts who are no longer members of the program
Community content may not be verified or up-to-date. Learn more.

Levels

Member levels indicate a user's level of participation in a forum. The greater the participation, the higher the level. Everyone starts at level 1 and can rise to level 10. These activities can increase your level in a forum:

  • Post an answer.
  • Having your answer selected as the best answer.
  • Having your post rated as helpful.
  • Vote up a post.
  • Correctly mark a topic or post as abuse.

Having a post marked and removed as abuse will slow a user's advance in levels.

View profile in forum?

To view this member's profile, you need to leave the current Help page.

Report abuse in forum?

This comment originated in the Google Product Forum. To report abuse, you need to leave the current Help page.

Reply in forum?

This comment originated in the Google Product Forum. To reply, you need to leave the current Help page.

false
Search
Clear search
Close search
Google apps
Main menu
Search Help Center
true
35
false