How to get a range from other ranges?

[bebo021999]

1- I have a range like this: A1:A4 = {1;2;3;4}
Use this formula: IF(A1:A4<4,1,""), return {1;1;1;""}
Now I 'd like to replace "", to get: {1;1;1}.

2- I have A1=1; A2=2; C1=3; C2=4
I'd like to have a range like this {1;2;3;4}


For 1 and 2, Could you please assist me on which formula to use?

 

[bebo021999]

Can some one help me? urgently, thanks in advance.

 

[grizle]

Your question is ambiguous, can you clarify please.

1. Taking the general case, are you asking for a result where all elements of a range which do not meet the set criteria are removed to produce a new range with a reduced number of elements all of which are "true"? If so then all information regarding the location of the "false" element would be lost and the range {1;1;1} gives no more infmation than number "3". If that is sufficient then use COUNTIF(A1:A4,"<4") instead.

2. As far as I know ranges have to be contiguous. The cell contents will need copying to a contiguous location. E.g. map A1,A2,C1,C2 to G1:G4 and then work with that range.

 

[bebo021999]

@grizle

Thanks for your reply,

1- I mean, with a range of 4 elements, for examle, with criteria, can we produce to a new range, can be viewed by F9, with less or more elements (3 or 5 element...)?

2- I'm trying to find another way to make an unique distinc list from multicolumns in 1 sheet or from many sheets. For example, with cells A1,A2,C1,C2 ({1;2}, {2;4} , if we can consolidate into 1 range (named DATA, can be viewed by F9 ={1;2;2;4}), for ex), we can use this formular : INDEX(DATA,MATCH(0,COUNTIF(RANGE,DATA),0)) to get unique distinc items.

The new range, if can be produced, is used in formula only, shouldnot be showed on any cell in worksheets

Thanks in advance your kind attention!