Combine multiple rows into a single row

[kyddrivers]

Here is a sample table of the data we need to combine into single rows of data based on the office.

	Measure 1	Measure 2	Measure 3	Measure 4	Measure 5	Measure 6	Measure 7	Measure 8	Measure 9
Office 1	8950			8950			8950
Office 1		3732			3732			3732
Office 1			8045			8045			8045
Office 2	6728		6728		6728		6728		6728
Office 2		6681		6681		6681		6681
Office 3			2712			2712			2712
Office 3				4814				4814
Office 3		7494			7494		7494
Office 4		2137		2137		2137		2137
Office 4			4515		4515		4515		4515
Office 5	2585		2585	1603		1603	2585	1603

The number of measures will vary from project to project
The number of times an office repeats can vary, sometimes they will not repeat
Some offices may not have a value for each measure, but will not have more than 1 value for each measure

The ultimate goal is to combine all of an office's measure into a single line. Example: Office 5

Thank you in advance for your help!!

 

[sandy666]

something like this?

 

[kyddrivers]

Guess I was thinking of a vba script, but a pivot table may work

 

[sandy666]

first, use Power Query

// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Unpivot = Table.UnpivotOtherColumns(Source, {"Office"}, "Attribute", "Value")
in
    Unpivot

second, create Pivot from this Query

but if you want vba maybe someone else will give you that

 

[Peter_SSs]

Assuming ..
- One office never has more than one entry in a measure column then try
- There is nothing below this data and nothing in the column immediately to the right
.. try

Sub CombineOffices()
  Dim a As Variant
  Dim i As Long, j As Long, k As Long
  
  a = Range("A1").CurrentRegion.Value
  k = 1
  For i = 2 To UBound(a)
    If a(i, 1) <> a(i - 1, 1) Then k = k + 1
    For j = 1 To UBound(a, 2)
      If Len(a(i, j)) > 0 Then a(k, j) = a(i, j)
    Next j
  Next i
  Range("A" & Rows.Count).End(xlUp).Offset(2).Resize(k, UBound(a, 2)).Value = a
End Sub

My sample data & results:

Book1
	A	B	C	D	E	F	G	H	I	J
1		Measure 1	Measure 2	Measure 3	Measure 4	Measure 5	Measure 6	Measure 7	Measure 8	Measure 9
2	Office 1	8950			8950			8950
3	Office 1		3732			3732			3732
4	Office 1			8045			8045			8045
5	Office 2	6728		6728		6728		6728		6728
6	Office 2		6681		6681		6681		6681
7	Office 3			2712			2712			2712
8	Office 3				4814				4814
9	Office 3		7494			7494		7494
10	Office 4		2137		2137		2137		2137
11	Office 4			4515		4515		4515		4515
12	Office 5	2585		2585	1603		1603	2585	1603
13
14		Measure 1	Measure 2	Measure 3	Measure 4	Measure 5	Measure 6	Measure 7	Measure 8	Measure 9
15	Office 1	8950	3732	8045	8950	3732	8045	8950	3732	8045
16	Office 2	6728	6681	6728	6681	6728	6681	6728	6681	6728
17	Office 3		7494	2712	4814	7494	2712	7494	4814	2712
18	Office 4	6728	2137	4515	2137	4515	2137	4515	2137	4515
19	Office 5	2585	6681	2585	1603		1603	2585	1603
Combine Offices

 

[sandy666]

C12 vs C19 ?

 

[Peter_SSs]

C12 vs C19 ?

Thanks. There some others too. Trying to be too clever.

Hopefully this is better. ?

Sub CombineOffices_v2()
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, k As Long, lr As Long
  
  a = Range("A1").CurrentRegion.Value
  ReDim b(1 To UBound(a, 1), 1 To UBound(a, 2))
  For i = 2 To UBound(a)
    If a(i, 1) <> a(i - 1, 1) Then k = k + 1
    For j = 1 To UBound(a, 2)
      If Len(a(i, j)) > 0 Then b(k, j) = a(i, j)
    Next j
  Next i
  lr = Range("A" & Rows.Count).End(xlUp).Row
  Range("A" & lr + 2).Resize(, UBound(a, 2)).Value = a
  Range("A" & lr + 3).Resize(k, UBound(a, 2)).Value = b
End Sub

Book1
	A	B	C	D	E	F	G	H	I	J
1		Measure 1	Measure 2	Measure 3	Measure 4	Measure 5	Measure 6	Measure 7	Measure 8	Measure 9
2	Office 1	8950			8950			8950
3	Office 1		3732			3732			3732
4	Office 1			8045			8045			8045
5	Office 2	6728		6728		6728		6728		6728
6	Office 2		6681		6681		6681		6681
7	Office 3			2712			2712			2712
8	Office 3				4814				4814
9	Office 3		7494			7494		7494
10	Office 4		2137		2137		2137		2137
11	Office 4			4515		4515		4515		4515
12	Office 5	2585		2585	1603		1603	2585	1603
13
14		Measure 1	Measure 2	Measure 3	Measure 4	Measure 5	Measure 6	Measure 7	Measure 8	Measure 9
15	Office 1	8950	3732	8045	8950	3732	8045	8950	3732	8045
16	Office 2	6728	6681	6728	6681	6728	6681	6728	6681	6728
17	Office 3		7494	2712	4814	7494	2712	7494	4814	2712
18	Office 4		2137	4515	2137	4515	2137	4515	2137	4515
19	Office 5	2585		2585	1603		1603	2585	1603
Combine Offices

 

[sandy666]

There some others too

if one element is wrong so whole result is wrong,
now it seems to be good