INSERT Number of Rows based on Cell Value and populate rows below with initial value

Hi there

Attached is a spreadsheet with two tabs - one includes the starting data, the other Tab shows the result that I am trying to generate from the source data.

What I am trying to do is insert a number of rows between each row based on the value in Column B. I can do that using the following or code:

Please Login or Register  to view this content.

What this refers to is a *.pdf filename and then consequtive page numbering in each PDF document.

What I want to do is populate the inserted rows with consequtive numbers in the inserted rows below each number in Column A. I also want to generate the individual page number in the PDF document in Column C and populate Column D with the original filename number for each page in that range. It is probably easier to see what I mean by looking at the "Desired output example" tab in the attached spreadsheet.

Just a quick question.
Should Cell B18 in your example not be zero(0) and shift the next three values one cell down?

Hi Sintek,

I see that I may have changed the numbers on "sheet 1" as I was playing around with trying to get the filename in Column D of that sheet to show correctly. The desired output in rows 101 and 102 that you have higlighted above are correct in principle, (provided that the input document numbers were 101.0100 and 101.0101 (each of these documents being only 1 page in length).

I deleted everything except the data in Column A
It looks like it gives you the result you want.

Please Login or Register  to view this content.

Jolivanes,

Thanks. That is a convienient way to do it and it is very helpful to have the "undo result" as well. However it is not quite correct as what is in Column C needs to be 101.0001 in rows C2 to C14 etc (i.e. for the 13 pages in document 101.0001 all File Names must be 101.0001.pdf). If it did that this solution would work fine. A "nice to have" is making the original data in bold in the output as it makes it easy to see the divisions between documents but that is not critical.

Sintek

Thank you that works except that any File name reference number in Column A that ends in 0 (e.g.101.0070 or 101.0110) produces the incorrect file name in Column C for the remainder of the entries for that file reference number because Excel truncates the trailing zero. I haven't worked out how to stop excel from truncating the trailing zero. Any ideas?

Thanks that works perfectly.

I just left the "undo result" code attached to that button that was in your previous example and it works for what I need i.e. it returns the sheet back to the starting point. In any event I always have the document identifiers in Column A as they are extracted from the Windows folder container all of the underlying files so I can always repopulate Column A with any numbers I need. It's just convienient in case I missed a row etc.

Many thanks

Re: "it returns the sheet back to the starting point." Only when the data of Column A has been copied into a sheet named "Desired output example" from range F2:F21.
Of course if you delete the copy and paste part out of the code, it does clear the sheet.
Thanks for letting us know that all is working to your liking and good luck.