From 2 rows or column how to find common numbers

  • Thread starter Thread starter A S Matharu
  • Start date Start date
A

A S Matharu

I have one row with 6 columns and having different numbers say from A1 to F1
Then i have one column with 20 different numbers say from H1 to H20. But
there are common numbers between this row and the column. I want to find the
common or repetitive numbers by comparing row with the column. And also tolal
common numbers between this row and column.
 
One of the ways is to enter in I1 this formula
=IF(COUNTIF($A$1:$F$1,H1)=1,"Common","")
and copy down...
It will show you Common against the numbers in both lists...

This in J1 and copied down...
COUNTIF($A$1:$F$1,H1)*H1
will either show the number itself or 0 depending upon presence/absence in
the list...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel How to find the most common pairs and triplets numbers? 5
Find values across a row and return a text value from the same column 2
Excel Numbering rows by font color, but skipping blanks 1
Find difference between 2 cells if critera in a 3rd cell is met 3
Excel Subtracting multiple columns from another column 3
lookup row number 3
Comparing data between two columns 4
How to combine two different excel files 1

Back
Top