How to breakdown one row into several identical rows based on certain date range criteria?

F

fleer

New Member
Joined
Mar 6, 2018
Messages
4
I would like to breakdown one row data into several rows based on start and end date say feb 4- Jun 15 as Feb 4-28, Mar 1-31,Apr 1-30,May 1-31 and June 1-15 and populate the results onto another spreadsheet with the same columns. I am new to vba and need kind help from your guys.

[TABLE="width: 1085"]
<tbody>[TR]
[TD]EVENT_ID[/TD]
[TD]EVENT_KIND[/TD]
[TD]UNIT_NAME[/TD]
[TD]UNIT_ID[/TD]
[TD]OWNER_NAME[/TD]
[TD]PLANT_ID[/TD]
[TD]PLANT_NAME[/TD]
[TD]IND_CODE[/TD]
[TD]IND_DESC[/TD]
[TD]PHYS_CITY[/TD]
[TD]START_DATE[/TD]
[TD]END_DATE[/TD]
[TD]ASSIGNEDTO[/TD]
[TD]ESTVALUE[/TD]
[TD]ESTCLOSE[/TD]
[TD]RELEASE_DT[/TD]
[/TR]
[TR]
[TD="align: right"]368314[/TD]
[TD]T[/TD]
[TD]ABT Nr. 202 (ABT-2/2) (Crude 02)[/TD]
[TD="align: right"]2112273[/TD]
[TD]Bakinsk NPZ JSC[/TD]
[TD="align: right"]1051655[/TD]
[TD]Bakinsk Heydar Aliyev Lower Refinery (Azerneftyag)[/TD]
[TD="align: right"]6[/TD]
[TD]Petroleum Refining (HPI)[/TD]
[TD]Baku[/TD]
[TD="align: right"]18-Aug-17[/TD]
[TD="align: right"]11-Sep-17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]19-Oct-17[/TD]
[/TR]
[TR]
[TD="align: right"]368318[/TD]
[TD]T[/TD]
[TD]ABT Nr. 202 (ABT-2/2) (Crude 02)[/TD]
[TD="align: right"]2112273[/TD]
[TD]Bakinsk NPZ JSC[/TD]
[TD="align: right"]1051655[/TD]
[TD]Bakinsk Heydar Aliyev Lower Refinery (Azerneftyag)[/TD]
[TD="align: right"]6[/TD]
[TD]Petroleum Refining (HPI)[/TD]
[TD]Baku[/TD]
[TD="align: right"]15-Sep-18[/TD]
[TD="align: right"]9-Oct-18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]17-Jan-18[/TD]
[/TR]
[TR]
[TD="align: right"]368315[/TD]
[TD]T[/TD]
[TD]ABT Nr. 202 (ABT-2/2) (Crude 02)[/TD]
[TD="align: right"]2112273[/TD]
[TD]Bakinsk NPZ JSC[/TD]
[TD="align: right"]1051655[/TD]
[TD]Bakinsk Heydar Aliyev Lower Refinery (Azerneftyag)[/TD]
[TD="align: right"]6[/TD]
[TD]Petroleum Refining (HPI)[/TD]
[TD]Baku[/TD]
[TD="align: right"]12-Sep-15[/TD]
[TD="align: right"]6-Oct-15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]21-Apr-17[/TD]
[/TR]
[TR]
[TD="align: right"]368313[/TD]
[TD]T[/TD]
[TD]ABT Nr. 202 (ABT-2/2) (Crude 02)[/TD]
[TD="align: right"]2112273[/TD]
[TD]Bakinsk NPZ JSC[/TD]
[TD="align: right"]1051655[/TD]
[TD]Bakinsk Heydar Aliyev Lower Refinery (Azerneftyag)[/TD]
[TD="align: right"]6[/TD]
[TD]Petroleum Refining (HPI)[/TD]
[TD]Baku[/TD]
[TD="align: right"]6-Aug-16[/TD]
[TD="align: right"]30-Aug-16[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]21-Apr-17[/TD]
[/TR]
[TR]
[TD="align: right"]368349[/TD]
[TD]T[/TD]
[TD]ABT Nr. 305 (ABT-2/1) (Crude 01)[/TD]
[TD="align: right"]2112270[/TD]
[TD]Bakinsk NPZ JSC[/TD]
[TD="align: right"]1051655[/TD]
[TD]Bakinsk Heydar Aliyev Lower Refinery (Azerneftyag)[/TD]
[TD="align: right"]6[/TD]
[TD]Petroleum Refining (HPI)[/TD]
[TD]Baku[/TD]
[TD="align: right"]12-Sep-15[/TD]
[TD="align: right"]6-Oct-15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]17-Jan-18[/TD]
[/TR]
[TR]
[TD="align: right"]368346[/TD]
[TD]T[/TD]
[TD]ABT Nr. 305 (ABT-2/1) (Crude 01)[/TD]
[TD="align: right"]2112270[/TD]
[TD]Bakinsk NPZ JSC[/TD]
[TD="align: right"]1051655[/TD]
[TD]Bakinsk Heydar Aliyev Lower Refinery (Azerneftyag)[/TD]
[TD="align: right"]6[/TD]
[TD]Petroleum Refining (HPI)[/TD]
[TD]Baku[/TD]
[TD="align: right"]18-Aug-17[/TD]
[TD="align: right"]11-Sep-17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]19-Oct-17[/TD]
[/TR]
[TR]
[TD="align: right"]368340[/TD]
[TD]T[/TD]
[TD]ABT Nr. 305 (ABT-2/1) (Crude 01)[/TD]
[TD="align: right"]2112270[/TD]
[TD]Bakinsk NPZ JSC[/TD]
[TD="align: right"]1051655[/TD]
[TD]Bakinsk Heydar Aliyev Lower Refinery (Azerneftyag)[/TD]
[TD="align: right"]6[/TD]
[TD]Petroleum Refining (HPI)[/TD]
[TD]Baku[/TD]
[TD="align: right"]15-Sep-18[/TD]
[TD="align: right"]9-Oct-18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]21-Apr-17[/TD]
[/TR]
[TR]
[TD="align: right"]368348[/TD]
[TD]T[/TD]
[TD]ABT Nr. 305 (ABT-2/1) (Crude 01)[/TD]
[TD="align: right"]2112270[/TD]
[TD]Bakinsk NPZ JSC[/TD]
[TD="align: right"]1051655[/TD]
[TD]Bakinsk Heydar Aliyev Lower Refinery (Azerneftyag)[/TD]
[TD="align: right"]6[/TD]
[TD]Petroleum Refining (HPI)[/TD]
[TD]Baku[/TD]
[TD="align: right"]6-Aug-16[/TD]
[TD="align: right"]30-Aug-16[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]21-Apr-17[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Sort by date Sort by votes
Hi fleer,

I understand that you want to make a new sheet based on the information from the original sheet, but I don't understand what information you'd like to be on the new sheet. Can you show me what the new sheet is supposed to look like based on your example?
 
Upvote 0
The_Macrotect said:
Hi fleer,

I understand that you want to make a new sheet based on the information from the original sheet, but I don't understand what information you'd like to be on the new sheet. Can you show me what the new sheet is supposed to look like based on your example?
Click to expand...
Hi mate tks for the response. Actually I would like to have all the other columns intact ie the row is split into several only based on the multiple periods and other info are automatically copied down. Guess it is not easy to achieve? Looking forward to ur reply. Thanks a lot .
 
Upvote 0
fleer said:
Hi mate tks for the response. Actually I would like to have all the other columns intact ie the row is split into several only based on the multiple periods and other info are automatically copied down. Guess it is not easy to achieve? Looking forward to ur reply. Thanks a lot .
Click to expand...
I'm still unclear on what the final result would be. What would the new sheet look like in your example?
 
Upvote 0
Ok, I think I understand...

Should the first two rows look like this on the second sheet?

Excel 2007 32 bit
ABCDEFGHIJKLMNOP
EVENT_IDEVENT_KINDUNIT_NAMEUNIT_IDOWNER_NAMEPLANT_IDPLANT_NAMEIND_CODEIND_DESCPHYS_CITYSTART_DATEEND_DATEASSIGNEDTOESTVALUEESTCLOSERELEASE_DT
TABT Nr. 202 (ABT-2/2) (Crude 02)Bakinsk NPZ JSCBakinsk Heydar Aliyev Lower Refinery (Azerneftyag)Petroleum Refining (HPI)Baku
TABT Nr. 202 (ABT-2/2) (Crude 02)Bakinsk NPZ JSCBakinsk Heydar Aliyev Lower Refinery (Azerneftyag)Petroleum Refining (HPI)Baku
TABT Nr. 202 (ABT-2/2) (Crude 02)Bakinsk NPZ JSCBakinsk Heydar Aliyev Lower Refinery (Azerneftyag)Petroleum Refining (HPI)Baku
TABT Nr. 202 (ABT-2/2) (Crude 02)Bakinsk NPZ JSCBakinsk Heydar Aliyev Lower Refinery (Azerneftyag)Petroleum Refining (HPI)Baku

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]368314[/TD]

[TD="align: right"]2112273[/TD]

[TD="align: right"]1051655[/TD]

[TD="align: right"]6[/TD]

[TD="align: right"]18-Aug-17[/TD]
[TD="align: right"]31-Aug-17[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]19-Oct-17[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]368314[/TD]

[TD="align: right"]2112273[/TD]

[TD="align: right"]1051655[/TD]

[TD="align: right"]6[/TD]

[TD="align: right"]1-Sep-17[/TD]
[TD="align: right"]11-Sep-17[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]19-Oct-17[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]368318[/TD]

[TD="align: right"]2112273[/TD]

[TD="align: right"]1051655[/TD]

[TD="align: right"]6[/TD]

[TD="align: right"]15-Sep-18[/TD]
[TD="align: right"]30-Sep-18[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]17-Jan-18[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]368318[/TD]

[TD="align: right"]2112273[/TD]

[TD="align: right"]1051655[/TD]

[TD="align: right"]6[/TD]

[TD="align: right"]1-Oct-18[/TD]
[TD="align: right"]9-Oct-18[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]17-Jan-18[/TD]

</tbody>
Sheet2​
 
Upvote 0
The_Macrotect said:
Ok, I think I understand...

Should the first two rows look like this on the second sheet?

Excel 2007 32 bit
ABCDEFGHIJKLMNOP
EVENT_IDEVENT_KINDUNIT_NAMEUNIT_IDOWNER_NAMEPLANT_IDPLANT_NAMEIND_CODEIND_DESCPHYS_CITYSTART_DATEEND_DATEASSIGNEDTOESTVALUEESTCLOSERELEASE_DT
TABT Nr. 202 (ABT-2/2) (Crude 02)Bakinsk NPZ JSCBakinsk Heydar Aliyev Lower Refinery (Azerneftyag)Petroleum Refining (HPI)Baku
TABT Nr. 202 (ABT-2/2) (Crude 02)Bakinsk NPZ JSCBakinsk Heydar Aliyev Lower Refinery (Azerneftyag)Petroleum Refining (HPI)Baku
TABT Nr. 202 (ABT-2/2) (Crude 02)Bakinsk NPZ JSCBakinsk Heydar Aliyev Lower Refinery (Azerneftyag)Petroleum Refining (HPI)Baku
TABT Nr. 202 (ABT-2/2) (Crude 02)Bakinsk NPZ JSCBakinsk Heydar Aliyev Lower Refinery (Azerneftyag)Petroleum Refining (HPI)Baku

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]368314[/TD]

[TD="align: right"]2112273[/TD]

[TD="align: right"] 1051655 [/TD]

[TD="align: right"]6[/TD]

[TD="align: right"]18-Aug-17[/TD]
[TD="align: right"]31-Aug-17[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]19-Oct-17[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]368314[/TD]

[TD="align: right"]2112273[/TD]

[TD="align: right"] 1051655 [/TD]

[TD="align: right"]6[/TD]

[TD="align: right"]1-Sep-17[/TD]
[TD="align: right"]11-Sep-17[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]19-Oct-17[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]368318[/TD]

[TD="align: right"]2112273[/TD]

[TD="align: right"] 1051655 [/TD]

[TD="align: right"]6[/TD]

[TD="align: right"]15-Sep-18[/TD]
[TD="align: right"]30-Sep-18[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]17-Jan-18[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]368318[/TD]

[TD="align: right"]2112273[/TD]

[TD="align: right"] 1051655 [/TD]

[TD="align: right"]6[/TD]

[TD="align: right"]1-Oct-18[/TD]
[TD="align: right"]9-Oct-18[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]17-Jan-18[/TD]

</tbody>
Sheet2​
Click to expand...
Yes mate you r right
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
adding cells based on criteria across several columns
Replies
5
Views
1K
Cubist
Cubist
G
  • Question Question
Copy Range of Cells based on Criteria, Paste it on rows below it, Do This Until All conditions are met, Then Delete All Copied Rows
Replies
0
Views
580
glaccounting
G
T
  • Solved
Pulling Data from range given Date Criteria in Rows
Replies
6
Views
647
Kerryx
K
P
  • Solved
Calculate Year-to-date figures taken from another table in one cell based on selecting a month in a dropdown list
Replies
3
Views
608
Alex Blakenburg
A
H
  • Question Question
Lookup based on multiple criteria and date Range
Replies
2
Views
710
Toadstool
Toadstool

Forum statistics

Threads
1,225,481
Messages
6,185,236
Members
453,283
Latest member
Shortm88

Share this page

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
Back
Top