How to concatenate several cells

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

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

regards

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

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

Rizky,

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

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

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.
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
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
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.

I hope that I might help you.