ExcelBanter - Elimnating subsequent cells

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Elimnating subsequent cells (https://www.excelbanter.com/excel-discussion-misc-queries/69751-elimnating-subsequent-cells.html)

Elimnating subsequent cells

Cells in column A contain certain values.
How can I eliminate the same values in subsequent cell(s)?

Example.

Original worksheet:

A
1 DOG
2 12
3 CAT
4 15
5 BRUCE
6 DOG
7 1
8 2
9 CAT
10 DIANE
11 CATHY
12 3
13 DOG
14 15
15 PRUFROCK
16 HORSE
17 16
18 4
19 MOUSE
20 23
21 22
22 21
23 CAT
24 JOHN
25 DOG
26 JACK
27 26
28 HORSE
29 5
30 6
31 CAT
32 CAT
33 12
34 DOG
35 6
36 7
37 CAT
38 DOG
39 MOUSE
40 13
41 HORSE
42 52
43 DOG

Result:

A
1 DOG
2 12
3 CAT
4 15
5 BRUCE
6
7 1
8 2
9
10 DIANE
11 CATHY
12 3
13
14 15
15 PRUFROCK
16 HORSE
17 16
18 4
19 MOUSE
20 23
21 22
22 21
23
24 JOHN
25
26 JACK
27 26
28
29 5
30 6
31
32
33 12
34
35 6
36 7
37
38
39
40 13
41
42 52
43

Elimnating subsequent cells

Since the first item will always be in the result list make B1 equal
A1.
Put the following formula into cell B2 and copy it down:

=IF(ISERROR(MATCH(A2,$A$1:A1,0)),A2,"")

Elimnating subsequent cells

You have a few options, here's one:

Put a heading at the top of the list.
Assuming A1: MyList

C1: MyList

Select your list
DataFilterAdvanced Filter
Check: Copy to another location
Check: Unique Records Only
List Range: (your already selected list)
Copy to: $C$1
Click the [OK] button
(that will put all unique list values under C1)

Do what you want with your original list.

Does that helkp?

Regards,
Ron

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro

"GARY" wrote:

Cells in column A contain certain values.
How can I eliminate the same values in subsequent cell(s)?

Example.

Original worksheet:

A
1 DOG
2 12
3 CAT
4 15
5 BRUCE
6 DOG
7 1
8 2
9 CAT
10 DIANE
11 CATHY
12 3
13 DOG
14 15
15 PRUFROCK
16 HORSE
17 16
18 4
19 MOUSE
20 23
21 22
22 21
23 CAT
24 JOHN
25 DOG
26 JACK
27 26
28 HORSE
29 5
30 6
31 CAT
32 CAT
33 12
34 DOG
35 6
36 7
37 CAT
38 DOG
39 MOUSE
40 13
41 HORSE
42 52
43 DOG

Result:

A
1 DOG
2 12
3 CAT
4 15
5 BRUCE
6
7 1
8 2
9
10 DIANE
11 CATHY
12 3
13
14 15
15 PRUFROCK
16 HORSE
17 16
18 4
19 MOUSE
20 23
21 22
22 21
23
24 JOHN
25
26 JACK
27 26
28
29 5
30 6
31
32
33 12
34
35 6
36 7
37
38
39
40 13
41
42 52
43