If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
Formula for finding matching numbers
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 |
#3
|
|||
|
|||
Formula for finding matching numbers
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. |
#4
|
|||
|
|||
Formula for finding matching numbers
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. |
#5
|
|||
|
|||
Formula for finding matching numbers
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. |
#6
|
|||
|
|||
Formula for finding matching numbers
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. |
#7
|
|||
|
|||
Formula for finding matching numbers
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 |
#8
|
|||
|
|||
Formula for finding matching numbers
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 |
#9
|
|||
|
|||
Formula for finding matching numbers
.. 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 --- |
Thread Tools | |
Display Modes | |
|
|