Splitting Row in to Multiple Rows based on Column Value

Yanfly

New Member
Joined
May 27, 2019
Messages
1
Hey! First time here so apologies in advance if I'm asking this incorrectly.
I have a pretty good basic understanding of excel but I've only ever really used it for some easy to moderate formulas and a few macros I've been helped with. I'm trying to figure out where to start with a problem I have
I have maybe 400 rows of data on a sheet. I need to separate each row of data based on the values in 4 of the columns. I'm trying to attach a screenshot I've made with a brief example of what I'm trying to achieve.
https://i.imgur.com/7EvIKt3.jpg
The top part of the screenshot is how the data is now. The bottom part is how I want the data to be modified. A new row is created for any fee thats been incurred (base fee for every row, then if they have incurred an order fee then a new row is added, if a priority fee has been incurred then a new row is added etc. If it's '0' then no new row).
If any one has any guidance on how to do this it'd be great. I'm not asking for the solution, but just some tips on what I can research or what I should be learning to accomplish something like this!!

The actual data I'm working with is a lot more complex but I want to try and figure out the basic idea on how to do it and then I can try to apply it to the sheet I have.

The data I'm using is based on Sharepoint - so I've been looking in to real basic Power Query to filter and pull that data from SP in to my sheet. I then want to modify the data in a way similar to the screenshot I posted.

Thanks :)
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows
You can do it with a macro.
Put your data in the sheet1 in this way. The result will be in the sheet2

<table style="font-family:Arial; font-size:12pt; border-style: groove ;border-color:#0000FF;background-color:#fffcf9; color:#000000; "><tr><td ><b>Sheet1</b></td></tr></table>
<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:43.72px;" /><col style="width:57.98px;" /><col style="width:79.84px;" /><col style="width:105.5px;" /><col style="width:70.34px;" /><col style="width:83.64px;" /><col style="width:70.34px;" /><col style="width:81.74px;" /><col style="width:70.34px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">NAME</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">EMAIL</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">ORDER DATE</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">ORDER AMOUNT</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">CURRENCY</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">EXPEDITE FEE</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">CURRENCY</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">COURIER FEE</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">CURRENCY</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >BOB</td><td style="color:#0000ff; text-decoration:underline; ">BOB@</td><td style="text-align:right; ">27-may</td><td style="text-align:right; ">100</td><td >USD</td><td style="text-align:right; ">0</td><td >USD</td><td style="text-align:right; ">10</td><td >USD</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >SAM</td><td style="color:#0000ff; text-decoration:underline; ">SAM@</td><td style="text-align:right; ">27-may</td><td style="text-align:right; ">120</td><td >USD</td><td style="text-align:right; ">0</td><td >USD</td><td style="text-align:right; ">0</td><td >USD</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >CLI</td><td style="color:#0000ff; text-decoration:underline; ">CLI@</td><td style="text-align:right; ">27-may</td><td style="text-align:right; ">500</td><td >USD</td><td style="text-align:right; ">120</td><td >USD</td><td style="text-align:right; ">20</td><td >USD</td></tr></table>


Code:
Sub Splitting_Row()
    Dim sh1 As Worksheet, sh2 As Worksheet
    Dim i As Long, j As Long, lr2 As Long
    
    Set sh1 = Sheets("Sheet1")
    Set sh2 = Sheets("Sheet2")
    
    sh2.Cells.ClearContents
    
    lr2 = 2
    For i = 2 To sh1.Range("A" & Rows.Count).End(xlUp).Row
        sh2.Cells(lr2, "A").Value = sh1.Cells(i, "A").Value
        sh2.Cells(lr2, "B").Value = "Base Fee"
        sh2.Cells(lr2, "C").Value = "USD"
        sh2.Cells(lr2, "D").Value = 150
        lr2 = lr2 + 1
        For j = 4 To 8 Step 2
            If sh1.Cells(i, j).Value <> 0 Then
                sh2.Cells(lr2, "A").Value = sh1.Cells(i, "A").Value
                sh2.Cells(lr2, "B").Value = sh1.Cells(1, j).Value
                sh2.Cells(lr2, "C").Value = sh1.Cells(i, j + 1).Value
                sh2.Cells(lr2, "D").Value = sh1.Cells(i, j).Value
                lr2 = lr2 + 1
            End If
        Next
    Next
    MsgBox "End"
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,122,938
Messages
5,598,959
Members
414,269
Latest member
FJXMTT

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top