• 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.

Extract numbers from a column that match a criterion

X

xander

New Member
Hi I am wondering if you can help me with this:

I have file with several columns. Column B contains cells that equals a value of 10 or has values > 10. Column C contains data that is corresponding to the value in column B. Now I would like to retrieve only those numbers from column C corresponding to the data in column B with value > 10. When the values from column C have been retrieved I want to put them in a new column so I have all relevant data in a single column.


I looked at several suggestions with the MATCH and INDEX function, but sofar this only works when you want to lookup a specified number. There must be some clever way how this filter should also work for values > 10.


Can anyone help me with it?


Thanks in advance,


xander
 
Xander


Firstly, Welcome to the Chandoo.org forums.


You may want to look at using either an Advanced Filter

Refer: http://chandoo.org/forums/topic/chandoo-advanced-filter

which has a number of links to Advanced Filter posts here and elsewhere


or


Look at using a technique that Luke M described at Formula Forensics 003:

http://chandoo.org/wp/2011/11/18/formula-forensics-003/
 
Dear Hui,


Thanks for your quick reply. I tried the techniques described by Luke M and it actually works if the criteria is set to a fixed number or a text string (in the example it was "Vetgetable"

But I would like to filter only those values that are not equal to the criterion. In the example from Luke that would be "<> Vetgetables". Is there a way to do this?


Many thanks, Xander
 
It's exactly as you said. To get everything that doesn't match criteria (vegetables)

=IF(COUNTIF(A$2:A$10,"<>"&$D$2) < ROWS($E$2:E2), "", INDEX(B:B, SMALL( IF($A$2:$A$10 <>$D$2, ROW( $A$2:$A$10)), ROW(A1))))


One tricky thing that can happen is that if you don't limit the range to look at (A2:A10) to just cells with data, you may pull a bunch of blank cells, since blank <> criteria.
 
Luke,


Many thanks! It works now and your answer has been of great help. However in case I want to expand the selection criteria by adding another IF statement I seem to get into trouble.


This is how I set up the formula:

=IF(COUNTIFS(K:K,"<>"&$A$8,R:R,$A$9)<ROWS($AB$3:AB3),"",INDEX(L:L,SMALL(IF(AND($K$3:$K$40802<>$A$8,$R$3:$R$40802=$A$9),ROW($K$3:$K$40802)),ROW(K1))))


So I added criterion $A$9 that is to be found in column R.

With respect to the previous formula I changed countif to countifs and added the AND function (both <> $A$8 and =$A$9 need to be met).


Can you indicate what goes wrong?


Thanks again, Xander
 
the COUNTIFS function is ok, but the AND won't work as that function returns a single output, and we need an array. We need tu multiply the logic arrays against each other if we want an "and" type logic check (add if it had been an "or"), so make it look like this:

=IF(COUNTIFS(K:K,"<>"&$A$8,R:R,$A$9)<ROWS($AB$3:AB3),"",INDEX(L:L,SMALL(IF(($K$3:$K$40802<>$A$8)*($R$3:$R$40802=$A$9),ROW($K$3:$K$40802)),ROW(K1))))


The multiplication is similar to how SUMPRODUCT can be used as a sort of SUMIFS function.

http://chandoo.org/wp/2009/11/10/excel-sumproduct-formula/
 
Luke,


It's really amazing to see how this works!! Now to make it even more sophisticated is it possible to do the following:

Column K contain values either equal to 10 or >10, so with the formula above each set of data matching the criteria is printed below each other in column AB. Is it possible to print each dataset to a different column, instead of printing them all to column AB? This would improve the visibility of my spreadsheet.


Thanks for your excellent help sofar!


Regards, Xander
 
I'm afraid you lost me there xander. What exactly is the question? Could you show an example maybe?
 
Luke,


Sorry for not phrasing this more clearly. Here's an example that will hopefully clarify what I mean (refer to the example in one of our previous conversations). Right now the output in column AB is like this (only values <> 10 are printed):

[pre]
Code: 
Column K     Column L     Column AB
10            0             120
10            0             130
10            0             200
12            120           210
15            130           15
10            0
10            0
24            200
26            210
10            0
17            15
So all values matching the criteria are printed below each other in column AB.


What I want is the output to be like this:

Column AB   Column AC   Column AD
120           200          15
130           210
[/pre]
Hope this clarifies what I mean.


Thanks, Xander
 
If you know how many columns wide you want to spread it over, yes. Assuming you go with 3 columns, change the end of the formula like so:

=IF(COUNTIFS($K:$K,"<>"&$A$8,$R:$R,$A$9)<ROWS($AB$3:AB3),"",INDEX($L:$L,SMALL(IF(($K$3:$K$40802<>$A$8)*($R$3:$R$40802=$A$9),ROW($K$3:$K$40802)),COLUMN(A1)+(ROW(A1)-1)*3)))


If it will be more/less than 3 columns, change the "3" at the end of the bolded section.
 
Luke,


It is not working as described above. In which column is the output supposed to be printed? Is that A1? With your suggestion the output is still only printed in column AB and the output is different than it was with the original formula as shown below


=IF(COUNTIFS(K:K,"<>"&$A$8,R:R,$A$9)<ROWS($AB$3:AB3),"",INDEX(L:L,SMALL(IF(($K$3:$K$40802<>$A$8)*($R$3:$R$40802=$A$9),ROW($K$3:$K$40802)),ROW(K1))))


In addition I do not know how many columns the data will be spread over, because this can vary.


Any idea?
 
For a single formula to work, we'll need to know either the # of rows your going to use, or the # of columns. It doesn't matter where you first put the formula. My intent was for you to put it somewhere, then copy over to make 3 columns, and then down as needed. Note that I also added some absolute references in the first part of formula so that they don't change when you copy the cell to the right.
 
Luke,


It works now. Made a mistake in copying the last part of the formula...

I changed the row with columns in the last part of the formula and the output is like this now:


Column AB Column AC Column AD

120 200 15

130 210

200 15

210

15


So allmost as I want it; but column AB also shows values 200, 210 and 15 and column AC also shows the value 15. Is there a possibility that these "duplicate" values in columns AB and AC are not printed?


Thanks, Xander
 
You must log in or register to reply here.
Back
Top