Count unique occurences based on two criteria
Count unique occurences based on two criteria
(OP)
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
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
Talk To Other Members
RE: Count unique occurences based on two criteria
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: Eng-Tips.com Forum Policies http://eng-tips.com/market.cfm?
RE: Count unique occurences based on two criteria
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
RE: Count unique occurences based on two criteria
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: Eng-Tips.com Forum Policies http://eng-tips.com/market.cfm?