Grab value from a range based on certain criteria

[bittubadri]

My input ranges are D2:I2; D4:I5.
D2:I2 generates positive number. It may also generate 0 or “” (null).
D4:I4 has 3 values “A”; “B” & “C” and balance 3 cells contains “” (null).
Output Range1:M2:R2 It should contain all numbers from D2:I2 in increasing order i.e. from minimum to maximum, going from left to right.
Output Range2: U2:W2
U2=value from D2:I2 where the corresponding cell in D4:I4 contains “A”
V2=value from D2:I2 where the corresponding cell in D4:I4 contains “B”
W2=value from D2:I2 where the corresponding cell in D4:I4 contains “C”

How to accomplish? Thanks in advance.

[Fluff13]

How about
In M2

Please Login or Register  to view this content.

and in U2

Please Login or Register  to view this content.

[bittubadri]

It worked Fluff13. But when I made G2="" (null), the formulas gave
R2=#NUM! (Complete: 0, 7, 12, 20, 33, #NUM! for D2:I2=20, 33, 0, "", 12, 7). I want it to give "", 0, 7, 12, 20, 33 preferable OR otherwise 0, 0, 7, 12, 20, 33
V2=0 Can the formula give here "" (since "" in G2)

[bittubadri]

Also, can the formula be re-adjusted to 'ignore' corresponding values from D2:I2 when it finds "Z" in D4:I4 while 'arranging' values in M2:R2. For such "z" corresponding cells, the formula should give 0.

[wk9128]

Try Cell U2 formula , Drag right , the copy U2:W2 ranges and paste to Cell U4

Please Login or Register  to view this content.

[bittubadri]

Formula works well for U2:W2. What about the formula for M2:R2 to avoid #NUM! for cell in D2:I2 containing "" (null)

[wk9128]

ans post #6

M2

Cell M2 formula , drag right

Please Login or Register  to view this content.

OR

Please Login or Register  to view this content.

[bittubadri]

wk9128, Your first formula works well for me. But I would like to know what is being done by COLUMN(A$1)

[AliGW]

COLUMN(A$1) will return the number of the column (here 1). As you drag right, the column letter will increment, and with it the number returned.

[bittubadri]

Hold on…wk9128
Your first formula is treating 0s in D2:I2 as a value ‘which has to be considered’ for sorting.
For example: D2:I2 is 0, 88, 0, “”, “”, 77
Formula is giving (in M2:R2) 0, 0, 77, 88, “”, “” (wrong)
I need: 77, 88, “”, “”, “”, “”
Please see the attached file.

[bittubadri]

Okay learned

[wk9128]

Try this formula

Please Login or Register  to view this content.

[bittubadri]

Yes. Now it works perfectly well.

[bittubadri]

wk9128,
Is your formula of post #12 very volatile. My Excel file got hanged today when I used it for the first time today, using real time feed data in D2:I2
If it is volatile, can I get a less volatile formula

Similarly, less volatile required against post #5 formula for U2:W2 range

[wk9128]

@bittubadri

No volatile here , when you see a formula with OFFSET or INDIRECT is a volatile formula
Upload the attachment in question, the formula seems to be fine