# Consolidate and Concatenate by Creating Columns

1. ## Re: Consolidate and Concatenate by Creating Columns

If I understand correctly the sample source having one record of 31 relatives/friends would require 93 columns?

In column D IDs are copied/pasted and the applied 'Remove duplicates'.

In E2 filled down until IDs are exhausted and across to column CY this formula.
Formula:
`Please Login or Register  to view this content.`

2. ## Re: Consolidate and Concatenate by Creating Columns

Hi again, andytaylordesigns!

Check new code:
PHP Code:
``` Sub ReOrdData()     Dim a, r, c&, d&, s&, q&, f\$, m&, i&, j&           a = Range("A1").CurrentRegion.Value     s = Range("D" & Rows.Count).End(xlUp).Row           f = "frequency(D2:D" & s & ",D2:D" & s & ")"     q = Evaluate("sum(n(" & f & ">0))"): m = Evaluate("max(" & f & ")")     ReDim r(1 To q, 1 To 7 + 3 * m)           For i = 1 + LBound(a) To UBound(a)         If a(i, 4) <> a(i - 1, 4) Then             d = 1 + d: c = 7             For j = 1 To 7                 r(d, j) = a(i, j)             Next j         End If         For j = 8 To 10             c = 1 + c: r(d, c) = a(i, j)         Next j     Next i           With Range("M1")         .CurrentRegion.Clear         Range("A1:G1").Copy Range("M1")         With .Offset(, 7)             .Resize(, 3) = [{"relation 1", "relation name 1", "dod 1"}]             .Resize(, 3).AutoFill .Resize(, 3 * m)         End With         With .Resize(, 7 + 3 * m)             .Font.Bold = True             .Interior.Color = RGB(180, 180, 180)         End With         .Offset(1).Resize(q, 7 + 3 * m) = r         .CurrentRegion.Columns.AutoFit     End With           Erase a, r End Sub  ```
Check file. Blessings!

3. ## Re: Consolidate and Concatenate by Creating Columns

@johnmpl Runtime Error 9 Subscript out of range

4. ## Re: Consolidate and Concatenate by Creating Columns

Originally Posted by andytaylordesigns
@johnmpl Runtime Error 9 Subscript out of range
Which line have errors? I try the code above, just press the arrow, and work all fine...

5. ## Re: Consolidate and Concatenate by Creating Columns

Originally Posted by johnmpl
Which line have errors? I try the code above, just press the arrow, and work all fine...
It works with those few rows, but when I try the script with all 35,000 rows, it gives me that error.

6. ## Re: Consolidate and Concatenate by Creating Columns

Originally Posted by andytaylordesigns
It works with those few rows, but when I try the script with all 35,000 rows, it gives me that error.

7. ## Re: Consolidate and Concatenate by Creating Columns

Originally Posted by johnmpl
I'm sorry I can't, it's proprietary information.

8. ## Re: Consolidate and Concatenate by Creating Columns

Originally Posted by andytaylordesigns
I'm sorry I can't, it's proprietary information.
Change some sensible data, and send it. It's hard to say what's going on without see your file.

9. ## Re: Consolidate and Concatenate by Creating Columns

Try something: Sort your data by ID column (Column D) and run the code again, because this code works when the data is sorted by ID. Blessings!

Page 2 of 2 First 1 2

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