Transpose Formula for Values in Range

[Metcalfe]

Hello, I am trying to use the Transpose function to place values from several cells into a single cell if they are between a specific range.

Please see attached example sheet with manually input "Output Required".

As always, any help is much appreciated.

I have "Positions" in Column A which will fall into various Sections "Column C" as dictated by the Range "From" and "To" in Columns D and E.

My formula I am using for the output only returns a single value rather than a range?

Thanks.

[XOR LX]

Hi,

So you want the returns in a single cell? And without any form of separator, as in your one example?

With Excel 2010, you'll only be able to do that with VBA. Is that something you're ok with? Otherwise, it's quite straightforward to have each return placed into its own cell, e.g. in consecutive columns to the right of each entry.

Regards

[Special-K]

I think you'll need to use TEXTJOIN (if you have the right version of Excel) or VBA for this rather than a straightforward formula.

[Metcalfe]

Correct but I would rather not use VBA (no experience of it) and I do not have the ExCel Verizon which supports TEXTJOIN.
I have additional columns I would also like the same done to so could do with a formula if its possible..

[XOR LX]

In F2:

=IF(COLUMNS($A2:A2)>COUNTIFS($A$2:$A$422,">="&$D2,$A$2:$A$422,"<="&$E2),"",INDEX($A:$A,AGGREGATE(15,6,ROW(A$2:A$422)/($A$2:$A$422>=$D2)/($A$2:$A$422<=$E2),COLUMNS($A2:A2))))

Copy to the right until you start to get blanks for the results and also down to give similar results for rows 3, 4, etc.

Regards

[Metcalfe]

Thanks that's great but I want all values in a single cell rather than multiple columns?

[XOR LX]

But you said you have Excel 2010 and also that you'd rather not use VBA?

Regards

[Metcalfe]

Sorry yes that is correct but with the formula you have suggested all values are in new columns rather than a single cell, which means I need to combine them again...

[XOR LX]

But as I explained in post #2, to put them in a single cell can only be done with either Excel 2016+Office 365 or else VBA.

Regards