We help IT Professionals succeed at work.

Excel array to concat some values

oncall4you used Ask the Experts™
I am facing a challenge that I can't seem to get my head around.  I need to create an array in excel that takes the values from two columns, cast them into strings, concatenate the strings, and then sort in numeric order to delete duplicates.  

I can do the delete duplicates via vb code
For x = 1 To counter
            a = ActiveSheet.Range("h" & x).Value
          'MsgBox ("a=" & a)
            y = x + 1
            c = ActiveSheet.Range("h" & y).Value
       ' MsgBox ("C=" & c)
        If a = c Then
            If a = "" Then
                GoTo err:
            End If
            x = x - 1
        End If

What I have currently tried is having a cell with =h1&g1 but since the values are numeric it is messing it up rather than concatenating the columns.  For example 41638 and 4 are resulting in 414110.  I can't see how that is happening, so I figured casting would fix it.

Any ideas?
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
do you have your variables defined?  DIM?

convert them before concatenating.  So that you make sure you get the values you need.

Then because they aren't "numbers" hopefully you won't see the scrambling.

Mechanical Engineer
Most Valuable Expert 2013
Top Expert 2013
If you use the .Text rather than .Value property of a cell, you will capture the value that is actually displayed as a string rather than a number in base 2. This should allow you to do a more reliable concatenation.

If you don't specify .Text or .Value, then VBA defaults to .Value.


That got my value for me thanks.