Convert matrix to column (Vector)

[rajamdade]

Iam having huge matrix data (csv format) from B2:FHI4275

Taxa A\B GBCAT-01 GBCAT-02 GBCAT-03 GBCAT-04
GBCAT-01 100 81.1 78.6 81.4
GBCAT-02 81.1 100 79.9 82.2
GBCAT-03 78.6 79.9 99.9 81.6
GBCAT-04 81.4 82.2 81.6 100
…so on

I need output in column format for all three columns to be diplayed

Taxa A Taxa B Distance
GBCAT-01 GBCAT-01 100
GBCAT-01 GBCAT-02 81.1
GBCAT-01 GBCAT-03 78.6
GBCAT-01 GBCAT-04 81.4
GBCAT-02 GBCAT-01 81.1
GBCAT-02 GBCAT-02 100
GBCAT-02 GBCAT-03 79.9
GBCAT-02 GBCAT-04 82.2
GBCAT-03 GBCAT-01 78.6
GBCAT-03 GBCAT-02 79.9

…so on

Please suggest a formula or VBA script (In case of VBA iam new to it)

 

[Hui]

Rajamdale

Firstly, Welcome to the Chandoo.org Forums

Could you possibly post a sample file so we can see how the data is laid out and variability in field sizes

 

[rajamdade]

Thanks for your reply, now it has been solved as:
considere row labels in A2:E2 and column labels A3:A6, and matrix data B3:E6, further inserted formula displayed below in G2, H2 and it worked.

g2: =index($a$3:$a$6, mod(int( (rows(h$2:h2) - 1) / rows($a$3:$a$6)), rows($a$3:$a$6)) + 1)

h2: =index($b$2:$e$2, mod(rows(h$2:h2) - 1, columns($b$2:$e$2)) + 1)

i2: =index($b$3:$e$6, match(g2, $a$3:$a$6, 0), match(h2, $b$2:$e$2, 0))