Hello,

I have a table containing the documents in each box.
In sheet1,
For example, In A1 there is box number 1 and in B1 "photos".
In A2 there is box number 1 and B2 "letters"
In A3 there is box number 1 and B3 "papers"
In A4 there is box number 2 and B4 "letters"
In A5 there is box number 2 and B5 "photos"

I want to run a macro that paste all the boxes in sheet2:
In A1 I want box number 1 and in B1 I want to concatenate
"photos"&"letters"&"papers"
In A2 I want box number 2 and in B2 I want to concatenate
"letters"&"photos".

The thing is I want to show 1 box per row on sheet2 with datas from sheet1

Thanks.

This is according to my interpretation:

Sub TransferData()
Dim rng As Range
Dim c As Range, cc As Range
Dim ws1 As Worksheet, ws2 As Worksheet
Dim i As Integer
Dim BoxNum As String, txt As String

Set ws1 = Sheets("Sheet1")
Set ws2 = Sheets("Sheet2")
Set rng = Range(ws1.Range("A1"), ws1.Range("A1").End(xlDown))
Set cc = ws2.Range("A1")
BoxNum = rng(1, 1).Value: txt = ""
Application.ScreenUpdating = False
For i = 1 To rng.Count + 1
Set c = rng(i, 1)
If c.Value = BoxNum Then
txt = txt & IIf(i = 1, "", " + ") & c(1, 2).Value
Else
cc.Value = BoxNum
BoxNum = c.Value
cc(1, 2).Value = txt
txt = c(1, 2).Value
Set cc = cc(2, 1)
End If
Next
Application.ScreenUpdating = True
End Sub

Best regards,
Greg Wilson

"Gwen" wrote:

Hello,

I have a table containing the documents in each box.
In sheet1,
For example, In A1 there is box number 1 and in B1 "photos".
In A2 there is box number 1 and B2 "letters"
In A3 there is box number 1 and B3 "papers"
In A4 there is box number 2 and B4 "letters"
In A5 there is box number 2 and B5 "photos"

I want to run a macro that paste all the boxes in sheet2:
In A1 I want box number 1 and in B1 I want to concatenate
"photos"&"letters"&"papers"
In A2 I want box number 2 and in B2 I want to concatenate
"letters"&"photos".

The thing is I want to show 1 box per row on sheet2 with datas from sheet1

Thanks.