Three Excel Tricks for Lining up Data for Import into Dynamics NAV, GP, AX

image

Recently, I have had to import lots of different data for clients.  They give me Excel worksheets that are full of names, numbers and telephone numbers.  If you are working with Dynamics NAV or GP, you know that some of the fields can be very fussy in the import.  Dynamics AX is more forgiving.  I clean up the data, and get it ready. 

Most clients ask me how I clean up columns, especially phone numbers. If you use Excel a lot, you will know these formulas or tricks.  This post is for those who do not have to do this very often, and need some help.

1. Using Excel to change Words from All CAP to All lowercase, or to PROPER

    • you can use the UPPER, LOWER, or PROPER functions to automatically change the case of existing text to uppercase, lowercase, or proper case.
    • First, insert a temporary column next to the column that contains the text you want to convert
    • In the temporary column, for example,  cell B2, type =PROPER(A2)
    • The other choices are  =UPPER(A2) or  =LOWER(A2)

Now, all of the names, countries, states, provinces, etc. can have standardized type.

2. Removing spaces and symbols in a phone number

    • Often we get a list of telephone numbers that look like this

image

    • To get rid of the (, ), –, Ext. 0000, use the find and replace function
    • To get rid of the space, use the find and replace, using the space bar
      1. Press Ctrl + Space to select all cells in a column.
      2. Press Ctrl + H to open the “Find & Replace” dialog box.
      3. Press Space bar in the Find What field and make sure the “Replace with” field is empty.

3.  Filling in the extension on a phone number

    • Now  you want to add trailing zeroes to make all the numbers with a fixed length, such as 14 digits for a telephone number in Canada and the US
    • Create a blank column right of the existing phone number
    • Use the EXCEL function =A2&REPT(“0”,14-LEN(A2)) where “A” represents the column with the telephone number in it
    • The results are

image

      And, the number now becomes easy to import.

Here Is A List of Helpful Excel HOW TO Articles

image Graphic from news.techgenie.com 

I love it when people take the time to write up step-by-step HOW TO articles or blogs.  And, I like Excel HOW TO articles because it is an easy way to give a tutorial to a client when I need them to create/fix/organize data for loading into an ERP system. 

Recently, I have found quite a few key Excel HOW TO procedures.  As I like to keep things where I can easily find them, I am putting them on my blog so that others have the list, too.

Here we go.

1. Copy a formula in Excel    http://www.wikihow.com/Copy-Formulas-in-Excel

2. Excel Data Validation for Dates  (This is awesome!) http://www.contextures.com/exceldatavalidationdates.html#download

3. How to find duplicates in Excel and numbers (uses conditionals and COUNTIF)  http://www.makeuseof.com/tag/find-duplicates-excel-numbers/

4. All about Pivot Tables http://www.excel-easy.com/data-analysis/pivot-tables.html or at https://fiveminutelessons.com/learn-microsoft-excel/how-create-pivot-table-excel

5. Eleven advanced Excel tricks – Vlookup, Index match,  3D Sum, $ sign, Ampersand symbol, arrays, goal seek, pivot tables, http://www.businessinsider.com/excel-tricks-vlookup-index-match-pivot-tables-array-2013-5#-1

6. How to split data from one column to two http://smallbusiness.chron.com/split-excel-data-two-columns-54661.html or https://www.ablebits.com/office-addins-blog/2014/02/27/split-cells-excel/

7. Link data in two workbooks https://www.youtube.com/watch?v=-foPogO3QNE

8. General Excel training from Microsoft https://support.office.com/en-us/article/Excel-training-9bc05390-e94c-46af-a5b3-d7c22f6990bb?ui=en-US&rs=en-US&ad=US 

How To Fix Your iPhone if it is Acting Slow

image

The following information comes from Business Insider Tech and it is valuable information.  Go ahead and try it.

——————————————————————————————————————–

There’s a weird way to fix your iPhone if it’s acting slow — and it takes less than 30 seconds (AAPL)

by Maya Kosoff (mkosoff@businessinsider.com)|December 31, 2015

If your iPhone’s not properly displaying app updates or if it’s acting slow, there’s a weird — and incredibly simple — thing you can do that could fix it.

We first saw the trick over at AppAdvice, though the first person to discover it out appears to be developer Zachary Drayer.

Here’s what you do:

First, go into the App Store.

Then, tap one of the buttons at the bottom — it doesn’t matter which — 10 times quickly in succession. You must tap the same button 10 times.

Read the rest of the article here…http://www.businessinsider.com/how-to-refresh-your-iphone-app-store-cache-2015-12?utm_source=Pulse&amputm_medium=App&amputm_campaign=partner&utm_medium=referral&utm_source=pulsenews

Where To Find Business Process Walkthroughs for Dynamics NAV 2016

image

Dynamics NAV 2016 has quite a few changes.  In one blog that I read, the writer indicated that there were more than sixty new features and adjustments to the program.  Microsoft has done a great job of laying out the processes.  You can see them here at BUSINESS PROCESS WALKTHROUGHS at this link  https://msdn.microsoft.com/en-us/library/hh174286(v=nav.90).aspx.

New “How To” Book on Dynamics AX Implementation

Microsoft Dynamics AX Implementation Guide

As Dynamics AX  implementations become more complex because of changing software technologies, you may benefit from this “how to” guide  by long time AX implementers, Yogesh Kasat of Real Dynamics and JJ Yadav of Ignify.  The book is available from Packt Publishing at https://www.packtpub.com/application-development/microsoft-dynamics-ax-implementation-guide

The Table of Contents covers:

Table of Contents

1: PREPARING FOR A GREAT START

2: GETTING INTO THE DETAILS EARLY

3: INFRASTRUCTURE PLANNING AND DESIGN

4: INTEGRATION PLANNING AND DESIGN

5: DATA MIGRATION – SCOPING THROUGH DELIVERY

6: REPORTING AND BI

7: FUNCTIONAL AND TECHNICAL DESIGN

8: CONFIGURATION MANAGEMENT

9: BUILDING CUSTOMIZATIONS

10: PERFORMANCE TUNING

11: TESTING AND TRAINING

12: GO-LIVE PLANNING

13: POST GO-LIVE

14: UPGRADE

This book is very useful for newcomers to the Dynamics AX world.

Pixlr for Photo Editing

 

image

Fig 1.  Picture is from website at https://pixlr.com/

I had about 100 pictures that I wanted to edit in some way.  I could not find a great way to do the editing with Microsoft’s Paint so I went online and asked for recommendations from my groups. 

I received lots of recommendations.  The one that seems the easiest and has the best formatting choices for me is a great free software called PIXLR. 

Pixlr provides you with a wide range of choices for using the software as noted below in Fig 2.

image

Fig 2. shows the different choices on the Pixlr site.

I use the desktop version which has lots of editing features as you can see in Fig 3.

                       image

Fig 3. shows the different editing features in Pixlr desktop.

The blog at http://pixlr.com/blog/ has lots of information on it as well as different contests. 

Until I finish going through all my photos, I will not be entering any contests. 

How To Delete Hiberfil.sys in Windows 10

image

I have been running Windows 10 Pro since I did the install at the end of July.  Since that time, I have seen my hard drive fill up without me adding files.  This is suspicious behaviour.  When I looked at my drive, I had two files that were both 6.3 GB.  One file was the pagefile.sys and the other was the hiberfil.sys.

I Googled the Hiberfil.sys and found a great article at How To Geek that explains what the hiberfil.sys is and why you may, or may not, need it.  It also explains how to disable or delete the hiberfil.sys. 

I chose to delete the file.  Here are the instructions for doing that.

You’ll need to open an administrator mode command prompt by right-clicking on the command prompt in the start menu, and then choosing Run as Administrator. Once you’re there, type in the following command:

powercfg -h off

Picture from How To Geek’s article on “What is Hiverfil.sys and How Do I Delete It

I immediately freed up the space which is great because I have more room to run Hyper-V.

Google and How to Put Your Music Collection Online and Access It From Any Device

image

From the article….

Music streaming is the hot new thing, with many services offering access to millions of songs for a monthly fee. If you have your own music collection on your computer, you can put it online and stream it from anywhere — free. 

See more at:

http://www.howtogeek.com/227211/how-to-put-your-music-collection-online-and-access-it-from-any-device/