• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to remove null values from an array that is created by an array formula.

Q

questio

New Member
Hi!

I tried to find a descriptive title for a problem that has been nagging me for a while.

If I use an array formula of the type

If I have 2 columns a and B with


A B


1 X

2 X

3 Z

4 Y

5 X


and I want to make a table with

X,Y,Z with the results being the values in A when the column value in B is either X, Y or Z

using an array formula

=(A2:A6)*(B2:B6="X") down the column of the table with heading X, Y and Z, I will get values interspaced by 0.

I was hoping that there might be a way to force the array to shrink to an array with only the numerical values.

I find it hard to explain it in text so would like to send a spreadsheet with the formulas I have been using to clarify the question but I do not know how to share such a spread sheet. It would clarify my question I think.

I had also hoped to use dynamic ranges but the indirect function with dynamic range is a bit problematic. But it would be handy!

Thank you for taking the time to help others out. It is awesome, and I am really grateful for your inputs that fire the urge to trial to do things outside the square.

Many thanks,

Danièle
 
I realised Luke had taken the time to help loading files, and i had missed it!

I hope I managed OK,

This shoudl be the link to the sample workbook.

https://www.dropbox.com/sh/i5i5m45dt4zghdv/pzMgjGDPP5/array%20reduction.xlsm


Thank you!

Danièle
 
Hi Daniele ,


I don't think so. Your formula does the job always. I scrambled the values in column A , and there was no problem. The problem was the dots . in the columns A and B ; if you do not have any data to enter , leave the cells blank , instead of entering dots.


I have created two named ranges , which will ensure that you do not consider the blanks in the columns A and B.


The first named range is A , referring to :


=Sheet1!$A$2:INDEX(Sheet1!$A:$A,COUNTA(Sheet1!$A:$A))


The second named range is B , referring to :


=Sheet1!$B$2:INDEX(Sheet1!$B:$B,COUNTA(Sheet1!$A:$A))


The formulae in the range E3:G100 or even beyond , are :


=IFERROR(SMALL((B=E$2)*(A),(ROWS(A)-COUNTIF(B,E$2)+ROW(A1))),"")


Narayan
 
Thanks Narayan!

I am really sorry to have been so unclear. I also apologise for sending the file with the dots as those were only placed to show that the values can go further down, but I do not use them normally. I realise how confusing my question and my file was!

I was not clear when I said the values in A needed to be sequential. That was definitely the wrong word! I meant that the formulas would not work if there were duplicate values in A. For example, the values would go from 1 to 100, then again 1 to 100. Sorry about that!

I also sent a table I was trying to set up to show that were a simple formula such as (A2:A6)*(B2:B6="X") filled down actually gives all the result I need, the values are interspaced by “0” where the value in column B is not equal to X. To get rid of those “0”, I need to use a more complicated formula. Not being very mathematically minded, I thought that maybe there was a way to force a result array to only come up with the non “0” values. In that case, my heavy formula could be thrown in the bin and become much easier. The results would go from {1,0,0,,3,0,4,5,0,0,0,0,0}to {1,3,4,5} in a simple way. Is that possible?

Thank you for the named dynamic ranges and the formulae. I never thought of using index for named dynamic ranges. Quite handy. A new trick up my sleeve!

Narayan, I hope that I have clarified a bit what I am trying to achieve. I am enjoying array formulas, but am hopeless at manipulating them well, or understanding them enough to find a way to end up with arrays containing only the non”0” values.

Thanks 

Danièle
 
Danièle

Often it is possible to do something like:

if((A2:A6)*(B2:B6="X")>0,(A2:A6)*(B2:B6="X"))

this may result in {1,False,False,False,3,False,4,5,False,False,False,False,False}


depending on what the array is used for this is often better than having 0's as False doesn't evaluate to zero unless forced

And so functions like Small() ignore the Falses


Hope that helps in some way
 
Hi Danièle,


Building on what Hui suggested, you could try something like the following to get the subset that is non-zero.

[pre]
Code: 
#    A      B     C
1    10     X     X
2    11     A     Y
3    12     Y     Z
4    13     B
5    14     B
6    15     Z
[/pre]

Assuming that the rows in Col A is Named "datarange"

And your criteria is in C1:C3


The following formula will return an array {10,12,15}

=SMALL(COUNTIF(C1:C3,B1:B6)*datarange, ROW(INDEX(A:A,SUMPRODUCT(--(COUNTIF(C1:C3,B1:B6)=0))+1):INDEX(A:A,ROWS(datarange))))


COUNTIF(C1:C3,B1:B6)*datarange returns {10;0;12;0;0;15}

The segment ROW(INDEX(....):INDEX(...)) returns {4;5;6}, indicating the number of non-zero values.

The SMALL() function then returns the 4th smallest, 5th smallest and 6th smallest values.


One disadvantage with this approach is that you get a sorted sub-list. Perhaps that would work for you.


Cheers,

Sajan.
 
Thanks Hui and Sajan,

I am still not clear I think in describing what I am looking for though both replies opened new view for me.

The formula I wrote was to be able to list in each column the samples in column A that had resulted in result X, Y or Z in column. There are no other results possible but X, Y or Z. To get those results in each column headed X, Y or Z, I would like a simple formula of the type ($A2:$A12)*($B2:$B12="X") that could be filled down. This would give me the results together with zero values. The resulting array would be like {1,0,0,4,5,0,0,8,0} rather than {1,4,5,8} what I was hoping was for a way of dealing with the array with the zero values to end up with the array with no zero values.

Hui, thanks for the tip about using FALSE so that the long winded COUNTIF part of the formula can be omitted as SMALL ignores them! That is a step towards getting there!

Sajan, your method is a bit like the one I used, where the small formula chooses the first smallest value after the zeros, the next one etc. That was the only way I could figure to get the values. Your answer though does not discriminate between X, Y or Z which is what I am aiming for (the spread sheet shows it better). From your answer though, I fear that there is no easy way to remove from an array specific values without some roundabout way, like using SMALL or LARGE. 

Thanks for all the help on the search for easy way to remove unwanted values from an array. I did learn as always more than I even expected!

Danièle
 
Questio


If you could give us the context of what your trying to do instead of just asking for an array without 0's that would be better.


Ultimately It doesn't matter if the 0's or Falses are there, depending on what your going to do next with the Array ?
 
Hi Questio,


If you use ($B$2:$B$17=E$2)*($A$2:$A$17) all FALSE values will be converted to 0, so SMALL will return zeros too. Use SMALL with IF, so SMALL only take TRUE values, even If data Is NOT in sequential.


In E3 with CTRL+SHIFT+ENTER,


=IFERROR(SMALL(IF($B$2:$B$100=E$2,$A$2:$A$100),ROWS(E$3:E3)),"")


Copy down & across. So this will return 1, 4, 7, 8 etc in each cell.


If you want to get all these values in an array,


=SMALL(IF($B$2:$B$100=E$2,$A$2:$A$100),ROW(INDIRECT("1:"&COUNTIF($B$2:$B$100,E$2))))


Will give all possible values in an Array like


{1;4;7;8;11;12;13;15}


Hope this helps.

Haseeb
 
Hi Hui!

All I would like is to be able to line up the results of the array in a column so that only the real values are lined up rather than all the values (real values and False or null values) of the array.

I want the cells that should be filled from entries from the array to look like the column on the right rather than the column on the left.

Column with results from Array with “False” or “o” Column with Array with only values of interest

29

0

0

12

0

4

0

5

0

0

0 29

12

4

5


just to show how I would like the resulting values to line up.

As cells are populated by values from an array, I was hoping there was some trick to force that array to simply be manipulated into new array where "False" values or "null" values who be left out so that when you lined up the values in the column, you would only have the values you wanted.

It seems that to achieve this is not a one-step trick, or one step conversion trick. The discussion here helped me realise that! I suppose for neophytes, you always hope that there is some miracle way, but it is not always the case.

I tried to clarify the question in the attached workbook called array reduction 2.

Sorry to have not been very clear.

Thanks for all the help! It probably was very frustrating for the excel ninjas, but you did help me enormously and I did get an answer, albeit not the simple one I had thought might be possible.

Danièle

http://dl.dropbox.com/u/70211496/array%20reduction%202.xlsm
 
Hi Hui!

I just wanted the values listed. The sorting was the only way I could think of to get rid of the zeros. The only "must" was to get rid of what is not needed,i.e. the zeros! :)

Danièle
 
Hi Hui!

I just wanted the values listed. The sorting was the only way I could think of to get rid of the zeros. The only "must" was to get rid of what is not needed,i.e. the zeros! :)

Danièle
 
Danièle


In your sample file in cell E5:

=INDEX($A$2:$A$17,SMALL(IF($B$2:$B$17=E$4,ROW($B$2:$B$17)-1),ROW()-ROW($E$4)),0) Ctrl+Shift+Enter


Now copy and paste down and across


or maybe this extension:

=IFERROR(INDEX($A$2:$A$17,SMALL(IF($B$2:$B$17=E$4,ROW($B$2:$B$17)-1),ROW()-ROW($E$4)),0),"") Ctrl+Shift+Enter
 
Thanks! It seems that you definitely need to use the formula "SMALL" to gather the first value bigger that "0" which means that the results will be sorted! :)

Good gymnastic of teh brain!

Danièle
 
Danièle


The formula returns the Values from Column A in order of there appearance not sorted


If you change A2 to 10 and A5 to 15

the list is now 10, 15, 7...

That is in order, not sorted
 
Oops, yes!

I am so grateful for all the input as i am now getting to use more and more array formulas though they are a bit slower for large quantity of data, but are great fun.

the forum here gives such "kicks" in to trial out new approaches. As I read in some other posts, yes, it is very user friendly and some posts that seem so simple, you feel at eas with them because you know that those simple posts are the ones that pave the enxt harder queries.

Thanks you all experts for that!

Danièle
 
You must log in or register to reply here.
Back
Top