I have an Excel table with accounting transactions that are stored on a single line:
| Date | To Account | From Account | Amount | Type |
|---|---|---|---|---|
| 2023-06-01 | 2192 | 2281 | $100.00 | Transfer |
I’m attempting to convert that single line to double-entry transactions like this:
| Date | To Account | From Account | Amount | Type |
|---|---|---|---|---|
| 2023-06-01 | 2192 | $-100.00 | Expense | |
| 2023-06-01 | 2281 | $100.00 | Revenue |
I’ve got thousands of entries and will need to update this regularly (multiple times each month), so I’m looking for a systematic way to repeat the process. The only solution I’ve found, so far, is to copy the records and than use helper columns to partition the data based upon whether the row is the original or the copy. I’m open to an Excel, Power Query, SSIS, or SQL solution (the data will end up in a SQL Server database). I’d like to avoid VBA.
