Sleeper: Copy data to new sheet to remove duplicates

[bobulos]

Hi I am hoping someone can help with some vba. See the data below as on a s/sheet.

I want to be able to copy the data to another sheet to remove duplicate data so e.g. for the first entry PFEIL,ULRIKE TRUE, TRUE, TRUE.

Any ideas would be most appreciated.

UserLandlineFaxMobilePFEIL,ULRIKETRUEPFEIL,ULRIKETRUEPFEIL,ULRIKETRUEROSELL ,CARLESTRUEROSELL,CARLESTRUEROSELL,CARLESTRUEDELESALLE,PATRICIATRUEDELESALL E,PATRICIATRUEDELESALLE,PATRICIATRUEMUENCH,GABRIELATRUEMUENCH,GABRIELATRUEK OEHLER,THOMASTRUEKOEHLER,THOMASTRUEKOEHLER,THOMASTRUEMURRAY,KARENTRUEMURRAY ,KARENTRUEMURRAY,KARENTRUE

[Simon Lloyd]

What is it you want to keep? do you want to keep all the true's? I recorded this:


Sub Macro1()
Selection.TextToColumns Destination:=Range("A3"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False, TrailingMinusNumbers:=True
Rows("3:3").Select
Selection.Copy
Range("A5").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Application.CutCopyMode = False
Range("A5").Select
End Sub
which gave a split at the comma's but your data is inconsistent you quote your first entry as: PFEIL,ULRIKE TRUE, TRUE, TRUE.
but it is actually:PFEIL and the second is:ULRIKETRUEPFEIL.

Can you explain further?

[RonMcK3]

Simon,

Go back to his original message, quote it into a new reply, and you'll notice that what looks like:

PFEIL,ULRIKETRUEPFEIL,ULRIKETRUEPFEIL,ULRIKE TRUE

is really (after exchanging brackets [] for braces {}):

{FONT=MS Sans Serif}PFEIL,ULRIKE{/FONT}{FONT=MS Sans Serif}TRUE{/FONT}{FONT=MS Sans Serif}{/FONT}{FONT=MS Sans Serif}{/FONT}
{FONT=MS Sans Serif}PFEIL,ULRIKE{/FONT}{FONT=MS Sans Serif}{/FONT}{FONT=MS Sans Serif}TRUE{/FONT}{FONT=MS Sans Serif}{/FONT}
{FONT=MS Sans Serif}PFEIL,ULRIKE{/FONT}{FONT=MS Sans Serif}{/FONT}{FONT=MS Sans Serif}{/FONT}{FONT=MS Sans Serif}TRUE{/FONT}

and, using the 'LastName,FirstName' field to break records, it might be:

User, Landline, Fax, Mobile
PFEIL,ULRIKE, TRUE, <blank>, <blank>
PFEIL,ULRIKE, <bland>, TRUE, <blank>
PFEIL,ULRIKE, <blank>, <blank>, TRUE

So, his desired result of

PFEIL,ULRIKE, TRUE, TRUE, TRUE

is not an unreasonable expectation.

As I look at it, I see no field delimiters or record delimiters, so, I am a bit perplexed about structure the import and translatioin. I see that you ran into that problem, too. :banghead:

Does this help you any?

Cheers!

[Simon Lloyd]

Lol cheers Ron!

Bobulos please upload a sample workbook so we can see your structure and what you are trying to achieve!