Formula for finding matching numbers

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.

Debra Dalgleish shows how you can compile a unique list of phone numbers
using Advanced Filter he

'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. Just
copy this down to get a count for each of the phone numbers dialled.

Hope this helps.

Pete

wookie;289279 Wrote:
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
------------------------------------------------------------------------
Pete_UK's Profile: http://www.thecodecage.com/forumz/member.php?userid=205
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=80845

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" wrote:

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.

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" wrote:

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" wrote:

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.

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" wrote:

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" wrote:

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" wrote:

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.

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

"Francis" wrote:

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" wrote:

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" wrote:

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" wrote:

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.

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!!!!!

"Pete_UK" wrote:


Debra Dalgleish shows how you can compile a unique list of phone numbers
using Advanced Filter he

'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. Just
copy this down to get a count for each of the phone numbers dialled.

Hope this helps.

Pete

wookie;289279 Wrote:
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
------------------------------------------------------------------------
Pete_UK's Profile: http://www.thecodecage.com/forumz/member.php?userid=205
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=80845

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.


"Pete_UK" wrote:


Debra Dalgleish shows how you can compile a unique list of phone numbers
using Advanced Filter he

'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. Just
copy this down to get a count for each of the phone numbers dialled.

Hope this helps.

Pete

wookie;289279 Wrote:
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
------------------------------------------------------------------------
Pete_UK's Profile: http://www.thecodecage.com/forumz/member.php?userid=205
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=80845

.. 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,R OWS($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,R OWS($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
---