Questions? We have answers.

Data Formatting Guide: Ungrouping Files: ungroup and split

Article author
Kate H
  • Updated

Summary

This guide is to help you understand the best ways to ungroup and split account numbers and account descriptions that appear in the same column within a file. 


Resolution

Consider the following excerpt from a GL file:

mceclip1.png

 

The problems:

  1. This file is grouped by account: only one row contains account information (rows 7, 5695), followed by all transactions for that row (rows 8-5691, 5696-6176) (violates row requirement 1)
  2. Account information is in column A but contains account number and account description separated by " - " (space character plus - plus space character) (rows 7, 5695) (violates column requirement 1)
  3. There are unneeded total/subtotal rows (5692-5693, 6177-6178) and blank rows under them (violates overall requirement 2)

The solution (formatting needed):

  1. Ungroup by account (drag down account information to each transaction row)
  2. Split account number and account description into different columns
  3. Exclude extraneous total/subtotal/blank rows and extraneous rows (rows 1-4) above the header row (row 5)

 

We insert two columns, "Account ID" and "Account Description", before the beginning of the file to split out account number and account description and drag them down:

mceclip2.png

Account ID: Cell A7 and below

=IFERROR(LEFT(C7,SEARCH(" - ",C7)-1),A6)
  • SEARCH(" - ",C7)
    finds the position in the string C7 where the first " - " string appears. We subtract 1 from that to get the number of characters the account number takes up (5 characters for account 11110),
  • then we use LEFT(C7,) to get the leftmost number of characters from that account string (so the first 5 characters in cell C7).
  • Finally, IFERROR(A,B) returns A if A is not an error, B if A is an error.
      • We want to extract the account number and account description only from rows with that information in column C.
      • We notice that these cells with account number and account description are identified by the presence of " - " separating account number and account description. SEARCH() returns an error (#VALUE!) if the string is not found; thus, for all the rest of the rows, #VALUE! is returned by SEARCH() (which flows up to the LEFT() formula that it is nested in. For these other rows, we just want to return the account number that we had extracted in the row above.
      • So in this example, the nested LEFT() and SEARCH() in cell A7 returns 1110, because cell C7 has " - " in it.
      • But in cell A8,
        =IFERROR(LEFT(C8,SEARCH(" - ",C8)-1),A7)
        the nested LEFT() and SEARCH() returns #VALUE! because cell C8 (2019-04-01) does not have " - " in it.
      • This is caught by IFERROR(), which, seeing the #VALUE!, moves on to A7, which is 1110 as discussed above, and thus also returns 1110. This all continues until it hits row 5695, when it finds that cell C5695 has " - " in it and therefore extracts account 1-1112

 

Account Description: Cell B7 and below

=IFERROR(MID(C7,SEARCH(" - ",C7)+3,LEN(C7)),B6)

This column's formulas follow the same logic as that of the Account ID column (column A), except instead of using LEFT(), it uses MID(), which is useful for extracting text from anywhere not at the beginning all the way to the end, or not to the end. The syntax is:

MID(text, start_num, num_chars)

where text is the string to search (C7)

start_num is the position to start at. We use

SEARCH(" - ",C7)+3

because we want to start after the " - ".

Recall from above, SEARCH(" - ",C7) gives the position where the " - " starts. Since " - " is 3 characters long, we add 3 to get the position of where the account description starts.

Finally, the LEN(C7) gives the length of the string in cell C7, to ensure that we take it all the way to the end.

 

Subtotals

The following are the formulas for cells G1 and H1:

=SUBTOTAL(9,G5:G50000)
=SUBTOTAL(9,H5:H50000)

We use 50000 because this particular file has under 50000 rows. Change that to a large enough number to accommodate the total number of rows your file has. We add these formulas in order to check that the file balances when we do the final step of filtering to keep only the transaction rows (next).

 

Filter to keep only transaction rows

After turning on filters, check column C (Date) and we see that there are the 2020 and 2019 dates, then the rest are account numbers:

mceclip3.png

 

Copy and paste values into a new workbook

Noting that all the dates are the transaction rows, select only the dates:

mceclip6.png

 

Note that results of our SUBTOTAL() formulas in cells G1 and H1 are showing that the total transactions are in balance (yay!)

 

Our final step is to select the header row and selected transaction rows (i.e., row 5 onward in the filtered view - you can start at cell A5, 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

mceclip8.png

 

Finally, in the new workbook, be sure to format the Date column as Date in YYYY-MM-DD format (hit Ctrl-1 on PC or ⌘1 on Mac):

mceclip9.png

 

And now we have a beautifully formatted file, ready to import into MindBridge!

mceclip10.png

 

 

Ungroup in Excel, split in MindBridge!

MindBridge does already provide a feature during column mapping that allows the splitting of columns, so if you prefer to use a simpler formula only to ungroup, without splitting at the same time, you can just add one Account column that will pull the value from column B if it contains " - ", otherwise pull the value found above:

mceclip11.png

=IF(ISERROR(SEARCH(" - ",B7)),A6,B7)

Then proceed with the steps described above to filter, copy and paste into a new workbook.

Begin importing the formatted GL file into MindBridge, then at Step 2 for column mapping, click on the three dots to the right, and select Split column. See this article for more information and step by step instructions to split columns: Splitting Columns

 

More complicated account splitting

See here for a more complex example of how to build a formula to split out an account number


Anything else on your mind?

Head over to the community with your thoughts and inquiries! You can also chat with us, or, submit a request for further assistance.


 

Share this:

Was this article helpful?

Comments

0 comments

Please sign in to leave a comment.