August 2021 Tip Of The Month

August 2021 News & Tips

NEWS		SAGE 50		EXCEL
Version 2022 Upgrades Going Smoothly Special upgrade offer to users of versions 2016 through 2020		Building a 12 Period Balance Sheet		Removing duplicate entries from a list

News

Version 2022: I'm happy to be able to say that upgrades to version 2022 have been going smoothly. I have not personally seen any problems with the upgrade and I haven't seen reports of problems online. If you regretted being an early adopter of version 2021 it looks like you can expect a much smoother upgrade experience this year. If you want help installing the upgrade call or email me to schedule an appointment.

Special Upgrade Offer: If you are using Sage 50 version 2016 through 2020, Sage is offering 50% off the first year of Sage 50cloud subscriptions. Not sure if you want to switch from a traditional perpetual license to a Sage 50cloud subscription? Read my guide Choosing Between Sage 50 and Sage 50cloud. If you decide a Sage 50cloud subscription is a good fit for you, then take advantage of this great offer (good through September 30th). If you want to keep your traditional license it's still a great time to upgrade. I'm offering 20% off upgrades to Sage 50 2022 with a traditional perpetual license. Contact me for details about either offer.

Steve Collins
IQ Accounting Solutions LLC
918-851-9713
scollins@iqaccountingsolutions.com
www.iqaccountingsolutions.com

A complete archive of these tips is available at http://www.iqaccountingsolutions.com/blog

Sage 50/Peachtree Tip - Creating a 12 Period Balance Sheet

Sage 50 comes standard with an income statement that has 12 monthly columns. But recently I had a request for a 12 month balance sheet. When building this report I realized what a great example it is of how Sage 50's financial statement designer is powerful, flexible, and easy to use.

At first this report seems like it will be rather time consuming to build. But when you realize you can take building blocks from existing financial statements and modify them to fit your needs, the 12 period balance sheet becomes very easy.

Start by going to the Reports & Forms menu and choose Financial Statements. In the list of financial statements select <Standard> Income - 12 Period and click the Design button at the top of the window.
Near the left edge of the Design window there will be a column of grey buttons. These are the components that define this report. Right-click on the Column Desc button and choose Copy.
Close the Design window. If prompted, don't save your changes.
Back at the list of financial statements, select the balance sheet format you want to use as your starting point for the new report and click the Design button.
In the Design window, right-click on the Column Desc button and choose Paste. You will see the column descriptions from the 12 period income statement appear above the original balance sheet column descriptions.
Right-click the new Column Desc and choose Properties. The Column Description window will open showing a table. Each row in the table defines a column on the report. Column 1 is the account descriptions, so that will remain unchanged. For columns 2 through 13 (periods 1-12) change the "Contents" from Activity to Balance (the easiest way is to click in that field and type the letter B). When you're done, click OK to save your changes.
Now right click on the original Column Desc and choose Delete.
Click the Save button and give your report a new name.

That's all it takes to create a 12 period balance sheet. But remember that, just like the 12 period income statement, for this report to work correctly, the date range must be set FROM December 1st TO December 31st or (whatever your last period is if you are not on a calendar year). This is because the report works by defining the date range for the last column and each previous column counts back one more month/period from that date range.

Excel Tip - Remove Duplicates From a List

If you have a list in Excel, you may need to remove duplicate entries from that list. You could sort your list and manually delete the duplicates, but Excel has an easier way. On the Data tab of the ribbon, you’ll find a Remove Duplicates button in the Data Tools section. The word “remove” tells you that you need to use this tool with caution. And if you don’t like the results, remember that Ctrl+Z is the keyboard shortcut for Undo.

If you have a single column list, then it’s pretty simple:

Select the range of cells from which you want to remove duplicates, or just select the whole column if there is nothing else below your list.
Go to the Data tab on the ribbon.
Click the Remove Duplicates button.
If you have a heading at the top of your list, make sure the My data has headers box is checked in the Remove Duplicates window.
Click OK.

Excel will then tell you how many duplicate values it found/removed and how many unique values remain. It will also move the remaining values up to fill in the gaps left by removing the duplicates. That’s a nice finishing touch since you won’t have to manually remove the blank rows, but it is also the reason that you have to be very careful with this tool when your list has multiple columns.
If you have multiple columns of data but only want to check one of them for duplicates, you must select all columns in your list before removing duplicates. If you don’t, then after the duplicates are removed, the remaining values will no longer be on the same line as their corresponding data in the other columns.

Also, when you select multiple columns of data, you can choose how many of those columns should be checked for duplicates by simply checking or clearing the box next to each column name in the Remove Duplicates Windows. Excel will then look for duplicates based on the combined content of the chosen columns, so checking multiple columns will result in fewer lines being removed, not more.

Here is an example of how you will get different results depending on the columns you select:

If you highlight columns A and B, then click the Remove Duplicates button, and select only the Product column in the Remove Duplicates window, lines 3, 5, 7, 9, 10, and 11 will all be removed (including the Color data on those lines). You’ll be left with only one entry each for Widget A, Widget B, Widget C, and Widget D.

	A	B
1	PRODUCT	COLOR
2	Widget A	Blue
3	Widget A	White
4	Widget B	Blue
5	Widget B	White
6	Widget C	Blue
7	Widget C	White
8	Widget D	Blue
9	Widget D	White
10	Widget C	Blue
11	Widget D	White

But, if you selected both the Product and Color columns in the Check for Duplicates window, only rows 10 and 11 would be removed. That’s because Excel will only consider it a duplicate if the combined contents of both columns create a duplicate.

If you know other Sage 50/Peachtree users, do them (and me) a favor and pass this along to them. If you are someone that this has been passed on to, and would like to be added to my email list, you can subscribe for free at www.iqaccountingsolutions.com/signup.html or send me an email at newsletter@iqaccountingsolutions.com. If you need to change your email address or would like to unsubscribe, you can use the links at the end of this email.

Steve Collins
IQ Accounting Solutions LLC
918-851-9713
10611 E 17th Place
Tulsa, OK 74128