Unique Combination of Values

[Kishan]

Hi, Peter

I found #post3 code very useful for me and I am using it. I want if you could adapt my need will be great of you.

I want to find unique among any of 14 columns B To O, selecting continuous or alternate 1, 2, 4, 6, or all 14
In example below I have shown 3 columns C, G, K in my selection.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Date	P1	P2	P3	P4	P5	P6	P7	P8	P9	P10	P11	P12	P13	P14
2	Date	P1	P2	P3	P4	P5	P6	P7	P8	P9	P10	P11	P12	P13	P14
3	01/12/2013	2	X	2	1	1	1	X	1	1	1	2	X	1	1
4	02/12/2013	2	2	X	1	2	1	X	X	2	1	2	1	2	X
5	03/12/2013	1	1	1	1	1	X	1	X	1	1	X	X	1	2
6	04/12/2013	1	1	1	2	1	1	1	2	X	1	1	1	X	1
7	05/12/2013	1	X	1	1	X	1	2	X	1	1	1	1	1	X
8	06/12/2013	1	1	1	2	2	X	1	1	1	X	1	1	1	X
9	07/12/2013	1	1	X	1	1	1	1	2	1	X	1	1	X	1
10	08/12/2013	1	2	1	1	1	1	2	1	1	X	1	1	1	2
11	09/12/2013	2	2	X	2	1	1	2	X	2	1	1	X	1	2
12	10/12/2013	X	1	1	1	1	2	2	1	1	2	1	X	1	1
13	11/12/2013	1	2	1	1	X	1	1	1	1	X	X	1	X	X
14	12/12/2013	1	1	2	X	2	2	1	2	X	1	1	X	2	1
15	13/12/2013	1	X	1	X	1	2	2	1	1	1	1	2	2	2
16	14/12/2013	X	X	1	1	2	1	1	2	2	2	X	1	1	1
17	15/12/2013	2	2	2	2	2	X	1	1	1	2	X	2	X	1
18	16/12/2013	X	1	1	1	X	X	X	1	1	1	X	1	2	2
19	17/12/2013	2	2	X	1	2	1	2	1	2	1	X	1	1	X
20	18/12/2013	X	1	1	2	1	1	2	1	X	2	X	2	2	1
21	19/12/2013	1	1	1	2	1	2	X	2	2	X	1	2	1	1
22	20/12/2013	2	1	1	1	X	2	1	1	X	1	X	1	2	2
Sheet1

I want result in sheet 2 as shown column B count match.

Book1 A B C D E 1 P2|P6|P10 Count Match P2 P6 P10 2 X|1|1 2 X 1 1 3 2|1|1 3 2 1 1 4 1|X|1 2 1 X 1 5 1|1|1 1 1 1 1 6 1|X|X 1 1 X X 7 1|1|X 1 1 1 X 8 2|1|X 2 2 1 X 9 1|2|2 1 1 2 2 10 1|2|1 2 1 2 1 11 X|2|1 1 X 2 1 12 X|1|2 1 X 1 2 13 2|X|2 1 2 X 2 14 1|1|2 1 1 1 2 15 1|2|X 1 1 2 X Sheet2 Using Excel Version 2000 Thank You Regards, Kishan

<colgroup><col><col><col span="3"></colgroup><tbody>
</tbody>

 

[Peter_SSs]

I am trying code with my data #post1 selecting column C, G, K it produce error ‘438’ after debug highlight following line.

.Columns("A").RemoveDuplicates Columns:=1, Header:=xlNo

.

I knew there was a third remark I was going to make.

3. RemoveDuplicates is not available in Excel 2000.

 

[Kishan]

Hi Peter,

I just add code to my original file which contain at present 2478 rows, code run perfect if I select any 2 columns, but it stop if I select 3 or more it produce error '13' and highlight at line below.

s = Join(Application.Index(a, i, 0), "|")

Please could you take a look?

Thank you

Regards,
Kishan

 

[Peter_SSs]

Hi Peter,

I just add code to my original file which contain at present 2478 rows, code run perfect if I select any 2 columns, but it stop if I select 3 or more it produce error '13' and highlight at line below.

s = Join(Application.Index(a, [COLOR="#FF0000"][B]i[/B][/COLOR], 0), "|")

Please could you take a look?

Thank you

Regards,
Kishan

I haven't been able to reproduce that error.

Does it error if you select any 3 columns or is it one particular column that seems to be the problem?

Run the code again & 'debug' when it errors.
Then hover your cursor over the "i" in that yellow highlighted line and note the pop-up number.
Suppose that number is 15, then the problem row in your sheet is row 16. Have a look at that row and see if there is anything unusual about it, especially in those columns that you have highlighted.

You could also then delete most of the rows below the problem row, but leave a couple, and most of the rows above the problem row (unless it is row < 5) but leave a couple above too if possible.

Run the code again and if it still errors on that same set of data, post that small set of data and advise which row is the problem.
Also advise if it seems to be one particular column in a group that is causing the error.

 

[Kishan]

Run the code again & 'debug' when it errors.

Hi Peter,

Very strange situation 14 column B:O in general whatever column is selected in any order reproduce error

With 5000 lines code works if I select any “1” column, if I select 2 col give error 'debug' show "i" =1
With 2000 lines code works if I select any “2” column, if I select 3 col give error 'debug' show "i" =1
With 1100 lines code works if I select any “3 or 4” column, if I select 5 col give error 'debug' show "i" =1
With 800 lines code works if I select any “5 or 6 ” column, if I select 7 col give error 'debug' show "i" =1
With 600 lines code works if I select any “7, 8 or 9” column, if I select 10 give error 'debug' show "i" =1
With 400 lines code works if I select any “10, 11,12 or 13” column, if I select 14 give error 'debug' show "i" =1
With 200 lines code works if I select all “14” column

This is what I have noticed tried all very carefully as line increase less column filters. And in all in all situation 'debug' show "i" =1


Regards,
Kishan

 

[Peter_SSs]

I can't reproduce the problem. I guess again it could relate to your very old Excel version.
Try ..
1. Add a new Dim j As Long at the top of the code, then
2. Remove that problem line & replace it with these blue lines of code.

For i = 1 To UBound(a)
  <del>s = Join(Application.Index(a, i, 0), "|")</del>
  s = vbNullString
  For j = 1 To UBound(a, 2)
    s = s & "|" & a(i, j)
  Next j
  s = Mid(s, 2)
  If d.exists(s) Then

 

[Kishan]

I can't reproduce the problem. I guess again it could relate to your very old Excel version.
Try ..
1. Add a new Dim j As Long at the top of the code, then
2. Remove that problem line & replace it with these blue lines of code.

For i = 1 To UBound(a)
  <del>s = Join(Application.Index(a, i, 0), "|")</del>
  s = vbNullString
  For j = 1 To UBound(a, 2)
    s = s & "|" & a(i, j)
  Next j
  s = Mid(s, 2)
  If d.exists(s) Then

Hi Peter,

Yes this did the trick I tried code with 5000 lines and it is working flawless like a magic.

Problem SOLVED.

Thank you for your passion and for helping me.

Kind Regards,
Kishan

 

[Peter_SSs]

Hi Peter,

Yes this did the trick I tried code with 5000 lines and it is working flawless like a magic.

Problem SOLVED.

Thank you for your passion and for helping me.

Kind Regards,
Kishan

Glad we got there in the end.

BTW, time to upgrade to a newer Excel version.