+ Reply to Thread
Results 1 to 6 of 6

Concatenate cell values for duplicate numbers

  1. #1
    Registered User
    Join Date
    10-13-2015
    Location
    Fort Worth, Texas
    MS-Off Ver
    MS Office 365 on Mac
    Posts
    5

    Concatenate cell values for duplicate numbers

    I have an speadsheet that has two columns. Column "A" is a list of part numbers. Column "B" Reference values.
    The Part Numbers can have muliple values listed in column B. I need to concatenate those values into a single cell with comma delimentation.
    THIS IS EXAMPLE OF THE SPREADSHEET:
    P/N H-D#
    105200 96600-36B
    105200 98705-85V
    106400 96815-46
    106400 98715-85V
    109736 6762-B
    109736 70011-81
    109737 67697-81
    109737 70097-75
    109738 6762B
    109738 70069-65
    109739 67621-94
    109739 70011-81
    120010 38619-71B
    120010 40037-79
    120010 40037-79A
    120011 38599-83A
    120011 40007-36A
    120011 40007-36B

    OUTPUT NEEDS TO BE:
    P/N H-D#
    105200 96600-36B, 98705-85V
    106400 96815-46, 98715-85V
    109736 6762-B, 70011-81
    109737 67697-81, 70097-75
    109738 6762B, 70069-65
    109739 67621-94, 70011-81
    120010 38619-71B, 40037-79, 40037-79A
    120011 38599-83A, 40007-36A, 40007-36B

    Does anyone know of a way to do this?
    Any help would be greatly appreciated.
    Thanks
    Joanie

  2. #2
    Valued Forum Contributor Neil_'s Avatar
    Join Date
    04-19-2013
    Location
    Yorkshire
    MS-Off Ver
    Office 365 Enterprise E3 2013 / 2016
    Posts
    479

    Re: Concatenate cell values for duplicate numbers

    This should do it
    Please Login or Register  to view this content.
    Frob first, tweak later

  3. #3
    Registered User
    Join Date
    10-13-2015
    Location
    Fort Worth, Texas
    MS-Off Ver
    MS Office 365 on Mac
    Posts
    5

    Re: Concatenate cell values for duplicate numbers

    I get an error

    Sub test1()
    Dim sTemp As String, r As Long, lastrow As Long
    lastrow = Range("A" & Rows.Count).End(xlUp).Row
    With CreateObject("Scripting.Dictionary")
    For r = 2 To lastrow
    sTemp = Range("A" & r)
    If Not .Exists(sTemp) Then
    .Item(sTemp) = Range("B" & r)
    Else
    .Item(sTemp) = .Item(sTemp) & ", " & Range("B" & r)
    End If
    Next r
    For r = 2 To lastrow
    sTemp = Range("A" & r)
    Range("B" & r) = .Item(sTemp)
    Next r
    End With
    Range("A1:B" & lastrow).RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes
    End Sub

    Do you know what I am doing wrong?
    I am really a novice at Excel.
    Sorry.
    Joanie

  4. #4
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Concatenate cell values for duplicate numbers

    Here is a formula solution using several helper columns:
    Enter in C2 and fill down and across to column E
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Enter in F2 and fill down
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Enter in G2 and fill down to enter a , between the values concatenated.
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Enter in I2 and fill across and down to get the unique P/N values with the associated H-D#s
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  5. #5
    Valued Forum Contributor Neil_'s Avatar
    Join Date
    04-19-2013
    Location
    Yorkshire
    MS-Off Ver
    Office 365 Enterprise E3 2013 / 2016
    Posts
    479

    Re: Concatenate cell values for duplicate numbers

    Sorry, didn't spot that you were on Mac

    This should work on Mac (untested)
    Please Login or Register  to view this content.
    Last edited by Neil_; 01-04-2016 at 05:13 PM.

  6. #6
    Registered User
    Join Date
    10-13-2015
    Location
    Fort Worth, Texas
    MS-Off Ver
    MS Office 365 on Mac
    Posts
    5

    Re: Concatenate cell values for duplicate numbers

    Neil,
    That worked PERFECTLY!!!!! Thank you so much.
    This just saved me.
    Joanie

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Need to find if duplicate values exist in a column, concatenate cells and then delete
    By Siemieniuk in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-22-2017, 07:22 PM
  2. [SOLVED] Finding Row numbers of all duplicate values
    By Dj Duck in forum Excel General
    Replies: 6
    Last Post: 10-03-2015, 07:18 AM
  3. [SOLVED] concatenate text values for duplicate entries
    By SAsplin in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 05-21-2015, 10:44 AM
  4. Replies: 20
    Last Post: 11-27-2014, 02:37 AM
  5. [SOLVED] Find if duplicate values exist in a column, concatenate cells and then de
    By filky in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-15-2014, 10:14 PM
  6. Replies: 7
    Last Post: 12-07-2013, 02:33 PM
  7. Getting rid of duplicate values in a cell after using concatenate
    By outofgum in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 03-28-2011, 11:53 AM

Tags for this Thread

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