Erase the unique combinations

**antchh111** · 07-03-2014, 04:04 PM

Hello to all,

I am confronted with a problem that I do not managed to solve, it has been 2 days that I'm working on...

I have a file with approximately 185 000 lines and about twenty columns.

I have three columns that interest me. In these three I want to isolate the unique combinations to erase them.

I want that when the data of a line of my table make a unique combination (excluding the empty ones) that this one can be isolated in order to be erased.

For more understanding, in the enclosed file, I have underlined the lines that I wish to erase because they "pollute" my file.

I thank you for your help

**shg** · 07-03-2014, 04:28 PM

How about a couple of formulas and then filter?

	A	B	C	D	E	F
1	Col1	Col2	Col3	Catenation	Count
2	AAAAA	BBBBB		AAAAA\|BBBBB\|	3	D2: =A2 & "\|" & B2 & "\|" & C2
3	AAAAA	BBBBB		AAAAA\|BBBBB\|	3	E2: =COUNTIF($D$2:$D$46, D2)
4	AAAAA	BBBBB		AAAAA\|BBBBB\|	3
5	ZZZZZZ	EEEEEE		ZZZZZZ\|EEEEEE\|	1
6	ZZZZZZ	RRRRRR		ZZZZZZ\|RRRRRR\|	2
7	ZZZZZZ	RRRRRR		ZZZZZZ\|RRRRRR\|	2
8	ZZZZZZ	TTTTTTT		ZZZZZZ\|TTTTTTT\|	4
9	ZZZZZZ	TTTTTTT		ZZZZZZ\|TTTTTTT\|	4
10	ZZZZZZ	TTTTTTT		ZZZZZZ\|TTTTTTT\|	4
11	ZZZZZZ	TTTTTTT		ZZZZZZ\|TTTTTTT\|	4
12	ZZZZZZ	YYYYYYY		ZZZZZZ\|YYYYYYY\|	2
13	ZZZZZZ	YYYYYYY		ZZZZZZ\|YYYYYYY\|	2
14	ZZZZZZ	UUUUUU	PPPPPPP	ZZZZZZ\|UUUUUU\|PPPPPPP	7
15	ZZZZZZ	UUUUUU	PPPPPPP	ZZZZZZ\|UUUUUU\|PPPPPPP	7
16	ZZZZZZ	UUUUUU	PPPPPPP	ZZZZZZ\|UUUUUU\|PPPPPPP	7
17	ZZZZZZ	UUUUUU	PPPPPPP	ZZZZZZ\|UUUUUU\|PPPPPPP	7
18	ZZZZZZ	UUUUUU	PPPPPPP	ZZZZZZ\|UUUUUU\|PPPPPPP	7
19	ZZZZZZ	UUUUUU	PPPPPPP	ZZZZZZ\|UUUUUU\|PPPPPPP	7
20	ZZZZZZ	UUUUUU	PPPPPPP	ZZZZZZ\|UUUUUU\|PPPPPPP	7
21	ZZZZZZ	IIIIIIIII		ZZZZZZ\|IIIIIIIII\|	1
22	ZZZZZZ	22222222		ZZZZZZ\|22222222\|	1
23	ZZZZZZ	33333333		ZZZZZZ\|33333333\|	1
24	ZZZZZZ	QQQQQQ	SSSSSSS	ZZZZZZ\|QQQQQQ\|SSSSSSS	10
25	ZZZZZZ	QQQQQQ	SSSSSSS	ZZZZZZ\|QQQQQQ\|SSSSSSS	10
26	ZZZZZZ	QQQQQQ	SSSSSSS	ZZZZZZ\|QQQQQQ\|SSSSSSS	10
27	ZZZZZZ	QQQQQQ	SSSSSSS	ZZZZZZ\|QQQQQQ\|SSSSSSS	10
28	ZZZZZZ	QQQQQQ	SSSSSSS	ZZZZZZ\|QQQQQQ\|SSSSSSS	10
29	ZZZZZZ	QQQQQQ	SSSSSSS	ZZZZZZ\|QQQQQQ\|SSSSSSS	10
30	ZZZZZZ	QQQQQQ	SSSSSSS	ZZZZZZ\|QQQQQQ\|SSSSSSS	10
31	ZZZZZZ	QQQQQQ	SSSSSSS	ZZZZZZ\|QQQQQQ\|SSSSSSS	10
32	ZZZZZZ	QQQQQQ	SSSSSSS	ZZZZZZ\|QQQQQQ\|SSSSSSS	10
33	ZZZZZZ	QQQQQQ	SSSSSSS	ZZZZZZ\|QQQQQQ\|SSSSSSS	10
34	ZZZZZZ	QQQQQQ		ZZZZZZ\|QQQQQQ\|	1
35	ZZZZZZ	DDDDDDD		ZZZZZZ\|DDDDDDD\|	1
36	ZZZZZZ	FFFFFF		ZZZZZZ\|FFFFFF\|	3
37	ZZZZZZ	FFFFFF		ZZZZZZ\|FFFFFF\|	3
38	ZZZZZZ	FFFFFF		ZZZZZZ\|FFFFFF\|	3
39	GGGGGGG	HHHHHHH	MMMMM	GGGGGGG\|HHHHHHH\|MMMMM	1
40	GGGGGGG	JJJJJJJJJJ	WWWWW	GGGGGGG\|JJJJJJJJJJ\|WWWWW	1
41	GGGGGGG	KKKKKKK	XXXXXXX	GGGGGGG\|KKKKKKK\|XXXXXXX	1
42	GGGGGGG	LLLLLLLL	DDDDDD	GGGGGGG\|LLLLLLLL\|DDDDDD	3
43	GGGGGGG	LLLLLLLL	DDDDDD	GGGGGGG\|LLLLLLLL\|DDDDDD	3
44	GGGGGGG	LLLLLLLL	DDDDDD	GGGGGGG\|LLLLLLLL\|DDDDDD	3
45	CCCCCCCC	BBBBBBBBB		CCCCCCCC\|BBBBBBBBB\|	1
46	VVVVVVV	BBBBBBBBB	NNNNNN	VVVVVVV\|BBBBBBBBB\|NNNNNN	1

**newdoverman** · 07-03-2014, 04:30 PM

I interpret your meaning of unique to mean the combination of the 3 elements. If that is the case, I used a helper column to concatenate the contents of the 3 columns then used a countif to identify those entries that are unique.

**antchh111** · 07-03-2014, 04:44 PM

See upload the real figures and those that are underlined are the lines that I don't want

Thanks for you help

**antchh111** · 07-03-2014, 04:53 PM

Thanks for your answer but the blank are important, when you get a blank, the line does not have to be erased

**shg** · 07-03-2014, 05:10 PM

I don't know who you're responding to, but you can change the formula to accommodate that:

	A	B	C	D	E	F
1	Col1	Col2	Col3	Catenation	Count
2	AAAAA	BBBBB		AAAAA\|BBBBB\|	0	D2: =A2 & "\|" & B2 & "\|" & C2
3	AAAAA	BBBBB		AAAAA\|BBBBB\|	0	E2: =COUNTIF($D$2:$D$46, D2) * (COUNTA(A2:C2)=3)
4	AAAAA	BBBBB		AAAAA\|BBBBB\|	0
5	ZZZZZZ	EEEEEE		ZZZZZZ\|EEEEEE\|	0
6	ZZZZZZ	RRRRRR		ZZZZZZ\|RRRRRR\|	0
7	ZZZZZZ	RRRRRR		ZZZZZZ\|RRRRRR\|	0
8	ZZZZZZ	TTTTTTT		ZZZZZZ\|TTTTTTT\|	0
9	ZZZZZZ	TTTTTTT		ZZZZZZ\|TTTTTTT\|	0
10	ZZZZZZ	TTTTTTT		ZZZZZZ\|TTTTTTT\|	0
11	ZZZZZZ	TTTTTTT		ZZZZZZ\|TTTTTTT\|	0
12	ZZZZZZ	YYYYYYY		ZZZZZZ\|YYYYYYY\|	0
13	ZZZZZZ	YYYYYYY		ZZZZZZ\|YYYYYYY\|	0
14	ZZZZZZ	UUUUUU	PPPPPPP	ZZZZZZ\|UUUUUU\|PPPPPPP	7
15	ZZZZZZ	UUUUUU	PPPPPPP	ZZZZZZ\|UUUUUU\|PPPPPPP	7
16	ZZZZZZ	UUUUUU	PPPPPPP	ZZZZZZ\|UUUUUU\|PPPPPPP	7
17	ZZZZZZ	UUUUUU	PPPPPPP	ZZZZZZ\|UUUUUU\|PPPPPPP	7
18	ZZZZZZ	UUUUUU	PPPPPPP	ZZZZZZ\|UUUUUU\|PPPPPPP	7
19	ZZZZZZ	UUUUUU	PPPPPPP	ZZZZZZ\|UUUUUU\|PPPPPPP	7
20	ZZZZZZ	UUUUUU	PPPPPPP	ZZZZZZ\|UUUUUU\|PPPPPPP	7
21	ZZZZZZ	IIIIIIIII		ZZZZZZ\|IIIIIIIII\|	0
22	ZZZZZZ	22222222		ZZZZZZ\|22222222\|	0
23	ZZZZZZ	33333333		ZZZZZZ\|33333333\|	0
24	ZZZZZZ	QQQQQQ	SSSSSSS	ZZZZZZ\|QQQQQQ\|SSSSSSS	10
25	ZZZZZZ	QQQQQQ	SSSSSSS	ZZZZZZ\|QQQQQQ\|SSSSSSS	10
26	ZZZZZZ	QQQQQQ	SSSSSSS	ZZZZZZ\|QQQQQQ\|SSSSSSS	10
27	ZZZZZZ	QQQQQQ	SSSSSSS	ZZZZZZ\|QQQQQQ\|SSSSSSS	10
28	ZZZZZZ	QQQQQQ	SSSSSSS	ZZZZZZ\|QQQQQQ\|SSSSSSS	10
29	ZZZZZZ	QQQQQQ	SSSSSSS	ZZZZZZ\|QQQQQQ\|SSSSSSS	10
30	ZZZZZZ	QQQQQQ	SSSSSSS	ZZZZZZ\|QQQQQQ\|SSSSSSS	10
31	ZZZZZZ	QQQQQQ	SSSSSSS	ZZZZZZ\|QQQQQQ\|SSSSSSS	10
32	ZZZZZZ	QQQQQQ	SSSSSSS	ZZZZZZ\|QQQQQQ\|SSSSSSS	10
33	ZZZZZZ	QQQQQQ	SSSSSSS	ZZZZZZ\|QQQQQQ\|SSSSSSS	10
34	ZZZZZZ	QQQQQQ		ZZZZZZ\|QQQQQQ\|	0
35	ZZZZZZ	DDDDDDD		ZZZZZZ\|DDDDDDD\|	0
36	ZZZZZZ	FFFFFF		ZZZZZZ\|FFFFFF\|	0
37	ZZZZZZ	FFFFFF		ZZZZZZ\|FFFFFF\|	0
38	ZZZZZZ	FFFFFF		ZZZZZZ\|FFFFFF\|	0
39	GGGGGGG	HHHHHHH	MMMMM	GGGGGGG\|HHHHHHH\|MMMMM	1
40	GGGGGGG	JJJJJJJJJJ	WWWWW	GGGGGGG\|JJJJJJJJJJ\|WWWWW	1
41	GGGGGGG	KKKKKKK	XXXXXXX	GGGGGGG\|KKKKKKK\|XXXXXXX	1
42	GGGGGGG	LLLLLLLL	DDDDDD	GGGGGGG\|LLLLLLLL\|DDDDDD	3
43	GGGGGGG	LLLLLLLL	DDDDDD	GGGGGGG\|LLLLLLLL\|DDDDDD	3
44	GGGGGGG	LLLLLLLL	DDDDDD	GGGGGGG\|LLLLLLLL\|DDDDDD	3
45	CCCCCCCC	BBBBBBBBB		CCCCCCCC\|BBBBBBBBB\|	0
46	VVVVVVV	BBBBBBBBB	NNNNNN	VVVVVVV\|BBBBBBBBB\|NNNNNN	1

**antchh111** · 07-03-2014, 05:14 PM

Thank you I'm gonna check that

Originally Posted by shg

I don't know who you're responding to, but you can change the formula to accommodate that:

	A	B	C	D	E	F
1	Col1	Col2	Col3	Catenation	Count
2	AAAAA	BBBBB		AAAAA\|BBBBB\|	0	D2: =A2 & "\|" & B2 & "\|" & C2
3	AAAAA	BBBBB		AAAAA\|BBBBB\|	0	E2: =COUNTIF($D$2:$D$46, D2) * (COUNTA(A2:C2)=3)
4	AAAAA	BBBBB		AAAAA\|BBBBB\|	0
5	ZZZZZZ	EEEEEE		ZZZZZZ\|EEEEEE\|	0
6	ZZZZZZ	RRRRRR		ZZZZZZ\|RRRRRR\|	0
7	ZZZZZZ	RRRRRR		ZZZZZZ\|RRRRRR\|	0
8	ZZZZZZ	TTTTTTT		ZZZZZZ\|TTTTTTT\|	0
9	ZZZZZZ	TTTTTTT		ZZZZZZ\|TTTTTTT\|	0
10	ZZZZZZ	TTTTTTT		ZZZZZZ\|TTTTTTT\|	0
11	ZZZZZZ	TTTTTTT		ZZZZZZ\|TTTTTTT\|	0
12	ZZZZZZ	YYYYYYY		ZZZZZZ\|YYYYYYY\|	0
13	ZZZZZZ	YYYYYYY		ZZZZZZ\|YYYYYYY\|	0
14	ZZZZZZ	UUUUUU	PPPPPPP	ZZZZZZ\|UUUUUU\|PPPPPPP	7
15	ZZZZZZ	UUUUUU	PPPPPPP	ZZZZZZ\|UUUUUU\|PPPPPPP	7
16	ZZZZZZ	UUUUUU	PPPPPPP	ZZZZZZ\|UUUUUU\|PPPPPPP	7
17	ZZZZZZ	UUUUUU	PPPPPPP	ZZZZZZ\|UUUUUU\|PPPPPPP	7
18	ZZZZZZ	UUUUUU	PPPPPPP	ZZZZZZ\|UUUUUU\|PPPPPPP	7
19	ZZZZZZ	UUUUUU	PPPPPPP	ZZZZZZ\|UUUUUU\|PPPPPPP	7
20	ZZZZZZ	UUUUUU	PPPPPPP	ZZZZZZ\|UUUUUU\|PPPPPPP	7
21	ZZZZZZ	IIIIIIIII		ZZZZZZ\|IIIIIIIII\|	0
22	ZZZZZZ	22222222		ZZZZZZ\|22222222\|	0
23	ZZZZZZ	33333333		ZZZZZZ\|33333333\|	0
24	ZZZZZZ	QQQQQQ	SSSSSSS	ZZZZZZ\|QQQQQQ\|SSSSSSS	10
25	ZZZZZZ	QQQQQQ	SSSSSSS	ZZZZZZ\|QQQQQQ\|SSSSSSS	10
26	ZZZZZZ	QQQQQQ	SSSSSSS	ZZZZZZ\|QQQQQQ\|SSSSSSS	10
27	ZZZZZZ	QQQQQQ	SSSSSSS	ZZZZZZ\|QQQQQQ\|SSSSSSS	10
28	ZZZZZZ	QQQQQQ	SSSSSSS	ZZZZZZ\|QQQQQQ\|SSSSSSS	10
29	ZZZZZZ	QQQQQQ	SSSSSSS	ZZZZZZ\|QQQQQQ\|SSSSSSS	10
30	ZZZZZZ	QQQQQQ	SSSSSSS	ZZZZZZ\|QQQQQQ\|SSSSSSS	10
31	ZZZZZZ	QQQQQQ	SSSSSSS	ZZZZZZ\|QQQQQQ\|SSSSSSS	10
32	ZZZZZZ	QQQQQQ	SSSSSSS	ZZZZZZ\|QQQQQQ\|SSSSSSS	10
33	ZZZZZZ	QQQQQQ	SSSSSSS	ZZZZZZ\|QQQQQQ\|SSSSSSS	10
34	ZZZZZZ	QQQQQQ		ZZZZZZ\|QQQQQQ\|	0
35	ZZZZZZ	DDDDDDD		ZZZZZZ\|DDDDDDD\|	0
36	ZZZZZZ	FFFFFF		ZZZZZZ\|FFFFFF\|	0
37	ZZZZZZ	FFFFFF		ZZZZZZ\|FFFFFF\|	0
38	ZZZZZZ	FFFFFF		ZZZZZZ\|FFFFFF\|	0
39	GGGGGGG	HHHHHHH	MMMMM	GGGGGGG\|HHHHHHH\|MMMMM	1
40	GGGGGGG	JJJJJJJJJJ	WWWWW	GGGGGGG\|JJJJJJJJJJ\|WWWWW	1
41	GGGGGGG	KKKKKKK	XXXXXXX	GGGGGGG\|KKKKKKK\|XXXXXXX	1
42	GGGGGGG	LLLLLLLL	DDDDDD	GGGGGGG\|LLLLLLLL\|DDDDDD	3
43	GGGGGGG	LLLLLLLL	DDDDDD	GGGGGGG\|LLLLLLLL\|DDDDDD	3
44	GGGGGGG	LLLLLLLL	DDDDDD	GGGGGGG\|LLLLLLLL\|DDDDDD	3
45	CCCCCCCC	BBBBBBBBB		CCCCCCCC\|BBBBBBBBB\|	0
46	VVVVVVV	BBBBBBBBB	NNNNNN	VVVVVVV\|BBBBBBBBB\|NNNNNN	1

**shg** · 07-03-2014, 05:36 PM

**newdoverman** · 07-03-2014, 07:48 PM

Could this possibly be what you are looking for. I'm a little confused about the values that have 3 columns and 2 columns of the three are repeated. I have some "unique" values at the bottom of the list that you haven't identified.

Erase the unique combinations

LinkBack

Thread Tools

Rate This Thread

Display

Erase the unique combinations

Re: Erase the unique combinations

Re: Erase the unique combinations

Re: Erase the unique combinations

Re: Erase the unique combinations

Re: Erase the unique combinations

Re: Erase the unique combinations

Re: Erase the unique combinations

Re: Erase the unique combinations

Thread Information

Users Browsing this Thread

Similar Threads

Unique combinations of 7

VBA - Finding unique combinations

Unique Combinations List

[SOLVED] Getting all unique combinations with in a collection?

Count unique combinations

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions