+ Reply to Thread
Results 1 to 13 of 13

How to do CONCAT formula properly with merged cells?

  1. 07-17-2023, 08:11 AM #1
    sjaviergmanso
    sjaviergmanso is offline
    Registered User
    Join Date
    07-17-2023
    Location
    Oxford
    MS-Off Ver
    Office 365
    Posts
    7

    How to do CONCAT formula properly with merged cells?

    Hello guys

    I wonder if someone call help me with this question I have about the formula =CONCAT

    I have a table made out with 5 columns and 27 rows which I have merged as some of the figures belong to several cells

    I would like to get a code per row but when I type and drag and drop the formula to the cells below the code jumps and get figures from another cells

    Please find attached a screenshot and the excel file

    concat example JG.xlsx

    Could someone give throw some light on this issue?

    Screenshot 2023-07-17 125415.png

    Regards,
    J
    Reply With QuoteRegister To Reply
  2. 07-17-2023, 08:13 AM #2
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,855

    Re: How to do CONCAT formula properly with merged cells?

    Welcome to the forum.

    The best answer is this: NEVER use merged cells. They are Satan's spawn.
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  3. 07-17-2023, 08:45 AM #3
    63falcondude
    63falcondude is offline
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: How to do CONCAT formula properly with merged cells?

    Even though it looks like "B01", for example, is in C2:C7, it is actually only in C2. C3:C7 are blank.

    This is why you don't want to use merged cells on data that you may want to analyze.

    It is best to repeat "B01" in all 6 cells, even though this may not be as visually appealing.

    If the merged data is the result of a data export, I would recommend using Power Query to fill in all the blank cells, so that you can do whatever analysis you wish.
    Reply With QuoteRegister To Reply
  4. 07-17-2023, 09:04 AM #4
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,053

    Re: How to do CONCAT formula properly with merged cells?

    There's probably a nicer way of doing with with a LAMBDA... but so far, it is eluding me!!

    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Glenn



    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
    Reply With QuoteRegister To Reply
  5. 07-17-2023, 09:44 AM #5
    HansDouwe
    HansDouwe is offline
    Forum Guru HansDouwe's Avatar
    Join Date
    06-21-2022
    Location
    Nederland
    MS-Off Ver
    365 V2403 (Build 17330.20000)
    Posts
    6,428

    Re: How to do CONCAT formula properly with merged cells?

    With LAMBDA, please try:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  6. 07-17-2023, 09:49 AM #6
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,053

    Re: How to do CONCAT formula properly with merged cells?

    100,000 curses.... TRANSPOSE was the bit I was missing....
    Reply With QuoteRegister To Reply
  7. 07-17-2023, 10:06 AM #7
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,053

    Re: How to do CONCAT formula properly with merged cells?

    HAD I got there... I would have ended it slightly differently:

    =TOCOL(BYCOL(SCAN("",TRANSPOSE(A2:E27),LAMBDA(x,y,IF(y="",x,y))),LAMBDA(x,TEXTJOIN("-",,x))))

    Nice one Hans!
    Reply With QuoteRegister To Reply
  8. 07-17-2023, 10:15 AM #8
    sjaviergmanso
    sjaviergmanso is offline
    Registered User
    Join Date
    07-17-2023
    Location
    Oxford
    MS-Off Ver
    Office 365
    Posts
    7

    Re: How to do CONCAT formula properly with merged cells?

    Quote Originally Posted by 63falcondude View Post
    Even though it looks like "B01", for example, is in C2:C7, it is actually only in C2. C3:C7 are blank.

    This is why you don't want to use merged cells on data that you may want to analyze.

    It is best to repeat "B01" in all 6 cells, even though this may not be as visually appealing.

    If the merged data is the result of a data export, I would recommend using Power Query to fill in all the blank cells, so that you can do whatever analysis you wish.

    Hi 63falcondude, thanks for the clarification, I had read before about how merged cells work and I do not think the excel principle behind it is totally right, but anyways..

    This is not a result from a data export, I just created as an example of a template for a coding system I am planning to use.

    I wonder now how would you complete the blank cells so that you would only need to change the code in one place and it would change automatically in the other cells?

    Example 1 code to repeat.png

    Thanks!
    Last edited by sjaviergmanso; 07-17-2023 at 10:19 AM.
    Reply With QuoteRegister To Reply
  9. 07-17-2023, 10:20 AM #9
    sjaviergmanso
    sjaviergmanso is offline
    Registered User
    Join Date
    07-17-2023
    Location
    Oxford
    MS-Off Ver
    Office 365
    Posts
    7

    Re: How to do CONCAT formula properly with merged cells?

    Quote Originally Posted by Glenn Kennedy View Post
    There's probably a nicer way of doing with with a LAMBDA... but so far, it is eluding me!!

    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Hi Glenn, thanks a lot, you are a genius to me, I did not know at all about that formula LET, I will explore and see how it works! but it looks very clean and efficient!
    Cheers!
    Reply With QuoteRegister To Reply
  10. 07-17-2023, 10:23 AM #10
    sjaviergmanso
    sjaviergmanso is offline
    Registered User
    Join Date
    07-17-2023
    Location
    Oxford
    MS-Off Ver
    Office 365
    Posts
    7

    Re: How to do CONCAT formula properly with merged cells?

    Hi Hans

    Thanks a lot for your proposal, I will review it and see how it works with BYROW, TRANSPOSE, LAMBDA AND TEXJOIN! Many formulas to learn lol
    Cheers!
    Reply With QuoteRegister To Reply
  11. 07-17-2023, 10:41 AM #11
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,053

    Re: How to do CONCAT formula properly with merged cells?

    Thanks... but use either the one at Post 5 or the variant of it (at Post 7). They will probably be more efficient than my original monster.
    Reply With QuoteRegister To Reply
  12. 10-27-2023, 12:50 AM #12
    jamesmillere
    jamesmillere is offline
    Registered User
    Join Date
    10-27-2023
    Location
    USA
    MS-Off Ver
    Office 365
    Posts
    11

    Re: How to do CONCAT formula properly with merged cells?

    Hi

    Is this working? I tried it but in my case it's not working. Is there any compatibility issue in my MS office.
    Thanks
    James
    Reply With QuoteRegister To Reply
  13. 10-27-2023, 12:59 AM #13
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,855

    Re: How to do CONCAT formula properly with merged cells?

    Administrative Note:

    Welcome to the forum.

    We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

    Please see Forum Rule #1 about hijacking and start a new thread for your query.

    If you are not familiar with how to start a new thread see the FAQ: How to start a new thread
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] extract last 3 numbers from each concat. value in col. B and add to col. A concat cells
    By therealdees in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-17-2021, 11:02 AM
  2. [SOLVED] Mail Merged Pictures Do Not Save Properly in Document
    By greatertyler in forum Word Formatting & General
    Replies: 3
    Last Post: 04-08-2021, 09:02 AM
  3. [SOLVED] Formula to concat cells together and then add new components in LEN is not 18
    By batexcel in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-14-2019, 05:20 AM
  4. Using Concat function - Not Sorting properly
    By yousuf0203 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-24-2017, 03:24 PM
  5. Auto size merged cells to hold data from linked merged cells.
    By pandabear28 in forum Excel General
    Replies: 0
    Last Post: 11-25-2014, 07:08 AM
  6. How to make Macro skip over merged cells [yes, i know merged cells were formed in Hell]
    By mastrellz in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-28-2012, 11:53 AM
  7. Can word wrap and merged cells auto row height properly in Excel
    By wchernock in forum Excel General
    Replies: 1
    Last Post: 05-30-2006, 10:10 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1