Removes the Duplicate in a single Cell

[lancerj017]

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]

lancerj017,

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...

Training / Books / Sites

MrExcel's Products: Books, CDs, Podcasts Discuss topics related to Holy Macro! Products: Learn Excel from MrExcel, VBA and Macros for Microsoft Excel,Holy Macro! It's 2500 VBA Examples CD, Guerilla Data Analysis Using Microsoft Excel and Excel Knowledge Base CD and the MrExcel Podcasts.
http://www.mrexcel.com/forum/forumdisplay.php?f=19

How to Learn to Write Macros
http://articles.excelyogi.com/playin...ba/2008/10/27/

How to use the macro recorder
http://articles.excelyogi.com/

Click here and scroll down to Getting Started with VBA.
http://www.datapigtechnologies.com/ExcelMain.htm

If you are serious about learning VBA try
http://www.add-ins.com/vbhelp.htm

Excel Tutorials and Tips - VBA - macros - training
http://www.mrexcel.com/articles.shtml

See David McRitchie's site if you just started with VBA
http://www.mvps.org/dmcritchie/excel/getstarted.htm

What is a Visual Basic Module?
http://www.emagenit.com/VBA Folder/what_is_a_vba_module.htm

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

Creating custom functions
http://office.microsoft.com/en-us/excel/HA011117011033.aspx

Writing Your First VBA Function in Excel
http://www.exceltip.com/st/Writing_Your_First_VBA_Function_in_Excel/631.html

Where to paste code in VBE VBA
Introducing the Excel VBA Editor
http://www.ask.com/web?qsrc=2417&o=101881&l=dis&q=Where+to+paste+code+in+the+Excel+VBA+Editor

VBA for Excel (Macros)
http://www.excel-vba.com/excel-vba-contents.htm

VBA Lesson 11: VBA Code General Tips and General Vocabulary
http://www.excel-vba.com/vba-code-2-1-tips.htm

Excel VBA -- Adding Code to a Workbook
http://www.contextures.com/xlvba01.html

http://www.excel-vba.com/
http://www.mvps.org/dmcritchie/excel/getstarted.htm
http://www.exceltip.com/excel_links.html

(livelessons video)
Excel VBA and Macros with MrExcel
ISBN: 0-7897-3938-0
http://www.amazon.com/Excel-Macros-M...7936479&sr=1-1

Learn to debug:
http://www.cpearson.com/excel/debug.htm

Excel Tutorials / Video Tutorials - Functions
http://www.contextures.com/xlFunctions02.html

http://www.xl-central.com/index.html

http://www.datapigtechnologies.com/ExcelMain.htm

Cascading queries

http://www.tushar-mehta.com/excel/ne...ing_dropdowns/

Excel VLOOKUP Function and VLOOKUP Example
http://www.contextures.com/xlFunctions02.html

INDEX MATCH - Excel Index Function and Excel Match Function
http://www.contextures.com/xlFunctions03.html

http://www.contextures.com/xlDataVal02.html
http://www.contextures.com/xlDataVal05.html
http://www.contextures.com/xlDataVal08.html#Larger

Excel Data Validation - Add New Items
http://www.contextures.com/excel-data-validation-add.html

Programming The VBA Editor - Created by Chip Pearson at Pearson Software Consulting LLC
This page describes how to write code that modifies or reads other VBA code.
http://www.cpearson.com/Excel/vbe.aspx

Locating files containing VBA
Searching Files in Subfolders for VBA code string:
http://www.dailydoseofexcel.com/arch...a-code-string/

http://www.pcreview.co.uk/forums/thread-978054.php

Excel 2003 Power Programming with VBA (Excel Power Programming With Vba)
by John Walkenbach

VBA and Macros for Microsoft Excel, by Bill Jelen "Mr.Excel" and Tracy Syrstad

Excel Hacks 100 Industrial-Strength Tips & Tools, by David & Traina Hawley

VBA and Macros for Microsoft Excel 2007, by Bill Jelen "Mr.Excel" and Tracy Syrstad

Excel 2007 Book: you can try this...there is a try before you buy ebook available at this link…
http://www.mrexcel.com/learnexcel2.shtml

Professional Excel Development
by Stephen/ Bovey, Rob/ Green, John Bullen (Paperback - Feb 11, 2005)

Excel 2002 VBA: Programmers Reference
by Rob Bovey, Stephen Bullen, John Green, and Robert Rosenberg (Paperback - Sep 26, 2001)

VB & VBA in a Nutshell: The Language
(http://www.amazon.co.uk/VB-VBA-Nutsh...4671189&sr=1-2)

Writing Excel Macros with VBA
(http://www.amazon.co.uk/Writing-Exce...4671189&sr=1-3)

User Form Creation
http://www.contextures.com/xlUserForm01.html

DonkeyOte: My Recommended Reading
Volatility
http://www.decisionmodels.com/calcsecretsi.htm

Sumproduct
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Arrays
http://www.xtremevbtalk.com/showthread.php?t=296012

Pivot Intro
http://peltiertech.com/Excel/Pivots/pivotstart.htm

Sync Pivot Tables
http://www.mrexcel.com/forum/showthr...g+pivot+tables
Check out rorya's post at the very bottom of this link. It should do the trick:
http://www.experts-exchange.com/Soft..._22844558.html
Multiple pivot tables 1 filter to control all
http://www.excelforum.com/excel-prog...ntrol-all.html

Email from XL - VBA
http://www.rondebruin.nl/sendmail.htm

Outlook VBA
http://www.outlookcode.com/article.aspx?ID=40

Function Dictionary
http://www.xlfdic.com/

Function Translations
http://www.piuha.fi/excel-function-name-translation/

Dynamic Named Ranges
http://www.contextures.com/xlNames01.html

How to create Excel Dashboards
http://www.contextures.com/excel-dashboards.html
http://chandoo.org/wp/excel-dashboards/
http://chandoo.org/wp/management-dashboards-excel/
http://www.exceldashboardwidgets.com/

Excel Dashboard / Scorecard Ebook
http://www.qimacros.com/excel-dashboard-scorecard.html

Templates
http://www.cpearson.com/Excel/Topic.aspx
http://www.contextures.com/excel-template-golf-scores.html

Mike Alexander from Data Pig Technologies
Excel 2007 Dashboards & Reports For Dummies

Date & Time stamping:
http://www.mcgimpsey.com/excel/timestamp.html

Get Formula / Formats thru custom functions:
http://dmcritchie.mvps.org/excel/formula.htm#GetFormat

A nice informative MS article "Improving Performance in Excel 2007"
http://msdn.microsoft.com/en-us/library/aa730921.aspx

Progress Meters
http://www.andypope.info/vba/pmeter.htm
http://www.xcelfiles.com/ProgressBar.html

And, as your skills increase, try answering posts on sites like:
http://www.mrexcel.com
http://www.excelforum.com
http://www.ozgrid.com
http://www.vbaexpress.com
http://www.excelfox.com

 

[hiker95]

mirabeau,

WOW again.....

Your timer code yielded a number in scientific notation. I then changed the timer routine to one I have used in the past, and it returned a ZERO.

Excel Workbook
	A	B
1	with apple, with banana, 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	with banana
5
6	with apple, with banana, 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	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	with banana
13
14	with apple, with banana, 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	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	with banana
21
22	with apple, with banana, 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	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	with banana
29
Sheet1

Sub asdfgh()
' mirabeau, 02/28/2012
' http://www.mrexcel.com/forum/showthread.php?t=615831
Dim t As Single: t = Timer
Dim c(), a, x, y
Dim rws As Long, i As Long, j As Long
rws = Range("A" & Rows.Count).End(xlUp).Row
ReDim c(1 To rws, 1 To 1)
For i = 1 To rws
  [B]a = Trim(Cells(i, 1)) & ","[/B]
  For j = 1 To Len(a)
    x = Trim(Left(a, j))
    y = Trim(Mid(a, j + 1, j))
    If x = y Then Exit For
  Next j
  c(i, 1) = Left(x, Len(x) - 1)
Next i
Range("B1").Resize(rws) = c
MsgBox Timer - t
End Sub

 

[chirp]

i still don't understand that solution.

wouldn't that solution be directly equivalent to checking the first two "values" for equivalence, then checking the first half and the second half for equivalence?

wouldn't that just be something like this:

Sub chirp()
Dim a As String, v, rng As Range
Dim rws As Long, i As Long, j As Long
rws = Range("A" & Rows.Count).End(xlUp).Row
Set rng = Range("a1:a" & rws)
v = rng.Value2

For i = 1 To rws
    a = Trim$(CStr(v(i, 1)))
    j = InStr(1, a, ",")
    If j Then
        If MID$(a, 1, j - 1) = MID$(a, j + 2, j - 1) Then
            v(i, 1) = MID$(a, 1, j - 1)
        ElseIf MID$(a, 1, Len(a) / 2 - 1) = MID$(a, Len(a) / 2 + 2) Then
            v(i, 1) = MID$(a, 1, Len(a) / 2 - 1)
        End If
    End If
Next

rng.Offset(0, 1) = v
End Sub

this code is just written to explicitly show what this code:

Sub asdfgh()
' mirabeau, 02/28/2012
' http://www.mrexcel.com/forum/showthread.php?t=615831
Dim t As Single: t = Timer
Dim c(), a, x, y
Dim rws As Long, i As Long, j As Long
rws = Range("A" & Rows.Count).End(xlUp).Row
ReDim c(1 To rws, 1 To 1)
For i = 1 To rws
  a = Trim(Cells(i, 1)) & ","
  For j = 1 To Len(a)
    x = Trim(Left(a, j))
    y = Trim(Mid(a, j + 1, j))
    If x = y Then Exit For
  Next j
  c(i, 1) = Left(x, Len(x) - 1)
Next i
Range("B1").Resize(rws) = c
MsgBox Timer - t
End Sub

is checking...dont know what my point is but i am confused

 

[mirabeau]

Chirp,

I'm not exactly sure what you're looking for.

If you're wanting to delete duplicated, space separated strings in some cell, perhaps you mean something like this:

Sub deletedupspacesepstrings()
Dim a, b, s1, s2, i, j
a = Range("A1")
s1 = Space(1): s2 = Space(2)
Do
a = Replace(a, s2, s1)
Loop Until InStr(a, s2) = 0
b = Split(a, s1)
For i = 0 To UBound(b) - 1
    For j = i + 1 To UBound(b)
        If b(i) = b(j) Then b(j) = vbNullString
Next j, i
a = Replace(Join(b, s1), s2, s1)
Range("A2") = a
End Sub

If you want to delete any duplicated strings then maybe you want any entry such as "aardvark" to become "ardvk", etc. That's easy enough to do.

or if you want ... (etc)

I can only reiterate that I tried to provide codes that answered OP's question as asked but not for any and all possibilities as to what conceivably might have been asked.

If you want to expand further on whatever your point is, then maybe a new thread is appropriate.

 

[lancerj017]

this is the fastest code you posted in my tread mirabeau
but this code is for column A Only i need to set multiple columns so that i don't need to run again and again

Sub asdfgh()
' mirabeau, 02/28/2012
' http://www.mrexcel.com/forum/showthread.php?t=615831
Dim t As Single: t = Timer
Dim c(), a, x, y
Dim rws As Long, i As Long, j As Long
rws = Range("A" & Rows.Count).End(xlUp).Row
ReDim c(1 To rws, 1 To 1)
For i = 1 To rws
  a = Trim(Cells(i, 1)) & ","
  For j = 1 To Len(a)
    x = Trim(Left(a, j))
    y = Trim(Mid(a, j + 1, j))
    If x = y Then Exit For
  Next j
  c(i, 1) = Left(x, Len(x) - 1)
Next i
Range("B1").Resize(rws) = c
MsgBox Timer - t
End Sub

BTW Hiker95 thank you for the link you post it here, i will use your link to study macro have a good day....
http://www.mrexcel.com/forum/member.php?u=163704

 

[mirabeau]

Lancer,

You can try this code if you have more than one column and many rows

Sub testcode()
Dim t As Single: t = Timer
Dim c(), a, x, y, Q
Dim rws As Long, cls As Long
Dim u As Long, v As Long, j As Long
rws = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
cls = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
Q = Range("A1").Resize(rws, cls)
ReDim c(1 To rws, 1 To cls)
For u = 1 To rws
    For v = 1 To cls
        a = Trim(Q(u, v)) & ","
        For j = 1 To Len(a)
            x = Trim(Left(a, j))
            y = Trim(Mid(a, j + 1, j))
            If x = y Then Exit For
        Next j
        c(u, v) = Left(x, Len(x) - 1)
    Next v
Next u
Range("A1").Resize(rws, cls) = c
MsgBox "Code took " & Format(Timer - t, "0.000 secs")
End Sub

In Post #31 hiker95 gave an excellent list of VBA references. I certainly can't add anything to it.

 

[hiker95]

mirabeau,

What can I say.....

Fast, Faster, the Fastest, even Faster then the Fastest.....


Thanks again for all you help.

 

[chirp]

@mirabeau

all i was saying was that if those conditions were consistent why don't you just explicitly check them (as in the code i posted) and avoid the inner loop?

 

[lancerj017]

Guys How can i fix the Out of Memory Error? i got this when my data exceed to 300k rows with 65 columns

 

[mirabeau]

Guys How can i fix the Out of Memory Error? i got this when my data exceed to 300k rows with 65 columns

That's a lot of data.

I suppose you mean that the error shows when you try to run the code.

I'd approach that by splitting the problem into a number of sections, and running the code separately on each.

Say run 1 is 100k rows and 65 cols
Run 2 similarly
run 3 similarly

If it still errors then split it up further.

You could also put more RAM into your computer. RAM is cheap these days. But any improvement from that source would depend on how efficiently your version of Excel uses the existing and new RAM.