Mass concatenation, varying number of rows per instance

[bikeking8]

I have a spreadsheet with 35,000+ rows I need to analyze. Specifically, I need to find which departments have similar values as well as how similar they are. What I've come up with is the generation of a concatenated field so I can quickly use excel to analyze it all. I can't seem to figure this out, though, since the number of departments on each floor differs as demonstrated in the example. Is there any way to do this without going through all 35,000 rows and using the "=A1:A6" [F9 Button] technique?

[TABLE="width: 500"]
<TBODY>[TR]
[TD="align: center"]DEPARTMENT[/TD]
[TD="align: center"]VALUE CATEGORY[/TD]
[TD="align: center"]VALUE[/TD]
[TD="align: center"]DESIRED OUTCOME[/TD]
[/TR]
[TR]
[TD="align: center"]Dept 1 Flr A[/TD]
[TD="align: center"]Value 1[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"]YNNY[/TD]
[/TR]
[TR]
[TD="align: center"]Dept 1 Flr A[/TD]
[TD="align: center"]Value 2[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]Dept 1 Flr A[/TD]
[TD="align: center"]Value 3[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]Dept 1 Flr A[/TD]
[TD="align: center"]Value 4[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]Dept 2 Flr A[/TD]
[TD="align: center"]Value 1[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"]YYNN[/TD]
[/TR]
[TR]
[TD="align: center"]Dept 2 Flr A[/TD]
[TD="align: center"]Value 2[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]Dept 2 Flr A[/TD]
[TD="align: center"]Value 3[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]Dept 2 Flr A[/TD]
[TD="align: center"]Value 4[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]Dept 3 Flr B[/TD]
[TD="align: center"]Value 5[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]GHH[/TD]
[/TR]
[TR]
[TD="align: center"]Dept 3 Flr B[/TD]
[TD="align: center"]Value 6[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]Dept 3 Flr B[/TD]
[TD="align: center"]Value 7[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]Dept 4 Flr B[/TD]
[TD="align: center"]Value 5[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]GHG[/TD]
[/TR]
[TR]
[TD="align: center"]Dept 4 Flr B[/TD]
[TD="align: center"]Value 6[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]Dept 4 Flr B[/TD]
[TD="align: center"]Value 7[/TD]
[TD="align: center"]G[/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]

 

[bikeking8]

I figured out a way to do it via SQL query. I'd still be open to suggestions on how to do this in excel, though.

 

[sheetspread]

Excel 2010
	A	B	C
1	DEPARTMENT	VALUE CATEGORY	VALUE
2	Dept 1 Flr A	Value 1	Y
3	Dept 1 Flr A	Value 2	N
4	Dept 1 Flr A	Value 3	N
5	Dept 1 Flr A	Value 4	Y
6	Dept 2 Flr A	Value 1	Y
7	Dept 2 Flr A	Value 2	Y
8	Dept 2 Flr A	Value 3	N
9	Dept 2 Flr A	Value 4	N
10	Dept 3 Flr B	Value 5	G
11	Dept 3 Flr B	Value 6	H
12	Dept 3 Flr B	Value 7	H
13	Dept 4 Flr B	Value 5	G
14	Dept 4 Flr B	Value 6	H
15	Dept 4 Flr B	Value 7	G
Sheet5 (2)

Sub concatgroups()
Dim x As Integer
Dim y As Integer
Dim lr As Integer
Dim groupstr As String
lr = Cells(Rows.Count, 1).End(xlUp).Row
groupstr = ""
y = 0
For x = 2 To lr
y = y + 1
groupstr = groupstr & Cells(x, 3).Value
If Cells(x, 1).Value <> Cells(x + 1, 1).Value Then
Cells(x - y + 1, 4).Value = groupstr
groupstr = ""
y = 0
Else
End If
Next
End Sub

Excel 2010
	A	B	C	D
1	DEPARTMENT	VALUE CATEGORY	VALUE
2	Dept 1 Flr A	Value 1	Y	YNNY
3	Dept 1 Flr A	Value 2	N
4	Dept 1 Flr A	Value 3	N
5	Dept 1 Flr A	Value 4	Y
6	Dept 2 Flr A	Value 1	Y	YYNN
7	Dept 2 Flr A	Value 2	Y
8	Dept 2 Flr A	Value 3	N
9	Dept 2 Flr A	Value 4	N
10	Dept 3 Flr B	Value 5	G	GHH
11	Dept 3 Flr B	Value 6	H
12	Dept 3 Flr B	Value 7	H
13	Dept 4 Flr B	Value 5	G	GHG
14	Dept 4 Flr B	Value 6	H
15	Dept 4 Flr B	Value 7	G
Sheet5 (2)