Transposing data until column in row is unique.

[digitaltoast]

Sorry for the vague title. Let's start with an example. I have in columns A and B:

Please Login or Register  to view this content.

I need to add column C of the sizes per costume and remove the duplicates.

Please Login or Register  to view this content.

As usual, I've tried to fix this myself. I know how to find, use and remove the last letter (the SIZE: S,M,L) from column A.
I know how to use conditional formatting to find unique or duplicate rows and then filter on that formatting.
I've found functions like "UNIQUE" and "TRANSPOSE" in other applications like Google Spreadsheets (it doesn't matter if this is done in Excel or not).

Where I'm stuck is finding a combination of functions for the following:

Where text in column B (OR the first 5 numbers of column A) of row 2 of is the same as row 1, cut or copy the last character of the text in row 2 of column A, into row 1 and concatenate with a comma.
Repeat while the next row is the same as row 1.

[jraj1106]

Hi...
This code will extract the last character for SIZE and place it in col C.

Sub comparevals()
Dim rcnt As Long, cnt As Long

rcnt = Range("A" & Rows.Count).End(xlUp).Row
cnt = 1
j = 2

For i = 1 To rcnt
If Range("B" & i).Value = Range("B" & j).Value Then
If Range("C" & cnt).Value = "" Then
Range("C" & cnt).Value = Right(Range("A" & i).Value, 1) & "," & Right(Range("A" & j).Value, 1)
Else
Range("C" & cnt).Value = Range("C" & cnt).Value & "," & Right(Range("A" & j).Value, 1)
End If
Else
cnt = cnt + 1
End If
j = j + 1
Next
End Sub

[digitaltoast]

Thank you - I've tried it, but I couldn't quite get it to work for me. It only seemed to do the first 5 rows.

I found that later in the data, there were sizes like XL and XS, meaning 2 size codes, which didn't work either.

But I also found that there were only ever a maximum of 3 and a minimum of 2 sizes per item.

I decided to have a bash with plain formulas, and after bashing my head against the wall with Excel's hopeless "one line" formula editor, I tried LibreOffice's formula builder to build a formula, and then copied it back into Excel.

So, this is what I ended up with - it's not pretty but it does the job and steers clear of VBA

IF(AND(LEFT(A2,5)=LEFT(A3,5),LEFT(A2,5)=LEFT(A4,5)),CONCATENATE(MID(A2,6,2),",",MID(A3,6,2),",",MID(A4,6,2)),CONCATENATE(MID(A2,6,2),",",MID(A3,6,2)))

Incidentally, I don't mean to sound ungrateful - what you did set me on the right track with what I could use as pseudocode to build a formula.