+ Reply to Thread
Results 1 to 6 of 6

Concatenate values in different columns

  1. #1
    Registered User
    Join Date
    09-19-2013
    Location
    VA
    MS-Off Ver
    Excel 2003
    Posts
    2

    Concatenate values in different columns

    Hi,

    I am completely new to this forum. Not sure if I am doing this correct.

    I have a column (Column 5) which contain cells with strings separated by commas (example: Cell 2: F1, F2, F3, Cell 3: F4, F5, F6 etc). I am trying to find mistakes or duplicate values in these cells.

    I took these cells (strings) and separated them in columns and using conditional formatting, It can highlight duplicate strings (if any) in red.

    Next, I was trying to concatenate all these columns into the original cell by keeping the format (highlighted color) so the user can see what's has been duplicated.

    When I concatenate, the format is lost. I am stuck looking for an answer on this.

    I have this so far but the color is not being transfer when performing the macro:

    Please Login or Register  to view this content.






    ColumnWithCells.png
    Last edited by fjavier415; 02-16-2021 at 11:56 AM.

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,620

    Re: Concatenate values in different columns

    Administrative Note:

    Welcome to the forum.

    We would very much like to help you with your query, however you need to include code tags around your code.

    Please take a moment to add the tags. Posting code between tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

    Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

    (Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)
    If posting code please use code tags, see here.

  3. #3
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Concatenate values in different columns

    Hi and welcome to the forum.

    Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

    Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.

    To upload a file click the Go Advanced button at the foot of your post, look underneath the post area for the Manage Attachments section and take it from there. The yellow banner at the top of the forum also explains this.

    Please also note when posting code we require you wrap it between CODE tags Administrative Note:

    Please see Forum Rule #2 about code tags. I'll change them for you on this occasion since you are new, but please note for the future. More information about these and other tags can be found here

  4. #4
    Forum Expert JLGWhiz's Avatar
    Join Date
    02-20-2011
    Location
    Florida, USA
    MS-Off Ver
    Windows 10, Excel 2013
    Posts
    2,070

    Re: Concatenate values in different columns

    both of these should be copied and pasted into code module1. The Function is courtesy "Code VBA" website.

    Please Login or Register  to view this content.
    This eliminates the need to use the text to columns and then rejoining the data by concatenating. Just run the code on the original data in column E.
    Last edited by JLGWhiz; 02-14-2021 at 05:13 PM.
    Any code provided by me should be tested on a copy or a mock up of your original data before applying it to the original. Some events in VBA cannot be reversed with the undo facility in Excel. If your original post is satisfied, please mark the thread as "Solved". To upload a file, see the banner at top of this page.
    Just when I think I am smart, I learn something new!

  5. #5
    Registered User
    Join Date
    09-19-2013
    Location
    VA
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: Concatenate values in different columns

    HI

    Thanks for the help! I went ahead and add the CODE symbol there (well, it was there already. I just learned how to do this).

    The code highlights the cell if there is a duplicate within the cell but I was looking to highlight the characters instead of the whole cell. That way the user knows exactly what are the duplicates within that cell. In addition, I was looking to find duplicates in the same column (E) from cell to cell. Sometimes, we see duplicates in different cells.

    I appreciate your guys help. You guys responded so quick!

  6. #6
    Forum Expert JLGWhiz's Avatar
    Join Date
    02-20-2011
    Location
    Florida, USA
    MS-Off Ver
    Windows 10, Excel 2013
    Posts
    2,070

    Re: Concatenate values in different columns

    We will still use the Function from Code VBA but the primary macro is modified to color only the font

    Please Login or Register  to view this content.

+ 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. Concatenate values in range if value in multiple columns is true
    By LGN in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-14-2020, 08:18 AM
  2. Concatenate values in columns based on headers
    By datacruncher99 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-12-2019, 03:25 PM
  3. Replies: 3
    Last Post: 07-28-2016, 01:47 AM
  4. [SOLVED] Concatenate data from cells by values from two columns
    By YasserKhalil in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 04-14-2016, 04:02 PM
  5. Copy Values from two columns and Paste them in one (Concatenate).
    By pipoliveira in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 07-08-2014, 11:12 AM
  6. Concatenate values from columns and deleting duplicates
    By RandomP in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-15-2011, 06:20 PM
  7. concatenate values two columns
    By matrixknow in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-21-2007, 08:52 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