How to fix too many data sets ???

oldngrmpy1
oldngrmpy1 Member ✭✭✭✭
What is the best way to merge 2 files. I have a Visa acct 2, dates 1-28-2019 which is  current, balanced and reconciled.  What I want to do is add or merge another previous previous Visa account 1 to it. Account 1 has transaction from 8-06-2004 to 1-28-2019. How do I stack Visa Acct 1 in front of Visa Acct 2?  I've tried cut and paste, but am now running into "too many dataset errors" to continue. This shouldn't be that hard, what am I missing here?  Someone somewhere mentioned setting up a new account, how, where, and why, if that is what I need to do? Don't think I ever remember setting up a new account, so not sure how, why or where!

Answers

  • Boatnmaniac
    Boatnmaniac SuperUser ✭✭✭✭
    edited November 2020
    Hi, @TJ102 .  Do you have 2 separate data files with one Visa account in each?  Or are both Visa accounts already in the same data file and you just want them merged into a single Visa account?
    (QW Premier Subscription: R36.23 on Windows 10)
  • oldngrmpy1
    oldngrmpy1 Member ✭✭✭✭
    Yes - 2 seperate data files one on each.
  • Greg_the_Geek
    Greg_the_Geek SuperUser, Windows Beta ✭✭✭✭✭
  • Boatnmaniac
    Boatnmaniac SuperUser ✭✭✭✭
    edited November 2020
    @TJ102 - It sounds like your merge situation is fairly simple and not complex.  You might want to try the following steps to see if this gets you what you want.  FYI--This process is not a bad or complex as it looks...I have added a lot of explanatory comments/notes which makes it look worse than it really is.
    1)  Decide which data file you want to maintain as your working file going forward.  This will be the one that you want to import data into from the other file.
    2)  Backup this working file in case something goes wrong during this process or you don't like the results of this process.
    3)  Open the other file (the one with the account data you want to be merged into the other file):
    • Make note of the Opening Balance of the credit card account.  (You will want this later on in this process.)
    • File > File Export > QIF File > select the location to save the QIF file to > select the account you want data exported from > select the date range of the data you want exported > check the box for Transactions and, if you want, the boxes for Memorized Payees and Category List > OK.
    4)  Open your working file.
    5)  Add a manual credit card account.  Give it a unique name.
    6)  Then: 
    • File > File Import > QIF File > browse to the location where you saved the exported QIF file and select it > select the manual credit card account you just created > check the boxes for Transactions and Special Handling for Transfers and, if applicable, the boxes for Memorized Payees and Category list...do not check the boxes for Account List and Security List > Import. 
    • During this import process you might get notified that the account name does not exist and then asked if you wish to create that account.  If this happens:  Select "No" > Continue Importing?:  Select "OK" > Select "Done".
    7)  Pull up the Transaction report (under Banking) and customize it by clicking on the upper right Gear icon:
    • Date Range:  Earliest to Date
    • Display tab:  Sort by = Category
    • Accounts tab:  Clear all accounts and then select only the new manual credit card account.
    • Categories, Payees and Tags tabs:  Select All
    • Advanced tab:  Amounts = All; Transaction types = All transactions; Transfers = Include all; Subcategories = Show all; check all boxes under Status.
    • Click "OK".
    8)  Scroll down the Transaction report.  Look for the following categories (which should be grouped together):
    • No Category Transactions:  If any are found, you will want to right click on the transaction and then left click on "Recategorize Transaction(s)" and assign a category to it.  (If you have multiple transactions listed which all need to be categorized the same way, left click on the top transaction, scroll down to the bottom transaction and while holding down on the Shift key left click on it, then right click anywhere on the shaded shaded transactions and left click on "Recategorize Transaction(s)".  You can then do a mass recategorizating of the selected transactions.
    • Transfer Category(ies) Transactions:  Transfers will not appear like transfers with the account name enclosed in brackets...example:  [Checking Account] will instead now look like Checking Account.  If you wish, you will need to recategorize these so they are once again really transfers.  But if you do this, you should note that this will impact the balances of the transfer accounts so you will need to reconcile them afterward.
    • Opening Balance Transactions:  You can only have one Opening Balance.  Find the correct Opening Balance transaction (the one that matches the Opening Balance amount you noted in Step #3 above) and delete any other(s).
    • Do a final review of all transactions to ensure they have the correct categories.
    9)  At this point you will need to make a decision:  Keep this account as a separate manual account...or merge the transactions in this account with the other credit card account.
    • If you keep this account as a separate account, you are pretty much done.  You can, if you wish, close the account (not delete it) so the account with the imported data so cannot be selected for future transactions but all the transactions data will be available for reports and other tools.  (My suggestion would be to select this option.  It will provide you with all of the account history in your reports without any additional work being necessary.)
    • If, however, you wish to merge the account data into the other credit card account, proceed to the next step.
    10)  Back up your working data file, again, so if something goes wrong you won't have to start all over again from scratch.
    11)  Going forward I will refer to CC #1 (the account with the data to be moved out) and CC #2 (the account which will have CC #1 moved into). 
    12)  Delete the Opening Balance transaction of CC #2.
    13)  In CC #1 Account Register: 
    • Left click on the Opening Balance transaction. 
    • Scroll down to the last transaction in the register and while holding down the Shift key left click on it.  This will shade all of the transactions in the register.
    • Right click anywhere on the shaded transactions.
    • Left click on "Reconcile Status" and the click on "Cleared".  You will be asked to confirm that you want all reconciled transactions changed to cleared status.  Confirm it.  NOTE: If you do not do this, you will be prompted to confirm the move for each and every transaction in the next step which can be very time consuming and monotonous.
    • Left click on "Move Transaction(s)", select CC #2 as the account to move the transactions to and click OK.  Depending on how many transactions are to be moved, this can take a little time so be patient and let the process complete.
    14)  At this point there should be no transactions left in CC #1.  If there are any, move them to CC #2 either individually or per the last step.  Then go to Account Details for CC #1 and delete it.
    15)  Check your CC#2 account balance. 
    • If it is accurate, reconcile the account to change all transactions back to "R".
    • If it is not accurate, make sure the account is filtered by date and then scroll through the register looking for duplicate transactions.  I'm guessing that if there are any duplicate transactions they will be within a window of a couple of months prior to 1-28-2019.  Delete any duplicates you find.
    • If it is still inaccurate you will need to troubleshoot the register to see what transactions might be missing or inaccurate.  Another option you might want to consider is to simply enter a balance adjustment transaction and be done with it.
    There you have it.  Let me know if this process helped you successfully merge the two credit card accounts or if you have any questions.
    (QW Premier Subscription: R36.23 on Windows 10)