Removes the Duplicate in a single Cell

lancerj017 · Feb 22, 2012

hi i have a question for you guys is it possible to remove duplicates the data in a single cell using macro?

Example

Column A
with apple, with banana, with apple, with banana
with apple, with apple, with apple
with banana, with banana, with banana

Need to do:

Column A
with apple, with banana
with apple
with banana

that's data is example only!

hiker95 · Feb 27, 2012

lancerj017, mirabeau, Biz,

Sample raw data (with each cell of data containing a trailing space character):

Excel Workbook

A

1

with apple, with banana, with apple, with banana

2

with apple, with apple, with apple

3

with banana, with banana, with banana

4

with banana

5

6

with apple, with banana, with apple, with banana

7

with apple, with apple, with apple

8

with banana, with banana, with banana

9

with apple, with banana, with apple, with banana

10

with apple, with apple, with apple

11

with banana, with banana, with banana

12

with banana

13

14

with apple, with banana, with apple, with banana

15

with apple, with apple, with apple

16

with banana, with banana, with banana

17

with apple, with banana, with apple, with banana

18

with apple, with apple, with apple

19

with banana, with banana, with banana

20

with banana

21

22

with apple, with banana, with apple, with banana

23

with apple, with apple, with apple

24

with banana, with banana, with banana

25

with apple, with banana, with apple, with banana

26

with apple, with apple, with apple

27

with banana, with banana, with banana

28

with banana

29

Sheet1

Results from:

RemoveDupesV3, in 0.9375 seconds.
qwerty(), with "d.comparemode = vbTextCompare", in 0.3125 seconds.

Excel Workbook

A

1

with apple, with banana

2

with apple

3

with banana

4

with banana

5

6

with apple, with banana

7

with apple

8

with banana

9

with apple, with banana

10

with apple

11

with banana

12

with banana

13

14

with apple, with banana

15

with apple

16

with banana

17

with apple, with banana

18

with apple

19

with banana

20

with banana

21

22

with apple, with banana

23

with apple

24

with banana

25

with apple, with banana

26

with apple

27

with banana

28

with banana

29

Sheet1

And, results from asdfgh(), in 0.015625 seconds:

Excel Workbook

A

B

1

with apple, with banana, with apple, with banana

2

with apple, with apple, with apple

with apple

3

with banana, with banana, with banana

with banana

4

with banana

5

6

with apple, with banana, with apple, with banana

7

with apple, with apple, with apple

with apple

8

with banana, with banana, with banana

with banana

9

with apple, with banana, with apple, with banana

10

with apple, with apple, with apple

with apple

11

with banana, with banana, with banana

with banana

12

with banana

13

14

with apple, with banana, with apple, with banana

15

with apple, with apple, with apple

with apple

16

with banana, with banana, with banana

with banana

17

with apple, with banana, with apple, with banana

18

with apple, with apple, with apple

with apple

19

with banana, with banana, with banana

with banana

20

with banana

21

22

with apple, with banana, with apple, with banana

23

with apple, with apple, with apple

with apple

24

with banana, with banana, with banana

with banana

25

with apple, with banana, with apple, with banana

26

with apple, with apple, with apple

with apple

27

with banana, with banana, with banana

with banana

28

with banana

29

Sheet1

mirabeau · Feb 27, 2012

mirabeau · Feb 27, 2012

Rich (BB code):

 ...
...
a = Trim(Cells(i, 1)) & ","
...

and could you recheck your times for the scripting dictionary approaches?
they often seem slow on the first run

lancerj017 · Feb 27, 2012

hi guys here my next question i hope you can help me again... advance thank you :D http://www.mrexcel.com/forum/showthread.php?p=3059192#post3059192

lancerj017 · Feb 27, 2012

wow all of you are good in vb... can you give me a sample of tutorials? so i can learn your techniques? hays i wish i could learn that vb someday... i can read the code but not totally i can create macro i just know how to understand it...

chirp · Feb 27, 2012

@miribeau + whomever

your second code identifies symmetric strings and splits them in half, but it obviously does not remove non symmetric strings that contain duplicates.

i have not gone over each previous post, but is it assumed then that the strings will be symmetric?

also strings like:

with banana, with banana, with apple, with apple

would also be "wrong"...so i guess your code assumes the strings are all alternating and symmetric is that accurate?

mirabeau · Feb 28, 2012

chirp said:
@miribeau + whomever

your second code identifies symmetric strings and splits them in half, but it obviously does not remove non symmetric strings that contain duplicates.

i have not gone over each previous post, but is it assumed then that the strings will be symmetric?

also strings like:

would also be "wrong"...so i guess your code assumes the strings are all alternating and symmetric is that accurate?

Chirp,

(1) The code splits strings in half if relevant, and splits them in less than half when relevant.

(2) The code answers the OP question as asked. Do you consider that it should have done something else?
If you feel the need for analysis of different strings perhaps you can start a new thread on the topic.

chirp · Feb 28, 2012

i agree that the code will correctly remove duplicates in the supplied test data, and was just confirming that the test data is the only format in which the data would be supplied.

in the rather obvious cases like:

Excel Workbook

A

B

1

with apple, with apple, with banana

with apple

2

with banana, with apple, with apple

Sheet2

lancerj017 · Feb 28, 2012

what's the difference of code of mirabeau And hiker95
Hiker95 code return the exact value and the blank cells treats as BLANK
mirabeau return the exact value too and fastest than hiker95 code... but the problem is the blank cell treats not as BLANK "try to put single quote in a blank cell" thats what happen after i run the code of Mr. mirabeau i don't know what do you call on that but you can try the RemoveDupesV3 Or qwerty() code if you like just to understand what im talking about..

lancerj017 · Feb 28, 2012

super duper thanks for all of your helps guys im satisfy with your help no need to argu on that :D

Removes the Duplicate in a single Cell

lancerj017

Active Member

hiker95

Well-known Member

Excel Facts

mirabeau

Banned user

mirabeau

Banned user

lancerj017

Active Member

lancerj017

Active Member

chirp

Active Member

mirabeau

Banned user

chirp

Active Member

lancerj017

Active Member

lancerj017

Active Member

Similar threads

Forum statistics

Share this page

Removes the Duplicate in a single Cell

Active Member

Well-known Member

Excel Facts

Banned user

Banned user

Active Member

Active Member

Active Member

Banned user

Active Member

Active Member

Active Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock