Thanks for the excellent video and I am putting the formulas to make it easy for others to follow - First see the full video. Assumption Row 1 is header and Row 2 onwards data. Column A is Date, Column B is narration in multiple rows, Column C, D and E are deposit, withdrawal and balance. After Balance column on your statement assuming it is Column E, leave a column F blank, use column G for unique numbering and use column H for revised narration and column I for bringing the full narration to first line of transaction. =IF(ISNUMBER(A2),B2,CONCAT(H1,B2)) for making multi line to one line, =IF(ISNUMBER(A2),G1+1,G1) for getting unique number only when a row with date is available, =XLOOKUP(G2,G:G,H:H,,0,-1) for bringing the concinnated line to first line before deleting unwanted rows and copy the narration to main table. Also, the short cut keys are "crtl+shift+down" to select all rows then "alt+;" to select only visible rows, then "ctrl+-"(minus) to delete unwanted rows, then copy over narration original column and you are done.
This was really a very helpful video I was struggling from long time to get bank statements in order, Can we do this same in power query so next time when we connect the excel it generate the single narration line work sheet in a click. Thanks alot for your valued video.
How could we solve if the same date is repeated more than once in the date column with different narration and we need separate narration on the same date as stated above?
This technique would work irrespective of date is repeated or not. Date column is just acting as placeholder for logic of determination of narration start/end
i am struggling to convert multiple PDF ages into excel. After converting into excel data goes to different columns. Do you have any suggestion or option to apply in power query.
I would say, Power Query is able to decode the tabular structures, which are fairly simply, but not expert at decoding complex structures. So, if you find any professional dedicated PDF to Excel software, go for it. In that scenario, Power Query can always assist you post converting to clean up the data & remove extra rows
I’m struggling with combining Dr side and Cr which are separately recorded in different column. Dr line is above Cr line. So it’s hard to merge them into single column for account code.
This might be the fix for this. Conceptual demo below: =A2 (Debit Amount cell) In adjacent cell, write formula =A3 (Credit amount cell) Now if you copy paste these two cells till bottom, you will get debit credit amount in the single row. Just delete repetition in the adjacent rows
It is possible in Power Query, but that would require some M Language scripting. I personally do not recommend taking those routes which requires scripting / programming efforts, when you can do things easily with simple tricks.
V Good, mai screenshot leke rakh leta hu, ki kabhi kaam ayega, but office 365 nhi hai to jab galary me screen shot milta hai to office me kaam nhi aata, abhi pta chal gya ki 365 lagega, to specially likh diya sath, me 👍
How he complicated things by giving all cumbersome steps, there are easy ways to do it simply..... some people are like him they make things complicated and difficult to remember....
There are multiple ways to achieve a solution. I will suggest that you create a video and publish it on your TH-cam channel, to share the trick you know which will benefit everyone.
If you have better technique, then I would love to see your own version of video too. Let's use this chat to build up a better knowledge sharing opportunity.
I've looked at many solutions to this problem, and this one is by far the easiest and best. Thanks for sharing.
Thanks for the excellent video and I am putting the formulas to make it easy for others to follow -
First see the full video.
Assumption Row 1 is header and Row 2 onwards data. Column A is Date, Column B is narration in multiple rows, Column C, D and E are deposit, withdrawal and balance.
After Balance column on your statement assuming it is Column E, leave a column F blank, use column G for unique numbering and use column H for revised narration and column I for bringing the full narration to first line of transaction.
=IF(ISNUMBER(A2),B2,CONCAT(H1,B2)) for making multi line to one line, =IF(ISNUMBER(A2),G1+1,G1) for getting unique number only when a row with date is available, =XLOOKUP(G2,G:G,H:H,,0,-1) for bringing the concinnated line to first line before deleting unwanted rows and copy the narration to main table.
Also, the short cut keys are "crtl+shift+down" to select all rows then "alt+;" to select only visible rows, then "ctrl+-"(minus) to delete unwanted rows, then copy over narration original column and you are done.
Really appreciate constructive comment, which will help the viewers. Super like shortcut keys you mentioned.
Oh wow🎉 Thank you, I’ve had so much trouble with this scenario.
Perfect, we need more this type of data shortage videos
Sir.... Very useful trick for my works..... Thanks a lot.... ❤
This was really a very helpful video I was struggling from long time to get bank statements in order, Can we do this same in power query so next time when we connect the excel it generate the single narration line work sheet in a click. Thanks alot for your valued video.
Sir....i want to add space between the word...how can i do in this formula?
Much wanted video. Many thanks for sharing
Thanku sir to giving me one another useful method.
How could we solve if the same date is repeated more than once in the date column with different narration and we need separate narration on the same date as stated above?
This technique would work irrespective of date is repeated or not. Date column is just acting as placeholder for logic of determination of narration start/end
Perfect Man👍
i am struggling to convert multiple PDF ages into excel. After converting into excel data goes to different columns. Do you have any suggestion or option to apply in power query.
I would say, Power Query is able to decode the tabular structures, which are fairly simply, but not expert at decoding complex structures. So, if you find any professional dedicated PDF to Excel software, go for it. In that scenario, Power Query can always assist you post converting to clean up the data & remove extra rows
@@ExcelKida Thank you for the suggestion
I’m struggling with combining Dr side and Cr which are separately recorded in different column. Dr line is above Cr line. So it’s hard to merge them into single column for account code.
This might be the fix for this. Conceptual demo below:
=A2 (Debit Amount cell)
In adjacent cell, write formula
=A3 (Credit amount cell)
Now if you copy paste these two cells till bottom, you will get debit credit amount in the single row. Just delete repetition in the adjacent rows
Nice effort. Thanks.
Thanks for the video any possibility in power query
It is possible in Power Query, but that would require some M Language scripting. I personally do not recommend taking those routes which requires scripting / programming efforts, when you can do things easily with simple tricks.
@@ExcelKida thanks for the video once you free sir of possible draft m language to do automate Process
Good way of life , thanks for your sharing
It helped me a lot brother. Thanks.
Mera excel old ver he usme xlukap nahi uske jagah ham kaya use sakte he
Excel upgrade कर लिजिये, या फिर, Excel Online version use किजिये जो की free of cost है व उस्मे XLOOKUP supported है. XLOOKUP को कोई तोड नाही है
Ap Xlookup ki jgh Only Look Up formula apply kro work krega mera bhi ho gya
Excellent
Thank you sir for helping as
Sir plzzz formula likhe do na appka screen nhi deek ra hai barabar
TH-cam ने pause button उसी के लिये दी है
Kindly use pause button of TH-cam and then you can take your own time to write formula
mere data me work nahi kar raha h, #VALUE! aa raha h
Email me screenshot of your error with formula including 4-5 rows above that cell. I might be able to guide you what's the issue with your formula
CONTEC NO PROVIDE
???
Awesome
Brother invoice image to tally data transfer PE vedio banaye
Already software available है उसके लिये market मे
@@ExcelKida
Please send me link vedio
@@ExcelKida bro aap vedio create kijiye
V Good, mai screenshot leke rakh leta hu, ki kabhi kaam ayega, but office 365 nhi hai to jab galary me screen shot milta hai to office me kaam nhi aata, abhi pta chal gya ki 365 lagega, to specially likh diya sath, me 👍
You can use web based Office 365 Excel, which is free.
FB link
Didn't get your comment
Do you mean you want a Facebook link?
How he complicated things by giving all cumbersome steps, there are easy ways to do it simply..... some people are like him they make things complicated and difficult to remember....
There are multiple ways to achieve a solution. I will suggest that you create a video and publish it on your TH-cam channel, to share the trick you know which will benefit everyone.
make your own video then! i would be awesome
Sure my friend. Stay tuned to the channel for more tutorials & tricks.
@@ExcelKida i was talking about @Indian_1996 hehe
Poor knowledge provider
If you have better technique, then I would love to see your own version of video too. Let's use this chat to build up a better knowledge sharing opportunity.
sIR, i AM PUTTING THIS FORMULA =XLOOKUP(H2,H:H,I:I) AND THE NAME ERROR IS COMING.#NAME?
That means you are using incompatible version of Excel. You need to use office 365 version of Excel
Thanku@@ExcelKida