Forum Discussion

Centaur's avatar
Centaur
Helper V
4 years ago
Solved

Transpose or Unpivot

Hello,   I have a file  Co Name, Amount and Date are all in separate columns.  I want: Date to be transposed to rows but keep the Co Name and Amount in rows But Group co name.    I am not sur...
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    4 years ago

    try this (my) last guess!

Centaur's avatar
Centaur
Helper V
4 years ago

Hi, I think it woudl be better that I provide the complete file.  

here it is on my onedrive.  

[removed link]

sheet 2 has the data.  

 

let me know if you have any questions.  

If I could add a twist. 

Grouping:

parent:  

ProjID (in 1 row)

then by

LCID (in same row as ProjID)

Company Name (in same row as ProjID)

Amount (in same row as ProjID)

LCName (in same row as ProjID)

 

then the dates along top 

I hope that makes sense.  

I think its a lot easier than how I am explaining. 

 

CompanyName01/10/202101/11/202101/12/202101/01/2022

Co A10203040

much like the original but adding projID I guess the "parent" grouping.  I need this since there are same companies in different projects.  

ronrsnfld's avatar
ronrsnfld
Super User
4 years ago

Is this what you want for output from your posted file (obviously with more columns and rows)?

 

 

If so, it's just a matter of 

  • delete the unwanted columns
  • Pivot on the Date column
    • Values = Amt
    • No aggregation
  • Centaur's avatar
    Centaur
    Helper V
    4 years ago

    Hi ronrsnfld, yes I think that is what I want.  I did not know it was that simple.  I was over thinking I think.  thank you very much for the explanation.