How to Change Data to Row Titles and vice versa [SOLVED]

[mikallangelo]

I am trying to build a product for pilot scheduling. Currently I track pilots based on which route they are flying on a given day as shown below.

[pre]

Name	01-Oct	02-Oct	03-Oct	04-Oct	05-Oct	06-Oct	07-Oct
Pilot A	Dallas	OFF	Memphis	Denver	Houston	Miami	Chicago
Pilot B	Chicago	Dallas	OFF	Memphis	Denver	Houston	Miami
Pilot C	Miami	Chicago	Dallas	OFF	Memphis	Denver	Houston
Pilot D	Houston	Miami	Chicago	Dallas	OFF	Memphis	Denver
Pilot E	Denver	Houston	Miami	Chicago	Dallas	OFF	Memphis
Pilot F	Memphis	Denver	Houston	Miami	Chicago	Dallas	OFF
Pilot G	OFF	Memphis	Denver	Houston	Miami	Chicago	Dallas

I need help figuring out how to pull the name of the pilot for each date based on the route they are flying. The table below shows what I want the output to look like:


Route	01-Oct	02-Oct	03-Oct	04-Oct	05-Oct	06-Oct	07-Oct
Dallas	Pilot A
Chicago	Pilot B
Miami	Pilot C
Houston	Pilot D
Denver	Pilot E
Memphis	Pilot F

[/pre]
The first column is an example of how the data I'm trying to pull. In summary, I'm trying to change the row titles into the data, and the data into the row titles. Thank you for the help!


A bonus question: is there a way to get excel to output two pilot names? For example if I had both Pilot A and Pilot F scheduled to fly the Chicago route on 5 Oct, can I get the table to output both names (or more) seperated by commas (maybe concatenate?). Thanks again for the help!

 

[dan_l]

I don't know if there's a quick way to achieve this. Maybe you could code the cities and pilots in numeric values and use a pivot table.


There's this too:

http://www.clearlyandsimply.com/clearly_and_simply/2011/06/emulate-excel-pivot-tables-with-texts-in-the-value-area-using-vba.html

 

[NARAYANK991]

Hi Angelo ,


I am not sure , but can you check this file ?


https://www.dropbox.com/s/vdsgjtnv1hn7ah8/Angelo_Example.xlsx


Narayan

 

[SirJB7]

Hi, mikallangelo!


Based on NARAYANK991's model here's an automation for any number of pilots and cities. Check if that's suitable for your needs. Give a look at this file:

https://dl.dropboxusercontent.com/u/60558749/How%20to%20Change%20Data%20to%20Row%20Titles%20and%20vice%20versa%20%28for%20mikallangelo%20at%20chandoo.org%29.xlsx


It uses 3 tables, which you can move as required as long as you keep the last one starting at row 1 (otherwise you should adjust the inner formula):

FlightByPilot: your source table

FlightByCity: your target table

BulkCity: helper table used for extracting unique cities (not needed if someday I found a formula for extracting unique values from a two dimensional array)


The formula for BulkCity entries is:

=SI.ERROR(INDICE(CityTable;ENTERO((FILA()-2)/FILAS(FlightsByPilot))+1;RESIDUO(FILA()-2;COLUMNAS(FlightsByPilot)-1)+1);"") -----> in english: =IFERROR(INDEX(CityTable,INT((ROW()-2)/ROWS(FlightsByPilot))+1,MOD(ROW()-2,COLUMNS(FlightsByPilot)-1)+1),"")


The additional array formula for extracting the unique cities from the one dimensional array BulkCity is:

{=INDICE(CityBulk;COINCIDIR(0;CONTAR.SI(A$12:A12;CityBulk);0))} -----> in english: {=INDEX(CityBulk,MATCH(0,COUNTIF(A$12:A12,CityBulk),0))}


The original array formula (slightly tweaked) for retrieving pilots per city and date is:

{=SI.ERROR(INDICE(PilotList;COINCIDIR([@City];INDICE(CityTable;0;COINCIDIR(INDICE(DateList;COLUMNA()-1);DateList;0));0));"")} -----> in english: {=IFERROR(INDEX(PilotList,MATCH([@City],INDEX(CityTable,0,MATCH(INDEX(DateList,COLUMN()-1),DateList,0)),0)),"")}


Remember that array formulas should be entered with Ctrl-Shift-Enter instead of just Enter.


There are several dynamic named ranges, many for use in the formulas and the other just in case:


1) From 1st table, FlightByPilot

a) PilotList: =DESREF(FlightsByPilot[Pilot];;; -----> in english: =OFFSET(FlightsByPilot[Pilot],,,,)

b) DateList: =DESREF(FlightsByPilot[#Encabezados];;1;;COLUMNAS(FlightsByPilot[#Encabezados])-1) -----> in english: =OFFSET(FlightsByPilot[#Headers],,1,,COLUMNS(FlightsByPilot[#Headers])-1)

c) CityTable: =DESREF(FlightsByPilot[#Datos];;1;;COLUMNAS(FlightsByPilot)-1) -----> in english: =OFFSET(FlightsByPilot[#Data],,1,,COLUMNS(FlightsByPilot)-1)


2) From 2nd table, FlightByCity

a) CityList: =DESREF(FlightsByCity[City];;; -----> in english: =OFFSET(FlightsByCity[City],,,,)

b) PilotTable: =DESREF(FlightsByCity[#Datos];;1;;COLUMNAS(FlightsByCity)-1) -----> in english: =OFFSET(FlightsByCity[#Datos],,1,,COLUMNS(FlightsByCity)-1)

Just advise if any issue.


The procedure for using it is:

I) Locate the 3 tables as you need, in the same worksheet or in others, taking care of the consideration of starting row for BulkCity

II) Enter data in your source table

III) Add (copy) rows to BulkCity as required or more (initially I set up 100 rows, more than the double you need for the posted data)

IV) Copy the last row of target table until a blank row appears (if you go on copying there's no issue but displaying #N/A values)


Just advise if any issue.


Regards!

 

[mikallangelo]

Thank you all! Both NARAYANK991's and SirJB7's solutions are excellent, and I will be able to utilize both to build my schedules.

- Mike

 

[SirJB7]

Hi, mikallangello!

Glad you solved it. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.

Regards!

PS: Don't forget sending the monthly tickets fee, one for NARAYANK991 and two for me, First Class if availabe.