Insert columns based on cell value

I have a bit of a sticky problem, and I'm not really comfortable with VBA, so please bear with me... Hopefully one of you experts who like really tough nuts will enjoy cracking this

I have three massive spreadsheets (of archival photograph inventories for the curious) that I'm working on cleaning up. I've attached the smallest of them, but the largest one will have nearly 100,000 rows total. So you can hopefully understand why I want to automate the process.

The workbook has two sheets: singles and clusters. singles is what it's supposed to look like, so all of the work needs to be done in clusters. I need to achieve the following steps:

1. insert a number of rows beneath the filled row, according to the value in column -1
2. copy values in rows to those new rows
3. make the catalog number sequential from the first value addressed in column A

For example, in row 2, I need to insert two rows ("B2"-1) underneath the row, copy the values from row 2 into new rows 3 and 4, THEN assign A2 a value of 05_00490, A3=05_00491, and A4=05_00492

And of course all of the values in the B column are different (sigh)

Now I anticipate that steps 2 and 3 will need to be hand-done, but if there's a way to automate that, too, that would be great.

I'm on the super-annoying Excel 2010 for Mac, but if necessary, I can move the file to the PC version, so if someone has some tips, please let me know which version you were working on so I can test it? Thanks in advance for any help anyone might be able to provide!!

Isara,

I wrote some code that I believe accomplishes what you are hoping to do. Review the code in the attached workbook. I copied the Clusters sheet, creating a new sheet Clusters(2), then ran the Macro on the clusters (2) sheet. Essentially, the Clusters(2) sheet is what the data will look like after running the macro. Let me know if this works for you, and also let me know if it needs to be tweaked in anyway.

I created this file using Excel 2007.

Please report back with details

Dear BigBas:

You. are. amazing!!!

That worked exactly as I needed it to (once I realized that I was using Mac Excel 2008.. which doesn't have VB Editor. Grrr). I realized that some normalization on my other spreadsheets was necessary, so I ended up having to adjust the numbering scheme to match the macro, since I couldn't figure out how to change it to match something that looks like this:

02_A-00456
02_Q-00103

(those were single-values in the A column, but there was an associated range in the B column)

So in the future, if I need to do that again, what bits would I look at changing to fit the data?

Thank you so, so, so much for saving my bacon. I ended up with approximately 150,000 rows across three spreadsheets, by the way.