A while ago I posted a question about a macro that expands data across multiple rows. The post can be found at the following url:
I attached an excel file with sample data in the above mentioned post. Sheet 1 contains 2 rows (excluding the header row). The first row contains 3 colours in Column B and the second row contains 6 colours in Column B. If you look at Sheet 2, you can see that there are now 9 rows, ie. 3 for the first SKU (in sheet 1) and 6 for the second SKU (in sheet 1) and each of the colours from column B is now separated out onto its own row.
In order to process sheet 1 so that it looks like sheet 2, I was given the following macro which works great:
For Idx = Range("A" & Rows.Count).End(xlUp).Row To 2 Step -1
Set c = Cells(Idx, 7)
If InStr(1, c, "|") > 0 Then
Colors = Split(c, "|")
NrColors = UBound(Colors)
Rows(c.Row + 1 & ":" & c.Row + NrColors).Insert Shift:=xlDown
For Idx1 = 0 To NrColors
c.Offset(Idx1) = Colors(Idx1)
However I now need the opposite to happen, ie. the data in sheet 2 is the original data and I need it to be collapsed into the data of sheet 1.
I wondered if it was possible to revise the above macro so that it does the opposite as described above?
I’d really be grateful for any advice.
Thanks in advance.