Vb code or Excel formula Help for unique rows

[sem]

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

[martindwilson]

use advanced filter on column A filter on unique rows copy paste the results to a new set of columns
hm not sure what makes unique here

[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

[martindwilson]

filter on a
copy a,b and c
paste somewhere

[sem]

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

[martindwilson]

i get this

[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

[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

[madhikari]

Hi,
First, Kindly add new worksheet as sheet2. Give list titles: LIST-1 LIST-2 LIST-3.

Please use this VBA code:

Sub Unique()
Sheets("Sheet1").Select
Columns("A:A").Select
Range("A1:A5776").AdvancedFilter Action:=xlFilterInPlace, Unique:=True
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Sheets("Sheet2").Select
Range("A1").Select
ActiveSheet.Paste
Columns("A:A").EntireColumn.AutoFit
Application.CutCopyMode = False
Sheets("Sheet1").Select
Range("A1").Select
ActiveSheet.ShowAllData
Columns("B:B").Select
Range("B1:B5776").AdvancedFilter Action:=xlFilterInPlace, Unique:=True
Range("B1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Sheets("Sheet2").Select
Range("B1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Sheets("Sheet1").Select
Range("B1").Select
ActiveSheet.ShowAllData
Columns("C:C").Select
Range("C1:C5776").AdvancedFilter Action:=xlFilterInPlace, Unique:=True
Range("C1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Sheets("Sheet2").Select
Range("C1").Select
ActiveSheet.Paste
Cells.Select
Cells.EntireColumn.AutoFit
Range("A1").Select
End Sub

[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

[sem]

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

[martindwilson]

i still cant see what makes unique ther are 165 unique values in column a alone

[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

[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

[martindwilson]

not in the list as in post #6 show some that are wrong

[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

[sem]

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

[NBVC]

To me it looks like from the original post the OP is looking for each row where column A changes.... that adds up to 165 as the OP verified.

So add a helper, in D1:

=IF(A2=A1,"",COUNT(D$1:D1)+1)

copied down

Then formula to get max in F1:

=MAX(D:D)

Then in E2 try:

=IF(ROWS($A$1:$A1)>$F$1,"",INDEX(A:A,MATCH(ROWS($A$1:$A1),$D:$D)))

copied down as far as necessary and across the next 2 columns.


P.S. I haven't read through all posts, so not sure if requirement changed. This is based solely on Post #1.

[martindwilson]

there are 495 unique values

[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

[martindwilson]

sorry but there are not 165 unique rows of non repeating data ,well i cant see that.

[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

[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

[sem]

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

[zbor]

Check blue and red cells...

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

[sem]

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

[zbor]

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

[sem]

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

[zbor]

Found it...

Good explanations is also important as good result

[sem]

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

ROW($A$1:$A$16)&" ","")))<=4,ROW(E2),"")

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

thanks and God Bless
sem

[zbor]

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)

[sem]

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

[zbor]

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

[sem]

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

[sem]

good evening,
any help on VB code Please.
sem

[snb]

I suppose you're not residing in London....
You didn't metion how you got these string, because there's a simple method to produce such unique strings.
'Filtering' unique items from existing data can be done with

sub snb()
  sq=cells(1).currentregion
  for j =1 to ubound(sq)*ubound(sq,2)
    if instr(c01 & "|" ,"|" & sq(1+ (j -1)\ubound(sq,2),(j-1) mod ubound(sq,2)+1) & "|")=0 then c01=c01 & "|" & sq(1+ (j -1)\ubound(sq,2),(j-1) mod ubound(sq,2)+1)
  next
  sn=split(mid(c01,2),"|")

  sheets(1).cells(1,20).resize(ubound(sn)+1)=application.transpose(sn)
End Sub

[zbor]

One more question before proceed:

In rows 37,38 and 39 you have:

01 02 03 05	04 06 07 08	09 10 11 12
01 02 03 05	04 06 07 09	08 10 11 12
01 02 03 05	04 06 07 10	08 09 11 12

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?

[sem]

Good Morning,
thanks for being still interested on this .because i done it manually i just choose any row it does not have duplicate from the previous row i choosed,actualynot just row 38and 39
i can choose from the next atleast 20 rows still would be correct.
so when you choose a row for next one any string on the red can not be used again;

Result		
01 02 03 04	05 06 07 08	09 10 11 12
01 02 03 05	04 06 07 10	08 09 11 12
01 02 03 06	04 05 08 09	07 10 11 12
01 02 03 07	04 09 10 11	05 06 08 12
01 02 03 08

i hope it's more clear now..
thanks again and have a good day.
sem

[zbor]

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...

[Marcol]

My reading was that the result should be presented in 3 columns

Perhaps this variation on snbs' code

Sub snb()
    sq = Cells(1).CurrentRegion
    For j = 1 To UBound(sq, 1) * UBound(sq, 2)
        If InStr(c01 & "|", "|" & sq(1 + (j - 1) \ UBound(sq, 2), (j - 1) Mod UBound(sq, 2) + 1) & "|") = 0 Then
            c01 = c01 & "|" & sq(1 + (j - 1) \ UBound(sq, 2), (j - 1) Mod UBound(sq, 2) + 1)
        End If
    Next
    sn = Split(Mid(c01, 2), "|")
    For jj = 0 To UBound(sq, 2) - 1
        For jjj = jj To UBound(sn) Step UBound(sq, 2)
            c02 = c02 & "|" & sn(jjj)
        Next
        sn1 = Split(Mid(c02, 2), "|")
        Sheets(1).Cells(1, 5 + jj).Resize(UBound(sn1) + 1) = Application.Transpose(sn1)
        c02 = ""
    Next
End Sub

Test workbook attached

[zbor]

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:

05 06 07 09	08 10 11 12	05 06 07 10

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.

[Marcol]

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.....

[Marcol]

This code will return the each first unique row for the table given in the example.

Sub FindUniqueCombinations()
    Dim LastRow As Long, RowNo As Long, NextRow As Long
    Dim Col1 As String, Col2 As String, Col3 As String
    Dim Unique1 As Boolean, Unique2 As Boolean, Unique3 As Boolean

    LastRow = Range("A" & Rows.Count).End(xlUp).Row
    For RowNo = 2 To LastRow
        Col1 = Cells(RowNo, 1)
        Col2 = Cells(RowNo, 2)
        Col3 = Cells(RowNo, 3)
        NextRow = Range("E" & Rows.Count).End(xlUp).Row + 1
        If WorksheetFunction.CountIf(Range("E1:G" & NextRow - 1), Col1) = 0 Then Unique1 = True
        If WorksheetFunction.CountIf(Range("E1:G" & NextRow - 1), Col2) = 0 Then Unique2 = True
        If WorksheetFunction.CountIf(Range("E1:G" & NextRow - 1), Col3) = 0 Then Unique3 = True
        If Unique1 And Unique2 And Unique3 Then
            NextRow = Range("E" & Rows.Count).End(xlUp).Row + 1
            Cells(NextRow, 5) = Col1
            Cells(NextRow, 6) = Col2
            Cells(NextRow, 7) = Col3
        End If
        Unique1 = False
        Unique2 = False
        Unique3 = False
    Next
End Sub

I have added a few check formula to the sheet to prove that each number group appears only once in the result, and the row number that the unique set occurs.

This does not return 165 rows in this case because there are duplicates that occur in B & C, this reduces the number of rows returned and will not return all the unique values found in Column A (165)

The "Unique Rows" returned will depend on how the data is sorted, in this case it seems to be
By Column A Then B then C.

Hope this helps.

[EDIT]
This is painfully slow when the proof columns are on the worksheet, once they are deleted it seems okay.

[Marcol]

This might be a formula solution, basically the formula behind the VBa in the previous post.

In E2

=IF(AND(COUNTIF($E$1:$G1,A2)=0,COUNTIF($E$1:$G1,B2)=0,COUNTIF($E$1:$G1,C2)=0),A2,"")

Drag/Fill Down to the bitter end
In F2

=IF(E2="","",B2)

Drag over to G2 then Fill Down

It's a bit slow but it gets there..............zzzzzzzzz

Then filter the result omitting blanks

Copy and paste special > values to your desired location

[sem]

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