How to combine values from multiple rows into a single row with a comma separator
I have an Excel file that lists product SKUs in one culumn and product image URLs in a another column. My problem is that the file has multiple rows for the same product...
...when the CMS I use on my shop can only read imports like this:
I would do it manually but there are hundreds of products, each with multiple image links. Basically, I need to have only one SKU001 (SKU002, SKU003, etc.) and merge its corresponding B column cells into one cell with all values separated by a comma. Is there any way to do this?
2 Answers
Say we have data in Sheet1. The SKUs are in column A and B can contain any data. The SKUs are sorted:
The following macro will produce re-organized data in Sheet2:
Sub ReOrganizer() Dim s1 As Worksheet, s2 As Worksheet Dim N As Long, i As Long, K As Long Dim v1 As String, v2 As String Set s1 = Sheets("Sheet1") Set s2 = Sheets("Sheet2") K = 1 N = Cells(Rows.Count, "A").End(xlUp).Row v1 = s1.Cells(2, 1).Value v2 = s1.Cells(2, 2).Value For i = 3 To N vn1 = s1.Cells(i, 1).Value vn2 = s1.Cells(i, 2).Value If vn1 = v1 Then v2 = v2 & "," & vn2 Else s2.Cells(K, 1) = v1 s2.Cells(K, 2) = v2 v1 = vn1 v2 = vn2 K = K + 1 End If Next i s2.Cells(K, 1) = v1 s2.Cells(K, 2) = v2
End SubThink I have a no macro solution
Which filters to
in C2 type =IF(A2=A1,C1&","&B2,B2)
Then in D2 type =COUNTIF(C3,B3)
And fill down to one row below your table
The C col concatenates with the string above if the same product, or starts a new chain if a new product
The D col finds the end of a chain (by checking if the row below is a new chain)
Please try this and tell me if it works :)
4
