Vb code or Excel formula Help for unique rows

hello,
i have a list of 5775 rows ,what i need is to get unique rows from this list.every string must be used only once no repeat.the final result is going to be 165 unique rows.like in example
in the attachment.
so is there any excel formula or Vb code to solve this?
thanks in advace for any help.
SEM

Thanks Martin i tried Advanced Filtering but that gives or copies column "A"only
what i need to include column "C" and "B" in the result.unless i'm doing something wrong.
would you care to explain how,if you dont mind?
Have a good day

Sem

hello Martin,
thanksfor for your help but filterin is not working ,see attachment for the result;
have a nice day
sem

Hello Martin
thanks for the effort but still not the right result
column"M" is ok but if you look to column"N" and "O" thre are alot of duplicate ie;
"O2.....O6",09 10 11 12" N6...N10 ;04 05 06 07.....and so on .my 4 rows example
are all unique no duplicate.what i needis similar to column"M"no repeat.
01 02 03 04 05 06 07 08, 09 10 11 12 in this row 2nd and 3rd
number string must not be used again in column "N" or "O.
I hope i explained my problem..
thank you again for your time.

Sem

Good Morning,
will it make any difference if i concatenate 3 column to 1 to solve this problem.
see attachment "sheet 2".out of 5775 rows i need only 165 unique rows
with no duplication.
thanks in advance for any assistance.
Sem

Hello Madhikari,
thank you for your help, but this code doing each column Advance Filtering ,the end result
is not correct,if you check in LIST2 string starting with"05.","04.","03.." you'll see duplicate of them in LIST3 and string numbers starting with "02..."are out of 165 rows range .the right result should be no duplicate of string,no duplicate number in any row aswell and the 3 lists must be equal "1-165 rows".
i hope i explained clearly.
thank and God Bless
Sem

Good Morning,
Any other suggestion apart from Advance filtering,would be much appreciated.
thank you....
Sem

Hello Martin,
Thank you for your interest in this tread i realy appreciated.
all columns must be unique and in a way connected.in attachment i gave different list for
example.also i gave the original Combin(12,4) thats where i got my list.
Thank you again and God Bless
Sem

Good Evening,
Just to give you more information;
In my first attachment List every string with 4 numbers are used 35 times,and every row
up to 5775 contain numbers from "1" to "12".when i use advance filtering
there are duplicate numbers in rowshere an example;
01 02 03 05, 05 06 07 09, 08 10 11 12
01 02 03 06, 05 06 07 10, 08 09 11 12
01 02 03 07, 05 06 07 11, 08 09 10 12
01 02 03 08, 05 06 07 12 ,08 09 10 11
01 02 03 09, 05 06 08 09, 07 10 11 12
in short my final result would be no duplicate in a row and every string of 4 numbers to be used once only.
Thank you for patience with me and God Bless
Sem

Good Evening Martin,
thanks for your time ,and i'm sorry for the trouble but i spent more than 4 hours last night
trying with Advance filtering but to no avail.either there are repeat or duplicate string
unfortunately for me ,it was waste of time.
i think it have to be Vb code to sort this problem,but i might be wrong ???
here is some adjestment to your post #6 attachmentjust to show you some of the string numbers total.as i said before every string(array) numbers must be used and only once .it does not matter which column they are in.
thank you and God Bless
Sem

Good Morning,
I would realy appreciate for any help please,Thanks.
Sem

Hello Martin,
thanks for your time,that list in your last attachment is ,my original list where i got
"combin(495,3)=20092215 rows" then i reduce it to 5775 rows.But in that list every string(array)of 4 numbers occur 35 times,what i need is to reduce 5575 rows list to 165 unique
rows each string to occur once only with out changing originality of the rows which is to have number"1" to "12" in them.
thanks again for your invaluable help.
Sem

good evening,
there are 165 unique rows as i said before every string of 4 numbers occur 35 times in that list "5775/35=165" and to find it manualy would take long time .That is why i'm seeking
excel formula or VB code to solve this,Anyway thanks for your time and effort.
Sem

Good day everybody,
this is how far i could do manualy,but it took me very long time to achive this.
See attachment;
would some please tell me if this solution would be achived with excel formula or Vb code?
i would be very greatful for valuable advice.
Thanks and Kind regards

I'm realy, very sorry for all this trouble..
i would appreciate for any suggestion to solve this problem.even if i have to search it in different website , with any link you could provide.
Have a good day
sem

Check blue and red cells...

I don't know why you have so short list from B column?

Good Afternoon,
Thanks Zbor for your help and effort,actually Column "A" is the shortes list after A. filtering
and column "B" is the longest the reason they have duplicate string.when i get my correct result all 3 columns should be the same as column "A" with 165 rows.
If you look at my expected result columns "H"and "I" they dont have any duplicate so far
with the helper columns "J :M" if you try to insert any row with red colour string, it would change to a green colour that mean its duplicate so you change it and choose different one with "0".I hope explained clearly.
Thanks for your time
sem

Sorry, I still can't get logic of it :/

Hello,
Zbor here an example from 3480 rows there are only 5 unique rows
which each row used only once and each row have number "1" to "16",
i hope this will shed light to the problem.
Have a nice day.
thanks
sem

Found it...

Good explanations is also important as good result

Good afternoon Zbor,
thank you very very much for wonderful help..appreciated very much.
But can explain some part of the formula please;

Please Login or Register  to view this content.

and how can i implement this formula to my original list?

thanks and God Bless
sem

Here is explanation (using ; as delimiter instead of , but doesn't matter):

=IF(COUNTIF($A$2:A2;A2)=1;IF(MAX(LEN(" "&A2&" "&B2&" "&C2&" ")-LEN(SUBSTITUTE(" "&A2&" "&B2&" "&C2&" ";" "&TEXT(ROW($A$1:$A$12);"00")&" ";"")))<=4;ROW(E2);"");"")

I'll separate it in 2 IF functions.

First IF function retur unique value in A column

COUNTIF($A$2:A2;A2)=1

So if this count is 1 (that's unique value) calulate or if not leave blank.
2nd IF function say:

Join together three column separated with " " (but also with blank at first and last place).

So from 01 02 03 0405 06 07 0809 10 11 12
you get 01 02 03 04 05 06 07 08 09 10 11 12

Now... replace each number with 01.
Then replace each with 02 etc.

that's what SUBSTITUTE(" "&A2&" "&B2&" "&C2&" ";" "&XXX&" ";"")

where XXX is TEXT(ROW($A$1:$A$12);"00") that would create array number (01, 02, 03, ..., 12)

And " "&XXX&" " will add " " at beggining and end of text giving _01_, _02_, etc

LEN of original string minus LEN of each string will give you something like:

=37-{33,33,33,33,33,33,33,33,33,33,33,33}
={4,4,4,4,4,4,4,4,4,4,4,4}

if each string is substituted only once.

If it's more than once you will get something like:

=37-{33,29,29,33,33,33,33,33,37,33,33,37}
={4,8,8,4,4,4,4,4,0,4,4,0}

meaning that numbers 2 and 3 are replaced twice, and numbers 9 and 12 none.

So MAX of it will return:

4 in first place
8 (or more) in any other case, meaning there are duplicated values so can not be unique...

IF it's unique return ROW number, else leave blank.

So =INDEX(A$2:A$10000;SMALL($D$2:$D$10000;ROWS($F$2:$F2)))

look in A column and return smallest row number, and how you go down it will increase. (Look more about SMALL function... this returns SMALL(array, 1), SMALL(array, 2), SMALL(array, 3) etc)

Now when I looking maybe its better to say:

IF MIN(LEN(.) - LEN(..) = 0) then MAX is 4... But that's finness...

If you manage to change it that means you get the function

Good evening Zbor,
I'm indebted for all your ingenious effort and help ,but unfortunately still did not solve my problem .Because if you look in your last attachment in second and third column's of the result there are alot of duplicate ,i know they are unique in a way but they are duplicate..
I dont expect you to to give your time anymore for that I salute you....
Have a Nice evening,and God Bless
SEM

One more question before proceed:

In rows 37,38 and 39 you have:

Please Login or Register  to view this content.

My (first) result returned 1st row (which is wrong, I got it why).
You write in result table it's 3rd row.

Why not second?

ok...
You'll need a macro, but for now at least we get the point of it

We have first row which is all unique (1, 1, 1 in D, E, F columns).
Questions: does it als need to check that all values within each cell are unique?
For example: 01 02 02 02 etc

Then in A column next is 2, 36, 1 where 2 and 36 are unique but 1 not (it's already taken from 1,1,1).
So it go further: 2, 37, 2

next is 3, 73, 3 (because 3, 71, 1 and 3, 72, 2 are already taken above)...

Etc...

Good evening Zbor,
thanks for still being interested on this,i realy appreciate your help.
If you go back to my post #22 attachment you'll see in column "E" how many times each string is used in 5775 rows,and also i managed to do 29 correct rows witout duplicate by having helper columns(J:M) to count correctly.as i said before every string or row in colum "E" must be used and only once.
from the begining i was thinking it needs VB code to solve it,but i might be wrong..
thank you for being very patience with me.
sem

good evening,
any help on VB code Please.
sem

I don't think it's right because there is no 05 06 07 09 in A column and you got it in E column as 2nd result...

Als, form the same 2nd result, this shouldn't be answer:

Please Login or Register  to view this content.

because there are duplicated 05, 06, 07 and 10. All should be unique (maybe I didn't write it but tha'ts because there is no duplication in original data).

If I get it corectly.

My apologies,
snbs' code correctly removes all duplicates from the Range A1:C5776, and presents them in one column, however the result is not transferable into columns to suit the request, as I too hastily decided.

Apologies again.....

Good Day,(Zbor,SNB,Marcol and NBVC)
Thank you very very much for all new info.i'm sorry for not responded sooner,i think the best result is Marcol's VB sulotion,its not complete but thats is the nearest to actual result.
i'm thinkering with result manualy so far just 10 row shorter than actual result.If i manage to solve it i'll keep you posted.
anyway i can not thank you enough for all this wonderful effort and help.
Have a nice evening..
Sem