Excel: Transpose data from rows to column headings -

my problem, title states, has transposing data rows columns original data came csv format in second column 'dates , rank' semicolons , spaces acted delimiters.

 country    dates , rank switzerland [2014-03-07] 7;[2014-10-17] 7; usa         [2011-07-01] 7;[2012-02-17] 7;[2012-09-30] 7;[2013-01-31] 7; france      [2011-07-01] 4;[2012-02-17] 4;[2012-09-30] 4; china       [2015-01-16] 7;[2015-03-06] 7;[2015-10-16] 7;[2015-10-23] 7; 

so, using text columns function excel separated data within 'dates , rank' column different cells. , result looks this:

 country        dates , rank  c   d               e   f               g   h                               switzerland    [2014-03-07]    7   [2014-10-17]    7                 usa            [2011-07-01]    7   [2012-02-17]    7   [2012-09-30]    7   [2013-01-31]    7  france         [2011-07-01]    4   [2012-02-17]    4   [2012-09-30]    4         china          [2015-01-16]    7   [2015-03-06]    7   [2015-10-16]    7   [2015-10-23]    7 

*i have deleted square brackets dates , assigned them ymd cells. need use dates headings while use rank (1-7) allocated data within cells. example:

 country        2011-07-01  2012-02-17  2014-03-07 2014-10-17         switzerland                                  6         7             usa                 5            7  

any suggestions please? thank you.

i'll assume data starts in cell a1 (i.e. a1 contains "country").

first, create new worksheet newly ordered data come. first need fill in column header row. can

1. manually copy-pasting date columns original data single column in new worksheet, dates in 1 column
2. remove duplicates (in ribbon: data > data tools > remove duplicates)
3. sort list if want (data > sort & filter > sort)
4. select resulting data, copy, , transpose-paste in cell b1 (home > paste > paste special, , check box "transpose"). can remove data copied.

second, copy-paste column of source worksheet column of destination worksheet.

finally, each country , each date want data right next date. can using following formula in b2:

= iferror( index( sheet1!2:2, 1, match( b$1, sheet1!2:2, 0 ) + 1 ), "" ) 

finally, copy formula down , across.

this should trick fine, not flexible solution , need follow steps explained. example, if countries in different order in original data sheet, won't work.