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

http://www.vbaexpress.com/forum/imag...AASUVORK5CYII=

Hi Scuba,

Did you intend to attach a file?

What version of Excel do you use: 2016, 2021, 365 etc.?

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

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.

Try, in Aussiebear's workbook, in cell D3:

Code:

---

=COUNTIFS($B$3:$B$35,B3,$C$3:$C$35,C3)

---

and copy down. It should work in any version of Excel from 2007

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.

Yes, the OP's question, seems contradictory:

Quote:

---

Originally Posted by Scuba

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

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?

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. :jail:

also, maybe you can use VBA?

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

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

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

Code:

---

=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.

Quote:

---

Originally Posted by Aussiebear

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

Code:

---

=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

Code:

---

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:

Code:

---

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.

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:
Attachment 31507

Quote:

---

Originally Posted by p45cal

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:
Attachment 31507

---

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

Code:

---

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

Code:

---

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

---

P45cal, I think its more a VBA issue than a formula one.

why can't you use a macro?

Quote:

---

Originally Posted by Scuba

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

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.

Attachment 31510

Quote:

---

Originally Posted by Aussiebear

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.

Quote:

---

Originally Posted by p45cal

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.
Attachment 31510

---

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

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?