Split rows in Excel

[egghi]

Hi,

I am trying to split rows in an Excel spreadsheet. My spreadsheet currently looks like this:

Procedure Description Category Price Modifier1 Modifier2
001234 Plastic bag V $12 TC890 TC990
001235 Paper box V $15 TB003
001236 Tin can J $20 TC999 13556

I would like to use the splitrow() and convert the spreadsheet into:

Procedure Modifiers Description Category Price
001234 TC890 Plastic bag V $12
001234 TC990 Plastic bag V $12
001235 TB003 Paper box V $15
001236 TC999 Tin can J $20
001236 13556 Tin can J $20

The current macro is posted below and it's not working:
Private Sub SplitRows()

Dim oRange As Range, lRow As Long, lCol As Long, oOut As Worksheet, lOut As Long
Dim sValue As String, sGeneral(5) As String, sCode As String
sGeneral(0) = oRange.Cells(lRow, 1).Value
sGeneral(1) = oRange.Cells(lRow, 2).Value
sGeneral(2) = oRange.Cells(lRow, 3).Value
sGeneral(3) = oRange.Cells(lRow, 4).Value
sGeneral(4) = oRange.Cells(lRow, 5).Value
sGeneral(5) = oRange.Cells(lRow, 6).Value
For i = 0 To 5

Set oRange = Application.ActiveSheet.UsedRange
Set oOut = Application.ActiveWorkbook.Worksheets.Add

For lRow = 1 To oRange.Rows.Count
i = oRange.Cells(lRow, 1).Value


If Len(i) <> 0 Then
For lCol = 7 To oRange.Columns.Count
sValue = oRange.Cells(lRow, lCol).Value

If Len(sValue) <> 0 Then
lOut = lOut + 1
oOut.Cells(lOut, 1).Value = i
oOut.Cells(lOut, 2).Value = sValue
End If
Next lCol
End If
Next lRow

Set oRange = Nothing
Set oOut = Nothing
Next
End Sub


Please help,
egghi

 

[SkipVought]

The easiest way is to use MS Query via Data/Get External data/New database Query -- Excel files -- YOUR WORKBOOK -- YOUR WORKSHEET SOURCE DATA....

Perform a UNION query

Select Procedure, Description, Category, Price, Modifier1 As Modifier
From ...

UNION

Select Procedure, Description, Category, Price, Modifier2 As Modifier
From ...

Skip,

_[red][/red]

 

[egghi]

Hello Skip,

Thank you very much for the input! The problem with union is that I cannot have more than 2 modifiers.

Also, if Modifier 1 is null and modifier 2 has a value, the union query would return a repeated row with no value in Modifier:

Procedure Description Category Price Modifier1 Modifier2
001235 Paper box V $15 TB003

becomes

Procedure Description Category Price Modifier
001235 Paper box V $15
001235 Paper box V $15 TB003

Thank you for your help! Hopefully we can figure out the ultimate solution!


egghi

 

[SkipVought]

WHERE MODIFIERn IS NOT NULL

Skip,

_[red][/red]

 

[egghi]

Hello Skip,

I was really dumb... I was too focused on vbscript and totally forgot about the power of SQL query!

One last thing to bug you: if I do have more than 2 modifiers (say I have 3 modifiers), should I create 2 queries? The first one is modifier 1 union modifier 2, and then the second one is modifier union modifier 3?

Is there any shortcut that I can take without running multiple queries for multiple modifiers?

THANK YOU SOOO MUCH,
egghi

 

[SkipVought]

Please state the logic you want to apply. A query can have some very complex criteria.

Skip,

_[red][/red]

 

[egghi]

Hi Skip,

So, I created two queries. The first one is called splitrow(1):
SELECT [PRODUCT CODE], DESCRIPTION, [CATEGORY], PRICE,[Modifier 1] AS [MODIFIER]
FROM products
WHERE [Modifier 1] IS NOT NULL

UNION SELECT [PRODUCT CODE], DESCRIPTION, [CATEGORY], PRICE, [Modifier 2] AS [MODIFIER]
FROM products
WHERE [Modifier 2] IS NOT NULL;

And the second query as:

SELECT [PRODUCT CODE], DESCRIPTION, [CATEGORY], PRICE,[Modifier 3] AS MODIFIER
FROM product
WHERE [Modifier 3] IS NOT NULL

UNION SELECT [PRODUCT CODE], DESCRIPTION, [CATEGORY], PRICE, MODIFIER
FROM [splitrow(1)];

I got the result I wanted after running the second query. Is there anyway to combine the two queries?

Have a great weekend,
egghi

 

[SkipVought]

SELECT [PRODUCT CODE], DESCRIPTION, [CATEGORY], PRICE,[Modifier 1] AS [MODIFIER]
FROM products 
WHERE [Modifier 1] IS NOT NULL

UNION SELECT [PRODUCT CODE], DESCRIPTION, [CATEGORY], PRICE, [Modifier 2] 
FROM products 
WHERE [Modifier 2] IS NOT NULL

UNION

SELECT [PRODUCT CODE], DESCRIPTION, [CATEGORY], PRICE,[Modifier 3] 
FROM product
WHERE [Modifier 3] IS NOT NULL;

Skip,

_[red][/red]