Convert Row/Column data to Database

[Aviaro]

Is there any easy method of converting/transposing the data in columns a-c
and rows 1-3 in the first section below to the database setup in the second
setion. The example here can be done manually in a few seconds but this would
not be possible when dealing with over 100 rows and over 13 columns?

a b c
1 10 40 70
2 20 50 80
3 30 60 90

1 a 10
2 a 20
3 a 30
1 b 40
2 b 50
3 b 60
1 c 70
2 c 80
3 c 90

Thanks

 

[smartin]

Is there any easy method of converting/transposing the data in columns a-c
and rows 1-3 in the first section below to the database setup in the second
setion. The example here can be done manually in a few seconds but this would
not be possible when dealing with over 100 rows and over 13 columns?

a b c
1 10 40 70
2 20 50 80
3 30 60 90

1 a 10
2 a 20
3 a 30
1 b 40
2 b 50
3 b 60
1 c 70
2 c 80
3 c 90

Thanks

Here's a way, but I am curious what others will come up with.

Set up your original table in Sheet1 and the following in a separate
worksheet for clarity.

Column labels in row 1:
A: Row index
B: Column index
C: Row label
D: Column label
E: Data

Formulae in row 2:
A: 1
B: 1
C: =INDEX(Sheet1!$A$2:$A$4,A2) 'reference the row labels in Sheet1
D: =INDEX(Sheet1!$B$1:$D$1,B2) 'reference the col labels in Sheet1
E: =INDEX(Sheet1!$B$2:$D$4,A2,B2) 'reference the data only in Sheet1

Formulae in row 3 and fill down as far as needed:
A: =A2+(B3=1)
B: =MOD(B2,COUNTA(Sheet1!$B$1:$D$1))+1 'reference the col labels in Sheet1
C,D,E: fill down from row 2

Adjust ranges 'as noted

Columns C-E on the new worksheet have the layout you are looking for.

 

[Aviaro]

Thank you "smartin", that was brilliant, your solution has solved an issue
which had no remedy locally. I would never have come up with the use of
functions "index", "counta" & "mod" for the solution.
Aviaro

 

[smartin]

Divide & Conquer! It's just a looping mechanism really.

Ironically, I never had occasion to use this method before, but I did
need it only three days after posting.