Hi everyone,

I have a time consuming task that I would like to automate.
Please see the attached file for an example of what I'm looking for.

Sheet Original has one line for each error, so to speak, with an address and error code.
The address and error code needs to be concatenated into one cell.

There are also columns that contain various text. These need to be distributed according to some rules:
  • One row in the original sheet is divided into three new rows.
  • Column A in the new sheet is created as =CONCATENATE(Original!A2;Original!B2;"0000000.*.";Original!C2;Original!$E$1)
  • The syntax in column A in the new sheet is important (and must be exactly as shown)

One row in the original table gets this treatment:
1st new row: A2 Concatenate according the example. B2 Value of D2 in original sheet.
2nd new row: A3 Concatenate as above, with contents of cell $E$1 in original sheet (Reason). B3 Value of E2 in original sheet.
3rd new row: A4 Concatenate as first row, but with contents of cell $F$1 in original sheet (Solution). B4 Value of F2 in original sheet.

This algorithm must be looped through for each line with content in the original sheet.

I would be forever grateful if someone could help with this.

Best regards,
Knut

Original sheet
A B Error number Designation Reason Solution
24 000 010 x1 y1 z1
24 000 011 x2 y2 z2
24 004 012 x3 y3 z3

Desirable outcome
Column A Column B
240000000000.*.010 x1
240000000000.*.010Reason y1
240000000000.*.010Solution z1
240000000000.*.011 x2
240000000000.*.011Reason y2
240000000000.*.011Solution z2
240040000000.*.012 x3
240040000000.*.012Reason y3
240040000000.*.012Solution z3