Concatenate Multiple Rows

[spersad]

Hi all:

I have a spreadsheet with 5500 rows. I am looking to join certain rows by a unique identifier. My column layout is as follows:

Account Name Current Investments
foo1 PE
foo1 FoHF
foo1 HF
foo2 HF
foo3 RE
foo3 PE

What I would like for my data upload is:

Account Name Current Investments
foo1 PE, FoHF, HF
foo3 RE, PE

Where the unique identifer = Account Name. Is there a way to do this with the CONCATENATE and IF functions in excel ?


Thanks in advance for the help.


Lenee

 

[spersad]

Hi Hiker95,

That worked! Thank you so much for helping me. I am going to try on some more of my large datasets and come back with questions. I learned a lot with your code - am a begginner.


Cheers,

Leneee

 

[hiker95]

spersad,

Below is an updated macro that eliminates the aa loop. Thank you VoG.


Screenshots would be the same as my last reply.


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Option Explicit
Sub ReorgDataV4()
' hiker95, 04/19/2011
' http://www.mrexcel.com/forum/showthread.php?t=544187
Dim w1 As Worksheet, wR As Worksheet
Dim LR As Long, a As Long, SR As Long, ER As Long
Application.ScreenUpdating = False
Set w1 = Worksheets(1)
If Not Evaluate("ISREF(Results!A1)") Then Worksheets.Add(After:=w1).Name = "Results"
Set wR = Worksheets("Results")
wR.UsedRange.Clear
w1.Columns(1).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=wR.Columns(1), Unique:=True
wR.Range("B1:V1").Value = w1.Range("B1:V1").Value
LR = wR.Cells(Rows.Count, 1).End(xlUp).Row
For a = 2 To LR Step 1
  SR = Application.Match(wR.Cells(a, 1), w1.Columns(1), 0)
  ER = Application.Match(wR.Cells(a, 1), w1.Columns(1), 1)
  w1.Rows(SR).Copy wR.Rows(a)
  If SR <> ER Then wR.Cells(a, 2) = Join(Application.Transpose(w1.Range("B" & SR & ":B" & ER)), ", ")
Next a
wR.Columns("A:B").AutoFit
wR.Activate
Application.ScreenUpdating = True
End Sub

Before you run the macro, save your workbook, Save As, a macro enabled workbook.



Then run the ReorgDataV4 macro.