Taking rows of data and moving to 4 columns of data, repeating the first 3 columns

I have a worksheet that has 3360 rows of data where the first 3 columns define Part Number, Description and Model type. Columns D through V define the Model Series. I have a need to define each model series on a separate row, however, I need to repeat the first three cells of every row for every Model Series. Some Part Numbers have no Model Series available, and some have 19.

I believe the best way to handle this is through Macro, I'm just not sure where to start. Any help would be greatly appreciated. I've attached a sample workbook with Input Data, which is what I currently have and Output Data which is what I want it to look like (minus the formatting) Eligibilty Sample.xlsx

Thanks in advance for your help.

Please Login or Register  to view this content.

I ran the macro, however, it doesn't pull all the data. I am starting with 3360 lines, but have 1221 unique part numbers, and should end up with around around 4502 lines, but I'm ending up with 4271 lines and 309 unique part numbers, and some of the data isn't accurate. I thought the error my be coming from having some blank cells in column D and those parts numbers were being skipped, but I put data in those fields as a place holder to check my assumption, and it didn't help.

It is very interesting to say the least trying to figure out where the problem lies. If I use the sample document provided, it has one blank cell in column D, when running it as is, it takes the next part number and eliminates it, using the information from the line with the blank cell in column D. However, if I populate it with data, say the word Blank, it removes or even the first 2 part numbers from the Input file all together.

I've tried to follow the logic of what is going on in the macro and I can't tell what causing the problem. If it makes a difference I'm using Excel 2013.

Thank you for helping.

These situations are always challenging. On the example workbook you provided, the macro accomplishes exactly what your output specified (well technically your output had a slight error on line 3 which I compensated for). However, I recognize that there may be many other factors going on which I unfortunately cannot anticipate. I would recommend submitting another example workbook that more clearly demonstrates some of the mitigating/variable factors, such as the unique values or various blank cells you just indicated. Remember to include a before and after.

At worst, I'm able to get rid of the blanks if I need to in order to make it work, but even when I tried that with the macro the way it is still yielded in-accurate results. I've done as you asked and included more data to resemble a decent sample of what I'm dealing with. The "Input Data" tab is the data I have, the "Output Data" tab is the results from the macro, and the "Output Data 1" is what the results should look like. I've highlighted the area where the macro has resulted in error.

Eligibilty Sample2.xlsx

Again I appreciate your help, normally I'm pretty decent a troubleshooting a macro, but this one has me confused.

Thank you.

Found the error.

Note: I think your Output Data 1 sheet, row 5, is incorrect so I ignored it.

Please Login or Register  to view this content.

You are right, Line 5 was in Error.

I believe that worked.

Thanks for your help.