Issue
I'm trying to create records based on multiple cells with comma delimited values.
Take This:
Handle | Title | Color | Size | Price
| | | | (0-04/06-08)
---------------------------------------------------------------------------
Jovani-JVN86957 | Jovani | Black, | 0, 02, | $199 (if size > 06 then +15)
JVN86957 | Red, White | 04, 06, 08 |
What I'm trying to do is have the Color and Size columns generate new records while including the Handle and Price(also if possible but not necessary to have the price changes based on the Size value ie: size 0-4 = $199 and size 6-8 = $219)
Turn to this:
Handle Title Color Size Price(0 04/06 08 +$15)
Jovani-JVN86957 Jovani-JVN86957 Black 0 $199
Jovani-JVN86957 Black 2 $199
Jovani-JVN86957 Black 4 $199
Jovani-JVN86957 Black 6 $199 +$15 = $214
Jovani-JVN86957 Black 8 $199 +$15 = $214
Jovani-JVN86957 Red 0 $199
Jovani-JVN86957 Red 2 $199
Jovani-JVN86957 Red 4 $199
Jovani-JVN86957 Red 6 $199 +$15 = $214
Jovani-JVN86957 Red 8 $199 +$15 = $214
Jovani-JVN86957 White 0 $199
Jovani-JVN86957 White 2 $199
Jovani-JVN86957 White 4 $199
Jovani-JVN86957 White 6 $199 +$15 = $214
Jovani-JVN86957 White 8 $199 +$15 = $214
Any help would be much appreciated.
Solution
Here is one method that is a bit more of a brute force approach by using multiple loops.
Sub SplitEntries()
Dim rng As Range
Set rng = Range("A2:A5")
'Copy over header
Range("G1:K1").Value = Range("A1:E1").Value
Dim sizeSplit As Variant
Dim colorSplit As Variant
Dim rowCntr As Integer: rowCntr = 2
'Loop thru range
For Each cell In rng
Range("H" & rowCntr).Value = cell.Offset(0, 1).Value
'Get all the colors
colorSplit = Split(cell.Offset(0, 2), ",")
'Loop through each color
For i = LBound(colorSplit) To UBound(colorSplit)
'Get all the sizes
sizeSplit = Split(cell.Offset(0, 3), ",")
'Loop through each size
For j = LBound(sizeSplit) To UBound(sizeSplit)
Range("G" & rowCntr).Value = cell.Value
Range("I" & rowCntr).Value = Trim(colorSplit(i))
Range("J" & rowCntr).Value = Trim(sizeSplit(j))
'Range("K" & rowCntr).Value = cell.Offset(0, 4).Value
If Trim(sizeSplit(j)) < 6 Then
Range("K" & rowCntr).Value = cell.Offset(0, 4).Value
Else
Range("K" & rowCntr).Value = cell.Offset(0, 4).Value + 15
End If
rowCntr = rowCntr + 1
Next j
Next i
Next cell
End Sub
The starting data looks like this:
And the results are put in new columns like this:
Answered By - Automate This Answer Checked By - Dawn Plyler (PHPFixing Volunteer)
0 Comments:
Post a Comment
Note: Only a member of this blog may post a comment.