Home

Data Format Conversion

%3CLINGO-SUB%20id%3D%22lingo-sub-803111%22%20slang%3D%22en-US%22%3EData%20Format%20Conversion%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-803111%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20data%20for%20over%201%2C500%20items%20in%20the%20following%20format%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F126786i1517070AE0666B2B%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22clipboard_image_0.png%22%20title%3D%22clipboard_image_0.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EAnd%20want%20to%20convert%20it%20to%20this%20format%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F126794i0716C131584FE27A%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22clipboard_image_3.png%22%20title%3D%22clipboard_image_3.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EPlease%20note%20that%20the%20data%20in%20the%20second%20worksheet%20is%20in%20weeks%20and%20doesn't%20directly%20align%20with%20the%20date%20in%20the%20first%20worksheet.%26nbsp%3B%20I%20would%20prefer%20for%20the%20quantities%20to%20be%20populated%20in%20the%20column%20of%20the%20date%20following%20the%20receipt%20date.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20suggestions%20would%20be%20greatly%20appreciated!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-803111%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-803133%22%20slang%3D%22en-US%22%3ERe%3A%20Data%20Format%20Conversion%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-803133%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F391651%22%20target%3D%22_blank%22%3E%40Teachtheworldtosing101%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20can%20approach%20that%20using%20the%20%3CA%20href%3D%22https%3A%2F%2Fsupport.office.com%2Fen-us%2Farticle%2Fcreate-a-pivottable-to-analyze-worksheet-data-a9a84538-bfe9-40a9-a8e9-f99134456576%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3EPivotTables%3C%2FA%3E%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F126816i6FB5C996F9C7EEC8%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22PivotTables.png%22%20title%3D%22PivotTables.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20that%20helps%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-803167%22%20slang%3D%22en-US%22%3ERe%3A%20Data%20Format%20Conversion%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-803167%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F391651%22%20target%3D%22_blank%22%3E%40Teachtheworldtosing101%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EBit%20expanding%20that%20-%20create%20helper%20Calendar%20table%20with%20dates%20and%20related%20weeks%2C%20create%20relationship%20on%20dates%20between%20tables%20and%20pivot%20from%20data%20model.%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F126820i34C78A416BD75043%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22clipboard_image_0.png%22%20title%3D%22clipboard_image_0.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-803180%22%20slang%3D%22en-US%22%3ERe%3A%20Data%20Format%20Conversion%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-803180%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F35679%22%20target%3D%22_blank%22%3E%40Haytham%20Amairah%3C%2FA%3E!%26nbsp%3B%20I%20have%20considered%20using%20a%20pivot%20table%20and%20grouping%20the%20data%20but%2C%20for%20some%20reason%2C%20the%20version%20of%20Excel%20that%20I%20have%20only%20allows%20for%20the%20data%20to%20be%20grouped%20by%20month%2C%20quarter%2C%20and%2For%20year.%26nbsp%3B%20Also%2C%20I%20simplified%20the%20worksheet%20that%20I%20am%20using%20in%20the%20previous%20post.%26nbsp%3B%20The%20receipt%20quantity%20is%20actually%20embedded%20in%20a%20larger%20set%20of%20data.%26nbsp%3B%20It%20would%20be%20in%20Row%2013%20of%20the%20worksheet%20shown%20below%20(just%20one%20part%20number%20of%20the%20over%201%2C500%20mentioned%20before).%26nbsp%3B%20I%20was%20hoping%20there%20would%20be%20a%20way%20to%20do%20it%20without%20creating%20a%20pivot%20table%20but%20maybe%20that%20is%20wishful%20thinking.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F126822i2F6527CE785C6DB4%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22clipboard_image_0.png%22%20title%3D%22clipboard_image_0.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Teachtheworldtosing101
New Contributor

I have data for over 1,500 items in the following format:

clipboard_image_0.png

And want to convert it to this format:

clipboard_image_3.png

Please note that the data in the second worksheet is in weeks and doesn't directly align with the date in the first worksheet.  I would prefer for the quantities to be populated in the column of the date following the receipt date.

 

Any suggestions would be greatly appreciated!

3 Replies
Highlighted

@Teachtheworldtosing101

 

Hi,

 

You can approach that using the PivotTables:

PivotTables.png

 

 

Hope that helps

@Teachtheworldtosing101 

Bit expanding that - create helper Calendar table with dates and related weeks, create relationship on dates between tables and pivot from data model.

clipboard_image_0.png

Thank you @Haytham Amairah!  I have considered using a pivot table and grouping the data but, for some reason, the version of Excel that I have only allows for the data to be grouped by month, quarter, and/or year.  Also, I simplified the worksheet that I am using in the previous post.  The receipt quantity is actually embedded in a larger set of data.  It would be in Row 13 of the worksheet shown below (just one part number of the over 1,500 mentioned before).  I was hoping there would be a way to do it without creating a pivot table but maybe that is wishful thinking.

 

clipboard_image_0.png

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies