+ Reply to Thread
Results 1 to 11 of 11

concatentate and remove duplicates

  1. #1
    Registered User
    Join Date
    01-23-2011
    Location
    west coast, south africa
    MS-Off Ver
    MSO 365
    Posts
    25

    concatentate and remove duplicates

    hi
    i am cleaning out 18+ years of accumulated contacts [names, addresses, emails and tel nos].
    somehow in the A column a lot of text, some of which duplication in one cell, has accumulated.
    i have managed to copy col A to another sheet and with the 'text to column' function separate the text entries into columns ranging A to Q, not all cells contain data.
    i need the cells to become 'one' again in col A without the duplicates.
    i can put spaces between the text strings in order to keep them separate when brought together again.
    the sample png is from the already separated entries in col A without any spaces.
    i have over 9000 rows which must be treated.
    thanking you for assistance in anticipation,
    God bless
    gabriel


    2013-06-01 22_57_48-Excel - New Users_Basics - Post New Thread.png

    ps
    i'm pretty excel illiterate, kindly keep that in mind when assisting.
    Last edited by ggsmit; 06-05-2013 at 06:02 AM.

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: concatentate and remove duplicates

    Okay, when you say remove duplicates, how do you want to define a duplicate? Are we looking within a certain row? I.e. in row 1171 boshoff and nicolene and gkv all appear twice so you'd delete the second set.

    Or
    are we looking in columns, so nicolene appears in column B in 1171 and 1172 so the second one should be removed

    Or
    are we looking at everything all together so once nicolene appeared in B1171, it should be omitted anywhere else on the sheet?
    Which cells in your example would be deleted?
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Registered User
    Join Date
    01-23-2011
    Location
    west coast, south africa
    MS-Off Ver
    MSO 365
    Posts
    25

    Re: concatentate and remove duplicates

    hi chemist,

    the end result should be that cell 1171A must contain
    "boshoff nicolene gkv"
    in other words we 1st have to delete the duplicate cell values in row 1171 and 2nd we must 'merge' the remainder on that row into the 1st cell [1171A]
    i.e. the whole operation only functions on the horizontal 'row' level.
    the duplicates which will appear on in the vertical 'column' level in the A column will be addressed later when the telephone numbers come into play.

    tx & God bless
    gabriel

  4. #4
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: concatentate and remove duplicates

    I'm going to use a combination of formulas and VBA. See the attachment.
    First, the formula to remove duplicates. To the right of your current data, (in my example, I started in Column K)

    =IF(COUNTA($A1:$J1)>=COLUMNS($A$1:A$1),IF(COUNTIF($A1:A1,A1)>1,"",A1),"")
    copy this to the right as far as you need and down as far as you need

    Then I used TigerAvatar's UDF (user defined function) ConcatAll(range, delimiter)
    I'll explain how to put the code in, next.
    So to the right of your new cells, (Column V in my case)
    =ConcatAll(K1:U1, ", ") I used "comma space" as my separater/delimiter)
    copied down.

    The code below must be copied and pasted into a module.
    ALT + F11 to open the Visual Basic Editor
    Insert (from the menu) > Module
    Then paste it in and close the Visual basic editor
    Please Login or Register  to view this content.
    Lastly, to remove the formula, and make it static. Copy column V and Paste Special> Values
    Questions?
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    01-23-2011
    Location
    west coast, south africa
    MS-Off Ver
    MSO 365
    Posts
    25

    Re: concatentate and remove duplicates

    please ignore post, i did slip up on instructions; will be back later!

    ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

    i might be missing something.
    please see screenshot
    i presume =IF(COUNTA($A1:$J1)>=COLUMNS($A$1:A$1),IF(COUNTIF($A1:A1,A1)>1,"",A1),"") should copy all unique cells to the right of column K [col e f g h i are hidden], but somehow it did not, it only took the values in col A.
    there are no spaces or invisible characters or commas etc in any cells.
    i have not used the 2nd function or the vba as i reason it can't do much with empty cells - am i on the wrong track?
    tx
    2013-06-03 22_45_09-Microsoft Excel - google soos gesuiwer op 2 junie 13.xlsx.png
    Last edited by ggsmit; 06-03-2013 at 04:58 PM.

  6. #6
    Registered User
    Join Date
    01-23-2011
    Location
    west coast, south africa
    MS-Off Ver
    MSO 365
    Posts
    25

    Re: concatentate and remove duplicates

    ok chemist

    Attachment 240364Attachment 240365

    i changed the code slightly to include more columns as you can see on the images

    =IF(COUNTA($A1:$S1)>=COLUMNS($A$1:A$1),IF(COUNTIF($A1:A1,A1)>1,"",A1),"") works like a bomb!

    =ConcatAll(T1:AF1, ", ") gives me a NAME error.

    what am i doing wrong?

    God bless
    gabriel

  7. #7
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: concatentate and remove duplicates

    Your attachments are coming up as invalid attachments. My guess is that you did not paste the code properly into a module. Then Excel will not recognize ConcatAll. Can you upload a spreadsheet with the code in it (Go Advanced>Manage Attachments)?

  8. #8
    Registered User
    Join Date
    01-23-2011
    Location
    west coast, south africa
    MS-Off Ver
    MSO 365
    Posts
    25

    Re: concatentate and remove duplicates

    hi chemist
    although your 1st function deletes duplicates, it also deletes some email addresses and some numbers - or am i missing out here?
    please see image
    uploading the spreadsheet to the forum will expose a lot of email addresses, if i could send it privately that can be done.
    God bless
    gabriel

    2013-06-05 00_17_32-Microsoft Excel - alle contacts van gmail op 25 5 13 oorspronklik begin suiw.png

  9. #9
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: concatentate and remove duplicates

    Quote Originally Posted by ggsmit View Post
    hi chemist
    .....
    uploading the spreadsheet to the forum will expose a lot of email addresses, if i could send it privately that can be done.
    God bless
    gabriel

    Attachment 240673
    If you are not prepared to share your data in a de-sensitived workbook, pictures are usless unless you want a picture of the solution, try the new Commercial Services Forum

  10. #10
    Registered User
    Join Date
    01-23-2011
    Location
    west coast, south africa
    MS-Off Ver
    MSO 365
    Posts
    25

    Re: concatentate and remove duplicates

    hi chemist and marcol,
    i followed the instructions chemist gave me to the tee on my workbook which i uploaded. everything worked 100%.
    would it be possible that excel makes random errors if the procedure is done on say 1 million cells?

    God bless
    gabriel
    Attached Files Attached Files
    Last edited by ggsmit; 06-05-2013 at 05:54 AM.

  11. #11
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: concatentate and remove duplicates

    The spreadsheet you uploaded had no formulas and no VBA. (VBA has to be saved in an xls or an xlsm file, not xlsx). In any case, here is the same file with the formula and codes.
    The formulas are in W:AQ (21 columns to match A:U)
    I put the concatall formula in column V
    =TRIM(Concatall(W1:AQ1, ", "))
    hope this helps.

    Edit: I also formatted Col V to "Wrap text". And no, Excel shouldn't just randomly start making mistakes due to the number of cells in the workbook. It may take longer to calculate with some complex formulas but I don't think this is the case here.
    Attached Files Attached Files
    Last edited by ChemistB; 06-05-2013 at 08:48 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1