Results 1 to 7 of 7

Thread: How to concatenate several cells

  1. #1

    How to concatenate several cells



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello,

    Please have a look of the attached. On the Tab called DataImport, I have to concatenate several cells that have the same serial number for example for 87319TOILETMD, I need to concatenate cells J1,J2 and J3. I know how to do that but the issue is if you look at the column A, we have 2 blanks seperating A1 from A4 and only one cell seperating A4 to A6 it is the same scenario throughout the entire column A . How can I can concatenate efficiently in this condition? Please help! Thanks
    Attached Files Attached Files

  2. #2
    Can you put the results example in your workbook? Not quite understand what you after

    regards

  3. #3

    Examples of Concanetation that I am looking for

    Hellow,

    Please see in the attached the example of the end result that I am looking for.

    Thanks
    Attached Files Attached Files

  4. #4
    I ahve just attached the example result that I am looking for. But it is also in the tab called Sheet13. Thanks

  5. #5
    Rizky,

    Did you get the chance to go over what I have attached as an Example?

  6. #6
    Yes I see the file, but in the previous sheet, where do I put the formula, Is that on DataImport Sheet? And where to Lookup the data, coz you have several sheets. The last is what the logic to concatenate.


    regards
    Last edited by Rizky; 2015-06-23 at 09:06 PM.

  7. #7
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    869
    Articles
    0
    Excel Version
    Excel 2013

    concatenate multiple cells ba formula

    If your goal is a unique data and copy to another workbook, here's an idea if you can help.
    You created an secondary table in range L1:W217
    I am by your table added another table that will return the unique data and merged cells. See attach.

    Note: I used IFERROR function, if you use Excel 2003 then instead IFERROR function, you use the IF/ISERROR. In the attache you have two files.
    Separator arguments formula that I use a semicolon (;) to you it can be a comma (,)

    Explanation of my solutions

    1. In the L column, I put the formula below. This formula combines the two first data that will later be used for the VLOOKUP formula.
    Code:
    =M2&"-"&N2
    2. In the range Y2:AI217 I have added a new table that pulls data from your auxiliary table
    In the Y column, I put the following formula below. This formula returns the maximum number of occurrences of data cells from Z2, which is a unique data
    Code:
    =IF($Z2="";"";COUNTIF(N2:N40;Z2))
    3. In the Z column, I put the following array formula below. This formula returns the unique data from your auxiliary table. Such a formula is complete with Ctrl+Shift+Enter
    Code:
    =IFERROR(INDEX($N$2:$N$40;SMALL(MATCH($N$2:$N$40;$N$2:$N$40;0);SUM((COUNTIF($N$2:$N$40;$Z$1:Z1)))+1));"")
    4. In the AA2 column, I put the following VLOOKUP formula below. This formula returns results from your auxiliary table. Copy this formula to the right to AI column. All formulas in the second row copy down.
    Code:
    =IFERROR(VLOOKUP($Y2&"-"&$Z2;$L$2:$W$40;COLUMN(D1);FALSE);"")
    I hope that I might help you.
    Attached Files Attached Files
    Last edited by navic; 2015-06-24 at 07:34 PM.

Posting Permissions

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