Formula for finding matching numbers

[wookie]

I have 3 months of phone numbers from phone bills and I need to know if
there is a way I can make a formula to show me the duplicate numbers from
month to month. I have input them in 3 columns and not sure what to do from
here. I've been reading on the forum and some of I'm reading is not making
sense. I know how to use the basics of excel but now getting this in depth
I'm not sure what to do.

 

[Pete_UK]

Debra Dalgleish shows how you can compile a unique list of phone number
using Advanced Filter here:

'Excel Filters -- Advanced Filter
(http://www.contextures.com/xladvfilter01.html)

It is better to put your unique list in column A of a separate sheet
then in column B (B2) you can have a formula like this:

=COUNTIF(Sheet1!A:A,A2)

assuming your phone numbers are in column A of the first sheet. Jus
copy this down to get a count for each of the phone numbers dialled.

Hope this helps.

Pete

 

[Francis]

Hi

Do you want to count the duplicates across the columns or down the columns

assuming that you have col A, B and C for Jan, Feb and Mar
and you want to count the numbers of repeating phone numbers across in Jan,
Feb and Mar. try this =COUNTIF(A2:C2,">1")

if you want to count down the column for duplicates, create a list of unique
phone numbers in a column, say D2 and down, you may use the Advance Filter
for
this and try this in the last empty cell in the column A
=COUNTIF($A$2:$A$14,D2), where D2 hold the first unique number

Adjust the ranges to yours





--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another

 

[wookie]

Francis,
Thank you for your reply.
I've tried this and I didn't get any results. It gives me a result of 2.

This is an example of what I have. I want to beable to pull the numbers
from A & B that match, 532-2114 shows up in both columns a few times. (this
is just a few lines of what I have and I will be adding more columns once I
get it to work for me). I want it to be able to tell me any numbers that are
the same in both columns. What would it do? Give me a report? Sorry for the
stupid questions this is the first time for me doing this sort of thing.
A B
March Feb
242-2465 245-5051
245-5343 245-5836
253-2025 253-2127
253-2025 253-2127
275-2291 253-2127
365-1700 365-2234
365-2309 365-5295
453-9044 530-2221
453-9044 532-2114
467-3755 532-2114
532-2114 532-2114
532-2114 532-2114
532-2114 532-2114
532-2114 532-2114

Again thank you
Wendy

 

[Francis]

Hi Wendy
try this
=IF(A2=$B$2:$B$15,"Appear","")
if the phone numbers in col A appear in col B, the formula wiil
tell you by returning the word "Appear", otherwise it return blank

Is this what you want?
--
Hope this is helpful
Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another

 

[wookie]

It did work but only if the number was directly across from it. If it was
somewhere else in the column it didn't work. hmmmm...... i appreciate your
help. Do you have anything else that I could try?
thanks
wendy

 

[wookie]

I think I have this working for me. But not quite exact. I need to know how
to change the ranges for it to calculate more for me. I would like to tell
me how many times that certain number was called on 2 separate months. I
think I can figure this out with a little more explanation. When I did it
for the second column it also included what was in column a. So for example
there was 6 in column A and 4 in column B it gave me a total of 10. I would
like it to 6 and 4. I'm getting closer!!! Thank you!!!!!

 

[wookie]

Pete,
Me again. As I'm sitting here thinking about this and playing with the
numbers and formula it's all making more sense. when you say unique list on
separate sheet if I was to take all the numbers for the 3 months and put
them in one column on a different sheet is there a way I can tell it to only
list the number once? to remove all duplicates for my unique list?
Thanks again so much.

 

[Max]

.. anything else that I could try?

With your source data as posted assumed in A2:B2 down
In C2:
=IF(A2="","",IF(COUNTIF(A$2:A2,A2)>1,"",ROW()))

In D2:
=IF(ROWS($1:1)>COUNT(C:C),"",INDEX(A:A,SMALL(C:C,ROWS($1:1))))
Copy C22 down to the last row of data in col A. Col D extracts the uniques
list of the tel nos in col A.

Then, to compare the uniques list in col D with the source data in col B
In E2:
=IF(D2="","",IF(COUNTIF(B:B,D2),ROW(),""))

In F2:
=IF(ROWS($1:1)>COUNT(E:E),"",INDEX(D,SMALL(E:E,ROWS($1:1))))
Copy E2:F2 down to the same extent. Col F will extract the desired uniques
list of tel nos in col A which are found in col B, with all results neatly
packed at the top.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:370 Subscribers:68
xdemechanik
---