PDA

View Full Version : aggregate & concatenate



Asi
06-13-2013, 12:40 AM
Hi

I have a table of Employees that has the following columns:
EmpID, Title, SubTitle.
Each Employee can have many Titles and some Titles can have many SubTitles. I refer to it as my "DB Sheet".

As you can understand, this table is in normalized form as usually done in SQL Server ( A row for each EmpID, Title, SubTitle). The reason for this is - I have a "Data Validation" criteria on the Title & SubTitle columns, the values are taken from a list , because I don't want them to be free text (I need it for pivoting later on).

What I want is to create a "Display Sheet" that will flat the data, meaning - A row for eac EmpID, Title and I want the SubTotal values to be concatenated.

For example:
If the "DB Sheet" has the following rows:

EmpID Title SubTitle 1 T1 ST11 1 T1 ST12 1 T1 ST13 1 T2 ST21 1 T2 ST22

I want the "Display Sheet" to look like this:
EmpID Title SubTitle 1 T1 ST11, ST12, ST13 1 T2 ST21, ST22

Any Ideas of how to aggregate & concatenate

Asi
06-13-2013, 12:45 AM
Since the example is not clear enough, I will try to type it again:

EmpIDTitleSubTitle
1 T1 ST11
1 T1 ST12
1 T1 ST13
1 T2 ST21
1 T2 ST22


transform it to this:

EmpIDTitleSubTitle
1 T1 ST11, ST12, ST13
1 T2 ST21, ST22


Thanks
Asi

snb
06-13-2013, 02:24 AM
Please post a sample workbook.

Asi
06-13-2013, 04:22 AM
Agg Concat Ex.xlsx (http://www.vbaexpress.com/forum/attachment.php?attachmentid=10143&stc=1&d=1371122422) file attached.

Thanks
Asi

Asi
06-16-2013, 10:48 AM
Does anyone have any idea ?

shrivallabha
06-17-2013, 01:57 AM
Does anyone have any idea ?
Here's one :sleuth:

Public Sub AggreConcat()
Dim strChk As String
Dim ws As Worksheet

Application.DisplayAlerts = False

With CreateObject("Scripting.Dictionary")

Set ws = Sheets("DB Sheet")
For i = 2 To ws.Range("A" & Rows.Count).End(xlUp).Row
strChk = ws.Range("A" & i) & "|" & ws.Range("B" & i).Value
If .exists(strChk) Then
.Item(strChk) = .Item(strChk) & ", " & ws.Range("C" & i).Value
Else
.Add strChk, ws.Range("C" & i).Value
End If
Next i

Set ws = Sheets("Display Sheet")
ws.Range("A2").Resize(.Count, 1).Value = Application.Transpose(.keys)
ws.Range("A2").Resize(.Count, 1).TextToColumns Destination:=[A2], _
DataType:=xlDelimited, Other:=True, OtherChar:="|"
ws.Range("C2").Resize(.Count, 1).Value = Application.Transpose(.items)

End With

Application.DisplayAlerts = True

End Sub