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

@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

Highlighted

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
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
30 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies