SOLVED
Home

Rearrange Data from Rows to Columns based on Time

%3CLINGO-SUB%20id%3D%22lingo-sub-332546%22%20slang%3D%22en-US%22%3ERearrange%20Data%20from%20Rows%20to%20Columns%20based%20on%20Time%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-332546%22%20slang%3D%22en-US%22%3E%3CP%3EHello!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20large%20file%20of%20Excel%20data%20I%E2%80%99m%20attempting%20to%20transform%20to%20prepare%20for%20use%20as%20a%20radial%20flow%20map%20within%20GIS%2C%20and%20I%20was%20hoping%20to%20receive%20some%20advice%20on%20how%20to%20use%20either%20pivot%20tables%20or%20formulas%20to%20achieve%20this%20result.%20I%20have%20a%20list%20of%20creator%20IDs%20for%20projects%20as%20well%20as%20the%20project%20start%20dates%20and%20locations.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEssentially%2C%20the%20table%20looks%20something%20like%20this%20(I%20removed%20irrelevant%20columns%20and%20kept%20on%20the%20first%2030%20rows%20as%20a%20sample%3A%20the%20full%20file%20is%20about%2070k%20rows%20long)%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20381px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F72257i33DA457C1F9CB9EF%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Image_1.png%22%20title%3D%22Image_1.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20column%20headers%20are%20largely%20self-explanatory%20(sequence%20is%20a%20numerical%20representation%20of%20the%20order%20in%20which%20the%20projects%20happened%3A%20%221%22%3D1st%2C%20%222%22%3D2nd%2C%20etc.).%20My%20hope%20is%20that%20a%20finished%20product%20could%20look%20something%20along%20the%20lines%20of%20this%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20500px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F72256i2847CA9E8CAE87C2%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Image_2.png%22%20title%3D%22Image_2.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EThere%20would%20be%20only%20one%20founder_id%20per%20row%3B%20and%20t1%20and%20t2%20would%20contain%20the%20first%20and%20last%20location%20values%20for%20each%20founder_id%20(I%20don%E2%80%99t%20need%20help%20finding%20lat%20and%20long%20values%3B%20I%22ll%20do%20that%20later).%20Is%20there%20a%20way%20for%20Excel%20to%20utilize%20either%20some%20complex%20variation%20on%20an%20IF%20formula%2C%20pivot%20table%20magic%2C%20or%20some%20other%20method%20to%20automate%20this%20process%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAlternatively%2C%20would%20there%20be%20a%20method%20to%20grab%20every%20location%20value%20for%20each%20founder_id%20and%20have%20the%20final%20table%20include%20t1%2C%20t2%2C%20t3%2C%20etc.%3F%20That%20might%20even%20be%20preferable.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20advice%20on%20how%20to%20even%20get%20started%20with%20this%20would%20be%20much%20appreciated!%20Thanks.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-332546%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-332985%22%20slang%3D%22en-US%22%3ERe%3A%20Rearrange%20Data%20from%20Rows%20to%20Columns%20based%20on%20Time%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-332985%22%20slang%3D%22en-US%22%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%2F72341i4194F3A336B1C25E%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22excel%20problem%20solve%204.png%22%20title%3D%22excel%20problem%20solve%204.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EHighlight%20sequence%20column%2C%20Go%20to%20Transform%20Tab%2C%20Select%20Pivot%20Column%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%2F72342iBC4F9499F94DD3BF%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22excel%20problem%20solve%203.png%22%20title%3D%22excel%20problem%20solve%203.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EValues%20Column%20%3D%20Location%2C%20Advanced%20options%20%22Don't%20Aggregate%22%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20the%20snips%20help!%20%3A%20)%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-332723%22%20slang%3D%22en-US%22%3ERe%3A%20Rearrange%20Data%20from%20Rows%20to%20Columns%20based%20on%20Time%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-332723%22%20slang%3D%22en-US%22%3E%3CP%3EOh%20nice%2C%20what%20you%20have%20there%20looks%20about%20perfect!%20I%20do%20appear%20to%20have%20access%20to%20the%20Power%20Query%20function%2C%20although%20I'm%20unfamiliar%20with%20it%20at%20this%20point%20in%20time.%20I've%20managed%20to%20follow%20your%20directions%20up%20to%20this%20point%20in%20time%3A%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%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F72293i6896DAD444449B36%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Screenshot%20(14).png%22%20title%3D%22Screenshot%20(14).png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20bullet%20points%20listed%20direct%20me%20to%20%22Select%20Pivot%20Column%22%20and%20that%20the%20%22Values%20Column%20Should%20%3D%20Location%22.%20I%20apologize%20if%20this%20is%20something%20fairly%20obvious%20I'm%20missing%2C%20but%20could%20you%20provide%20me%20with%20step-by-step%20directions%20on%20how%20to%20complete%20the%20operations%20from%20the%20point%20I%20posted%20until%20the%20end%20so%20I%20can%20create%20a%20table%20similar%20to%20the%20table%20you%20provided%3F%20I%20really%20appreciate%20your%20assistance!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-332693%22%20slang%3D%22en-US%22%3ERe%3A%20Rearrange%20Data%20from%20Rows%20to%20Columns%20based%20on%20Time%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-332693%22%20slang%3D%22en-US%22%3E%3CP%3EYou%20could%20solve%20this%20using%20Power%20Query%20if%20that%20is%20an%20option%20for%20you.%20This%20is%20the%20final%20Product.%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%2F72289iC07238AF23E5C2D9%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22excel%20problem%20solve.PNG%22%20title%3D%22excel%20problem%20solve.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CUL%3E%3CLI%3EMake%20data%20a%20table%3C%2FLI%3E%3CLI%3EGet%20Data%20From%20Table%2FRange%3C%2FLI%3E%3CLI%3EHighlight%20Sequence%20Column%3C%2FLI%3E%3CLI%3EGo%20to%20Transform%20Tab%3C%2FLI%3E%3CLI%3ESelect%20Pivot%20Column%3C%2FLI%3E%3CLI%3EValues%20Column%20Should%20%3D%20Location%3C%2FLI%3E%3CLI%3EClick%20arrow%20by%20advanced%20options%20and%20Select%20%E2%80%9CDon%E2%80%99t%20Aggregate%E2%80%9D%3C%2FLI%3E%3CLI%3EHit%20Okay%3C%2FLI%3E%3CLI%3EGo%20to%20Home%20Tab%3C%2FLI%3E%3CLI%3EClick%20Close%20and%20Load%3C%2FLI%3E%3C%2FUL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
michaelweigel
New Contributor

Hello!

 

I have a large file of Excel data I’m attempting to transform to prepare for use as a radial flow map within GIS, and I was hoping to receive some advice on how to use either pivot tables or formulas to achieve this result. I have a list of creator IDs for projects as well as the project start dates and locations.

 

Essentially, the table looks something like this (I removed irrelevant columns and kept on the first 30 rows as a sample: the full file is about 70k rows long):

Image_1.png

 

The column headers are largely self-explanatory (sequence is a numerical representation of the order in which the projects happened: "1"=1st, "2"=2nd, etc.). My hope is that a finished product could look something along the lines of this:

Image_2.png

There would be only one founder_id per row; and t1 and t2 would contain the first and last location values for each founder_id (I don’t need help finding lat and long values; I"ll do that later). Is there a way for Excel to utilize either some complex variation on an IF formula, pivot table magic, or some other method to automate this process?

 

Alternatively, would there be a method to grab every location value for each founder_id and have the final table include t1, t2, t3, etc.? That might even be preferable.

 

Any advice on how to even get started with this would be much appreciated! Thanks.

3 Replies
Solution

You could solve this using Power Query if that is an option for you. This is the final Product. 

excel problem solve.PNG

  • Make data a table
  • Get Data From Table/Range
  • Highlight Sequence Column
  • Go to Transform Tab
  • Select Pivot Column
  • Values Column Should = Location
  • Click arrow by advanced options and Select “Don’t Aggregate”
  • Hit Okay
  • Go to Home Tab
  • Click Close and Load

 

Oh nice, what you have there looks about perfect! I do appear to have access to the Power Query function, although I'm unfamiliar with it at this point in time. I've managed to follow your directions up to this point in time:

 

Screenshot (14).png

 

The bullet points listed direct me to "Select Pivot Column" and that the "Values Column Should = Location". I apologize if this is something fairly obvious I'm missing, but could you provide me with step-by-step directions on how to complete the operations from the point I posted until the end so I can create a table similar to the table you provided? I really appreciate your assistance!

 

excel problem solve 4.png

Highlight sequence column, Go to Transform Tab, Select Pivot Column

excel problem solve 3.png

 

Values Column = Location, Advanced options "Don't Aggregate" 

 

Hope the snips help! : ) 

 

 

 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 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
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies