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?

Actually not quite Scuba, since you asked for the reverse of this effect. With your lastest Workbook sample it only counts the first occurrence with the exception of Cell E2 which for some reason is showing =G4.

Quote:

---

Originally Posted by Aussiebear

Actually not quite Scuba, since you asked for the reverse of this effect. With your lastest Workbook sample it only counts the first occurrence with the exception of Cell E2 which for some reason is showing =G4.

---

Soz, I had corrected it but uploaded wrong version, correct version attached now

I did ask before, which version of Excel do you use?

For example, if you use Excel 365 you could use the below (first item gets a 1):

Code:

---

=LET(m,B2:B106,c,C2:C106,r,XLOOKUP(UNIQUE(m&c),m&c,ROW(m)),IF(ISNUMBER(XMATCH(ROW(m),r)),1,0))

---

The reverse version would be (last item gets a 1):

Code:

---

=LET(m,B2:B106,c,C2:C106,r,XLOOKUP(UNIQUE(m&c),m&c,ROW(m),,,-1),IF(ISNUMBER(XMATCH(ROW(m),r)),1,0))

---

Quote:

---

Originally Posted by Scuba

but I need to total (sum) column C so I can obtain a count of unique container numbers

---

So if arnelgp's vba solution is correct, that sum is 44, which you can also get from
=ROWS(UNIQUE(B2:C106))

@p45cal I make you right, but that depends on their Excel version and if they want to see the result row by row or just the overall sum of unique.

Quote:

---

Originally Posted by georgiboy

I did ask before, which version of Excel do you use?

For example, if you use Excel 365 you could use the below:

Code:

---

=LET(m,B2:B106,c,C2:C106,r,XLOOKUP(UNIQUE(m&c),m&c,ROW(m)),IF(ISNUMBER(XMATCH(ROW(m),r)),1,0))

---

The reverse effect version would be:

Code:

---

=LET(m,B2:B106,c,C2:C106,r,XLOOKUP(UNIQUE(m&c),m&c,ROW(m),,,-1),IF(ISNUMBER(XMATCH(ROW(m),r)),1,0))

---

---

Apologies, I use Office 365 :)

I'll try these now, thank you

Quote:

---

Originally Posted by georgiboy

depends on their Excel version

---

Examination of the zip version of their workbook implies it can handle UNIQUE:

Attachment 31513

Don't forget to look at @p45cal's option in post 24 if you are just wanting an overall sum of unique.

Quote:

---

Originally Posted by p45cal

Examination of the zip version of their workbook implies it can handle UNIQUE:

---

Appreciate that but I think the TS should inform us, especially when they have already been asked.

Quote:

---

Originally Posted by georgiboy

I did ask before, which version of Excel do you use?

For example, if you use Excel 365 you could use the below (first item gets a 1):

Code:

---

=LET(m,B2:B106,c,C2:C106,r,XLOOKUP(UNIQUE(m&c),m&c,ROW(m)),IF(ISNUMBER(XMATCH(ROW(m),r)),1,0))

---

The reverse version would be (last item gets a 1):

Code:

---

=LET(m,B2:B106,c,C2:C106,r,XLOOKUP(UNIQUE(m&c),m&c,ROW(m),,,-1),IF(ISNUMBER(XMATCH(ROW(m),r)),1,0))

---

---

This works perfectly for Office 365 and is exactly what I was trying to achieve. :bow: :clap:

Thank you everyone who contributed to this, we got there in the end.

Regards

Richard

If you do want an overall sum then you should look at post 24 from @p45cal as it makes more sense, less intensive on the system.

Quote:

---

Originally Posted by georgiboy

Appreciate that but I think the TS should inform us, especially when they have already been asked.

---

Absolutely!

Row2:

Code:

---

=--(COUNTIFS($B$2:$B$106,B2,$C$2:$C$106,C2)=COUNTIFS($B$2:$B2,B2,$C$2:$C2,C2))

---

copy down.

That's a nice simple option

simplifying your original formula:

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)

---

becomes:

Code:

---

=IF(COUNTIFS($B2:$B$106, B2,$C2:$C$106,C2)>1,0,1)

---

Quote:

---

Originally Posted by arnelgp

simplifying your original formula:

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)

---

becomes:

Code:

---

=IF(COUNTIFS($B2:$B$106, B2,$C2:$C$106,C2)>1,0,1)

---

---

That works even sexier :bow::clap:

Sorry to intrude again here but I was testing the results on a table, when I noticed that arnelgp's formula worked but Georgiboy's created a Spill error.

Such array formulae don't work well with tables.

Indeed, I feel that it is a design flaw on Microsofts part.

They push us to use table objects and then don't support their new features with them.