Re: Comma delimited list and rows into columns
Is that means that for ref U12_ we will never have more than 9 references: from U12_1 to U12_9
Can exist such reference: U12_4-U13_2
Re: Comma delimited list and rows into columns
Is that means that for ref U12_ we will never have more than 9 references: from U12_1 to U12_9
Can exist such reference: U12_4-U13_2
Re: Comma delimited list and rows into columns
sry PCI but what do u mean?
I can see values such as U12_4-U12_7
and there are some occurences of U123_4-U123_7
what do u mean by 9 places?
Re: Comma delimited list and rows into columns
Can exist reference like U12_4-U12_11 it means we have to prepare place numbers:
U12_4
U12_5
...
U12_10
U12_11
Can exist reference like U12_4-U13_2 it means we have to prepare place numbers:
U12_4
U12_5
U12_6
...
U12_9
U13_1
U13_2
Re: Comma delimited list and rows into columns
oh...it doesn't run over 9...so it would just be something like:
U123_4-U123_7:
U123_4
U123_5
U123_6
U123_7
thanks!
Re: Comma delimited list and rows into columns
btw, i can also have sometihng like U12A_4-U12A_7
Re: Comma delimited list and rows into columns
Try and double check
Code not optimized but simple to understand
Run macro in this order (or group them)
COMMA_Treat()
HYPHEN_Treat()
FINAL_Treat()
Sub HYPHEN_Treat()
Dim LastRow As Long
Dim NbPlace1 As Integer, NbPlace2 As Integer
Dim FirstNb As Integer, LastNb As Integer
Dim I As Long, J As Long
Dim Place
Dim TEMP
Dim PlaceRef As String
Application.ScreenUpdating = False
LastRow = Range("A" & Rows.Count).End(xlUp).Row
For I = LastRow To 3 Step -1
Place = Cells(I, "C")
NbPlace2 = Len(Place) - Len(Replace(Place, "-", ""))
If (NbPlace2 > 0) Then
'========= REFERENCE WITH HYPHEN
NbPlace2 = Len(Place) - Len(Replace(Place, "_", ""))
If (NbPlace2 > 0) Then
'--------- REFERENCE WITH UNDERSCORE
NbPlace2 = InStr(Place, "-")
'---------
TEMP = Left(Place, NbPlace2 - 1)
PlaceRef = Left(Place, NbPlace2 - 2) ' REMOVE HYPHEN AND LAST DIGIT
FirstNb = Replace(TEMP, PlaceRef, "") * 1
'---------
TEMP = Right(Place, Len(Place) - NbPlace2)
LastNb = Replace(TEMP, PlaceRef, "") * 1
'---------
NbPlace2 = LastNb - FirstNb
Rows(I + 1 & ":" & I + NbPlace2 - 1).Insert Shift:=xlDown
For J = 1 To NbPlace2
Range(Cells(I, "A"), Cells(I, Columns.Count).End(xlToLeft)).Copy Destination:=Cells(I + J, "A")
Cells(I + J, "C") = PlaceRef & FirstNb + J
Next J
Cells(I, "C") = PlaceRef & FirstNb
Else
'--------- REFERENCE WITHOUT UNDERSCORE
NbPlace2 = InStr(Place, "-")
'---------
TEMP = Left(Place, NbPlace2 - 1)
PlaceRef = Left(Place, NbPlace2 - 1)
For J = 0 To 9
PlaceRef = Replace(PlaceRef, J, "") ' REMOVE DIGITS
Next J
FirstNb = Replace(TEMP, PlaceRef, "") * 1
'---------
TEMP = Right(Place, Len(Place) - NbPlace2)
LastNb = Replace(TEMP, PlaceRef, "") * 1
'---------
NbPlace2 = LastNb - FirstNb
Rows(I + 1 & ":" & I + NbPlace2 - 1).Insert Shift:=xlDown
For J = 1 To NbPlace2
Range(Cells(I, "A"), Cells(I, Columns.Count).End(xlToLeft)).Copy Destination:=Cells(I + J, "A")
Cells(I + J, "C") = PlaceRef & FirstNb + J
Next J
Cells(I, "C") = PlaceRef & FirstNb
End If
End If
Next I
Application.ScreenUpdating = True
End Sub
Display More
Re: Comma delimited list and rows into columns
actually when running the macro, i found out where it broke down: sry but sometimes the place column could have:
U12A_49-U12A_52
i think this is where i get the error of Mismatch
Re: Comma delimited list and rows into columns
Quote
i think this is where i get the error of Mismatch
Yes it is where there is an issue as it is planned to have only one digit as already discussed.
So what are the specification?
Module 2 is used to store a macro to restaur data from sheet Save to sheet Data for debugging
Re: Comma delimited list and rows into columns
It's long way to ....
Sub HYPHEN_Treat()
Dim LastRow As Long
Dim NbPlace1 As Integer, NbPlace2 As Integer
Dim FirstNb As Integer, LastNb As Integer
Dim I As Long, J As Long
Dim Place
Dim TEMP
Dim PlaceRef As String
Application.ScreenUpdating = False
LastRow = Range("A" & Rows.Count).End(xlUp).Row
For I = LastRow To 3 Step -1
Place = Cells(I, "C")
NbPlace2 = Len(Place) - Len(Replace(Place, "-", ""))
If (NbPlace2 > 0) Then
'========= REFERENCE WITH HYPHEN
NbPlace2 = Len(Place) - Len(Replace(Place, "_", ""))
If (NbPlace2 > 0) Then
'--------- REFERENCE WITH UNDERSCORE
NbPlace2 = InStr(Place, "-")
'---------
TEMP = Left(Place, NbPlace2 - 1)
NbPlace1 = InStr(TEMP, "_")
PlaceRef = Left(TEMP, NbPlace1 - 1)
FirstNb = Replace(TEMP, PlaceRef & "_", "") * 1
'---------
TEMP = Right(Place, Len(Place) - NbPlace2)
NbPlace1 = InStr(TEMP, "_")
PlaceRef = Left(TEMP, NbPlace1 - 1)
LastNb = Replace(TEMP, PlaceRef & "_", "") * 1
'---------
NbPlace2 = LastNb - FirstNb
Rows(I + 1 & ":" & I + NbPlace2 - 1).Insert Shift:=xlDown
For J = 1 To NbPlace2
Range(Cells(I, "A"), Cells(I, Columns.Count).End(xlToLeft)).Copy Destination:=Cells(I + J, "A")
Cells(I + J, "C") = PlaceRef & FirstNb + J
Next J
Cells(I, "C") = PlaceRef & FirstNb
Else
'--------- REFERENCE WITHOUT UNDERSCORE
NbPlace2 = InStr(Place, "-")
'---------
TEMP = Left(Place, NbPlace2 - 1)
PlaceRef = Left(Place, NbPlace2 - 1)
For J = 0 To 9
PlaceRef = Replace(PlaceRef, J, "") ' REMOVE DIGITS
Next J
FirstNb = Replace(TEMP, PlaceRef, "") * 1
'---------
TEMP = Right(Place, Len(Place) - NbPlace2)
LastNb = Replace(TEMP, PlaceRef, "") * 1
'---------
NbPlace2 = LastNb - FirstNb
Rows(I + 1 & ":" & I + NbPlace2 - 1).Insert Shift:=xlDown
For J = 1 To NbPlace2
Range(Cells(I, "A"), Cells(I, Columns.Count).End(xlToLeft)).Copy Destination:=Cells(I + J, "A")
Cells(I + J, "C") = PlaceRef & FirstNb + J
Next J
Cells(I, "C") = PlaceRef & FirstNb
End If
End If
Next I
Application.ScreenUpdating = True
End Sub
Display More
Re: Comma delimited list and rows into columns
when i run the updated hyphentreat()...the underscore seems to disappear
sry about not being clear about the specifications. yeah so i guess we could have from U12A_49 to U12A_52
Re: Comma delimited list and rows into columns
Oh yes there is a miss.
In HYPHEN_Treat() use next piece of code
'--------- REFERENCE WITH UNDERSCORE
NbPlace2 = InStr(Place, "-")
'---------
TEMP = Left(Place, NbPlace2 - 1)
NbPlace1 = InStr(TEMP, "_")
PlaceRef = Left(TEMP, NbPlace1)
FirstNb = Replace(TEMP, PlaceRef, "") * 1
'---------
TEMP = Right(Place, Len(Place) - NbPlace2)
NbPlace1 = InStr(TEMP, "_")
PlaceRef = Left(TEMP, NbPlace1)
LastNb = Replace(TEMP, PlaceRef, "") * 1
'---------
NbPlace2 = LastNb - FirstNb
Rows(I + 1 & ":" & I + NbPlace2 - 1).Insert Shift:=xlDown
For J = 1 To NbPlace2
Range(Cells(I, "A"), Cells(I, Columns.Count).End(xlToLeft)).Copy Destination:=Cells(I + J, "A")
Cells(I + J, "C") = PlaceRef & FirstNb + J
Next J
Cells(I, "C") = PlaceRef & FirstNb
Display More
Re: Comma delimited list and rows into columns
thanks PCI for all your help! you are the best...awesome stuff.
Re: Comma delimited list and rows into columns
hey PCI, everything is working perfectly with the macro except I would really appreciate it if you could help me with one change (hopefully minor).
i have to run the macro in a demo next week and even after shortening my spreadsheet from 400+ rows of data to 100, running the macro kind of freezes excel or it takes quite a while to finish. is there a way to optimize the code?
Re: Comma delimited list and rows into columns
Can you send a large file to see what can be done
Re: Comma delimited list and rows into columns
yeah due to limitations...i don't think i can send any data. basically, it's the same as the file you sent except that each row has about 20 commas in the "Place" column and there are about 100+ rows of data.
hope you understand
Re: Comma delimited list and rows into columns
Hi,
I don't understand the whole logic.
If you can explain step by step then I could help.
Anyway, I created the function that converts
"A1,A2,A3,ABC123-ABC127" to "A1,A2,A3,ABC123,ABC124,ABC125,ABC126,ABC127"
or
"A1,A2,A3,A12_3-A12_5" to "A1,A2,A3,A12_3,A12_4,A12_5"
Hope this helps
Dim RegX As Object
Function Get_Series(ByVal txt) As String
Dim m As Object, i As Long, e As Object, temp1 As String, temp2 As String
If RegX Is Nothing Then Set RegX = CreateObject("VBScript.RegExp")
With RegX
.Pattern = "[^,]+"
.Global = True
If .test(txt) Then
Set m = .Execute(txt)
.Pattern = "(.+_?)(\d+)\-(.+_?)(\d+)"
For Each e In m
If .test(e) Then
For i = .Execute(e)(0).submatches(1) To .Execute(e)(0).submatches(3)
temp2 = temp2 & "," & .Execute(e)(0).submatches(0) & i
Next
temp2 = Mid$(temp2, 2)
Else
temp1 = temp1 & "," & e
End If
Next
If Len(temp1) Then temp1 = Mid$(temp1, 2) & IIf(Len(temp2), ",", "")
Get_Series = temp1 & temp2
Else
Get_Series = txt
End If
End With
End Function
Display More
Re: Comma delimited list and rows into columns
jstar88,
The very limited but faster way to speed up present code will be to include next statements at the beginning and at the end of each macro.
Another possibility is to rebuild the code using some arrays but it will take longer.
Now certainly to use VBS as Jindon is suggesting is the best way.
Re: Comma delimited list and rows into columns
what do u mean by adding "next statements" at the beginning and end?
i did the application screenupdating code and it seems to run a little bit faster.
Re: Comma delimited list and rows into columns
Hi Jindon,
Thanks for joining in. Basically, if you open PCI's Attached Oz9 file, I am trying to get each entry in the "Place Column" on separate rows by separating the hyphens and commas. For instance,
[TABLE="width: 124"]
[TD="class: xl65, width: 124"]U17,U120,C3-C5 becomes
[/TD]
[/TABLE]
U17
U120
C3
C4
C5
and
U12A_49-U12A_52 becomes
U12A_49
U12A_50
U12A_51
U12A_52
Don’t have an account yet? Register yourself now and be a part of our community!