Pasting Information from Multiple Cells

[Guest]

Hi,

I have a report that shows all open purchase order lines by part number. The
information shows the estimated delivery date and quantity. Some part numbers
have multiple lines due to different arrival dates. For each Part number I
would like to paste all the delivery dates into one cell on another
spreadsheet. Is this possible? If so how do I do it?

Thank you,

 

[Guest]

I believe a User Defined Function (UDF) would work for this situation. Place
the code for the UDF into a code module and then use it in cells just as you
would any other worksheet function. This code presumes that your source list
is on 'Sheet1' and that the part numbers are in column A, with delivery dates
in column B. Change code to fit reality.

Private Function BuildLongList(TestValue As Variant)
Dim EndOfList As Long
Dim SourceRange As Range
Dim LC As Long

Application.Volatile
Set SourceRange = Worksheets("Sheet1").Range("A1")
EndOfList = Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
Do While LC <= EndOfList
If SourceRange.Offset(LC, 0) = TestValue Then
BuildLongList = BuildLongList & SourceRange.Offset(LC, 1) & ", "
End If
LC = LC + 1
Loop
If Right(BuildLongList, 2) = ", " Then
BuildLongList = Left(BuildLongList, Len(BuildLongList) - 2)
End If
Set SourceRange = Nothing ' release
End Function

Then in a cell on the other sheet (or same one) where you want the list to
appear enter a formula similar to this:
=BuildLongList('Sheet1'!A1) ' collate all dates for P/N in A1 on Sheet1