Question description:
This user has given permission to use the problem statement for this
blog.
Hello, I have a spreadsheet with 3159 rows. One column consists of 9-digit numbers in each of the 3159 rows. I need the formula that can calculate how many of those numbers appear more than once.
Solved by D. H. in 56 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
08/08/2018 - 02:31
Welcome, thank you for choosing Got It Pro-Excel! I'll be glad to assist you with your concern. :)
Excelchat Expert
08/08/2018 - 02:33
From what I understood in your post, you would want to know how many of the 9-digit numbers have appeared more than once in your file composing of 3159 rows. Is this correct? :)
Excelchat Expert
08/08/2018 - 02:34
Hello? :)
Excelchat Expert
08/08/2018 - 02:36
It would be great if you can share the file you're working on, so that we can directly insert the formula in there. However, we can as well work on a sample data in the document preview on the right. :)
Excelchat Expert
08/08/2018 - 02:37
I can see your avatar as present in the document preview. I hope you'd be able to respond the soonest possible time so I could help! :)
User
08/08/2018 - 02:38
sorry im here
Excelchat Expert
08/08/2018 - 02:38
Oh nice! Thanks for responding. Will you be able to share the file you're working on? :)
User
08/08/2018 - 02:39
i didn't realize you were answering my question. I thought I had to post it on your site once i changed the temp password
User
08/08/2018 - 02:39
yes i can share it
User
08/08/2018 - 02:39
how do i send it to you
Excelchat Expert
08/08/2018 - 02:39
That would be nice! You can upload your file by clicking on the clip icon to the right and selecting your file. :)
User
08/08/2018 - 02:40
[Uploaded an Excel file]
User
08/08/2018 - 02:40
did you receive it
Excelchat Expert
08/08/2018 - 02:40
Yes, opening now.
User
08/08/2018 - 02:40
it should already be open on the tab "LOS and TransDown Combined"
Excelchat Expert
08/08/2018 - 02:41
Okay I see. Is column C the one we need to count on?
User
08/08/2018 - 02:41
No, column A
Excelchat Expert
08/08/2018 - 02:42
Oh I see, noted on that. So just to clarify on what you want to happen, you would want to count how many values in column A have appeared more than once, correct? :)
User
08/08/2018 - 02:42
Correct
User
08/08/2018 - 02:43
I need the forumla not the just the answer
User
08/08/2018 - 02:43
or will u use a pivot table
Excelchat Expert
08/08/2018 - 02:43
Okay noted on that. For this one, we will need to use a helper column to aide us in your main formula.
Excelchat Expert
08/08/2018 - 02:44
Using pivot will allow us to know how many times the visit numbers have appeared. We can as well make use of this if you wish. However, using a helper column would suffice.
User
08/08/2018 - 02:44
we can use the helper column
Excelchat Expert
08/08/2018 - 02:45
Noted on that. Let me work on it for a while and update you the soonest!
User
08/08/2018 - 02:45
okay
User
08/08/2018 - 02:45
can i leave my desk for five minutes?
Excelchat Expert
08/08/2018 - 02:46
Oh, I will need you to extend our session once it reaches 3 minutes! :)
User
08/08/2018 - 02:46
okay no prob. i'll stay here
Excelchat Expert
08/08/2018 - 02:46
Okay, thanks!
Excelchat Expert
08/08/2018 - 02:48
Where do we need the total count of values that appears more than once?
User
08/08/2018 - 02:48
under the column in question or in column M
Excelchat Expert
08/08/2018 - 02:49
Okay, let me just put it under column A then. I'm almost done. I'll be able to upload the file once done.
User
08/08/2018 - 02:49
ok
Excelchat Expert
08/08/2018 - 02:50
Okay, please see attached for your review!
[Uploaded an Excel file]
Excelchat Expert
08/08/2018 - 02:52
I inserted a a helper column in column B, which has the following formula: =IF(COUNTIF($A$2:A3,A3)=2,"Yes","") This returns "Yes" once it detects that the value aligned to it has already appeared once above. However, if the COUNTIF detects that the value aligned to it appeared already twice, it won't return "Yes". This prevents us from having two "Yes" values for the same number. Thus, as long as the number has appeared more than once, it would return a Yes only ONCE even if it appeared 50 times.
User
08/08/2018 - 02:53
some entries have more than two
User
08/08/2018 - 02:53
more than two occurrences
Excelchat Expert
08/08/2018 - 02:53
If an entry appears more than two, it should be counted once only, correct?
User
08/08/2018 - 02:53
correct
Excelchat Expert
08/08/2018 - 02:54
Okay, that's what the formula above does. So basically, even if it appeared 50 times, that value will only have one "Yes" in the whole column B.
User
08/08/2018 - 02:55
i know it's something simple but can you tell me the formula for counting the yes's in the helper column
Excelchat Expert
08/08/2018 - 02:55
In cell A3161, we have the formula =COUNTIF(B2:B3159,"Yes"). This counts all the "Yes" values in column B. Take note that a number that appears twice, thrice, or 50 times in column A is only counted ONCE.
Excelchat Expert
08/08/2018 - 02:56
The Yes's are counted in cell A3161. :) It uses the COUNTIF function. This function has the following syntax: =COUNTIF(array , criteria). So our criteria is "Yes" and our array is B2:B3159. So in this array, COUNTIF will count the times the "Yes" value has appeared. :)
User
08/08/2018 - 02:56
i'm so sorry. i forgot to filter the Request Type column on Internal Transfer
Excelchat Expert
08/08/2018 - 02:57
It's alright. I already unfiltered it and applied our formula from row 1 to row 3159.
Excelchat Expert
08/08/2018 - 02:57
Sorry, starting row 2.
User
08/08/2018 - 02:58
what the total?
User
08/08/2018 - 02:58
of internal transfer >1 entries
Excelchat Expert
08/08/2018 - 02:59
Oh sorry, do you mean to say you want to only count the numbers that appear more than once only for Internal transfer?
User
08/08/2018 - 02:59
yes
User
08/08/2018 - 02:59
please
Excelchat Expert
08/08/2018 - 02:59
Okay, I see. Let me reconfigure our formula then.
User
08/08/2018 - 03:00
cant we filter out everything except Internal Transfer and use the formula you used already?
Excelchat Expert
08/08/2018 - 03:01
Already done! Please see attached for the updated formula.
[Uploaded an Excel file]
Excelchat Expert
08/08/2018 - 03:02
Basically, we filtered only Internal transfer, and our formula changed to =IF(COUNTIFS($A$2:A2,A2,$C$2:C2,"Internal Transfer")=2,"Yes","")
Excelchat Expert
08/08/2018 - 03:02
This ensures that it will only count the number when the request type is "Internal transfer"
User
08/08/2018 - 03:02
something is wrong, i'm afraid
Excelchat Expert
08/08/2018 - 03:03
Sorry, what may be wrong?
Excelchat Expert
08/08/2018 - 03:03
Were you able to check the latest updated file I attached? :)
User
08/08/2018 - 03:03
it you will notice I used conditional formatting to highlight duplicates. And I counted the red highlighted entries manually. There are 113
User
08/08/2018 - 03:03
and someone used python and got 113
User
08/08/2018 - 03:04
yes, i have the latest greatest open. the count is 120
Excelchat Expert
08/08/2018 - 03:04
Okay I see. Let me recheck the formulas then.
Excelchat Expert
08/08/2018 - 03:08
I do have an error with the formula. Thanks for pointing that out! Let me revise it quickly and upload it to you.
User
08/08/2018 - 03:08
thanjs
Excelchat Expert
08/08/2018 - 03:17
Already done! Please see attached for your review.
[Uploaded an Excel file]
User
08/08/2018 - 03:17
ok
User
08/08/2018 - 03:17
excellent
User
08/08/2018 - 03:18
the formula is much simpler
Excelchat Expert
08/08/2018 - 03:18
The second previous formula provided was modified to incorporate an AND function. This lets us still use the COUNTIFS function, but another criteria was added, which is that the cell in column C should have "Internal Transfer" as data. :)
Excelchat Expert
08/08/2018 - 03:18
Do you have any clarifications with the provided solution? :)
User
08/08/2018 - 03:18
so COUNTIF finds dupes??
User
08/08/2018 - 03:19
or did you do something first before using that formula
Excelchat Expert
08/08/2018 - 03:19
You mean the COUNTIF in cell A3164 or the COUNTIFS in column B? :)
User
08/08/2018 - 03:20
OMG!! what a hefty formula you had to use!!!
Excelchat Expert
08/08/2018 - 03:21
The COUNTIF in cell A3164 counts the number of cells in column B that has a "Yes" value. The formula in column B incorporates the COUNTIFS function to determine if a value in column A is a duplicate AND that the value in column C is "Internal Transfer"
User
08/08/2018 - 03:22
you are smart!!!
Excelchat Expert
08/08/2018 - 03:23
It's a bit of a tricky calculation, but we needed to use multiple functions to return the correct "Yes" values. Basically, the formula in column B ensures that a "Yes" is returned IF the value has already appeared more than once and that the request type is "internal transfer"
Excelchat Expert
08/08/2018 - 03:23
Thanks for that! :)
User
08/08/2018 - 03:23
did you already explain how your arrived at the formula in column B?
Excelchat Expert
08/08/2018 - 03:24
Well, I had tests and retests until I arrived with the formula. :) I had to jumble actually the functions used until they're in the right place. And somehow, after seeing that it's working correctly, it already made sense when I try to follow how Excel calculates with the use of that formula.
Excelchat Expert
08/08/2018 - 03:25
Basically, the first thing I did was to clear out the conditions needed for a "Yes". I just had to translate this then into a formula. :)
User
08/08/2018 - 03:26
thank you very much. I'll have to study what you have taught me
Excelchat Expert
08/08/2018 - 03:27
Thanks! Yes, I hope you'd be able to follow the steps moving forward. :)
Excelchat Expert
08/08/2018 - 03:27
Nice! You can now end the session via the button on your upper right (sorry, can't do it from our end). I would also appreciate it if you can leave a great feedback after this.Thanks so much for using Got it pro and have a nice day! :)
User
08/08/2018 - 03:27
I hope too
Excelchat Expert
08/08/2018 - 03:27
I'm sure you'll do! :)
User
08/08/2018 - 03:27
thanks again!
Excelchat Expert
08/08/2018 - 03:28
Hoping for your great feedback on this. Have a nice day! You can now press on the END SESSION on the upper right of your screen. :)
User
08/08/2018 - 03:28
Definitely was great help. Be happy to fill out the feedback form.
User
08/08/2018 - 03:28
bye
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
