Aling multiple sets of data by header column
 

I am trying sort data by headers in a situation where I have a lot of
variables but some are missing. I have all my data like this:

City A Value for variable 1
City B Value for variable 1
City C Value for variable 1

City A Value for variable 2
City B Value for variable 2
City C Value for variable 2

My problem is, that sometimes I do not have the data for some variable for a
given city. For these cities, I do not have the city name either. To do
plots, I put the data into the same worksheet in adjacent columns.

What happens is this:
City A Value for variable 1 City A Value for variable 2
City B Value for variable 1 City C Value for variable 2
City C Value for variable 1 City D Value for variable 2
City D Value for variable 1 City E Value for variable 2

This is what I would need:
City A Value for variable 1 City A Value for variable 2
City B Value for variable 1
City C Value for variable 1 City C Value for variable 2
City D Value for variable 1 City D Value for variable 2

I would like to do this for all my variables for an end result like this:
City A Value for variable 1 Value for variable 2 Value for variable 3
City B Value for variable 1 N/A Value for
variable 3
City C Value for variable 1 Value for variable 2 N/A
City D Value for variable 1 Value for variable 2 Value for variable 3
Etc.

Any advice will be greatly appreciated.

Aling multiple sets of data by header column
 

Hi MarkusO,

It looks like you may have solved the problem already. How feasible is it
to simply assign "N/A" as the Text value for any variable that does not have
a value before sorting or before plotting?

If this is not feasible, let me know why, so that I have more info to work
with in helping you solve your data issue.

~Rob Walton


"MarkusO" wrote in message
...
I am trying sort data by headers in a situation where I have a lot of
variables but some are missing. I have all my data like this:

City A Value for variable 1
City B Value for variable 1
City C Value for variable 1

City A Value for variable 2
City B Value for variable 2
City C Value for variable 2

My problem is, that sometimes I do not have the data for some variable for
a
given city. For these cities, I do not have the city name either. To do
plots, I put the data into the same worksheet in adjacent columns.

What happens is this:
City A Value for variable 1 City A Value for variable 2
City B Value for variable 1 City C Value for variable 2
City C Value for variable 1 City D Value for variable 2
City D Value for variable 1 City E Value for variable 2

This is what I would need:
City A Value for variable 1 City A Value for variable 2
City B Value for variable 1
City C Value for variable 1 City C Value for variable 2
City D Value for variable 1 City D Value for variable 2

I would like to do this for all my variables for an end result like this:
City A Value for variable 1 Value for variable 2 Value for variable 3
City B Value for variable 1 N/A Value for
variable 3
City C Value for variable 1 Value for variable 2 N/A
City D Value for variable 1 Value for variable 2 Value for variable 3
Etc.

Any advice will be greatly appreciated.

Aling multiple sets of data by header column
 

Hi

The problem is that if I do not have the variable I do not have the city
name either. Putting N/A is not feasible as I have no way of adding the
missing cities into the lists.

I have a list of all cities like this (total 430 cities):
Alahärmä
Alajärvi
Alastaro
Alavieska
Alavus
Anjalankoski
Artjärvi
Asikkala
Askainen
Askola
Aura
Brändö
Dragsfjärd
Eckerö
Elimäki
Eno
Enonkoski
Enontekiö
Espoo
Eura
Eurajoki
Evijärvi
Finström
Forssa
Föglö
Geta

I have value lists like this:
Espoo 1
Hamina 1
Haukivuori 1
Heinävesi 1
Helsinki 1
Hirvensalmi 1
Houtskari 1
Hyrynsalmi 1
Isojoki 1
Joroinen 1
Jurva 1
Juva 1
Kangasniemi 1
Karijoki 1
Kauhajoki 1
Kauniainen 1
Kontiolahti 1
Korppoo 1
Korsnäs 1
Kuhmo 1
Kuivaniemi 1
Kuopio 1
Lohja 1
Maalahti 1
Mäntsälä 1
Mäntyharju 1
Nauvo 1
Nurmes 1
Pertunmaa 1
Pieksämäki 1
Pieksänmaa 1
Pielavesi 1
Porvoo 1
Punkaharju 1
Puolanka 1
Puumala 1
Rantasalmi 1
Rautalampi 1
Riihimäki 1
Ristiina 1
Savonranta 1
Sotkamo 1
Sulkava 1
Tampere 1
Teuva 1
Turku 1
Tuusula 1
Vantaa 1
Virolahti 1
Ylikiiminki 1

Not like this:
Alahärmä
Alajärvi
Alastaro
Alavieska
Alavus
Anjalankoski
Artjärvi
Asikkala
Askainen
Askola
Aura
Brändö
Dragsfjärd
Eckerö
Elimäki
Eno
Enonkoski
Enontekiö
Espoo 1
Eura
Eurajoki
Evijärvi
Finström
Forssa
Föglö
Geta

(The values are not always 1, here the data type just happens to be binary)

So, I would like to "align" these too lists so that the values would pop
next to the right city in the city name list. I would like to repeat this for
many many variables for end result like this (with about 30 variables):
City Name Population PopDensity CompDummy1 CompDummy2 GDPperCapita
Alahärmä 32004 533,5 1 0 53435‚¬
Alajärvi 435224 1560,1 0 1 43555‚¬
etc.

My problem is that now I type all the missing cities to the second list by
hand, which becomes unfeasible. I have done this kind of straight typing now
for one week already with my data. Also, sometimes in my lists the city list
is old ie. there is no matching town that exists anymore.

I hope this helps in understanding the problem more.

BR
Markus

"Rob Walton" wrote:

Hi MarkusO,

It looks like you may have solved the problem already. How feasible is it
to simply assign "N/A" as the Text value for any variable that does not have
a value before sorting or before plotting?

If this is not feasible, let me know why, so that I have more info to work
with in helping you solve your data issue.

~Rob Walton


"MarkusO" wrote in message
...
I am trying sort data by headers in a situation where I have a lot of
variables but some are missing. I have all my data like this:

City A Value for variable 1
City B Value for variable 1
City C Value for variable 1

City A Value for variable 2
City B Value for variable 2
City C Value for variable 2

My problem is, that sometimes I do not have the data for some variable for
a
given city. For these cities, I do not have the city name either. To do
plots, I put the data into the same worksheet in adjacent columns.

What happens is this:
City A Value for variable 1 City A Value for variable 2
City B Value for variable 1 City C Value for variable 2
City C Value for variable 1 City D Value for variable 2
City D Value for variable 1 City E Value for variable 2

This is what I would need:
City A Value for variable 1 City A Value for variable 2
City B Value for variable 1
City C Value for variable 1 City C Value for variable 2
City D Value for variable 1 City D Value for variable 2

I would like to do this for all my variables for an end result like this:
City A Value for variable 1 Value for variable 2 Value for variable 3
City B Value for variable 1 N/A Value for
variable 3
City C Value for variable 1 Value for variable 2 N/A
City D Value for variable 1 Value for variable 2 Value for variable 3
Etc.

Any advice will be greatly appreciated.