SOLVED

Rearrange Data from Rows to Columns based on Time

Copper 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
best response confirmed by michaelweigel (Copper Contributor)
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! : ) 

 

 

 

1 best response

Accepted Solutions
best response confirmed by michaelweigel (Copper Contributor)
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

 

View solution in original post