Extracting a continuous list based on criteria

Dear Forum,

Is there a way I can get a continuous list, ie no missing rows, from a much larger list. (New list in separate columns to the R of the list.)

I want to extract data from column A, only if the data in col C matches my specified criteria - I want this info to appear as a list with no gaps, ie I can do this easily using "If" but there will be lots of blank rows doing it this way.

Furthermore, as I need two different lists from the same data block, I need to specify whether number (extract these to col N) or text (extract to col O).

Have explained fully (I think) in attached - will be grateful for any help

Bungaree

If you can do this manually (or with VBA) have you considered using Advanced Filtering ?

Next point -- I'm not sure what you mean by "it has SH2 or the contents of cell in the same row in Column C" ? I'm presuming you mean either Cx = "SH2" or Cx = Ax ?

If you discard Filtering etc.. and are determined to use formulae I can't recommend strongly enough that you use "helpers" else you will be left with lots of array formulae which by the sound of this is not advisable given the size of your real range. The helpers will permit you to avoid potentially expensive array.

I base the below suggestion on the fact you have only 2 conditions - ie number or text -- if you have more this wouldn't work....

In M range:

M3: =IF(OR($D3="SH2",$D3=$B3),IF(ISNUMBER($B3),MAX($M$2:$M2)+1,MIN(0,$M$2:$M2)-1),"")

The above generates a unique index where numerical values are assigned values 1 to x ... and text values -1 to -x

Then use row 2 to store the count of values to be returned based on M, eg:

N2: =COUNTIF($M$3:$M$2000,">0")
P2: =COUNTIF($M$3:$M$2000,"<0")
Q2: =P2

The above help you avoid unnecessary calcs in rows 3 onwards

N3: =IF(ROW()-2>N$2,"",INDEX(B$3:B$2000,MATCH(ROW(N3)-2,$M$3:$M$2000,0),1))
the -2 is an adjustment (row above first formula) --> you could use ROWS(N$3:N3) instead of ROW()-2 if you prefer.


P is pretty much the same -- you could write the formula to handle both N & P with one formula but you would be adding one extra calc to each of your formulae (to differentiate between > 0 & < 0) so probably not worth while...

P3: =IF(ROW()-2>P$2,"",INDEX(B$3:B$2000,MATCH(-1*(ROW(P3)-2),$M$3:$M$2000,0),1))
(so in the above you're multiplying the ROW()-2 by -1 to look for the -ve equivalent... ie -1,-2 etc...

For Q I would still use the same approach as N & P... unless you are 100% sure that you will only ever have distinct forenames... so just copy formula from P to Q.

Regards using named ranges etc... you can switch obviously ... you can use INDEX with named range to ref appropriate column.

Still would be worth looking at filtering though...

DonkeyOte,

Thank you so much for your detailed answer - your solution works perfectly!!

I've not used INDEX and MATCH before and look forward to seeing if I can use this elsewhere in my s/sheets.

My apology, that should have read read
"it has "SH2" or the contents of cell M2 in the same row in column C"
and have adjusted your formula in column M to refer to that cell so that I can simply change one cell rather than using find and replace.

I don't think I can use filtering as I have several other s/sheets which link to the contents of columns N, P and Q, hence the need for a continuous list in those particular columns which updates as the data in columns A-K is updated (regularly). You mention advanced filtering, I'll probably need to explore that as I've not used filtering much.

Thanks again!
Bungaree