Re: Comma delimited list and rows into columns
In Oz09 the treatment is done from bottom the top and for Oz11 it is the opposite.
That's means, that before the final treatment is done, data is not in the order, but which one is the good one?
In another data configuration the first result (from Oz09) could not be good.
Don't we need to sort by Manufacturer Name ?
Re: Comma delimited list and rows into columns
so, before the final treatment, i need the order from Oz11 to match with Oz09.
Well, i'm currently not using the final treatment so i'm doing the comma treat and then the hyphen treat so i need the order that comes out from the Oz09. thanks!
Re: Comma delimited list and rows into columns
To get the same result from each file replace FINAL Treat in each file by next code.
A sort is done using the MAnufacturer Name, you will see that results are the same
Sub FINAL_Treat()
Dim LastRow As Long, LastCol As Long
Dim I As Long, J As Long
Dim MyRg As Range
Dim F
Dim MyValue
Application.ScreenUpdating = False
LastRow = Range("A" & Rows.Count).End(xlUp).Row
Range("A1:E" & LastRow).Sort Key1:=Range("D2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
For I = LastRow To 2 Step -1
Set MyRg = Range("C2:C" & I - 1)
MyValue = Cells(I, "C")
If (MyValue <> Empty) Then
With MyRg
Set F = .Find(What:=MyValue, After:=.Cells(1, 1), LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False)
If (Not F Is Nothing) Then
LastCol = Cells(F.Row, Columns.Count).End(xlToLeft).Column
Range("D" & I).Resize(1, 2).Copy Destination:=Cells(F.Row, LastCol + 1)
Rows(I & ":" & I).Delete Shift:=xlUp
End If
End With
End If
Next I
Application.ScreenUpdating = True
End SubRe: Comma delimited list and rows into columns
well, i don't need the final_treat part anymore. i found out that we only need to stop after the hyphen treat and the format of the Oz09 after using comma and then the hyphen is the format that i need.
IN OTHER WORDS, is it possible we can get the "data" sheet in the attachment to match the "Target" sheet after i run the COMMA_HYPHEN TREAT macro without running the FINAL_TREAT?
Thanks PCI for your dedication and help![ATTACH=CONFIG]39696[/ATTACH]
Re: Comma delimited list and rows into columns
See attached, must be same result and faster as Oz09
Option Explicit
Option Base 1
Sub MAIN()
COMMA_Treat2
HYPHEN_Treat2
End Sub
Sub COMMA_Treat2()
Dim LastRow As Long
Dim NbPlace1 As Integer
Dim FirstNb As Integer, LastNb As Integer
Dim I As Long, J As Long, K As Long
Dim II As Long, JJ As Long
Dim Place
Dim Data
Dim ORG(), DEST()
Dim Table()
Application.ScreenUpdating = False
LastRow = Range("A" & Rows.Count).End(xlUp).Row
ORG = Range("A1").CurrentRegion
ReDim DEST(10000, 5)
For I = 1 To LastRow
Place = ORG(I, 3)
NbPlace1 = Len(Place) - Len(Replace(Place, ",", "")) + 1 ' +1 FOR THE LAST PLACE AFTER COMMA
If (NbPlace1 > 1) Then
For K = 0 To NbPlace1 - 1
For J = 1 To 5
DEST(II + 1, J) = ORG(I, J)
Next J
DEST(II + 1, 3) = Split(Place, ",")(K)
II = II + 1
Next K
Else
II = II + 1
For J = 1 To 5
DEST(II, J) = ORG(I, J)
Next J
End If
Next I
Range("A1").Resize(II, 5) = DEST
End Sub
Sub HYPHEN_Treat2()
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, K As Long
Dim II As Long, JJ As Long
Dim Place
Dim TEMP
Dim PlaceRef As String
Dim Data
Dim ORG(), DEST()
LastRow = Range("A" & Rows.Count).End(xlUp).Row
ORG = Range("A1").CurrentRegion
ReDim DEST(10000, 5)
'--------------- HYPHEN --------------
For I = 1 To LastRow
Place = ORG(I, 3)
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)
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 + 1
For K = 0 To NbPlace2 - 1
II = II + 1
For J = 1 To 5
DEST(II, J) = ORG(I, J)
Next J
DEST(II, 3) = PlaceRef & FirstNb + K
Next K
' Range("A1").Resize(II, 5) = DEST ' FOR DEBUGGING
Else
'--------- REFERENCE WITHOUT UNDERSCORE
NbPlace2 = InStr(Place, "-")
'---------
TEMP = Left(Place, NbPlace2 - 1)
PlaceRef = Left(Place, NbPlace2 - 1)
For K = 0 To 9
PlaceRef = Replace(PlaceRef, K, "") ' REMOVE DIGITS
Next K
FirstNb = Replace(TEMP, PlaceRef, "") * 1
'---------
TEMP = Right(Place, Len(Place) - NbPlace2)
LastNb = Replace(TEMP, PlaceRef, "") * 1
'---------
NbPlace2 = LastNb - FirstNb + 1
For K = 0 To NbPlace2 - 1
II = II + 1
For J = 1 To 5
DEST(II, J) = ORG(I, J)
Next J
DEST(II, 3) = PlaceRef & (FirstNb + K)
Next K
End If
Else
II = II + 1
For J = 1 To 5
DEST(II, J) = ORG(I, J)
Next J
End If
' Range("A1").Resize(II, 5) = DEST ' FOR DEBUGGING
Next I
Suite:
Range("A1").Resize(II, 5) = DEST
Application.ScreenUpdating = True
End SubRe: Comma delimited list and rows into columns
thank you PCI so much! works amazing and just what i need.
sry for taking so much of your time.
shout outs to jindon and PCI for being the most dedicated and hardworking, working to solve all of my problems for sure.
thanks again! hopefully, no more problem with this.
Re: Comma delimited list and rows into columns
sry to bother u guys again but after running this macro on a new project, I came across another error saying "Subscript out of Range" and when debugging it, it points to the following line:
for PCI:
and for jindon:
What does this error exactly mean? not sure why it is not working. I think the macro keeps on stopping when an entry like "C1364" is in the place column.
Re: Comma delimited list and rows into columns
try expand the array "b" like
was 1 to 10000, 1 to UBound(a,2)
This is because we don't know how many rows will be required from the beggining, so we must guess it.
And if you still have such error, you will need to expand it more.
Re: Comma delimited list and rows into columns
wowww...just like that. works so well. thanks again guys for all your help.
Re: Comma delimited list and rows into columns
Ouffff...!
Do we got it really!!
PCI
Re: Comma delimited list and rows into columns
so far what you guys suggested made it work...hahaha, hopefully i dont come across another issue.
Don’t have an account yet? Register yourself now and be a part of our community!