Concatenating a range of cells in EXCEL

I often use Excel to create SQL UNION statements and update statements by harnessing Excel's concatenate function. Typically, I’m creating UNION statements. I list the column names from the tables I’m trying to union and then use Excel until I get them lined up and mapped correctly. My final step is to use the CONCATENATE function to append the necessary comma between the column names.

Recently, I created a view that combined the pay code, deduction code, and benefit code tables. I later used that view to create a SmartList object so that all of those different codes could be viewed in one place. You can download the SmartList on GPUG's 'share my code' site. Or e-mail me for it (leslievail@earthlink.net). I used Excel to build the UNIONs and the CONCANTENATE function to insert the commas. While using CONCANTENATE accomplished my objective, it was a bit time consuming than I had hoped. There has to be a better way, I thought. And there is.

The CREATE_DELIMITED function. Using a function to join a range of cells together separated by a certain character (or characters) is much easier than using the CONCATENATE function. I found an article on the Internet the other day that described such a function. It works magnificently. If you use Excel to create SQL statements as I have described, I recommend that you copy this function into your Personal Macro Workbook. By creating the function in your macro workbook, you’ll be able to use it in all of your workbooks. Katie Giles published this function on her blog on September 12, 2009. I modified it slightly to fit my needs, but the logic was created by Katie Giles.

The following text will show you how to create and call the function, how it works, and its limitations.

Concatenating the old fashioned way To combine four cells (A1, B1, C1 and D1) into one field, separated by commas, you could use the following two options: 1. Hard code the concatenation using the CONCATENANTE function and include the literal string between each cell: =CONCATENATE (A1,", ",B1,", ",C1,", ",D1) 2. Alternatively, you can use the ampersand to join the cells together =A1&", "&B1&", "&C1&", "&D1 Both of the options above will return the desired output, but if you need to join many cells together, the formula could become quite long and hard to manage.

Using a function to concatenate cells Having a function that will accept an optional parameter for the delimiter is much more efficient than creating the formula by hand. The VBA code below creates the function I now use to create my unions. Example VBA Function

Function CREATE_DELIMITED (ByVal cellRange As Range, Optional ByVal delimiter As String)
Dim c AS Range
Dim DataStatement AS String
Dim Count AS Integer
Count = 0
DataStatement = ""
For Each c in cellRange
Count = Count + 1
DataStatement = DataStatement & c.Value
If Count < cellRange. Count Then
DataStatement = DataStatement & delimiter
End If
Next
CREATE_DELIMITED = DataStatement
End Function
 
(thanks to Perpetual Acumatica Learner for correcting one of the lines of this code)

How the CREATE_DELIMITED function works To call the function, you would use the following syntax: =CREATE_DELIMITED (range [, string]) The function accepts two input parameters, a range and a string. · range is the selection of cells you want to join together · string is the character(s) you want to use for the delimiter. This is an optional parameter. The function uses a count variable to check whether the current cell is the last one in the range. You need to know when you are on the last sell so that you don't append the delimiter to the end of the statement.

Joining a range of cells with the CREATE_DELIMITED function Using the CREATE_DELIMITED function to create the concatenated result would look like this: =CREATE_DELIMITED (A1:D1,", ") The range is A1:D1 and the string delimiter is a comma and space.

Joining cells from multiple rows If the range you want to join includes more than one row, Excel works through each row in turn. For example, if your function call resembled the following: =CREATE_DELIMITED (A1:D2,", ") Excel would join cells A to D on row 1 followed by cells A to D on row 2. You could also achieve this by using two function calls and concatenating them together. The formula would look like this: =CREATE_DELIMITED (A1:D1,", ") & ", " & CREATE_DELIMITED (A2:D2,", ") If you were to use the built-in CONCATENATE function, the formula would look like this: =CONCATENATE(A1,", ",B1,", ",C1,", ",D1,", ",A2,", ",B2,", ",C2,", ",D2) The formula using the CREATE_DELIMITED function is by far the easiest one to use.

CREATE_DELIMITED Function Limitations The CREATE_DELIMITED function is only suitable for joining a range of adjacent cells. This function cannot be used to join cells in different locations on a spreadsheet or to join text strings entered directly into the formula (rather than being entered into the cells themselves).

Until next post!

Leslie

Paste to GP from Excel - the wait is over!

This window is a mock up – but it’s close to the real thing.  Included in the R2 release that is due out this spring, is the Paste feature! You will at last be able to past a general ledger entry from an Excel spreadsheet. It’s simple to set up and simple to do. First, your journal entry distributions need to be entered as below on a spreadsheet.

Next, highlight the information you want to put into your journal entry, and then put your curser on the first line of your journal entry. Finally, push the [Post] button and your distributions will populate the journal entry.

New Dex.ini switches added to the list

Thanks to Rick Kohler I can add some new Dex.ini switches to my collection. The switches below control the behavior of the HITB (Historical Inventory Trial Balance) tool.

HITB_SkipReconciles=TRUE	Enables Step 2 without having to complete all of the reconciles. Thanks to Rick Kohler for this switch.
HITB_SkipErrorChecking=TRUE	Enables Step 3 regardless of whether errors exist during the data integrity checks. Thanks to Rick Kohler for this switch.
HITB_SkipClearingTransactions =TRUE	Allows user to perform Step 5 regardless of whether balances still exist in the inventory accounts. Thanks to Rick Kohler for this switch.
HITB_SkipVersionChecks=TRUE	Allows the user to enter the window if on a version previous to build 1061. Thanks to Rick Kohler for this switch.
HITB_DebugFile=c:\somefile.txt	Used to create a detailed debug log of the reset dictionary (Dynamics GP must be re-launched to take effect):

Using Copy and Paste in a GP Macro

What if you want to copy and paste using a GP Macro? You can easily do it, but there’s a little trick to it. Let’s learn how!

The requirement was to create a bunch of manual payments with the User ID of who entered the payment being entered into the comment field. While there are many ways to accomplish this task, the client determined that using a mail-merge macro would be the best way to go.

Since the user running the macro is not static, the User ID needed to be copied from the GP Welcome window. The relevant part of the recorded macro is shown below. The copy and paste lines are highlighted.

CommandExec dictionary 'default'  form 'Command_System' command UserID
NewActiveWin dictionary 'default'  form Login window Login
  CommandExec form BuiLtin command cmdEditCopy 
  MoveTo field 'Cancel Button'
  ClickHit field 'Cancel Button'
NewActiveWin dictionary 'default'  form sheLL window sheLL
ActivateWindow dictionary 'default'  form 'PM_Manual_Payments' window 'PM_Manual_Payments'
  CommandExec form BuiLtin command cmdEditPaste
  TypeTo field 'Transaction Description' , 'sa'

Instead of actually performing the standard copy and paste, the macro hard-coded what it copied and retains that value forever. That’s not what they want, they need the recorded User ID to change dynamically according to whomever is logged in.

The solution is simple. When you record your macro, choose ‘Select All’ from the Edit menu and then select ‘Copy’. Do the same when you go to paste and the macro will perform the copy and paste function correctly. Here’s what the new macro code looks like after the change:

  CommandExec dictionary 'default'  form 'Command_System' command UserID
NewActiveWin dictionary 'default'  form Login window Login
  CommandExec form BuiLtin command cmdEditSelectAll
  CommandExec form BuiLtin command cmdEditCopy
  MoveTo field 'Cancel Button'
  ClickHit field 'Cancel Button'
NewActiveWin dictionary 'default'  form sheLL window sheLL
ActivateWindow dictionary 'default'  form 'PM_Manual_Payments' window 'PM_Manual_Payments'
  MoveTo field 'User Defined 1'
  CommandExec form BuiLtin command cmdEditSelectAll
  CommandExec form BuiLtin command cmdEditPaste
  MoveTo field 'User Defined 2'

Hopefully this little tip will help you record more dynamic macros.

Until next post!

Leslie

Convergence 2014 ! Pre-pre game–Atlanta

Left for Atlanta this morning (March 1st, 2014). It was cold and foggy in Dallas, so foggy that I had to slow down to read the signs on the highway. Still, I got to the airport on time, to my surprise I was ushered through the TSA Pre-check line. This pre-check deal is the way to go! I got to leave my shoes on, my laptop in its case and my quart-sized Ziploc bag full of toothpaste in my carryon luggage.

As I was walking through the DFW airport on my way to the gate, I noticed an odd product for sale:

‘N-Tune’ reminded be of a Microsoft product I worked with when it was in beta:

I thought then that the name was similar to:

What will be next?? 

 

I boarded one of the newest American Airlines Airbus planes. Wow! The seatback in front of me had an entertainment system, a dedicated three-prong plug and powered USB port. There was even an airbag stuffed into my seatbelt. Take a look: 

For now, the entertainment system is free, that will change in the future. In the mean time, if you have a chance to book your flight on one of the new Airbus planes, do it.

I had another surprise at the Atlanta airport. They have free payphones! How can a ‘pay’ phone be free? But still, you could call anywhere free for 5 minutes:

 

Cab fare from the airport to the convention center is a fixed fare of $40. I opted for the airport shuttle. You can get a round-trip ticket to/from convention center hotels for $29 or $16.50 one way. To take advantage of the shuttle, head towards ‘Ground Transportation’ to get your ticket. It’s a short wait and I was the only passenger. My day was still going well.

A quick trip on the shuttle, not too much traffic:

At last, I had arrived. I’m staying at the Omni; Sheila Jefferson-Ross will be my roomie. We are in room 1005, if anyone want's to call .

It’s my kind of room – near the elevator. Also, look at this great view right outside my window:

The Ferris wheel is a light show at night – pretty cool, lots of colors. I’ve never had a ride on a Ferris wheel, perhaps this will be the time!

Convergence will be a real blockbuster this year, as always. More to come!

Until next post!

Leslie