PIVOT TABLE FORMATING

%3CLINGO-SUB%20id%3D%22lingo-sub-1281034%22%20slang%3D%22en-US%22%3EPIVOT%20TABLE%20FORMATING%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1281034%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20attached%20data%20set%20represents%20the%20different%20taxes%20paid%20by%20various%20taxpayers%20over%20a%203-year%20period.%20I%26nbsp%3B%20wish%20to%20pivot%20the%20data%20so%20that%20I%20will%20have%20the%20%3CSTRONG%3ETIN%3C%2FSTRONG%3E%20(Taxpayer%20Identification%20Number)%20and%20the%20the%20%3CSTRONG%3ETaxpayer%3C%2FSTRONG%3E%20as%20row%20labels%20in%20the%20first%20two%20columns%20but%20on%20the%20same%20row.%20The%20%3CSTRONG%3ETIN%3C%2FSTRONG%3E%20and%20the%20%3CSTRONG%3ETaxpayer%3C%2FSTRONG%3E%20refer%20to%20%3CEM%3E%3CSTRONG%3Eone%20%3C%2FSTRONG%3E%3C%2FEM%3Eand%20the%20%3CSTRONG%3E%3CEM%3Esame%20person%3C%2FEM%3E%3C%2FSTRONG%3E.%20It%20is%20therefore%20desirable%20to%20have%20them%20on%20the%20same%20row.%26nbsp%3B%20%26nbsp%3BI%20am%20having%20a%20challenge%20achieving%20that%20outcome.%20I%20am%20a%20beginner%20in%20excel.%20Th%20e%20attached%20workbook%20has%20three%20tabs%26nbsp%3B%20showing%20the%20raw%20data%2C%20the%20pivot%20result%26nbsp%3B%20I%20managed%20to%20achieve%20(after%20several%20tries)%20and%20the%20pivot%20result%20I%20actually%20desire.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20hope%20somebody%20can%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESadorc%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1281034%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1281178%22%20slang%3D%22en-US%22%3ERe%3A%20PIVOT%20TABLE%20FORMATING%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1281178%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F607391%22%20target%3D%22_blank%22%3E%40sacord6465%3C%2FA%3E%26nbsp%3BYou%20did%20not%20attach%20the%20data.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1283015%22%20slang%3D%22en-US%22%3ERe%3A%20PIVOT%20TABLE%20FORMATING%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1283015%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3BHi%20Riny%2C%20Forgive%20me.%20I%20am%20new%20here%20and%20don't%20quite%20know%20my%20way%20around.%20I%20recall%2C%20though%2C%20that%20I%20did%20attach%20the%20workbook%20using%20the%20%22browse%20files%22%20option.%3C%2FP%3E%3CP%3EI%20have%20tried%20again%20but%20I%20got%26nbsp%3B%20the%20following%20feeback%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CEM%3EThe%20attachment's%20%23sadorc.xlsx%20content%20type%20(application%2Fvnd.openxmlformats-officedocument.spreadsheetml.sheet)%20does%20not%20match%20its%20file%20extension%20and%20has%20been%20removed%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20afraid%20you%20may%20have%20to%20guide%20me%20in%20uploading%20the%20file.%20It%20is%20an%20excel%20workbook.%3C%2FP%3E%3CP%3EThank%20you%20in%20advance%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1283547%22%20slang%3D%22en-US%22%3ERe%3A%20PIVOT%20TABLE%20FORMATING%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1283547%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F607391%22%20target%3D%22_blank%22%3E%40sacord6465%3C%2FA%3E%26nbsp%3BNever%20had%20such%20a%20message%20myself%2C%20and%20I'm%20no%20expert%20on%20file%20types%20and%20formats%2C%20but%20it%20seems%20that%20you%20are%20not%20uploading%20a%20%22regular%22%20Excel%20file.%20It%20may%20have%20the%20xlsx%20extension%2C%20but%20the%20system%20somehow%20doesn't%20recognise%20it%20as%20such.%20Try%20this.%20Open%20your%20file%20in%20Excel%2C%20save%20it%20under%20a%20different%20name%2C%20make%20sure%20you%20choose%20the%20correct%20file%20format%20and%20upload%20again.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202020-04-06%20at%2006.57.58.png%22%20style%3D%22width%3A%20200px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F182289iEF084714134CE320%2Fimage-dimensions%2F200x69%3Fv%3D1.0%22%20width%3D%22200%22%20height%3D%2269%22%20title%3D%22Screenshot%202020-04-06%20at%2006.57.58.png%22%20alt%3D%22Screenshot%202020-04-06%20at%2006.57.58.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1284936%22%20slang%3D%22en-US%22%3ERe%3A%20PIVOT%20TABLE%20FORMATING%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1284936%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3BThank%20you.%20Here%20we%20go.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1285162%22%20slang%3D%22en-US%22%3ERe%3A%20PIVOT%20TABLE%20FORMATING%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1285162%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F607391%22%20target%3D%22_blank%22%3E%40sacord6465%3C%2FA%3E%26nbsp%3BNot%20sure%20how%20your%20%22Desired%20result%22%20came%20about%2C%20but%20it%20doesn't%20seem%20to%20be%20in%20line%20with%20the%20underlying%20data.%20Have%20changed%20the%20lay-out%20of%20the%20PT%20and%20saved%20your%20file%20as%20an%20xlsx%20file.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20this%20what%20you%20had%20in%20mind%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1285943%22%20slang%3D%22en-US%22%3ERe%3A%20PIVOT%20TABLE%20FORMATING%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1285943%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3BIn%20fact%20my%20problem%20is%20basically%20a%20layout%20problem.%20The%20desired%20result%20is%20not%20a%20pivot%20table.%20It%20is%20an%20ordinary%20excel%20sheet%20in%20which%20I%20have%20entered%20the%20data%20the%20way%20I%20would%20like%20it%20to%20look%20to%20be%20laid%20out%20-%20to%20have%20the%20TIN%20field%20and%20the%20Taxpayer%20field%20on%20the%20same%20row.%20The%20result%20achieved%20is%20the%20pivot%20table%20I%20managed%20to%20produce%20based%20on%20the%20data%20and%20my%20facility%20with%20pivot%20tables.%20On%20it%20you%20would%20notice%20that%20the%20TIN%20and%20Taxpayer%20fields%20are%20in%20the%20same%20column%20but%20consecutive%20rows.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20are%20right%20there%20was%20a%20typo%20in%20the%20desired%20result%20sheet.%20I%20have%20corrected%20it.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20find%20the%20corrected%20file%20attached.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hi,

 

The attached data set represents the different taxes paid by various taxpayers over a 3-year period. I  wish to pivot the data so that I will have the TIN (Taxpayer Identification Number) and the the Taxpayer as row labels in the first two columns but on the same row. The TIN and the Taxpayer refer to one and the same person. It is therefore desirable to have them on the same row.   I am having a challenge achieving that outcome. I am a beginner in excel. Th e attached workbook has three tabs  showing the raw data, the pivot result  I managed to achieve (after several tries) and the pivot result I actually desire.

 

I hope somebody can help.

 

Thanks in advance,

 

Sadorc

6 Replies
Highlighted

@sacord6465 You did not attach the data.

Highlighted

@Riny_van_Eekelen Hi Riny, Forgive me. I am new here and don't quite know my way around. I recall, though, that I did attach the workbook using the "browse files" option.

I have tried again but I got  the following feeback: 

 

The attachment's #sadorc.xlsx content type (application/vnd.openxmlformats-officedocument.spreadsheetml.sheet) does not match its file extension and has been removed

 

I'm afraid you may have to guide me in uploading the file. It is an excel workbook.

Thank you in advance

Highlighted

@sacord6465 Never had such a message myself, and I'm no expert on file types and formats, but it seems that you are not uploading a "regular" Excel file. It may have the xlsx extension, but the system somehow doesn't recognise it as such. Try this. Open your file in Excel, save it under a different name, make sure you choose the correct file format and upload again.

Screenshot 2020-04-06 at 06.57.58.png

Highlighted

@Riny_van_Eekelen Thank you. Here we go.

Highlighted

@sacord6465 Not sure how your "Desired result" came about, but it doesn't seem to be in line with the underlying data. Have changed the lay-out of the PT and saved your file as an xlsx file. 

 

Is this what you had in mind?

Highlighted

@Riny_van_Eekelen In fact my problem is basically a layout problem. The desired result is not a pivot table. It is an ordinary excel sheet in which I have entered the data the way I would like it to look to be laid out - to have the TIN field and the Taxpayer field on the same row. The result achieved is the pivot table I managed to produce based on the data and my facility with pivot tables. On it you would notice that the TIN and Taxpayer fields are in the same column but consecutive rows.

 

You are right there was a typo in the desired result sheet. I have corrected it. 

 

Please find the corrected file attached.