PDA

View Full Version : [SOLVED] Arrangement of values in groups



Mati44
02-02-2018, 11:45 AM
Hi Everybody,

I have a set of values, each start with a letter (either A,B,C or D), and a number after that as can be seen in the example worksheet attached. I want to group them according to the first letter they start with. I made an example in the attached worksheet. There are some formulas for this type of work but they don't work in this case. I'd really appreciate any help. Thanks!!


21517

21520

Mati44
02-02-2018, 01:12 PM
any ideas, please?

Paul_Hossler
02-02-2018, 01:16 PM
Not elegant



Option Explicit

Sub MoveData()
Dim r As Long, c As Long, iStartCol As Long, n As Long

With ActiveSheet
For r = 2 To .Cells(1, 1).CurrentRegion.Rows.Count
For c = 1 To .Cells(1, 1).CurrentRegion.Columns.Count
Select Case Left(.Cells(r, c).Value, 1)
Case "A"
iStartCol = 8
Case "B"
iStartCol = 13
Case "C"
iStartCol = 18
Case "D"
iStartCol = 23
End Select


For n = iStartCol To iStartCol + 5
If Len(.Cells(r, n).Value) = 0 Then
.Cells(r, n).Value = .Cells(r, c).Value
Exit For
End If
Next n
Next c
Next r
End With

End Sub

Mati44
02-02-2018, 01:30 PM
Thanks a lot, Paul! I was expecting a formula but now I will use this. If there is a formula solution coming up, i will be open for that too.