• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Data Transpose

S

sambit

Member
Dear Sir,
Please help to resolve the issue.

Attached example file for your kind information.
 

Attachments

  • Example.xlsx
    15.9 KB · Views: 17
Vletm Sir,

I have inserted two remark column in Output Sheet. If I delete row no 4 in master sheet then the correspondence data of remark column also deleted.

For example –

If I delete row no 4 in master data

In output sheet Cell range (B8: F37) should be cleared.
 

Attachments

  • ExampleMO.xlsb
    19.3 KB · Views: 3
Using Power Query/Get and Transform here is the MCode for unpivoting the data

Code: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"E.Code", Int64.Type}, {"9/1/2018", type text}, {"9/2/2018", type text}, {"9/3/2018", type text}, {"9/4/2018", type text}, {"9/5/2018", type text}, {"9/6/2018", type text}, {"9/7/2018", type text}, {"9/8/2018", type text}, {"9/9/2018", type text}, {"9/10/2018", type text}, {"9/11/2018", type text}, {"9/12/2018", type text}, {"9/13/2018", type text}, {"9/14/2018", type text}, {"9/15/2018", type text}, {"9/16/2018", type text}, {"9/17/2018", type text}, {"9/18/2018", type text}, {"9/19/2018", type text}, {"9/20/2018", type text}, {"9/21/2018", type text}, {"9/22/2018", type text}, {"9/23/2018", type text}, {"9/24/2018", type text}, {"9/25/2018", type text}, {"9/26/2018", type text}, {"9/27/2018", type text}, {"9/28/2018", type text}, {"9/29/2018", type text}, {"9/30/2018", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"E.Code"}, "Attribute", "Value")
in
    #"Unpivoted Other Columns"
 
sambit
Did Your original 'hope' has any marks of those 'new columns'?
In output sheet Cell range (B8: F37) should be cleared.
So far, isn't there writings about those three columns?
How many 'remarks columns' could be there?
How about L-column? Should there happen something too?
Which rows .. any rows? Is there clear rule?
 
Vletm Sir,
Thanks for your response. Remark Colum may more than one in output sheet.
Actually, the remark column is to be inserted manually and the correspondence data of remark column is put manually.

if I delete row no 5 (E Code 10002) of the original sheet the correspondence data of output sheet delete. (Entire row)
 
sambit
There should be some kind of connection with both sheets informations...
Would next 'hope' that then You delete eg Code 10003 and
two days later, You would would like to UNDO Your deletion,
then also those 'possible remarks' should be there again?
or
You would like to add totally new sheet and
someway You write some 'remarks' somewhere,
which belongs to some rows from 'Master Sheet' ...
>>
If You write those Your 'remarks' to 'Master Sheet' cells comments
then those 'remarks' would follow to 'Output Sheet' too.
 
vletm sir,
Thanks for your comment for future activities. it may have happened. But
once delete any code in the master sheet. we don't undo the data in the master sheet.
 
You must log in or register to reply here.
Back
Top