Unique Rows

[Partjob]

Hi
I want to be able to count unique rows of data.
I have three columns (A) has a date (B) has the name of a person and (C) has an audit type.

I know I need an array but not sure how to structure it for 3 columns
<TABLE style="WIDTH: 184pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=245 border=0 x:str><COLGROUP><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2596" width=71><COL style="WIDTH: 19pt; mso-width-source: userset; mso-width-alt: 914" width=25><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 16pt; mso-width-source: userset; mso-width-alt: 768" width=21><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #e2e2e2; BORDER-TOP: #e2e2e2; BORDER-LEFT: #e2e2e2; WIDTH: 53pt; BORDER-BOTTOM: #e2e2e2; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=71 height=17>Date</TD><TD style="BORDER-RIGHT: #e2e2e2; BORDER-TOP: #e2e2e2; BORDER-LEFT: #e2e2e2; WIDTH: 19pt; BORDER-BOTTOM: #e2e2e2; BACKGROUND-COLOR: transparent" width=25></TD><TD style="BORDER-RIGHT: #e2e2e2; BORDER-TOP: #e2e2e2; BORDER-LEFT: #e2e2e2; WIDTH: 48pt; BORDER-BOTTOM: #e2e2e2; BACKGROUND-COLOR: transparent" width=64>Name</TD><TD style="BORDER-RIGHT: #e2e2e2; BORDER-TOP: #e2e2e2; BORDER-LEFT: #e2e2e2; WIDTH: 16pt; BORDER-BOTTOM: #e2e2e2; BACKGROUND-COLOR: transparent" width=21></TD><TD style="BORDER-RIGHT: #e2e2e2; BORDER-TOP: #e2e2e2; BORDER-LEFT: #e2e2e2; WIDTH: 48pt; BORDER-BOTTOM: #e2e2e2; BACKGROUND-COLOR: transparent" width=64>Audit type</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #e2e2e2; BORDER-TOP: #e2e2e2; BORDER-LEFT: #e2e2e2; BORDER-BOTTOM: #e2e2e2; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="39539">01/04/2008</TD><TD class=xl24 style="BORDER-RIGHT: #e2e2e2; BORDER-TOP: #e2e2e2; BORDER-LEFT: #e2e2e2; BORDER-BOTTOM: #e2e2e2; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #e2e2e2; BORDER-TOP: #e2e2e2; BORDER-LEFT: #e2e2e2; BORDER-BOTTOM: #e2e2e2; BACKGROUND-COLOR: transparent">David</TD><TD style="BORDER-RIGHT: #e2e2e2; BORDER-TOP: #e2e2e2; BORDER-LEFT: #e2e2e2; BORDER-BOTTOM: #e2e2e2; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #e2e2e2; BORDER-TOP: #e2e2e2; BORDER-LEFT: #e2e2e2; BORDER-BOTTOM: #e2e2e2; BACKGROUND-COLOR: transparent">CDM</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #e2e2e2; BORDER-TOP: #e2e2e2; BORDER-LEFT: #e2e2e2; BORDER-BOTTOM: #e2e2e2; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="39539">01/04/2008</TD><TD class=xl24 style="BORDER-RIGHT: #e2e2e2; BORDER-TOP: #e2e2e2; BORDER-LEFT: #e2e2e2; BORDER-BOTTOM: #e2e2e2; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #e2e2e2; BORDER-TOP: #e2e2e2; BORDER-LEFT: #e2e2e2; BORDER-BOTTOM: #e2e2e2; BACKGROUND-COLOR: transparent">John</TD><TD style="BORDER-RIGHT: #e2e2e2; BORDER-TOP: #e2e2e2; BORDER-LEFT: #e2e2e2; BORDER-BOTTOM: #e2e2e2; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #e2e2e2; BORDER-TOP: #e2e2e2; BORDER-LEFT: #e2e2e2; BORDER-BOTTOM: #e2e2e2; BACKGROUND-COLOR: transparent">1 in 10</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #e2e2e2; BORDER-TOP: #e2e2e2; BORDER-LEFT: #e2e2e2; BORDER-BOTTOM: #e2e2e2; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="39568">30/04/2008</TD><TD class=xl24 style="BORDER-RIGHT: #e2e2e2; BORDER-TOP: #e2e2e2; BORDER-LEFT: #e2e2e2; BORDER-BOTTOM: #e2e2e2; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #e2e2e2; BORDER-TOP: #e2e2e2; BORDER-LEFT: #e2e2e2; BORDER-BOTTOM: #e2e2e2; BACKGROUND-COLOR: transparent">Bill</TD><TD style="BORDER-RIGHT: #e2e2e2; BORDER-TOP: #e2e2e2; BORDER-LEFT: #e2e2e2; BORDER-BOTTOM: #e2e2e2; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #e2e2e2; BORDER-TOP: #e2e2e2; BORDER-LEFT: #e2e2e2; BORDER-BOTTOM: #e2e2e2; BACKGROUND-COLOR: transparent">1 in 10</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #e2e2e2; BORDER-TOP: #e2e2e2; BORDER-LEFT: #e2e2e2; BORDER-BOTTOM: #e2e2e2; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="39569">01/05/2008</TD><TD class=xl24 style="BORDER-RIGHT: #e2e2e2; BORDER-TOP: #e2e2e2; BORDER-LEFT: #e2e2e2; BORDER-BOTTOM: #e2e2e2; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #e2e2e2; BORDER-TOP: #e2e2e2; BORDER-LEFT: #e2e2e2; BORDER-BOTTOM: #e2e2e2; BACKGROUND-COLOR: transparent">David</TD><TD style="BORDER-RIGHT: #e2e2e2; BORDER-TOP: #e2e2e2; BORDER-LEFT: #e2e2e2; BORDER-BOTTOM: #e2e2e2; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #e2e2e2; BORDER-TOP: #e2e2e2; BORDER-LEFT: #e2e2e2; BORDER-BOTTOM: #e2e2e2; BACKGROUND-COLOR: transparent">Vehicle</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #e2e2e2; BORDER-TOP: #e2e2e2; BORDER-LEFT: #e2e2e2; BORDER-BOTTOM: #e2e2e2; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="39569">01/05/2008</TD><TD class=xl24 style="BORDER-RIGHT: #e2e2e2; BORDER-TOP: #e2e2e2; BORDER-LEFT: #e2e2e2; BORDER-BOTTOM: #e2e2e2; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #e2e2e2; BORDER-TOP: #e2e2e2; BORDER-LEFT: #e2e2e2; BORDER-BOTTOM: #e2e2e2; BACKGROUND-COLOR: transparent">David</TD><TD style="BORDER-RIGHT: #e2e2e2; BORDER-TOP: #e2e2e2; BORDER-LEFT: #e2e2e2; BORDER-BOTTOM: #e2e2e2; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #e2e2e2; BORDER-TOP: #e2e2e2; BORDER-LEFT: #e2e2e2; BORDER-BOTTOM: #e2e2e2; BACKGROUND-COLOR: transparent">Vehicle</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #e2e2e2; BORDER-TOP: #e2e2e2; BORDER-LEFT: #e2e2e2; BORDER-BOTTOM: #e2e2e2; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="39569">01/05/2008</TD><TD class=xl24 style="BORDER-RIGHT: #e2e2e2; BORDER-TOP: #e2e2e2; BORDER-LEFT: #e2e2e2; BORDER-BOTTOM: #e2e2e2; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #e2e2e2; BORDER-TOP: #e2e2e2; BORDER-LEFT: #e2e2e2; BORDER-BOTTOM: #e2e2e2; BACKGROUND-COLOR: transparent">John</TD><TD style="BORDER-RIGHT: #e2e2e2; BORDER-TOP: #e2e2e2; BORDER-LEFT: #e2e2e2; BORDER-BOTTOM: #e2e2e2; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #e2e2e2; BORDER-TOP: #e2e2e2; BORDER-LEFT: #e2e2e2; BORDER-BOTTOM: #e2e2e2; BACKGROUND-COLOR: transparent">CDM</TD></TR></TBODY></TABLE>
The answer for these 3 columns is 5. Rows 4 and 5 are the same so are only counted once.
I can get the arrray to count unique items in the whole range and in a column but not for each row.
I have a feeling this is going to obvious but I can't work it out.
Thanks for helping
Partjob

 

[Matty]

Thanks Domenic - I'll have a play around with this tomorrow.

Regards,

Matty

 

[Domenic]

Thanks Domenic - I'll have a play around with this tomorrow.

Regards,

Matty

You're very welcome!

 

[Partjob]

Hi Again
just when I thought I was beginning to understand arrays I get all confused.
I now need a different array, I am not sure if this is less or more complecated than the last one.
I need to know how many unique rows there are, using columns "Date & Audit Type" but restricting the criteria to the audit type.

For example I want to count how many "CDM" were on different dates (Answer 2)
But for Vehicle the answer would be 1 as both the vehicle audits were done on the same date.

<TABLE style="WIDTH: 184pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=245 border=0 x:str><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-LEFT-COLOR: #e2e2e2; BORDER-BOTTOM-COLOR: #e2e2e2; WIDTH: 53pt; BORDER-TOP-COLOR: #e2e2e2; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #e2e2e2" width=71 height=17>Date</TD><TD style="BORDER-LEFT-COLOR: #e2e2e2; BORDER-BOTTOM-COLOR: #e2e2e2; WIDTH: 19pt; BORDER-TOP-COLOR: #e2e2e2; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #e2e2e2" width=25></TD><TD style="BORDER-LEFT-COLOR: #e2e2e2; BORDER-BOTTOM-COLOR: #e2e2e2; WIDTH: 48pt; BORDER-TOP-COLOR: #e2e2e2; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #e2e2e2" width=64></TD><TD style="BORDER-LEFT-COLOR: #e2e2e2; BORDER-BOTTOM-COLOR: #e2e2e2; WIDTH: 16pt; BORDER-TOP-COLOR: #e2e2e2; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #e2e2e2" width=21></TD><TD style="BORDER-LEFT-COLOR: #e2e2e2; BORDER-BOTTOM-COLOR: #e2e2e2; WIDTH: 48pt; BORDER-TOP-COLOR: #e2e2e2; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #e2e2e2" width=64>Audit type</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-LEFT-COLOR: #e2e2e2; BORDER-BOTTOM-COLOR: #e2e2e2; BORDER-TOP-COLOR: #e2e2e2; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #e2e2e2" align=right height=17 x:num="39539">01/04/2008</TD><TD class=xl24 style="BORDER-LEFT-COLOR: #e2e2e2; BORDER-BOTTOM-COLOR: #e2e2e2; BORDER-TOP-COLOR: #e2e2e2; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #e2e2e2"></TD><TD style="BORDER-LEFT-COLOR: #e2e2e2; BORDER-BOTTOM-COLOR: #e2e2e2; BORDER-TOP-COLOR: #e2e2e2; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #e2e2e2"></TD><TD style="BORDER-LEFT-COLOR: #e2e2e2; BORDER-BOTTOM-COLOR: #e2e2e2; BORDER-TOP-COLOR: #e2e2e2; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #e2e2e2"></TD><TD style="BORDER-LEFT-COLOR: #e2e2e2; BORDER-BOTTOM-COLOR: #e2e2e2; BORDER-TOP-COLOR: #e2e2e2; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #e2e2e2">CDM</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-LEFT-COLOR: #e2e2e2; BORDER-BOTTOM-COLOR: #e2e2e2; BORDER-TOP-COLOR: #e2e2e2; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #e2e2e2" align=right height=17 x:num="39539">01/04/2008</TD><TD class=xl24 style="BORDER-LEFT-COLOR: #e2e2e2; BORDER-BOTTOM-COLOR: #e2e2e2; BORDER-TOP-COLOR: #e2e2e2; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #e2e2e2"></TD><TD style="BORDER-LEFT-COLOR: #e2e2e2; BORDER-BOTTOM-COLOR: #e2e2e2; BORDER-TOP-COLOR: #e2e2e2; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #e2e2e2"></TD><TD style="BORDER-LEFT-COLOR: #e2e2e2; BORDER-BOTTOM-COLOR: #e2e2e2; BORDER-TOP-COLOR: #e2e2e2; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #e2e2e2"></TD><TD style="BORDER-LEFT-COLOR: #e2e2e2; BORDER-BOTTOM-COLOR: #e2e2e2; BORDER-TOP-COLOR: #e2e2e2; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #e2e2e2">1 in 10</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-LEFT-COLOR: #e2e2e2; BORDER-BOTTOM-COLOR: #e2e2e2; BORDER-TOP-COLOR: #e2e2e2; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #e2e2e2" align=right height=17 x:num="39568">30/04/2008</TD><TD class=xl24 style="BORDER-LEFT-COLOR: #e2e2e2; BORDER-BOTTOM-COLOR: #e2e2e2; BORDER-TOP-COLOR: #e2e2e2; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #e2e2e2"></TD><TD style="BORDER-LEFT-COLOR: #e2e2e2; BORDER-BOTTOM-COLOR: #e2e2e2; BORDER-TOP-COLOR: #e2e2e2; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #e2e2e2"></TD><TD style="BORDER-LEFT-COLOR: #e2e2e2; BORDER-BOTTOM-COLOR: #e2e2e2; BORDER-TOP-COLOR: #e2e2e2; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #e2e2e2"></TD><TD style="BORDER-LEFT-COLOR: #e2e2e2; BORDER-BOTTOM-COLOR: #e2e2e2; BORDER-TOP-COLOR: #e2e2e2; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #e2e2e2">1 in 10</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-LEFT-COLOR: #e2e2e2; BORDER-BOTTOM-COLOR: #e2e2e2; BORDER-TOP-COLOR: #e2e2e2; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #e2e2e2" align=right height=17 x:num="39569">01/05/2008</TD><TD class=xl24 style="BORDER-LEFT-COLOR: #e2e2e2; BORDER-BOTTOM-COLOR: #e2e2e2; BORDER-TOP-COLOR: #e2e2e2; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #e2e2e2"></TD><TD style="BORDER-LEFT-COLOR: #e2e2e2; BORDER-BOTTOM-COLOR: #e2e2e2; BORDER-TOP-COLOR: #e2e2e2; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #e2e2e2"></TD><TD style="BORDER-LEFT-COLOR: #e2e2e2; BORDER-BOTTOM-COLOR: #e2e2e2; BORDER-TOP-COLOR: #e2e2e2; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #e2e2e2"></TD><TD style="BORDER-LEFT-COLOR: #e2e2e2; BORDER-BOTTOM-COLOR: #e2e2e2; BORDER-TOP-COLOR: #e2e2e2; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #e2e2e2">Vehicle</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-LEFT-COLOR: #e2e2e2; BORDER-BOTTOM-COLOR: #e2e2e2; BORDER-TOP-COLOR: #e2e2e2; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #e2e2e2" align=right height=17 x:num="39569">01/05/2008</TD><TD class=xl24 style="BORDER-LEFT-COLOR: #e2e2e2; BORDER-BOTTOM-COLOR: #e2e2e2; BORDER-TOP-COLOR: #e2e2e2; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #e2e2e2"></TD><TD style="BORDER-LEFT-COLOR: #e2e2e2; BORDER-BOTTOM-COLOR: #e2e2e2; BORDER-TOP-COLOR: #e2e2e2; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #e2e2e2"></TD><TD style="BORDER-LEFT-COLOR: #e2e2e2; BORDER-BOTTOM-COLOR: #e2e2e2; BORDER-TOP-COLOR: #e2e2e2; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #e2e2e2"></TD><TD style="BORDER-LEFT-COLOR: #e2e2e2; BORDER-BOTTOM-COLOR: #e2e2e2; BORDER-TOP-COLOR: #e2e2e2; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #e2e2e2">Vehicle</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-LEFT-COLOR: #e2e2e2; BORDER-BOTTOM-COLOR: #e2e2e2; BORDER-TOP-COLOR: #e2e2e2; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #e2e2e2" align=right height=17 x:num="39569">01/05/2008</TD><TD class=xl24 style="BORDER-LEFT-COLOR: #e2e2e2; BORDER-BOTTOM-COLOR: #e2e2e2; BORDER-TOP-COLOR: #e2e2e2; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #e2e2e2"></TD><TD style="BORDER-LEFT-COLOR: #e2e2e2; BORDER-BOTTOM-COLOR: #e2e2e2; BORDER-TOP-COLOR: #e2e2e2; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #e2e2e2"></TD><TD style="BORDER-LEFT-COLOR: #e2e2e2; BORDER-BOTTOM-COLOR: #e2e2e2; BORDER-TOP-COLOR: #e2e2e2; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #e2e2e2"></TD><TD style="BORDER-LEFT-COLOR: #e2e2e2; BORDER-BOTTOM-COLOR: #e2e2e2; BORDER-TOP-COLOR: #e2e2e2; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #e2e2e2">CDM
</TD></TR></TBODY></TABLE>

I have been trying to put and If statement on the front of the array but that not right.
I would have a seperate formula for each audit type, I am sure once I have one though I will be able to ajust to suit for the others
Thanks a lot
Partjob

 

[Krishnakumar]

I think Domenic is offline.

Try,

=SUM(IF(FREQUENCY(IF($C$2:$C$7=D2,IF($A$2:$A$7&$C$2:$C$7<>"",MATCH("~"&$A$2:$A$7&$C$2:$C$7,$A$2:$A$7&$C$2:$C$7&"",0))),ROW($A$2:$A$7)-ROW($A$2)+1)>0,1))

where D2 houses CDM

Confirmed with CTRL+SHIFT+ENTER

HTH

 

[Partjob]

Krishnakumar
There is no way in amillon years I was going to get that right.
Thanks a lot it is brilliant
Partjob

 

[oakwood_001]

Hi All,

Sorry Partjob hope you don't mind if I add a query onto your thread...

I have a similar(ish) problem, except this on a large amount of data.

Basically I have 15 columns. Columns 1-14 contain times and column 15 contains a text string. There are ~4000 rows and includes many 00:00 values

I need to extract only unique rows within this mass table of data - I first tried using an Advanced Filter on the entire table to extract unique rows only but when using a SUMPRODUCT formula to check, it wasn't right.

Then I concatenated all the cells together on each row and then used the Advanced Filter on a singular column. I *think* this worked and after using SUMPRODUCT to check the occurences it seems to be right.

Could, however, anyone suggest if this is the best way to do something like this and why it wouldn't work correctly using the whole table?

Thanks

 

[Partjob]

Oakwood
This is the first time I will have tried to help someone else but here goes.
Your solution sounds very similar to the advice I first recieved, I concatenated and then counted it worked but looked messy.
As understand your problem can't you adapt the arrays I have recieved here the first one I got sounds best. the one Dominic sent, and add more columns ($A$2:$A$7&$C$2:$C$7) this the bit of the formula refering to each of my 3 columns, there can not be any harm adding further column referances.
The only down side is how slow it could be working with 15 columns and 4000 rows.
Let me know I would be interested.
Partjob

 

[oakwood_001]

Thanks Partjob

It helped

Now, the thing I didn't know before, is that I need to extract each record name of all of the individual 4000 rows that match the unique rows (of which there are ~2000)

i.e. UNIQUE_ROW_0001 = INDIVIDUAL_ROW_0001; 0009; 0101; 3400 etc.

Can anyone assist?

Hope this makes sense

 

[Domenic]

Thanks Partjob

It helped

Now, the thing I didn't know before, is that I need to extract each record name of all of the individual 4000 rows that match the unique rows (of which there are ~2000)

i.e. UNIQUE_ROW_0001 = INDIVIDUAL_ROW_0001; 0009; 0101; 3400 etc.

Can anyone assist?

Hope this makes sense

To extract unique records, try...

[Option 1]

Advanced Filter > Unique records only

[Option 2]

Assuming that A2:C4000 contains the data...

D2, copied down:

=A2&B2&C2

E1: 0

(Enter zero in E1.)

E2, copied down:

=IF(A2<>"",IF(ISNA(MATCH(D2,$D$1:D1,0)),LOOKUP(9.99999999999999E+307,$E$1:E1)+1,""),"")

F2:

=LOOKUP(9.99999999999999E+307,E1:E4000)

G2, copied down and across:

=IF(ROWS(G$2:G2)<=$F$2,LOOKUP(ROWS(G$2:G2),$E$2:$E$4000,A$2:A$4000),"")

Hope this helps!

 

[oakwood_001]

Thanks Domenic

Only just saw you're reply but I've got it to another stage now...

Basically, I now have a match column for each entry in P:P, which says:

=MATCH(R4,$S$4:$S$9999,FALSE) and returns the position in S4:S9999 of the corresponding matching combination

In R:R I have all the individual combinations - e.g. "A B D D E F H"

In S:S I have all the unique combinations - corresponding to all these unique combinations in Q:Q is a list of names. One unique combination can have many individual combinations and in turn many names.

I therefore need some kind of lookup array that can hold many values which matches the unique shift (designated by the match column P:P) to the name in Q:Q.

I'm really struggling so any help is really appreciated.

Thanks again.