How I used Conditional Formatting in Excel to aid with a Dynamics GP data import.

So in May of this year my company purchased another company and we have been slowly working on getting things setup to roll them into our Dynamics GP environment. This has been a challenge at every step so far and today is no different. My current task is importing all the needed GP Payroll Posting Accounts which thankfully the import process is way easier than I thought it would be. 

So today I attempt to start the import process and I’m quickly met with thousands of validation errors referencing the Payroll Position Code doesn’t exist. Okay fair enough, I quickly dump these values out into a new Excel document and add a description column and use Smart Connect to import them.  This appears to work as I get back no errors, so I quickly jump back to my import and try again. This time around no joy either. WTF?!!!!  So I spend about 30 minutes running over my Excel document trying to figure out why not all of my things imported properly as Smart Connect did not show any failed objects.

image

Well with no errors in Smart Connect I move onto SQL for some additional information. Turns out the JOBTITLE field only allows 7 characters and many of my position codes exceeded that but I’m not sure why Smart Connect didn’t just error those out…

image

So I jump back to Excel and I quickly realize that I have no interest in going row by row to figure out which of these codes exceed that whole 7 character limit as that would literally take hours to comb through 2,000 + rows and count these letters.  So as I’m staring at this Excel document I remember that hey I can use Conditional Formatting to help me out here.

image

The thought here is I can use Conditional Formatting to highlight all the values in my document that exceed that 7 character limit to help quickly identify problematic data and correct it so I can move onto my next task. Turns out this is actually easier than I thought.

I click on the Conditional Formatting button in Excel and select New Formatting Rule. I then choose to use a formula as the type.

image

The root of my formula is going to be the LEN function in Excel which you can read more about here from Microsoft.  What I did here was say for the column C2 and rows C2 – End I want you to turn the cell background red and bold that cell font for anything that exceeds that 7 character limitation.

image

The results are shown below. I can quickly identify my problem data without having to visit each row of data one by one.

image

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.