Data Compilation

[LifeLongLearner]

Hello all,
Greetings! My first question to members of Chandoo forum.

I have been struggling to find a way to transform data entered by users into a flat data for ease of pivoting and data viz. I've tried to explain my intentions in the attached.

Any elegant way to solve that without using VBA? The issue might have been addressed previously. If so, appreciate a link.

Many thanks,
LLL

 

[Somendra Misra]

Hi LLL,

Use below array formula in A15 and copy down.

=INDEX(LOOKUP(COLUMN($A$3:$M$3),COLUMN($A$3:$M$3)/($A$3:$M$3<>""),$A$3:$M$3),ROWS(N$13:N13))

Enter with Just Enter.

Than in B15 enter below formula and copy down and right, also confirm with Ctrl+Shift+Enter.

=IFERROR(INDEX($A$5:$M$8,COUNTIF($N$13:$N13,$N13),MATCH(O$12&$N13,$A$4:$M$4&LOOKUP(COLUMN($A$3:$M$3),COLUMN($A$3:$M$3)/($A$3:$M$3<>""),$A$3:$M$3),0)),"")

Enter with Ctrl+Shift Enter.

Put the heading in A14:D14.

Regards,

 

[LifeLongLearner]

Wow SM,
Unbelievably super first... Followed the instructions... The first part worked like charm! Having blank value in the second part though I did follow this Array formula 19 times though..
Will have to spend quite a while to understand your formula with my limited brain
Thankssssss

 

[Somendra Misra]

Welcome Back LLL, anytime

Regards,