Write Common Number into Blank Cell

  • Thread starter Thread starter Olivier
  • Start date Start date
O

Olivier

Hi,

1– (5 cells – A1 ~ A5) containing the following data: 1 (cell A1) -
(cell A2...) - 2 - 3 - 6

2 – (4 cells – B 1 ~ B4) containing the following data: 5 - 2 - 4 - 3

4 – (5 + 4 cells – C1 ~ C10) no data inside

5 - I seek the formula to write the ONLY common numbers into cells
third raw, here: ‘2’ into cell C1 and ‘3’ into cell C2

I'm using Excel 97.

Thanks,


Olivie
 
One guess..

Assuming you have in A1:B5
the data below (with cell B5 blank):

1...5
2...2
2...4
3...3
6....

Put in C1: =IF(COUNTIF($A$1:$A$5,B1)>0,B1,"")

Array-enter the formula, viz.:
Hold down CTRL + SHIFT, press ENTER
(instead of just pressing ENTER)

Done correctly Excel will wrap curly braces around the formula:
{=IF(COUNTIF($A$1:$A$5,B1)>0,B1,"")}
(don't type-in the curly braces!)

Copy C1 down to C4
(this extracts values common in cols A and B)

Put in D1: =SMALL(C:C,ROW())
Copy down until #NUM! appears

or

Put in D1 : =LARGE(C:C,ROW())
Copy down until #NUM! appears
 
No need to array enter it Max

--

Regards,

Peo Sjoblom

Max said:
One guess..

Assuming you have in A1:B5
the data below (with cell B5 blank):

1...5
2...2
2...4
3...3
6....

Put in C1: =IF(COUNTIF($A$1:$A$5,B1)>0,B1,"")

Array-enter the formula, viz.:
Hold down CTRL + SHIFT, press ENTER
(instead of just pressing ENTER)

Done correctly Excel will wrap curly braces around the formula:
{=IF(COUNTIF($A$1:$A$5,B1)>0,B1,"")}
(don't type-in the curly braces!)

Copy C1 down to C4
(this extracts values common in cols A and B)

Put in D1: =SMALL(C:C,ROW())
Copy down until #NUM! appears

or

Put in D1 : =LARGE(C:C,ROW())
Copy down until #NUM! appears
Click to expand...
 
Back
Top