Is there a formula i can write to help me format this import error?

See attached pic for more context but essentially by data is getting imported incorrectly. I have a field that allows multi-line input and it looks like during the import it's putting each one of those lines into a seperate row. Is there a formula/formatting trick i can use to concatinate this?



Capture.PNG

I can't read your picture (nor could I edit it to play about with some formulae). Please attach a sample Excel workbook instead.

Pete

Thanks for your reply Pete. Uploaded the document

I've taken a quick look at this, although it's getting quite late here (nearly 2:00am), so I can't progress with it just now.

It would help, though, if you could indicate how you would like the first few records to appear, maybe on a separate sheet, as it is not clear to me what the numbers in column A represent. If nobody comes back to you while I'm asleep, I'll take another look in the morning.

Hope this helps.

Pete

Before

Column1 Column2
1
2 ipad mini 3 lcd replacement
3 ipad 2 lcd replacment
4 ding on the corner
5
6 repair screen
7 batter replacement
8 replace camera
9 Computer running slow
Check for virus and optimize
10 Computer running slow
Check for virus and optimize
11 Computer and Monitor
Keyboard
recipe printer
Solid State Drive
Pos Software
12 Computer and Monitor
Keyboard
recipe printer
Solid State Drive
Pos Software


After

Column1 Column2
1
2 ipad mini 3 lcd replacement
3 ipad 2 lcd replacment
4 ding on the corner
5
6 repair screen
7 batter replacement
8 replace camera
9 Computer running slow ,Check for virus and optimize,Check for virus and optimize
10 Computer running slow ,Check for virus and optimize,Check for virus and optimize
11 Computer and Monitor,Keyboard ,recipe printer,Solid State Drive,Pos Software
12 Computer and Monitor,Keyboard ,recipe printer,Solid State Drive,Pos Software

I've used two helper columns (D and E, shown in blue) with this formula in D2:

=IF(AND(ISNUMBER(Test[[#This Row],[Column1]]*1),LEN(Test[[#This Row],[Column1]])<5),Test[[#This Row],[Column1]]*10,D1+1)

and this one in E2:

=IF(MOD(D2,10)=0,D2/10,"")

These are copied down to the bottom of your data and help to identify where each new record occurs, and where fields have been split. Then I have used this formula in G2:

=ROWS($1:1)

and this one in H2:

=IF(ISNA(MATCH(G2,E:E,0)),"",INDEX(B:B,MATCH(G2,E:E,0)) & IFERROR(", "&INDEX(A:A,MATCH(G2*10+1,D:D,0)),"") & IFERROR(", "&INDEX(A:A,MATCH(G2*10+2,D:D,0)),"") & IFERROR(", "&INDEX(A:A,MATCH(G2*10+3,D:D,0)),"") & IFERROR(", "&INDEX(A:A,MATCH(G2*10+4,D:D,0)),"") & IFERROR(", "&INDEX(A:A,MATCH(G2*10+5,D:D,0)),"") )

which put the data in the format your require, although you may want to move these into a different sheet. These can be copied down as far as you need them, but if you copy too far then you will get blanks, as can be seen at the bottom.

You can see with the final formula that many of the terms are very similar - they just vary by the increment shown in red. The formula will cater for up to 5 fields which are split for each record, but if you need more than that (maximum of 9) you can copy the section shown in blue to the end of the formula (inside the final closed bracket), and just change the increment as required.

Hope this helps.

Pete