I am attaching excel file and in this file there is sheet named “paste mtr” when i paste data in this sheet output should be looks like of sheet “mtr in – out”
I am attaching excel file and in this file there is sheet named “paste mtr” when i paste data in this sheet output should be looks like of sheet “mtr in – out”
Last edited by KRIXXXX; 04-13-2020 at 09:54 PM.
Hi Krixxxx,
If you had a newer version of Excel with Power Query or Get & Transform, I could do your problem. See the attached where I worked on it for about an hour. Using VBA it would take me longer than that. Have you considered upgrading to the newest version of Excel? I did the attached just to prove to myself I could convert your data using the newer version.
PQ Transform Data to User Request.xlsx
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
But if you want VBA ...Please Login or Register to view this content.
Best Regards,
Maras.
Administrative Note:
Welcome to the forum.
We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.
Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.
As you are new I will add it for you Please read forum rules
https://www.mrexcel.com/board/thread...-data.1130117/
Thank You Very Much...
It works very well, but there is a catch. In input file "PASTE MTR" when you see you will notice that "Transaction Date"
is in DD-MMM-YY format but after running VBA code in output "Transaction Date" is generated in MM-DD-YYYY format which
will effect my work and outcome. I'm sure You will definitely will do this and this is not major issue for you.
One more request in this same file that if you can separate the Minus and Plus quantities of "Transaction Quantity
" column.
I'm attaching the file once again for your reference and highlighted the column in which i want separation.
Thank You Once again.
Last edited by KRIXXXX; 04-13-2020 at 09:54 PM.
Kindly read my reply...
Thanks for your effort but it is not possible in present at my workplace to upgrade in newer version of Excel.
"maras_mak" provides VBA code and it works Very Well but there is a little issue with it. in my system generated data there is column name "Transaction Date
" in which date format is DD-MMM-YY but after running VBA code in output Date format changes to MM-DD-YY. If you can help me with this it will be very helpful.
I wish one change then previous uploaded file after running the VBA code in Which "Transaction Quantity" should be bifurcated in two columns One with positive quantities and One with negative quantities. I am attaching this file.
and yes in this file if you can add one more column somehow in which it data generated date and time in below format
"11-Apr-20 07:55 PM"
Last edited by KRIXXXX; 04-13-2020 at 09:55 PM.
I have made two changes in my user expected file.
First is Bifurcation of the column "Transaction Quantity
" with Positive and Negative quantities.
Second is I have added column at the end with name "Report Generated Date & Time
" in which Date and Time should be of VBA code running Date and Time. and this Date and Time format should be "11-Apr-2020 08:01 PM" like this.
and Yes, Please do something with DATE format. because in your previous provided VBA code Date format of the column "Transaction Date" changes from "DD-MMM-YY" to "MM-DD-YY" in output file. and if you can do something that ind column "Transaction Date" after running your VBA code Date will shows in ""DD-MMM-YYYY" format.
Please reply soon with my requested updates.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks