If on excel I have two columns, A e B, how can I have the column C with all the elements of A, that are contents in B?

Example:

A   B
1   0
2   1
3   9
5   1
       

I would to have a column C: 

C
1
1

Thank u!

CodePudding user response：

This is a partial answer, since there are empty spaces between. You can use =IF(ISERROR(MATCH(B2,$A$2:$A$5,0)),"",B2) and drag that formula from C2 to C5

That gives C2 = "", C3 = 1, C4 = "", C5 = 1

Formulas:

You can sort this column afterwards

CodePudding user response：

In cell C2, place the following formula:

=FILTER(B2:B5,IFERROR(IF(MATCH(B2:B5,A$2:A$5,0)>0,1),0))

This creates an array of matches then filters the relevent column for the data