Sorting problems...

[leighhobson89]

My problem here is that I have a list of countries on the left. Then I have a list of countries on the right. One column to the right of the second column of countries is a column of numbers in the format 0.xxx. What I need is for that column of numbers to end up in the order that the first column of countries are, using the second column of countries as a cross-reference. Can you provide me with the correct method and formula to do that?

I have this data sample:

csvtemp.csv
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	id	country	startingPop	area	startingArmy	continent	res_gold	res_oil	res_food	res_cons_mats	upgrade_cost_base	dev_index
2	0	China	1447065329	9,706,961	0	Asia	NULL	NULL	NULL	NULL	NULL	NULL	Switzerland	0.962
3	1	India	1401310563	3,287,590	0	Asia	NULL	NULL	NULL	NULL	NULL	NULL	Norway	0.961
4	2	United States	334058426	9,372,610	0	North America	NULL	NULL	NULL	NULL	NULL	NULL	Iceland	0.959
5	3	Indonesia	278037263	1,904,569	0	Oceania	NULL	NULL	NULL	NULL	NULL	NULL	Hong Kong	0.952
6	4	Pakistan	227724796	881,912	0	Asia	NULL	NULL	NULL	NULL	NULL	NULL	Australia	0.951
7	5	Brazil	214832901	8,515,767	0	South America	NULL	NULL	NULL	NULL	NULL	NULL	Denmark	0.948
8	6	Nigeria	214507696	923,768	0	Africa	NULL	NULL	NULL	NULL	NULL	NULL	Sweden	0.947
9	7	Bangladesh	167247491	147,570	0	Asia	NULL	NULL	NULL	NULL	NULL	NULL	Ireland	0.945
10	8	Russia	145899956	17,098,242	0	Asia	NULL	NULL	NULL	NULL	NULL	NULL	Germany	0.942
11	9	Mexico	131046075	1,964,375	0	North America	NULL	NULL	NULL	NULL	NULL	NULL	Netherlands	0.941
12	10	Japan	125802521	377,930	0	Asia	NULL	NULL	NULL	NULL	NULL	NULL	Finland	0.940
13	11	Ethiopia	119590501	1,104,300	0	Africa	NULL	NULL	NULL	NULL	NULL	NULL	Singapore	0.939
14	12	Philippines	111913102	342,353	0	Asia	NULL	NULL	NULL	NULL	NULL	NULL	Belgium	0.937
15	13	Egypt	105390688	1,002,450	0	Africa	NULL	NULL	NULL	NULL	NULL	NULL	New Zealand
16	14	Vietnam	98655916	331,212	0	Asia	NULL	NULL	NULL	NULL	NULL	NULL	Canada	0.936
17	15	Turkey	85484777	783,562	0	Europe	NULL	NULL	NULL	NULL	NULL	NULL	Liechtenstein	0.935
18	16	Iran	85627052	1,648,195	0	Asia	NULL	NULL	NULL	NULL	NULL	NULL	Luxembourg	0.930
19	17	Germany	83975691	357,114	0	Europe	NULL	NULL	NULL	NULL	NULL	NULL	United Kingdom	0.929
20	18	Thailand	70039646	513,120	0	Asia	NULL	NULL	NULL	NULL	NULL	NULL	Japan	0.925
21	19	United Kingdom	68401087	242,900	0	Europe	NULL	NULL	NULL	NULL	NULL	NULL	South Korea
22	20	France	65520147	551,695	0	Europe	NULL	NULL	NULL	NULL	NULL	NULL	United States	0.921
23	21	Tanzania	62539416	945,087	0	Africa	NULL	NULL	NULL	NULL	NULL	NULL	Israel	0.919
24	22	Italy	60320493	301,336	0	Europe	NULL	NULL	NULL	NULL	NULL	NULL	Malta	0.918
csvtemp

 

[RoryA]

You could simply do a VLOOKUP in a new column using the country names in column B as the lookup value and your two columns on the right as the lookup table (assuming there is a 1:1 match on countries).

 

[leighhobson89]

Thank you. I got that tip from Chat-GPT too - the problem is i am having difficulty getting it working. Using United Kingdom as an example (as it features in both lists of the sample) could you provide the formula to put in say column O of row 21 to start me off please?

 

[RoryA]

It would be:

=VLOOKUP(B21,$M$2:$N$24,2,false)

You can then copy that up/down as required.

 

[leighhobson89]

Great, that sorted it, and something learned