Data Consolidation in an table

[Such]

Dear,

Can anyone help me to achieve below mentioned task?

I have an table with data related to company.

Example Current

Country Name Name 2 Name 3 Sex
AT Man One Bit Female
AT Jan Two Sit Male
AT San Three Kit Male
AU Pan Four Lit Female
AU Can Five nit Female
AU Ram Six rit Male
AU Sum Seven ait Female
AU Pam Eight cit Male
AU Sun Nine dit Female
AU Ten Ten mit Male


What needed

Country Name Sex
AT Man Female
AT One Female
AT Bit Female
AT Jan Male
AT Two Male
AT Sit Male


The above is Just an Example I have more number of Column in my spreadsheet.

Can you please help me to achieve the desired result? I am looking for an easy way to achieve this.


Thanks a Ton!!
Such

 

[SirJB7]

Hi, Such!

As a new user you might want to (I'd say should and must) read this:
http://chandoo.org/forum/forums/new-users-please-start-here.14/

And regarding your issue, try these formulas:
G2: =INDICE(A:A;ENTERO((FILA()-2)/3)+2) -----> in english: =INDEX(A:A,INT((ROW()-2)/3)+2)
H2: =INDICE(B:D;ENTERO((FILA()-2)/3)+2;RESIDUO(FILA()+1;3)+1) -----> in english: =INDEX(B:D,INT((ROW()-2)/3)+2,MOD(ROW()+1,3)+1)
I2: =INDICE(E:E;ENTERO((FILA()-2)/3)+2) -----> in english: =INDEX(E:E,INT((ROW()-2)/3)+2)
Copy down as required (until the 1st row of zeros appears, then delete it).

Regards!