Split a Cell : Need Help

[deven_deesha]

I have a row as follow .

COL B has a data seperated by Alt + Enter. There is a duplciate data.

If there is a duplicate data then i want to neglect it and consider only one.

ex. Consider the following example

SUIT/ADR is may times in COL B. This is a input File.

COL A COL B
1 Suit/ADR

Suit/ADR

Suit/ADR

Suit/ADR

Claim Folder

2 Claim Folder

Claim Mgmt


I want the output in new file as

1 SUIT/ADR

1 Claim Folder

2 Claim Folder

2 Claim Mgmt


I tried to write a macro using Split function. But I stucked. Can you please help me?


Thanks

Shriswaroop

 

[pooj_malh]

Your question is a little confusing.

Can you put data in excel, copy it from there and paste here for both input as well as output

Regards
Pooja

 

[deven_deesha]

How to copy the excel sheet here? I am not able to copy it.

Please confirm

Shriswaroop

 

[pooj_malh]

No you just select your columns from Excel copy them all and then paste it on this space

i hope its clear



Regards

 

[deven_deesha]

Consider there are two columns in excel sheet.

In Second column there is a data seperated by ALT + Enter. here second Column is COL B. In COL B Duplciate Data is available so I want to remove it. you can copy this data in excel sheet. But While Copying in COLB you just press ALT + ENTER after each String "SUIT/ADR" so it will be your input file. Like this I have a huge Data.


ROW ID COL A COL B
1 1 SUIT/ADR
SUIT/ADR
SUIT/ADR
SUIT/ADR

I hope it is clear. If not please let me know I willtry to explain in more detail.

Thanks for your help

Shriswaroop

[/url]

 

[Krishnakumar]

Hi,

Try Data > Filter > Advanced Filter.There is an option for unique records.

HTH

 

[deven_deesha]

No. I have the duplicate data in the same cell.

Ex

Cell A2 has "SUIT/ADR" word 10 times. and after each word there is a alt + enter. I want it only once.

Cell A2

SUIT/ADR

SUIT/ADR

SUIT/ADR

SUIT/ADR


I hope it is clear

 

[Krishnakumar]

Go to Data > Text to columns..

check on delimited> next > check others and type Alt+010(hold down Alt key and type 010) > Next > Finish.

HTH

 

[jindon]

Hi
try

Sub test()
Dim a, x, i As Long, b(), n As Long, e, myNum, c, myTxt, ii As Integer
Set dic = CreateObject("Scripting.Dictionary")
dic.CompareMode = vbTextCompare
a = Range("a1",Range("a" & Rows.Count).End(xlUp)).Value
ReDim b(1 To Rows.Count, 1 To 1)
With CreateObject("VBScript.RegExp")
   .Pattern = "^\d+"
   For Each e In a
      If InStr(e,Chr(10))>0 Then
         If .test(e) then
            myNum = .execute(e)(0)
         Else
            myNum = Empty
         End If
         myTxt = .replace(e,"")
         n = n + 1 : b(n,1) = myNum & myTxt
         x = Split(e,Chr(10))
         For ii = 1 To UBound(x)
            If Not dic.exists(x(ii)) Then
               n = n + 1
               b(n,1) = myNum & x(ii)
               dic.add x(ii), Nothing
            End If
         Next
      Else
         n = n + 1 : b(n,1) = e
      End If
      dic.removeall : myTxt = Empty : myNum = Empty
   Next
End With
Set dic = Nothing : Erase a
Range("c1").Resize(n).Value = b
End Sub

 

[deven_deesha]

you are bit near on the track But not exactly. I will explain more in detail.

In your code you have used

.Pattern = "^\d+"

Can you explain what it is?

For Each e In a

This sentence populates the following value in e

"Suit/ADR Suit/ADR Suit/ADR Suit/ADR" Spaces are there in between each word

and

x = Split(e, Chr(10))

above statement has value

x(0) = "Suit/ADR"
x(1) = " "
x(2) = "Suit/ADR " one space is there after each word
x(3) = " "
x(4) = "Suit/ADR "
x(5) = " "
x(6) = "Suit/ADR "

etc

I tried to use Chr(13) instead of Chr(10) but doesn't work

Can you please provide me some more help.

Thanks,

Shriswaroop