0

I have an Excel file that contains 3 columns. The first column contains the country/region codes, the second the country/region name and the third column contains the year.

In total I have 354 different values of country/region and 37 different years (from 1980 to 2016).

Here is a simplified example of my file. In this example there are 3 different values of country/region and 6 years (from 1980 to 1985).

|   COL A   |   COL B   |   COL C  |
+-----------+-----------+----------+
     AT     |  Austria  |   1980
     AT     |  Austria  |   1981
     AT     |  Austria  |   1982
     AT     |  Austria  |   1983
     AT     |  Austria  |   1984
     AT     |  Austria  |   1985
     AT     |  Austria  |   1980
     BE     |  Belgium  |   1980
     BE     |  Belgium  |   1981
     BE     |  Belgium  |   1982
     BE     |  Belgium  |   1983
     BE     |  Belgium  |   1984
     BE     |  Belgium  |   1985
    CH07    |  Ticino   |   1980
    CH07    |  Ticino   |   1981
    CH07    |  Ticino   |   1982
    CH07    |  Ticino   |   1983
    CH07    |  Ticino   |   1984
    CH07    |  Ticino   |   1985

What I would like is this:

|   COL A   |   COL B   |   COL C  |   COL D   |
+-----------+-----------+----------+-----------+
     AT     |  Austria  |   1980   |     A
     AT     |  Austria  |   1981   |     A
     AT     |  Austria  |   1982   |     A
     AT     |  Austria  |   1983   |     A
     AT     |  Austria  |   1984   |     A
     AT     |  Austria  |   1985   |     A
     AT     |  Austria  |   1980   |     B
     AT     |  Austria  |   1981   |     B
     AT     |  Austria  |   1982   |     B
     AT     |  Austria  |   1983   |     B
     AT     |  Austria  |   1984   |     B
     AT     |  Austria  |   1985   |     B
     AT     |  Austria  |   1980   |     C
     AT     |  Austria  |   1981   |     C
     AT     |  Austria  |   1982   |     C
     AT     |  Austria  |   1983   |     C
     AT     |  Austria  |   1984   |     C
     AT     |  Austria  |   1985   |     C
     AT     |  Austria  |   1980   |     D
     AT     |  Austria  |   1981   |     D
     AT     |  Austria  |   1982   |     D
     AT     |  Austria  |   1983   |     D
     AT     |  Austria  |   1984   |     D
     AT     |  Austria  |   1985   |     D
     BE     |  Belgium  |   1980   |     A
     BE     |  Belgium  |   1981   |     A
     BE     |  Belgium  |   1982   |     A
     BE     |  Belgium  |   1983   |     A
     BE     |  Belgium  |   1984   |     A
     BE     |  Belgium  |   1985   |     A
     BE     |  Belgium  |   1980   |     B
     BE     |  Belgium  |   1981   |     B
     BE     |  Belgium  |   1982   |     B
     BE     |  Belgium  |   1983   |     B
     BE     |  Belgium  |   1984   |     B
     BE     |  Belgium  |   1985   |     B
     BE     |  Belgium  |   1980   |     C
     BE     |  Belgium  |   1981   |     C
     BE     |  Belgium  |   1982   |     C
     BE     |  Belgium  |   1983   |     C
     BE     |  Belgium  |   1984   |     C
     BE     |  Belgium  |   1985   |     C
     BE     |  Belgium  |   1980   |     D
     BE     |  Belgium  |   1981   |     D
     BE     |  Belgium  |   1982   |     D
     BE     |  Belgium  |   1983   |     D
     BE     |  Belgium  |   1984   |     D
     BE     |  Belgium  |   1985   |     D
    CH07    |  Ticino   |   1980   |     A
    CH07    |  Ticino   |   1981   |     A
    CH07    |  Ticino   |   1982   |     A
    CH07    |  Ticino   |   1983   |     A
    CH07    |  Ticino   |   1984   |     A
    CH07    |  Ticino   |   1985   |     A
    CH07    |  Ticino   |   1980   |     B
    CH07    |  Ticino   |   1981   |     B
    CH07    |  Ticino   |   1982   |     B
    CH07    |  Ticino   |   1983   |     B
    CH07    |  Ticino   |   1984   |     B
    CH07    |  Ticino   |   1985   |     B
    CH07    |  Ticino   |   1980   |     C
    CH07    |  Ticino   |   1981   |     C
    CH07    |  Ticino   |   1982   |     C
    CH07    |  Ticino   |   1983   |     C
    CH07    |  Ticino   |   1984   |     C
    CH07    |  Ticino   |   1985   |     C
    CH07    |  Ticino   |   1980   |     D
    CH07    |  Ticino   |   1981   |     D
    CH07    |  Ticino   |   1982   |     D
    CH07    |  Ticino   |   1983   |     D
    CH07    |  Ticino   |   1984   |     D
    CH07    |  Ticino   |   1985   |     D

So what I want is:

  • create a new column (column D)
  • duplicate the 6 lines of each country/region 5 times (in order to have a total of 6 values for each value of column D)
  • the values of column D are standard (in this example they are always A, B, C and D)

How can I do that? Doing this manually is too much work and you risk making mistakes.

Unfortunately it is difficult to explain in words, but I hope the example is helpful.

Thank you

0

Try this:

  1. Insert a column D, enter value 'A' in all the 354*37 cells.
  2. Copy and paste this in a new sheet (say sheet_2)
  3. Go back to the original page and replace 'A' in column D with 'B'
  4. Append this to table in sheet_2
  5. Repeat steps 3 and 4 with remaining values ('C' and 'D' in example given)
  6. Sort sheet_2.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.