1

I have data in the below format.

enter image description here

This is the result I am looking to achieve.

enter image description here

As I don't find any direct way in excel to transpose the data as per my requirement, so i am looking it to do it programmatically but as i never wrote macro in VBA, can you tell me how can I proceed?

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
sonu888
  • 15
  • 5

1 Answers1

0

Assume "Input table" housed in A1:E4 include header

"Output table" criteria G1:I1: "Resource", "Skill" and "Value"

In "Output table" "Resource" column G2, copied down :

=IFERROR(INDEX(A$2:A$4,AGGREGATE(15,6,ROW(A$2:A$4)-ROW(A$1)/(B$2:E$4>0),ROWS($1:1))),"")

In "Output table" "Skill" column H2, copied down :

=IF(G2="","",INDEX(B$1:E$1,AGGREGATE(15,6,COLUMN(B$1:E$1)-COLUMN($A1)/(INDEX(B$2:E$4,MATCH(G2,A$2:A$4,0),0)>0),COUNTIF(G$2:G2,G2))))

In "Output table" "Value" column I2, copied down :

=IF(G2="","",INDEX(B$2:E$4,MATCH(G2,A$2:A$4,0),MATCH(H2,B$1:E$1,0)))
bosco_yip
  • 3,762
  • 2
  • 5
  • 10
  • Hi bosco_yip the 1st formula is not picking the next resource name, do I need to change something in formula? – sonu888 Oct 20 '19 at 06:57
  • 1] The "Output"f Resource name, in 1st formula G2:G10 return : "John","John","John","Bill","Bill","Mohan","Mohan","Mohan","Mohan". Then what do you wanted to return. 2] What is the meaning of "not picking the next resource name"? Give more information and example (Don't change anything in your original data prior to clarify your question) – bosco_yip Oct 20 '19 at 07:28
  • Hi @bosco_yip, it was my mistake while using the formula, the provided solution perfectly works for my need. You are super. Thanks a lot. – sonu888 Oct 20 '19 at 13:09