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;

[TABLE="width: 500"]
<tbody>[TR]
[TD]Room number[/TD]
[TD]Furniture Type[/TD]
[TD]Count[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Table[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Chair[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Cat[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Rug[/TD]
[TD]23[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Planet[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Chair[/TD]
[TD]6[/TD]
[/TR]
</tbody>[/TABLE]

into this;[TABLE="width: 500"]
<tbody>[TR]
[TD]Room Number[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Table 3, Chair 3, Cat 1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Rug 23[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Planet 1, Chair 6[/TD]
[/TR]
</tbody>[/TABLE]


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.