Consulting

Page 1 of 2 1 2 LastLast
Results 1 to 20 of 39

Thread: Count the number if times a unique number appears in a list

  1. 04-11-2024, 07:23 AM #1
    Scuba
    Scuba is offline
    VBAX Regular
    Joined
    Sep 2023
    Posts
    72
    Location

    Count the number if times a unique number appears in a list

    Hi all,

    I have a simple excel sheet that I need some help with a formula, I require a formula that looks at column B, and then Column C and returns the number of how many containers numbers match.

    I don't need the sum of the values, just how many times a given container number is displayed when it it covers or falls under the same number from column B, Unique count as it where.

    I have given an example of what I am after as the fished result in column D.

    For example cell B2 & B3 have same number and column C2 & C3 have same number so the unique count of the container number in Cell D2 & D3 is actually only 1 and so forth as you go down the list

    I'm probably not explaining myself clear here but hopefully column D will show what I'm after.

    I'm hoping one of you wizards in excel can help me with this?

    Thanks you in advance

    Reply With Quote Reply With Quote
  2. 04-11-2024, 07:33 AM #2
    georgiboy
    georgiboy is offline
    Moderator VBAX Master georgiboy's Avatar
    Joined
    Mar 2008
    Location
    Kent, England
    Posts
    1,199
    Location
    Hi Scuba,

    Did you intend to attach a file?

    What version of Excel do you use: 2016, 2021, 365 etc.?
    Click here for a guide on how to add code tags
    Click here for a guide on how to mark a thread as solved
    Click here for a guide on how to upload a file with your post

    Excel 365, Version 2403, Build 17425.20146
    Reply With Quote Reply With Quote
  3. 04-11-2024, 07:46 AM #3
    Scuba
    Scuba is offline
    VBAX Regular
    Joined
    Sep 2023
    Posts
    72
    Location
    For some reason, it won't upload my excel file and so I put a picture in the original post, but that appears to have been removed?

    I have shown below the data list,

    MAWB/MBL Container Number Container Count
    MEDUD8472729 TRLU4818477 0
    MEDUD8472729 TRLU4818477 1
    HLCUBC1240214568 TCLU4425450 0
    HLCUBC1240214568 TCLU4425450 0
    HLCUBC1240214568 TCLU4425450 1
    ONEYHAMDC0153500 TRHU4029022 1
    ONEYHAME01869400 TCLU8318357 1
    HLCUGDY240132553 FANU1488766 0
    HLCUGDY240132553 FANU1488766 0
    HLCUGDY240132553 FANU1488766 0
    HLCUGDY240132553 FANU1488766 1
    HLCUGDY240213489 FANU3261386 0
    HLCUGDY240213489 FANU3261386 0
    HLCUGDY240213489 FANU3261386 0
    HLCUGDY240213489 FANU3261386 0
    HLCUGDY240213489 FANU3261386 0
    HLCUGDY240213489 FANU3261386 1
    HLCUGDY240144047 HLXU8418344
    HLCUGDY240144047 BEAU4183530
    HLCUGDY240144047 HLXU8418344
    HLCUGDY240144047 BEAU4183530
    HLCUGDY240144047 BEAU4183530
    HLCUGDY240144047 HLXU8418344
    HLCUGDY240144047 HLXU8418344
    HLCUGDY240144047 HLXU8418344
    HLCUGDY240144047 HLXU8418344
    HLCUGDY240144047 BEAU4183530
    HLCUGDY240144047 HLXU8418344
    HLCUGDY240144047 HLXU8418344
    HLCUGDY240144047 HLXU8418344
    HLCUGDY240217987 HLXU8129634
    HLCUGDY240217987 FCIU7484437
    HLCUGDY240217987 FCIU7484437

    Thank you for answering so quickly
    Reply With Quote Reply With Quote
  4. 04-11-2024, 01:33 PM #4
    Aussiebear
    Aussiebear is offline
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,064
    Location
    In the attached workbook, I have given you two examples of how it might be done.

    Column E has a listing of the stripped numbers from Column B
    Column F then counts the number of occurrences from Column E

    Using Office 365 as the second example
    Column H uses "=Unique($E$2:$E$35)"
    Column I then counts the number of occurrences.
    Attached Files Attached Files
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link
    Reply With Quote Reply With Quote
  5. 04-11-2024, 02:14 PM #5
    p45cal
    p45cal is offline
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,876
    Try, in Aussiebear's workbook, in cell D3: 
    =COUNTIFS($B$3:$B$35,B3,$C$3:$C$35,C3)
    and copy down. It should work in any version of Excel from 2007
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.
    Reply With Quote Reply With Quote
  6. 04-11-2024, 03:41 PM #6
    Aussiebear
    Aussiebear is offline
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,064
    Location
    Hmmm.... that's different.

    On another note: my example workbook should be adjusted as we don't really know which column represents what exactly. Will need to give this another thought.
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link
    Reply With Quote Reply With Quote
  7. 04-11-2024, 03:52 PM #7
    p45cal
    p45cal is offline
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,876
    Yes, the OP's question, seems contradictory:
    Quote Originally Posted by Scuba View Post
    just how many times a given container number is displayed when it it covers or falls under the same number from column B
    and
    Quote Originally Posted by Scuba View Post
    cell B2 & B3 have same number and column C2 & C3 have same number so the unique count of the container number in Cell D2 & D3 is actually only 1
    Surely it's possible for the OP to upload a small file with expected results?
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.
    Reply With Quote Reply With Quote
  8. 04-11-2024, 04:07 PM #8
    Aussiebear
    Aussiebear is offline
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,064
    Location
    okay here a second shot at the title....

    In this workbook, I have stripped both Columns B & C down to unique values then conducted a count of.

    counting numbers is a passion of mine in my position.
    Attached Files Attached Files
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link
    Reply With Quote Reply With Quote
  9. 04-11-2024, 09:01 PM #9
    arnelgp
    arnelgp is offline
    VBAX Expert
    Joined
    Sep 2019
    Posts
    640
    also, maybe you can use VBA?
    Attached Files Attached Files
    Reply With Quote Reply With Quote
  10. 04-12-2024, 01:56 AM #10
    Scuba
    Scuba is offline
    VBAX Regular
    Joined
    Sep 2023
    Posts
    72
    Location
    Hi all,

    Sorry if my message is not clear, to try and clarify, the data in column B (MAWB/MBL) is a master file reference, under any given master file reference I could have instances where I have multiple container numbers (Column C 'Container Number').

    I need to be able to count the unique number of time a container number is displayed in column C, that falls under the same master reference from column B.

    I also need to leave the data as is i.e. I really need to find an option that works on the data as displayed, rather then doing further work first on the data.

    This data is from a much larger data sheet, I just simplified it for the purposes of asking for help here.

    I have managed to upload the file now, no idea why it didnt work before.

    Regards
    Attached Files Attached Files
    Reply With Quote Reply With Quote
  11. 04-12-2024, 02:07 AM #11
    Scuba
    Scuba is offline
    VBAX Regular
    Joined
    Sep 2023
    Posts
    72
    Location
    Replying to Post 9.

    This worked perfectly and displays the data I need, however, is there a way to do this via Excel rather then VBA?

    Regards

    Richard
    Reply With Quote Reply With Quote
  12. 04-12-2024, 02:12 AM #12
    Aussiebear
    Aussiebear is offline
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,064
    Location
    Your request was clear enough, its just the sample data you initially provided that was misleading. Did you try P45cals solution? From the workbook presented it appears you have done nothing. My issue is with the Counting, given that the count is duplicated. We can get around this by creating unique item listings for both columns and the count therefore is far more accurate. Which you can find in my second workbook.

    However since you said you want to leave the data as is then so be it.

    In short, in cell C2 enter the following formula 
    =Countifs($B$2:$B$136,B2,$C$2:$C$136,C2)
    and fill down. You simply need to adjust the range to suit your actual data.
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link
    Reply With Quote Reply With Quote
  13. 04-12-2024, 02:30 AM #13
    Scuba
    Scuba is offline
    VBAX Regular
    Joined
    Sep 2023
    Posts
    72
    Location

    Smile

    Quote Originally Posted by Aussiebear View Post
    Your request was clear enough, its just the sample data you initially provided that was misleading. Did you try P45cals solution? From the workbook presented it appears you have done nothing. My issue is with the Counting, given that the count is duplicated. We can get around this by creating unique item listings for both columns and the count therefore is far more accurate. Which you can find in my second workbook.

    However since you said you want to leave the data as is then so be it.

    In short, in cell C2 enter the following formula 
    =Countifs($B$2:$B$136,B2,$C$2:$C$136,C2)
    and fill down. You simply need to adjust the range to suit your actual data.

    Hi,

    Yes I did try P45 solution but this doesn't give a unique count, it just totals, which is not what I need to do, I need to count the total number of unique container numbers that are under the same Mawb number.

    S0 for example

    MAWB/MBL
Container Number
Container Count

    

    MEDUD8472729
TRLU4818477
0

    

    MEDUD8472729
TRLU4818477
1
    The above data is telling me I moved 1 container against Mawb/MBL MEDUD8472729.


    If I use P45 solution I get the below:

    MAWB/MBL
Container Number
Container Count

    

    MEDUD8472729
TRLU4818477
2

    

    MEDUD8472729
TRLU4818477
2
    Which gives wrong values

    If I have the same container number displayed, this needs to be 'counted' as 1.

    Hope that makes sense?

    And that's for your patience everyone.
    Reply With Quote Reply With Quote
  14. 04-12-2024, 02:39 AM #14
    p45cal
    p45cal is offline
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,876
    In the attached workbook I've added a column E. Do they show the correct values?
    If column D is your expected results, I don't understand how any value can be 0, and why the values in cells D2 and D3 are different.

    Screenshot of some of my results in the workbook:
    2024-04-12_103817.jpg
    Attached Files Attached Files
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.
    Reply With Quote Reply With Quote
  15. 04-12-2024, 02:51 AM #15
    Scuba
    Scuba is offline
    VBAX Regular
    Joined
    Sep 2023
    Posts
    72
    Location
    Quote Originally Posted by p45cal View Post
    In the attached workbook I've added a column E. Do they show the correct values?
    If column D is your expected results, I don't understand how any value can be 0, and why the values in cells D2 and D3 are different.

    Screenshot of some of my results in the workbook:
    2024-04-12_103817.jpg
    Hi,

    Unfortunately no, its the unique or distinct count I need of column c, where the fall under the same number shown in column B

    Some context, column B is a Master reference number, under any given master file reference, I could have multiple container numbers, some of these container numbers could be duplicated, as a result I need to count only like minded container numbers once, where they fall under the same Master Reference number (Column B)

    See example below

    MAWB/MBL
Container Number
Container Count

    

    MEDUD8472729
TRLU4818477
0

    

    MEDUD8472729
TRLU4818477
1

    

    HLCUBC1240214568
TCLU4425450
0

    

    HLCUBC1240214568
TCLU4425450
0

    

    HLCUBC1240214568
TCLU4425450
1

    

    ONEYHAMDC0153500
TRHU4029022
1

    

    ONEYHAME01869400
TCLU8318357
1

    

    HLCUGDY240132553
FANU1488766
0

    

    HLCUGDY240132553
FANU1488766
0

    

    HLCUGDY240132553
FANU1488766
0

    

    HLCUGDY240132553
FANU1488766
1

    

    HLCUGDY240213489
FANU3261386
0

    

    HLCUGDY240213489
FANU3261386
0

    

    HLCUGDY240213489
FANU3261386
0

    

    HLCUGDY240213489
FANU3261386
0

    

    HLCUGDY240213489
FANU3261386
0

    

    HLCUGDY240213489
FANU3261386
1

    

    HLCUGDY240144047
HLXU8418344
0

    

    HLCUGDY240144047
BEAU4183530
0

    

    HLCUGDY240144047
HLXU8418344
0

    

    HLCUGDY240144047
BEAU4183530
0

    

    HLCUGDY240144047
BEAU4183530
0

    

    HLCUGDY240144047
HLXU8418344
0

    

    HLCUGDY240144047
HLXU8418344
0

    

    HLCUGDY240144047
HLXU8418344
0

    

    HLCUGDY240144047
HLXU8418344
0

    

    HLCUGDY240144047
BEAU4183530
1

    

    HLCUGDY240144047
HLXU8418344
0

    

    HLCUGDY240144047
HLXU8418344
0

    

    HLCUGDY240144047
HLXU8418344
1
    You will note each container number that is the same number is only counted once, as its the same container container number, so its the distinct count I need.

    For example, from the above example under MAWB/MBL HLCUGDY240144047, I have only 2 container numbers, so the excel formula I need is to show 2

    MAWB/MBL
Container Number
Container Count

    

    HLCUGDY240144047
HLXU8418344
0

    

    HLCUGDY240144047
BEAU4183530
0

    

    HLCUGDY240144047
HLXU8418344
0

    

    HLCUGDY240144047
BEAU4183530
0

    

    HLCUGDY240144047
BEAU4183530
0

    

    HLCUGDY240144047
HLXU8418344
0

    

    HLCUGDY240144047
HLXU8418344
0

    

    HLCUGDY240144047
HLXU8418344
0

    

    HLCUGDY240144047
HLXU8418344
0

    

    HLCUGDY240144047
BEAU4183530
1

    

    HLCUGDY240144047
HLXU8418344
0

    

    HLCUGDY240144047
HLXU8418344
0

    

    HLCUGDY240144047
HLXU8418344
1
    Last edited by Scuba; 04-12-2024 at 03:26 AM.
    Reply With Quote Reply With Quote
  16. 04-12-2024, 03:10 AM #16
    Aussiebear
    Aussiebear is offline
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,064
    Location
    P45cal, I think its more a VBA issue than a formula one.
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link
    Reply With Quote Reply With Quote
  17. 04-12-2024, 03:27 AM #17
    arnelgp
    arnelgp is offline
    VBAX Expert
    Joined
    Sep 2019
    Posts
    640
    why can't you use a macro?
    Attached Files Attached Files
    Reply With Quote Reply With Quote
  18. 04-12-2024, 03:46 AM #18
    p45cal
    p45cal is offline
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,876
    Quote Originally Posted by Scuba View Post
    Unfortunately no, its the unique or distinct count I need of column c, where the fall under the same number shown in column B
    How can ANY result be 0?!
    My results are a distinct count of container number for every MAWB/MBL


    Quote Originally Posted by Scuba View Post
    You will note each container number that is the same number is only counted once, as its the same container container number, so its the distinct count I need.
    For example, from the above example under MAWB/MBL HLCUGDY240144047, I have only 2 container numbers, so the excel formula I need is to show 2
    This is a screenshot from my results; it looks like it's a 2 to me.

    2024-04-12_114254.jpg
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.
    Reply With Quote Reply With Quote
  19. 04-12-2024, 03:46 AM #19
    p45cal
    p45cal is offline
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,876
    Quote Originally Posted by Aussiebear View Post
    P45cal, I think its more a VBA issue than a formula one.
    It may be but I'm trying to get a definitive answer to what is required first.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.
    Reply With Quote Reply With Quote
  20. 04-12-2024, 03:58 AM #20
    Scuba
    Scuba is offline
    VBAX Regular
    Joined
    Sep 2023
    Posts
    72
    Location
    Quote Originally Posted by p45cal View Post
    How can ANY result be 0?!
    My results are a distinct count of container number for every MAWB/MBL



    This is a screenshot from my results; it looks like it's a 2.
    2024-04-12_114254.jpg

    Hi,

    You are correct but I need to total (sum) column C so I can obtain a count of unique container numbers, if I use your version I get incorrect values.

    I think I may have solved it myself here by using the below code

    =IF(COUNTIFS(B:B, B2,C:C,C2)>1,IF(COUNTIFS(B:B,B2,C:C,C2)>1,IF(COUNTIFS($B$2:B2, B2, $C$2:C2,C2)=1,1,0),""), 1)
    UPDATE!

    Actually this fails on some lines, unless I do a 'SORT' on column B in Ascending order, then formula works across all lines.

    Would be good to get this to work without the need to sort in ascending order each time though, any thoughts people?
    Attached Files Attached Files
    Reply With Quote Reply With Quote
« Previous Thread | Next Thread »

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

Forum Rules