Learn how to ungroup a data file by filling down multiple columns to produce a file that is supported by MindBridge. This is a common problem in many data exports taken from bookkeeping systems such as QuickBooks.
This method can even be used if not all columns have a value on the first row of the group, and to fill in blank account numbers when the account description is present but the account number is not.
Consider the following excerpt from a QuickBooks General Ledger file:
- This file is grouped by transaction; Date, Transaction Type, No., and Name are present on only the first row in the group of transactions and need to be dragged down.
- Account No. is not always present (see for example rows 8, 13, 17, 25), but Account is. Account can be mapped to Account ID, but Account No. cannot unless the blanks are filled in.
- There are unneeded transaction subtotal rows (9, 14, 19, 26)
The solution (formatting needed)
- Drag Date, Transaction Type, No., and Name down to each transaction row
- Fill in the blank Account No. with the value in Account
- Exclude extraneous total/subtotal/blank rows (9, 14, 19, 26) and extraneous rows (rows 1-4) above the header row (row 5)
We will put the revised columns at the end of the file.
- Date and Transaction Type are always present on the first row of the transaction group, but No. and Name are sometimes blank for a group. See the 3rd and 4th transaction groups beginning on rows 16 and 21.
- Our logic will be to take the value from the original column if we are on the first row of the transaction, or the previous value we found if we are not on the first row of the transaction.
Revised Date: Cell L6 and below
LEN($B6)>0tests if there is a value in column B by testing if the length is greater than 0. Since as we observed, Date is always present on the first row of the transaction group, this serves as our check if we are on the first row of the transaction. We put $ in front of the B for ease of copying the formula to the rest of the columns to drag down (Transaction Type, No., Name), as we will always be checking the presence of Date to check if we are on the first row of the transaction
IF(LEN(B6)>0,B6,"")returns the date if it is present, "" (empty string) if it is not. This can be simplified to simply B6 for the Date column because the Date is always present on the first row of the transaction group, but this logic is used to be able to copy and paste the same formulas for No. and Name, which, as noted above, are not always present. Because Excel will display 0 instead of blank when referencing an empty cell, we have to tell it to return "" in order for it to display an empty cell.
Note: The above formulas checking for empty string assume that the cells that look empty are indeed empty in Excel. If these empty-looking cells actually contain spaces, non-breaking spaces, or phantom data, substitute the following for all instances of B6 within the LEN() function:
- The TRIM() removes spaces
- CHAR(160) indicates the non-breaking space character, and the SUBSTITUTE() formula around it removes the non-breaking space characters (note: on a Mac, use CHAR(202) instead of CHAR(160))
So the final formula would be:
=IF(LEN(TRIM(CLEAN(SUBSTITUTE($B6,CHAR(160)," "))))>0,IF(LEN(TRIM(CLEAN(SUBSTITUTE(B6,CHAR(160)," "))))>0,B6,""),L5)
Revised Transaction Type, No., Name
Copy and paste and drag down the formula found for the revised Date.
The formulas in cells M6, N6, and O6 should be the following:
Revised Account No.
Our logic to fill in the blanks for Account No. in column P will be to take the original Account No. from column H if it is present (length is greater than 0), Account from column I if it is not (length is not greater than 0):
Filter to keep only transaction rows
We observe that every transaction row in the original file has an Account. So with filters turned on, filter out blanks from column I (Account):
Copy and paste values into a new workbook
Then select the header row and selected transaction rows (i.e., row 5 onward in the filtered view - you can start at cell B5, hit Ctrl-Shift-End to get to the end of the file, and adjust the position as necessary), then copy, then paste special values in a new workbook.