I have a column of data that could have several hundred numerical entries in it with duplicates up to the number 288.

The column to the left is labeled top to bottom 1 through 288.

I'd like to middle column (where the large list resides) to sort and concatenate duplicates separated by a comma in the adjacent cell to its corresponding number. Then in the right side (of three columns) to count the amount of concatenated numbers in the middle column (if any).

Any help is appreciated.

Please see the spreadsheet to visualize what I'm looking for.

Thanks!

test.xlsx
Sub EE_FindDuplicatesCOUNTIF()

Dim fsCount As Long, ssCount As Long, ssCountIF As Long, cellRow As Long

Dim fsRange As Range, ssRange As Range, strResult As String, cell As Range

fsCount = Range("W4").End(xlDown).Ro

ssCount = Range("X4").End(xlDown).Ro

Set fsRange = Range("W4:W" & fsCount)

Set ssRange = Range("X4:X" & ssCount)

For Each cell In ssRange

cell.Select

ssCountIF = Application.WorksheetFunct

cellRow = fsRange.Find(cell.Value, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=True).Row

Select Case (ssCountIF)

Case 0: Cells(cellRow, "y").Value = ""

Cells(cellRow, "z").Value = ""

Case 1: Cells(cellRow, "y").Value = cell.Value

Cells(cellRow, "z").Value = ssCountIF

Case Is > 1: For cv = 1 To ssCountIF

strResult = cell.Value & "," & strResult

Next cv

Cells(cellRow, "y").Value = Left(strResult, Len(strResult) - 1)

Cells(cellRow, "z").Value = ssCountIF

strResult = ""

End Select

Next cell

Range("X4:X" & fsCount).Delete shift:=xlToLeft

Range("W4:Y" & fsCount).HorizontalAlignme

Cells(1, "w").Select

End Sub

This assumes your data is in Columns W and X, starting in Row4. The data in Column X will be overwritten with the results.