• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Multiline Narration in Single line

Amit_KK

New Member
Hello Experts,
I Have an issue extraction the narration from bank statement,

The statement is too large in text format and I have imported it in excel. The problem sample file is attached for reference. Please refer.

Also please suggest if there are any tips to import text statements in proper way in excel.

Thanks.
 

Attachments

  • Sample File.xlsx
    9.6 KB · Views: 13
Hi,​
maybe a bad import or a bad file, who knows without any source file ?!​
If the source file was not created by Dumb or Dumber, that just needs to well answer to the Import Assistant …​
 
No No.. it is extracted from text file.. and it is formatted in such a fashion only..

The part narration in same column in new line in text file.. No error on extracting the data.

I not able to attach the sample text file here.
 
Hi, bank statements can be a mess. For me the easiest way to solve this is using Power Query.
I made a mess of he applied steps, but just to show a possible result, I've uploaded the file.

To apply the same (given you have Excel 2010 or higher and are working on Windows)

1. Load data from range/table
2. Add Index
3. Add Custom Conditional Column: if data is empty then null else Index
4. Fill Down Custom and Date
5. Group By on Date and Custom, using All Rows as aggregation. Name this column "All".
6. Add Custom column and type this formula: Table.Column ([All], "Description")
7. Expand this list and use extract values with delimiter of choice.
8. Close and Load to Excel

Most of the steps are done by using mouse operations on the UI. So it's rather user friendly.
 

Attachments

  • Copy of Sample File-1_44313.xlsx
    19.6 KB · Views: 15
I think Power Query is the better choice and herein formula solution just for your reference only.

1] In E2, formula copied down :
=IFERROR(AGGREGATE(15,6,Table1[Date],ROW(A1)),"")

2] In F2, formula copied down :
=IF(E2="","",TEXTJOIN(" ",1,INDEX(Table1[[#All],[Description]],AGGREGATE(15,6,ROW(Table1[Date])/(Table1[Date]<>""),ROW(A1))):INDEX(Table1[[#All],[Description]],IFERROR(AGGREGATE(15,6,ROW(Table1[Date])/(Table1[Date]<>""),ROW(A1)+1)-1,MATCH("zzz",C:C)))))

N.B. : TEXTJOIN is a new function available in Office 365 and Excel 2019 only

68004
 
Another formula solution
68005

With future versions of Office 365, the spreadsheet solution looks even more like a snippet of computer programming

68007

[Note: The LET formula requires Office 365 insider channel and can be deleted]
 

Attachments

  • Temp.xlsx
    23.3 KB · Views: 8
Last edited:
thanks alot bosco_yip
Amit_KK
if you haven't Textjoin Function in your Excel.... you can Insert New Module and Put This Code For This Function
Code:
Function TEXTJOIN(delimiter As String, ignore_empty As Boolean, ParamArray cell_ar() As Variant)
For Each cellrng In cell_ar
For Each cell In cellrng
If ignore_empty = False Then
result = result & cell & delimiter
Else
If cell <> "" Then
result = result & cell & delimiter
End If
End If
Next cell
Next cellrng
TEXTJOIN = Left(result, Len(result) - Len(delimiter))
End Function
 
Last edited by a moderator:
@herofox
Another function that would be of general value is ACCUMULATE(range) that runs through a range summing as it goes. Here
= ACCUMULATE( SIGN(Source[Date] ) )
to group the source data records. but, more generally, used to calculate running balances from flows.

I have a version written by Charles Williams written in C and with a few more parameters, but a VBA version would be quite usable.
Note: the other parameters provide an initial value. add a percentage change, and specify opening or closing balances.
 
thanks alot bosco_yip
Amit_KK
if you haven't Textjoin Function in your Excel.... you can Insert New Module and Put This Code For This Function
Code:
Function TEXTJOIN(delimiter As String, ignore_empty As Boolean, ParamArray cell_ar() As Variant)
For Each cellrng In cell_ar
For Each cell In cellrng
If ignore_empty = False Then
result = result & cell & delimiter
Else
If cell <> "" Then
result = result & cell & delimiter
End If
End If
Next cell
Next cellrng
TEXTJOIN = Left(result, Len(result) - Len(delimiter))
End Function
Sorry Sir,
But getting #Name error in code.. formula function not pooping up while typing.
 
Amit_KK
i didn't upload this Function to you before test it and it work as well !!
Ok.. probably the error from my side then ...

I have pasted the code in VB editor and that's it .. tried to write it in sheet .. is that anything more to do ? That I have not ?

Please inform.. thanks
 
@Amit_KK
I do not think it is you. As far as I can see, the function is written to concatenate text from a range, or even multiple ranges. In this case, I think the argument passed to the function is an array and not a range so an alternative loop structure is needed to work over the elements of an array.
@herofox
 
@herofox
I wrote a version of the formula that does return a range for each TEXTJOIN.

= LET(
start, XLOOKUP( [@Record], Source[Record], Source[Description], , , 1 ),
end, XLOOKUP( [@Record], Source[Record], Source[Description], , , -1 ),
concat, IF( ISREF(start:end), TEXTJOIN( " ", TRUE, start:end ), "" ),
concat )


The trouble is, by the time I have used LET and XLOOKUP, TEXTJOIN is the least of the problems.
What strikes me most of all, is that the result looks nothing like a spreadsheet formula! :eek:
 

Attachments

  • Joining rows in table using LET.xlsx
    24.9 KB · Views: 3
Back
Top