tag:infinitekind.tenderapp.com,2009-01-14:/discussions/general-questions/84816-is-there-a-way-to-generate-a-1-line-per-transaction-report-including-splitsInfinite Kind: Discussion 2019-04-03T12:14:18Ztag:infinitekind.tenderapp.com,2009-01-14:Comment/471038452019-03-22T17:10:12Z2019-03-22T17:10:12ZIs there a way to generate a 1 line per transaction report, including splits?<div><p>Note: I'm open here to Excel-implemented pre-processing solutions as well as options within MoneyDance.</p></div>Captain Moderatetag:infinitekind.tenderapp.com,2009-01-14:Comment/471038452019-03-22T17:19:23Z2019-03-22T17:19:23ZIs there a way to generate a 1 line per transaction report, including splits?<div><p>Hi Captain,</p>
<p>Try the Find & Replace extension.<br>
Leave both of the 'split' options unchecked.<br>
Once you generated the desired data, the 'Copy' button can be used to C&P the data into Excel.</p>
<p>-Kevin N. (not a member of MD support)</p></div>-Kevin N.tag:infinitekind.tenderapp.com,2009-01-14:Comment/471038452019-03-22T17:33:06Z2019-03-23T02:41:00ZIs there a way to generate a 1 line per transaction report, including splits?<div><p>x</p></div>Captain Moderatetag:infinitekind.tenderapp.com,2009-01-14:Comment/471038452019-03-22T19:40:16Z2019-03-22T19:40:16ZIs there a way to generate a 1 line per transaction report, including splits?<div><p>Hi Captain,</p>
<p>MD 2017.10 (1706)<br>
MD 2019.2 (1860)<br>
Windows 10 Home (1809)<br>
Excel 2000</p>
<p>I can not reproduce the behavior of the Date column in your screenshot using either version of MD listed above in Excel 2000.</p>
<p>Have you tried selecting column B and formatting it as a Date?</p>
<p>The Find & Replace Users Guide explains the various 'Amounts'.<br>
<a href="http://www.mennesoft.com/findandreplace/index.html#usage">http://www.mennesoft.com/findandreplace/index.html#usage</a></p>
<p>-Kevin N. (not a member of MD support)</p></div>-Kevin N.tag:infinitekind.tenderapp.com,2009-01-14:Comment/471038452019-03-22T20:04:24Z2019-03-22T20:04:24ZIs there a way to generate a 1 line per transaction report, including splits?<div><p>Thanks for sticking with me on this, Kevin. The information on the Amounts is very helpful.</p>
<p>The dates situation baffles me too. Yes, I have tried reformatting the cells Date, without success. I mis-reported above. The dates that appear with spaces are interpreted correctly, the dates with /s (e.g. "12/18/2018") have not been interpreted as anything but text.</p>
<p>There are text conversion tools in Excel that can be used. Excel's text to columns tool, for example, splits "12/18/2018" into 3 columns of month, day and year. I also wonder if I could export the worksheet into a CSV file and re-import it, specifying the date format for column B. But ugh! There must be a more convenient way!</p>
<p>I have encountered this inconsistent date interpretation after saving an MD file as a Tab-Delimited Variables file. The text file seems to consistently show the dates with "/" separators, but.... When imported into Excel, some dates are properly interpreted (showing with spaces in the Excel file) and some not (appearing with slashes). I have yet to find the clues to explain why. (This is frustrating, as commas sometimes appear in my notes for transactions, so I like using tab-delineated files instead of CSV.</p>
<p>If you think of anything else, please let me know. I very much appreciate your help and guidance.</p></div>Captain Moderatetag:infinitekind.tenderapp.com,2009-01-14:Comment/471038452019-03-22T20:15:14Z2019-03-22T20:15:14ZIs there a way to generate a 1 line per transaction report, including splits?<div><p>I just tried this workaround for fixing the date misinterpretation. Not satisfying, but it does work:<br>
1 - Use Find and Replace Extension to find all transactions desired.<br>
2 - Click the copy button<br>
3 - Open a new Excel file, and select paste.<br>
4 - Check for date interpretation problems. If they are present....<br>
5 - Save the next excel file as a CSV file.<br>
6 - Close the excel file (no need to save), and open a new, empty excel file. (This is so the import won't confuse the importing file with the previous file from which it was created.)<br>
7 - In excel, select File > Import, specify the CSV file, and specify that column B should be imported as a MDY date.</p>
<p>Again, if you can come up with a less convoluted solution, I would love to know about it.</p></div>Captain Moderatetag:infinitekind.tenderapp.com,2009-01-14:Comment/471038452019-03-22T20:24:06Z2019-03-22T20:24:06ZIs there a way to generate a 1 line per transaction report, including splits?<div><p>Hi Captain,</p>
<p>Something that you could try is to Paste the data from the F&R extension into a simple word processor (e.g. Notepad) and see if there is some anomaly in the Date column data that may explain the odd results that you're getting in Excel.</p>
<p>-Kevin N. (not a member of MD support)</p></div>-Kevin N.tag:infinitekind.tenderapp.com,2009-01-14:Comment/471038452019-03-22T20:42:54Z2019-03-22T20:42:54ZIs there a way to generate a 1 line per transaction report, including splits?<div><p>Good suggestion.</p>
<p>I think I figured it out. When I just paste the data into an Excel spreadsheet, Excel attempt to interpret the dates as Day/Month/Year, but the copied data from MD is in Month/Day/Year format. So Excel sees 12/01/2018 as 12 January 2018, when it is actually intended from MD as 1 December 2018. And when MD sends 12/18/2018 (meaning 18 December 2018), Excel sees a string which just cannot be a date (as there is no 18th month).</p>
<p>So perhaps this challenge is a matter of default computer or Excel settings. I'll look into it. Thank you for all the help!!</p></div>Captain Moderatetag:infinitekind.tenderapp.com,2009-01-14:Comment/471038452019-03-22T20:47:58Z2019-03-22T20:47:58ZIs there a way to generate a 1 line per transaction report, including splits?<div><p>Hi Captain,</p>
<p>Please post back with your findings if you are able to resolve this. OK?</p>
<p>I'm curious to find out what is the root cause.</p>
<p>-Kevin N. (not a member of MD support)</p></div>-Kevin N.tag:infinitekind.tenderapp.com,2009-01-14:Comment/471038452019-03-22T21:33:56Z2019-03-22T21:33:56ZIs there a way to generate a 1 line per transaction report, including splits?<div><p>Hi Captain,</p>
<p>Something that you could try which is less convoluted...<br>
1 - Use Find and Replace Extension to find all transactions desired.<br>
2 - Click the copy button<br>
3 - Open a new Excel file, and select paste.<br>
4 - Check for date interpretation problems. If they are present....<br>
5 - Select column B.<br>
6 - Menu bar > Data > Text to Columns. Click Next, Click Next, in step 3 of the wizard, select MDY from the 'Date' drop-down list. Click Finish.</p>
<p>-Kevin N. (not a member of MD support)</p></div>-Kevin N.tag:infinitekind.tenderapp.com,2009-01-14:Comment/471038452019-03-23T02:38:55Z2019-03-23T02:38:55ZIs there a way to generate a 1 line per transaction report, including splits?<div><p>Yep! That is an improvement. I especially appreciate you mentioning the Text To Columns function, as I had explored that before and only found ways to generate 3 new columns from the single date column. On your instigation I have discovered the key: select Delimeted, but then no Delimiter!</p>
<p>Thanks for the advice on both MD and Excel!</p></div>Captain Moderate