Contingent row copy (make duplicates)

[mnykamp]

Hey there! New to this place but figured i might as well debut with a nasty problem I haven't been able to figure out thus far. I have a situation in which i have a large table with nearly 600.000 rows and a decent amount of columns that all fit in excel 2007. One of the columns is an integer I made following the number of months the values inside row are actually valid. Now what i would want is to duplicate the rows (inside another table) contingent upon the number inside the row itself (number of months valid). Below example to sketch out the idea. Any help\hints on how to solve much appreciated!

Simplified example (table as is)

forecast broker analyst #validmonths startdate enddate
100 13 16 3 1-1-87 12-3-87
129 14 90 6 1-2-87 20-7-87

Simplified example (table after restructuring)

month forecast broker analyst
jan 87 100 13 16
feb 87 100 13 16
feb 87 129 14 90
mar 87 100 13 16
mar 87 129 14 90
ect ect ect

[lucas]

Hi mnykamp,
To be sure I understand, you want to copy all of the rows that meet a certain criteria(months valid) and then re-arrange some columns and delete others.

What is the criteria for months valid? Is it boolian? In other words is it valid if it has any date in it or does it have to be in a range of dates, etc.

The method I described in paragraph one may not be what we actually do but was stated as a way of understanding what you are trying to accomplish.....

[mnykamp]

Im sorry i should have been more specific. In essence what i'm trying to do is to replicate the entire row x times (inside another table), where x denotes the value of months valid. So it is not a criterium but a variable for the number of copies i need.

The idea is that i want to have each horizontal cell (forecast value, broker, analys ect) in a row for each month it is valid; hence the months valid integer. This is simply a slightly adapted date.dff function result that would be useful in counting the number of rows from the startdate onwards. In terms of columns i technically want all of them to remain exactly the same (altough the startdate,enddate and #valid month columns will become obsolete which is why i left them out in the example)

so i want all the rows (and their columns), but some more than others :) (i need to do some statistics on them) Hope this clears things up a bit.

tyn

[tstav]

Taking your post#3 into account, the example you presented in post#1 is wrong (as far as the second row is concerned), since the '129 14 90' starts from February .
We should cross out the second row, correct?

[tstav]

Plus, do you think the new table will fit in the 1,048,000 rows of the Excel Sheet? Your data table (600,000 rows) already takes up 60% of the total number of rows.

[mnykamp]

@tstav You are right; (i've edited it out now). Well on the excel limit i think i can be short; it will fall out of bounds... The average number of months valid is around 8. :(
I guess i'll have to revert to access vba then? Perhaps the post could be moved?

[tstav]

Hi mnykamp,
perhaps Excel 2014 would suit your needs better :) (just joking...).
I guess you'll have to get friends with Access.

Anyhow, since I already have something for you (in case your data rows ever get less), here's a solution for Excel.

You can supply the start row of data (not the title row) and the column you wish the output month to go in.
The output table is adjacent to the original.
The output data start on the same row as the original data.
In case you run out of rows, I'm providing a message to pop up.
At the end you may also sort the data by month.

Best regards,
tstav
Sub ReplicateRowsByNumberOfMonths()
Dim strDate As String, strYear As String
Dim col As String
Dim intI As Integer
Dim startRow As Long, endRow As Long, inputRow As Long, outputRow As Long
Dim R As Range, cell As Range
Dim varDate As Variant
'Output data starts at column=col, row=startRow
col = "G" '<-- Supply the output col for the month
startRow = 2 '<-- Supply the start row of data (title row = 1)
endRow = Range("A" & Rows.count).End(xlUp).Row '<-- end row of data
'Output titles
Range(col & startRow - 1).Offset(0, 0).Value = "month"
Range(col & startRow - 1).Offset(0, 1).Value = "forecast"
Range(col & startRow - 1).Offset(0, 2).Value = "broker"
Range(col & startRow - 1).Offset(0, 3).Value = "analyst"
Range(Range(col & startRow), Range(col & Rows.count).Resize(, 4)).Clear '<-- clear the output area
Columns(col).NumberFormat = "[$-408]mmm-yy;@" '<-- Turn output month col to date (Jan-87 format)
Application.ScreenUpdating = False
'For each row of data
For inputRow = startRow To endRow
Set cell = Range("E" & inputRow) '<-- start date
varDate = Split(cell.Value, "-")
'Create 4-digit year
strYear = IIf(Val("20" & varDate(2)) > Year(Date), "19" & varDate(2), "20" & varDate(2))
If Range(col & Rows.count).Value <> "" Then '<-- If Sheet full, exit
MsgBox "Sheet full"
GoTo SortData '<-- If you don't Sort, replace with Exit Sub
End If
'This is the end row of the output data so far
outputRow = Range(col & Rows.count).End(xlUp).Row '<-- Supply the column of your choice
For intI = 1 To Range("D" & inputRow).Value '<-- cell with number of months
If outputRow + intI > Rows.count Then '<-- If Sheet full, exit
MsgBox "Sheet full"
GoTo SortData '<-- If you don't Sort, replace with Exit Sub
End If
Range(col & outputRow + intI).Offset(0, 0).Value = DateSerial(strYear, varDate(1) + intI - 1, varDate(0))
Range(col & outputRow + intI).Offset(0, 1).Value = cell.Offset(0, -4).Value
Range(col & outputRow + intI).Offset(0, 2).Value = cell.Offset(0, -3).Value
Range(col & outputRow + intI).Offset(0, 3).Value = cell.Offset(0, -2).Value
Next
Next
SortData:
'In case you want to sort the output by date (column col)
If Range(col & Rows.count).Value <> "" Then
Set R = Range(Range(col & startRow), Range(col & Rows.count).Resize(, 4))
Else
Set R = Range(Range(col & startRow), Range(col & Rows.count).End(xlUp).Resize(, 4))
End If
R.Sort Key1:=Range(col & startRow), Order1:=xlAscending, Header:=xlNo
Application.ScreenUpdating = True
End Sub


Edit: In case you need anything else, I'll be back tomorrow, or someone else will answer your posts.

[mnykamp]

Sorry for the late reaction, i've be away for a couple of days. But wow :) that's great! The first thing i did this morning was to break up the table into manageble pieces (about 10) and give it a try. However i seem to be getting an overflow error in the following line;

Range(col & outputRow + intI).Offset(0, 0).Value = DateSerial(strYear, varDate(1) + intI - 1, varDate(0))

I can't really see why :/ ?

[tstav]

Hi mnykamp,

I thought you had turned to Access after not hearing from you for all these days.

Ok... since I'm not getting this error, please replicate it and when it happens, note down the whole message (number and description).
Then press debug to get to the code, hover your mouse over the yellow code line and note down the values of outputRow, intI, strYear, varDate(1) and varDate(0).
Finally, note down the original data of the original row e.g. 140, 20, 20, 5, 1-11-99.
Let us know what you noted.

tstav

[mnykamp]

Ok so i open up the file 1-2-2.xlsm, which is the first section of the data, alt-f11 into vba editor. Add the module and run. Error message pops op with text "Runtime error 6: Overflow". I then go to debug and find the line mentioned earlier highlighted. In the sheet the columns seem to have been added aligned exactly next to the exiting columns as you suggested. Anyhow many thanks for your continued interest in the matter !

perhaps it may be caused by column c where there is an identifier in this form; GB0008962655. I also tried to remove the the letters but need the leading zero's and thus the text formatting...

[mnykamp]

I've added the first section of the dataset on www.xs4all.nl/~mjwn (http://www.xs4all.nl/%7Emjwn) if you think its useful. The password to the archive is "nincompoop".

[tstav]

Where are the values of the variables of the erroring code line? You didn't post them.

[mnykamp]

whoops ok here goes;

Range(col & outputRow + intI).Offset(0, 0)= empty
col=G
outputrow=1
intI=1
strYear "191987"
varDate 0, 1 , 2 respectively 18, 2, 1987

Screenshot here (http://www.xs4all.nl/%7Emjwn/debug.jpg)



endRow 59999
startRow 2
R noting
cell 18-2-1987
col "G"
inputRow 2
intI 1
outputRow 1
strYear "191987"
varDate 0, 1 , 2 resp 18, 2, 1987

[tstav]

mnykamp,
the calculations on the date assumed the year was two digits, according to the example data you posted in your opening post.
So, I wrote code to create the four digit years.
Check the strYear variable: it has an extra "19" added before 1987.

Are all the years 4-digits? (I can't see your file, I use Excel 2003).

[tstav]

In case all years are 4-digit years, all you have to is:
1. Comment out the code line that creates 4-digit years.
2. Change the strYear to varDate(2) in the code line below.
That's all. Run it again and let me know.
tstav
'strYear = IIf(Val("20" & varDate(2)) > Year(Date), "19" & varDate(2), "20" & varDate(2))

Range(col & outputRow + intI).Offset(0, 0).Value = DateSerial(varDate(2), varDate(1) + intI - 1, varDate(0))

[mnykamp]

That's correct (again). I reformatted the values but forgot it didn't change the underlying values.. It works perfectly now and has lovely cyrillic encoding of the months ;) . Thanks so much for bearing with me! Please post/mail your name so I can mention you in the paper! :rotlaugh::bow:

[tstav]

Great!!! :):rotlaugh::) It'll give you a chance to study the Greek Alphabet!!!:rotlaugh:
Boy, did I laugh my heart out when I read your answer!

Ok, instead of taking Greek language courses, better replace the code line
Columns(col).NumberFormat = "[$-408]mmm-yy;@" '<-- Turn output month col to date (Jan-87 format)

with the one that'll give you month names in English.
To find the exact code for that, use the macro recorder and format any column to date (Mar-01 format).

mnykamp, you just lost your once-in-a-lifetime chance to learn one of the most ancient alphabets in the world...

[mnykamp]

You'd be surprised to know i spent four years of school learning just that. Too bad you don't talk in dactylic hexameters these days though :P

[tstav]

Mind you Cyrillic is not Greek but I bet both are mystery alphabets for the rest of the world...