Count unique occurences based on two criteria

[RE776]

Hi,

I have two columns of data,
Column A is product serial number e.g. GFRD38402DE
Column B is part number e.g.G3324-29

the values of these columns are duplicated in various combinations.

What I need to find out is the number of times a unique combination of column A and B of the same row occurs, by each product serial number.

This list is 609,343 long, I tried to put them in the table in excel and use
=COUNTIFS(Sheet1!$A$2:$A$609343,Sheet2!$A2,Sheet1!$B$2:$B$609343,Sheet2!B$1)
to find the count of unique communications I need. But as you imagine it takes a long long long time..

I used to use matlab, but I am struggling to transfer what I did from excel to Matlab.

any help would be appreciated!

Many THanks in advance

Felix

 

[GregLocock]

Assuming you have two arrays A and B and you are looking for serial number SN and part number PN then

sum((A==SN)&&(B==PN))

should do it.


The trick is getting your A and B sorted out, you need to make them arrays of equal length strings, called a char array.

Cheers

Greg Locock


New here? Try reading these, they might help FAQ731-376

http://eng-tips.com/market.cfm?

 

[RE776]

thanks Greg,

I will need to the number of counts for each unique combinations

i.e. I will want a table produce like this



A B C
GFRD38402DE G3324-29 34
GDYRK3520FD G3324-29 4684
GFRD38402DE E4582-53 56

etc.

Many Thanks

Felix

 

[GregLocock]

Oh, in that case you need to analyse A to find a list of SNs, and B to find a list of PNs and then test every combination.

That doesn't sound especially difficult, but we are committing the usual crime of expecting a numerical package to do a database's job. Given that you are a matlab noob, perhaps it might be worth switching to being an Access noob, and do it robustly.



Cheers

Greg Locock


New here? Try reading these, they might help FAQ731-376

http://eng-tips.com/market.cfm?