Combine multiple cell values when same ID value is found in sheet

[Patty3-4]

Good afternoon,

I'm requesting help from the brilliant minds on this forum for my newest project.

I need to combine column D (CATEGORY) into a column E (FINAL CATEGORY), WHEN the column A (ID NUMBER) is the same, and WHEN column D (CATEGORY) are different.

If column D (CATEGORY) is the same for the same ID NUMBER, the CATEGORY is to only show once.
Any one ID NUMBER can have an unlimited number of rows. Usually the data has about 10k rows of data.

My final result will be a pivoted summary of column E (FINAL CATEGORY), which will show me the final categories created and number of people (COUNT) in them.

Any help you could provide, I/we'd truly really appreciate it.

 

[HaHoBe]

Hi Patty3-4,

maybe use a procedure like

Public Sub MrE_1225755_1616D12()
' https://www.mrexcel.com/board/threads/combine-multiple-cell-values-when-same-id-value-is-found-in-sheet.1225755/
' Created: 20221231
' By:      HaHoBe

'/// PLEASE NOTE !!!!
'/// Please note: ALWAYS run the macros on a copy of your Workbook !!!!
'/// PLEASE NOTE !!!!

Dim arr
Dim lngCounter As Long
Dim lngStart As Long
Dim strTemp As String
Dim lngArr As Long
Dim lngLast As Long

Worksheets("Data").Copy after:=Sheets(Sheets.Count)
ActiveSheet.Name = "Data" & Format(Now, "_yymmdd_hhmmss")

lngLast = Cells(Rows.Count, 1).End(xlUp).Row

arr = Range("A2:F" & lngLast).Value
lngStart = LBound(arr, 1)
For lngCounter = LBound(arr, 1) + 1 To UBound(arr, 1)
  If arr(lngCounter, 1) <> arr(lngCounter - 1, 1) Then
    If Right(strTemp, 1) = "_" Then strTemp = Left(strTemp, Len(strTemp) - 1)
    For lngArr = lngStart To lngCounter
      arr(lngArr, 5) = strTemp
    Next lngArr
    arr(lngStart, 6) = 1
    lngStart = lngCounter
    strTemp = arr(lngCounter, 4) & "_"
  Else
    If InStr(1, strTemp, arr(lngCounter - 1, 4)) = 0 Then
      strTemp = strTemp & arr(lngCounter - 1, 4) & "_"
    End If
    If InStr(1, strTemp, arr(lngCounter, 4)) = 0 Then
      strTemp = strTemp & arr(lngCounter, 4) & "_"
    End If
  End If
Next lngCounter

If Right(strTemp, 1) = "_" Then strTemp = Left(strTemp, Len(strTemp) - 1)
For lngArr = lngStart To UBound(arr, 1)
  arr(lngArr, 5) = strTemp
Next lngArr
arr(lngStart, 6) = 1

Range("A2:F" & lngLast).Value = arr
End Sub

Please alter the name of the worksheet holding the data to suit.

Ciao,
Holger

 

[Patty3-4]

Good morning! Thank you! I'm not too familiar with VBA but this went in and worked great! This will make the weekly stat report 100% easier. I like how it creates another sheet too. Just beautiful!

 

[Peter_SSs]

Just want to check if the Category values will always occur in the same order and just what your requirement is.
For example, I ran the above code on the sample data shown in columns A, D and achieved these results. The two coloured sections are made up of exactly the same Category values but would not get combined if you make a Pivot Table or otherwise do a count. Is that what you want or should those two coloured sections show the three categories in the same order as each other?

Patty3-4.xlsm
	A			D	E	F
1	ID			Category	Final	Count
2	934			Yellow	Yellow	1
3	934			Yellow	Yellow
4	934			Yellow	Yellow
5	45038			Green	Green_Yellow_Pink	1
6	45038			Yellow	Green_Yellow_Pink
7	45038			Yellow	Green_Yellow_Pink
8	45038			Pink	Green_Yellow_Pink
9	328			Green	Green	1
10	44			Pink	Pink_Green_Yellow	1
11	44			Green	Pink_Green_Yellow
12	44			Yellow	Pink_Green_Yellow
13	6666			Green	Green_Yellow	1
14	6666			Yellow	Green_Yellow
15	6666			Yellow	Green_Yellow
16	6666			Green	Green_Yellow
Data_230105_093733