Excel Tranpose Text from Columns to Rows

[lhp2019]

Hello all,

This is a little bit complicate issue and I have been racking my brain to see how I do this in excel. The first tab "RawData" is sample of three accounts (which I have about 7,000). I would like to see how I can transpose from the RawData to how it should look in the "FinalData" tab. I would greatly appreciate anyone help on this issue. Thank you so much in advance!

[FlameRetired]

I am a little confused.

In the 'FinalData' sheet column C sometimes blanks are included and sometimes not. I do not see a pattern to the expected results.

Please enlighten me.

[AliGW]

This can be done quickly and easily with PowerQuery.

1. Load the data into the PQ editor (if you don't know how to do this, tell me).
2. With the first column selected, unpivot other columns (on the Transform ribbon).
3. Filter out blanks from the third column.
4. Close and load.

[lhp2019]

Hello Ali,

First time hearing about "Load the data into the PQ editor" so would you please explain. I will try to look it up on Google too. But the result is exactly what I wanted and you even took out all the blank field too. That is so great. Thank you so much!

[AliGW]

So, to load into the PQ editor, drag select the entire range of cells in the relevant grid on your master worksheet, then click on the From Table/Range icon on the Data ribbon (in the Get & Transform section). Then follow the instructions above.

I suggest you practise doing this from your original file, NOT my sample.

PS Close & Load is on the Home ribbon in the PQ editor.

[lhp2019]

Hello Dave,

The account is account for every month from 1/12012 to 12/1/2017. Every month in accounted for the account in a model. So one the account is Not in Firm (i.e. closed), then the month follow is blank. Thank you for looking into my question.

[AliGW]

See post #5 - the Unpivot Other is accessed via the drop-down on the Unpivot icon.

[lhp2019]

I will try in 30 minutes when I have a chance but how to or what do you mean by drag select the entire range of cells in the relevant grid "on your master worksheet?" How do I do that? Thanks!

[AliGW]

In other words, select cells A1 to BU11 so they are highlighted before clicking on the From Table/Range button - the whole range needs to be loaded.

I'll be back later on to see how you have got along.

[lhp2019]

I am using Excel 2013, I cannot find the Unpivot icon?

I cannot find "Form Table" from the power query icon. Again, please let me know if this option in not available in excel 2013 version. Thank you!

It looks like I have to download Power Query for excel 2013. I will look up how to do that than I will try again your suggestion.

So I have to wait for technology approval to download excel add-ins for Power Query as I do not see the option in the add-ins when I open excel 2013. So I will try once I get the add-ins Power Query. Thank you!

[AliGW]

My apologies - it is not standard in 2013, however you can download and install it for FREE - instructions here: https://www.excelcampus.com/install-power-query/

The From Table/Range icon is in Excel itself once you have the PQ add-in installed.

The Unpivot icon is on the Transform ribbon in the PQ window.

Download PowerQuery here: https://www.microsoft.com/en-us/down....aspx?id=39379

[lhp2019]

Ali,

Unfortunately, it takes a few days to get approval to download add-ins (yike!). I want to thank you for all your instructions and I will try it once I get the approval. On a side note, I use Tableau to do the pivot column and it works out as well. Have a great day!!!

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.