Selecting rows from a table without getting duplicate cells

[Jeff46A]

I don't know if this is an Excel problem, or if it belongs somewhere else. If it shouldn't be here please excuse me and I'll drop it.

The table contains rows of five numbers. The aim is to select as many rows as possible without duplicating any of the numbers. The full table is larger than this one, but I hope there's enough data to illustrate the question.

4,5,206,219,273
7,13,52,267,271
8,24,96,117,193
9,16,29,51,282
10,16,48,114,230
11,22,117,275,282
11,68,88,95,214
12,26,113,175,220
12,36,113,127,216
13,18,124,263,264
13,21,124,204,268
13,35,102,142,219
17,39,76,181,201
17,43,52,158,251
18,19,60,68,198
19,30,104,155,164
19,31,104,155,164
19,33,128,248,252
19,57,73,249,282
20,44,101,211,252
20,49,126,146,252
20,52,80,209,273
21,45,81,166,220
21,61,82,200,247
22,34,72,124,244

 

[jim may]

Point out in your shortened example where the 1st and second Duplicate occurs and what should be done if and when it happens.

 

[jim may]

In the Mean-time - here's a shot at what maybe you want...: Cell I3 Formula =

=COUNTIF($C$3:$G$27,C3)>1 Copy over and down...

<tbody>
</tbody>

Excel 2010

	A	B	C	D	E	F	G	H	I	J	K	L	M
1	** YOUR DATA **		*** Convert Your Data Text to Columns ***						*** Enter Countif Formula - True = Duplicate ***
2									*** Then Apply Conditional Format - Value = True***
3	4,5,206,219,273		4	5	206	219	273		FALSE	FALSE	FALSE	TRUE	TRUE
4	7,13,52,267,271		7	13	52	267	271		FALSE	TRUE	TRUE	FALSE	FALSE
5	8,24,96,117,193		8	24	96	117	193		FALSE	FALSE	FALSE	TRUE	FALSE
6	9,16,29,51,282		9	16	29	51	282		FALSE	TRUE	FALSE	FALSE	TRUE
7	10,16,48,114,230		10	16	48	114	230		FALSE	TRUE	FALSE	FALSE	FALSE
8	11,22,117,275,282		11	22	117	275	282		TRUE	TRUE	TRUE	FALSE	TRUE
9	11,68,88,95,214		11	68	88	95	214		TRUE	TRUE	FALSE	FALSE	FALSE
10	12,26,113,175,220		12	26	113	175	220		TRUE	FALSE	TRUE	FALSE	TRUE
11	12,36,113,127,216		12	36	113	127	216		TRUE	FALSE	TRUE	FALSE	FALSE
12	13,18,124,263,264		13	18	124	263	264		TRUE	TRUE	TRUE	FALSE	FALSE
13	13,21,124,204,268		13	21	124	204	268		TRUE	TRUE	TRUE	FALSE	FALSE
14	13,35,102,142,219		13	35	102	142	219		TRUE	FALSE	FALSE	FALSE	TRUE
15	17,39,76,181,201		17	39	76	181	201		TRUE	FALSE	FALSE	FALSE	FALSE
16	17,43,52,158,251		17	43	52	158	251		TRUE	FALSE	TRUE	FALSE	FALSE
17	18,19,60,68,198		18	19	60	68	198		TRUE	TRUE	FALSE	TRUE	FALSE
18	19,30,104,155,164		19	30	104	155	164		TRUE	FALSE	TRUE	TRUE	TRUE
19	19,31,104,155,164		19	31	104	155	164		TRUE	FALSE	TRUE	TRUE	TRUE
20	19,33,128,248,252		19	33	128	248	252		TRUE	FALSE	FALSE	FALSE	TRUE
21	19,57,73,249,282		19	57	73	249	282		TRUE	FALSE	FALSE	FALSE	TRUE
22	20,44,101,211,252		20	44	101	211	252		TRUE	FALSE	FALSE	FALSE	TRUE
23	20,49,126,146,252		20	49	126	146	252		TRUE	FALSE	FALSE	FALSE	TRUE
24	20,52,80,209,273		20	52	80	209	273		TRUE	TRUE	FALSE	FALSE	TRUE
25	21,45,81,166,220		21	45	81	166	220		TRUE	FALSE	FALSE	FALSE	TRUE
26	21,61,82,200,247		21	61	82	200	247		TRUE	FALSE	FALSE	FALSE	FALSE
27	22,34,72,124,244		22	34	72	124	244		TRUE	FALSE	FALSE	TRUE	FALSE

<tbody>
</tbody>

Sheet1​

 

[Jeff46A]

Thanks. What I did before I posted here was to look for the first row with a duplicate value. Number 16 appears in both the fourth and fifth rows, so I selected row 4. Going down the rows manually I selected 1-4, 7-8, 13, 16, 20 and 24-25 (your 3-7, 9-10, 15, 18, 22 and 26-27) and that gave me 11 rows and 55 unique values. I wonder if there is a way to get 12 rows or more, but I thought of the travelling salesman problem. There may be 2^25 combinations for the number of rows, or 2^125 for the number of values, but either way it isn't possible to do it be brute force.