Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

# Thread: Concatenating Many Columns To One

1. I agreed to these rules
Join Date
5th August 2008
Posts
10

## Concatenating Many Columns To One

Hi All

my problem is that i have a huge number of rows and columns, what i want is to concatenate the data in all the columns to the data in the first one, but separately, this is really complicated, so let us assume that i have the following table

-----A-----B-----C-----D-----E

1 ---98---13----17----19----44
2 ---94---18----20----23
3 ---19---22----245---45----62

so after concatenating the value in A to all the values in the other columns and put the result in a new column we get the following output

-----A
1---- 9813
2---- 9817
3---- 9819
4---- 9844
5---- 9418
6---- 9420
7---- 9423
8---- 1922
9---- 19245
10--- 1945
11--- 1962

also we need to take in consideration the empty cells as they must be skipped during the process, also please find attached my Excel sheet that needs the modification

Excel Video Tutorials / Excel Dashboards Reports

2. ## Re: Concatenating Many Columns To One

Attachment missing.

Excel Video Tutorials / Excel Dashboards Reports

3. I agreed to these rules
Join Date
5th August 2008
Posts
10

## Re: Concatenating Many Columns To One

sorry here a sample of it

Excel Video Tutorials / Excel Dashboards Reports

4. Super Moderator
Join Date
18th November 2004
Location
God's Own Country
Posts
4,648

## Re: Concatenating Many Columns To One

Hi,

VB:
```Sub kTest()
Dim a, w(), i As Long, c As Long, n As Long
a = Range("a1").CurrentRegion
Redim w(1 To Rows.Count, 1 To 1)

For i = 1 To UBound(a, 1)
For c = 2 To UBound(a, 2)
If Not IsEmpty(a(i, c)) Then
n = n + 1: w(n, 1) = a(i, 1) & a(i, c)
End If
Next
Next
With Range("a1")
.CurrentRegion.ClearContents
.Resize(n).Value = w
End With
End Sub

```

5. I agreed to these rules
Join Date
5th August 2008
Posts
10

## Re: Concatenating Many Columns To One

Krishnakumar what can i say...u r the man many thanks mate i really appreciate

Excel Video Tutorials / Excel Dashboards Reports

6. Super Moderator
Join Date
18th November 2004
Location
God's Own Country
Posts
4,648

## Re: Concatenating Many Columns To One

Originally Posted by raed_237
Krishnakumar what can i say...u r the man many thanks mate i really appreciate
You are welcome

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
porno