Formula to produce list of data based on criteria

[samcdavies]

I have a list of 20 job titles in column A and a list of data in the next column (B). I want to generate a list of job titles based on the data in B.

So if there is an X in column B the job title appears in a list.

Eg

Job 1 X
Job 2 X
Job 3
Job 4 X
Job 5
Job 6
Job 7
Job 8 X

..would return, in another column;

Job 1
Job 2
Job 4
Job 8

Thanks in advance for any help.

[canapone]

Hi,

in C2

Please Login or Register  to view this content.

You have to finish the input of the formula with Ctrl+Shift+Enter rather than Enter.

Array formulae are embraced by curlies to indicate that they are arrays o be confirmed with control+shift+enter

Greetings

[samcdavies]

Hi,

in C2..

Thanks, but how will this return all results if it's only in one cell (C2)?

[canapone]

Hi, once you have copied and confirmed the formula in C2 (in the example) you have to copy down the formula (in C3:C10 for istance).

=if(iserror(index($A$2:$A$100,small(if($B$2:$B$100="X",row($A$1:$A$99)),row(A1)))),"",index($A$2:$A$100,small(if($B$2:$B$100="X",row($A$1:$A$99)),row(A1))))

The segment row(a1) becomes row(a2) (i.e. 2 ), row(a3) (i.e. 3) ...and allows to extract from the list the other elements that match the criteria.

Attached a small example.

Hope it helps

[samcdavies]

Hi, once you...

That's brilliant, thankyou.

[dr.rohit]

Hey, I want to do a similar thing like this, but not with "X", instead I want to categorize my whole list of 2 columns into 3 separate lists, as per the 3 categories present in column 2. And the length of the list vary to any number. Could please help me putting out the same.

Thanks in advance.

[samcdavies]

Hey, I want to do a similar thing like this, but not with "X", instead I want to categorize my whole list of 2 columns into 3 separate lists, as per the 3 categories present in column 2. And the length of the list vary to any number. Could please help me putting out the same.

Thanks in advance.

I think the above solution should work, but change the "X" in your first 'results' column to your first value, your second column to your second value and third column to third value, then drag it down as long as needed.

[canapone]

Hi dr.rohit, hi samcdavies,

as you are an Excel 2010 user, you could also substitute the scheme

=if(iserror(formula),"", formula)

into a much simple

=iferror(formula,"")

Example:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Again, they are array formulae to be confirmed with control+shift+enter.

Cheers