Data transformation using Formula or Power Query

Hi,

I have a date set which needs to be maintained in two different layouts.
Every week I waste a lot of time converting from one layout to the other.
I know its possible to do it easily by Formula/Power Query

Would request all the experts to look into this.
Attached sheet with source and required results.

The revisions for an item may vary from 1 to even 5
The status in the final version should indicate status of the highest revision for that item



Thanks in advance

first: don't use merged cells

Originally Posted by sandy666

first: don't use merged cells

Hi sandy666,
Thanks for your reply and noted your point
That is the final layout.If we manage to get the data transformed in an unmerged condition also it will work.. the merging before final printing can be done later

But its the same layout as the source
The main challenge I face is to pivot the actual submission and actual review columns and displaying only the latest revision status

PowerQuery and PivotTable/PowerPivot works on columns and content (no mereged cells)
You can load source table as connection the use it to create PivotTable. With PowerQuery you can't iuse merged cells. You can try merge cells on PowerQuery table

like I said above: reorganize your target layout without merged cells if you want PQ or PT

Hi Sandy666,

I get your point.

Please check the modified target layout and advise if its possible to attain (even if with some slight modifications) using PQ or PT

Thanks

Hi sandy666,

Please find attached

I knew you want cut column to rows and columns.

All what I can do is not a wishful thinking but...

maybe someone else will give you solution with formula(s) or something

Thanks for your reply sandy666

Formula solution

B18

=INDEX($B$4:$B$13,MATCH($A18,$A$4:$A$13,0))

C18

=IFERROR(INDEX($C$4:$C$13,MATCH($A18&C$17,$A$4:$A$13&$D$4:$D$13,0)),"")

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Copy across to F and down

in G18

=IFERROR(INDEX($E$4:$E$13,MATCH($A18&G$17,$A$4:$A$13&$D$4:$D$13,0)),"")

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Copy across to J and down

in K18

=INDEX($F$4:$F$13,MATCH($A18,$A$4:$A$13,0)+COUNTIF($A$4:$A$13,$A18)-1)

Thanks JohnTopley and nflsales for your replies..

I will need a formula in Column A to get the list if unique items also..

Try

=LOOKUP(2,1/(COUNTIF($A$17:D17,$A$4:$A$13)=0),$A$4:$A13)