Expanding on 'concatenate-maybe-countif-but-how'

[MrCapita]

Dear Masters of Excel,

Expanding on an old thread - http://www.mrexcel.com/forum/excel-questions/356861-concatenate-maybe-countif-but-how.html - I could do with a little help.

Mr Excel offers the macro;

Sub Rearrange()
Dim lr AsLong, r AsLong

Application.ScreenUpdating = False
lr = Range("A" & Rows.Count).End(xlUp).Row
For r = lr To 2 Step -1
With Cells(r, 1)
If .Value = .Offset(-1).Value Then
.Offset(-1, 1).Value = .Offset(-1, 1).Value & ", " _
& .Offset(, 1).Value
.EntireRow.Delete
EndIf
EndWith
Next r
Columns("B").AutoFit
Application.ScreenUpdating = True
EndSub

This works great for combining fields in a single column. However, I need to combine 2 columns, I need to turn this;

Room number	Furniture Type	Count
1	Table	3
1	Chair	3
1	Cat	1
2	Rug	23
3	Planet	1
3	Chair	6

<tbody>
</tbody>

into this;

Room Number
1	Table 3, Chair 3, Cat 1
2	Rug 23
3	Planet 1, Chair 6

<tbody>
</tbody>

Any help at all would be really great...

 

[oldbrewer]

=sumproduct((a1:a6=1)*(B1:b6=table)) returns 3

instead of the 1 and table in that formula, reference row and column headings and then adjust the looks of your output table. Very simple approach.