Duplicate line and transpose data

If I do this manually, it’s easy to identify because I can see if the worksheet actually has the 4 quarterly columns in it. But the process is tedious when there are many data rows. If there are 100 lines, I need to repeat the same process 100 times. I want to build a macro to break each line with 4 quarterly score into four separate lines. The macro can change the data directly on the same worksheet, no need to create new worksheet.

Here are the steps.

1. Find 4 quarterly average, however, the heading texts are not consistent. These are the heading I can identify so far: Qtr1, Q01, Q’01, Q1’2014 , Q1’14.
2. Note: To make it even more difficult, there may be other column in between “Teacher”, “School”, “Class Number”, “Location”, “Average”, but the Q1 to Q4 column will almost always be continuous sequence (no other column in between them).
3. If the macro can’t find the 4 columns (qtr1 to qty4) with average data, then exit sub.
4. If found, do this.
   • Insert a new column, after “Location”, call it “C1”.
   • Insert a new column, call it “Qtr Avg”, at the last column .
   • Go to first teacher (first data row), copy the line.
   • Duplicate it 3 times, insert the lines below the line it copied.
   • In “C1” column, get the value from column “Class Number”, then add “-R-Q0#”. # is the quarterly number from 1 to 4.
   • In “Qtr Avg” column for each of the 4 lines, get/copy the respective quarter average. Then round up the value with no decimal point e.g. round(average, 0).
   • Go to next teacher. Repeat steps C to G.
   • Once the last data row is done, we can remove these 4 columns “Average”, “Q1”, “Q2”, “Q3” and “Q4”.

hi Efjoker, option, please check attachment, press Run button or runcode "test" (ALT+F8, select "test", press Run button).

You asked not to put result to a separate sheet but for testing it was done on a separate sheet for now. If you are happy with the testing results I will amend it to be on the same sheet.

Hi Bernie, I tried your macro too. Yes it got the job done. Thank you very much.

please check attachment

After I try the macro on more files, I found one exception.

There’s rare case where the line only has an average value, but none quarterly value. When I see this, I don't have to duplicate the line. The C1 column will have “Class Number” + “-R-A01”. This “-R-A01” is a fixed string. Then the Qty Avg will get the value from Average column. The file will look like 4Qa.xls.

please check attachment

I see. I tried similar if-then statement last night like you what did in 4Q(2), but I didn't know how to exit the loop to stop the repetition. The "exit for" is needed. Thank you very much.