Update Cells via External CSV File

[Ka001]

I am using Excel 2013. I have a file of golfers' names, handicaps and previous weekly scores in a file - 90 names in the file. Rather than updating the file manually I now get CSV file which I can use to update my own file. The CSV file is in Rank order whereas my file is in alphabetic order. I would like to be able to extract data from the CSV file to update my file but leave my file in alphabetic order and highlight the cells in my file according to the Place the player achieved.
In the attached sample I have the CSV data, columns with my current file data and columns that I would like to end up with as a result of the CSV feed.
I have tried all sorts of functions and combinations but can't get it to work. Hopefully someone can help.
Thanks

Update Cells via External CSV File.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	CSV Feed from External App							Previous Scores				New Scores Updated via External App
2
3	Place	Surname	Initial	Countback	Pts	PH		Player	Handicap	Score		Player	Handicap	Score	Countback	Place
4	1	Kenny	M		27	13		Allen M	15	19		Allen M	15	19
5	2	Gannon	J	Last Nine Holes	26	18		Carroll A	10	14		Carroll A	10	14
6	3	Bergin	P	Last Nine Holes	26	14		Conway P	27	19		Conway P	27	19
7	4	Higgins	D		26	11		Bergin P	14	17		Bergin P	14	26	Last Nine Holes	3
8	5	Madden	W		25	13		Dempsey L	23	15		Dempsey L	23	15
9	6	Harrington	D	Last Nine Holes	24	18		Devery K	21	15		Devery K	21	15
10								Donnelly M	24	15		Donnelly M	24	15
11								Duggan M	4	23		Duggan M	4	23
12								Duggan S	12	15		Duggan S	12	15
13								Fitzpatrick G	16	14		Fitzpatrick G	16	14
14								Flynn P	22	18		Flynn P	22	18
15								Gannon J	18	18		Gannon J	18	26	Last Nine Holes	2
16								Goff H	10			Goff H	10
17								Gorman L	16	20		Gorman L	16	20
18								Grealy J	32			Grealy J	32
19								Grehan G	12	14		Grehan G	12	14
20								Harrington D	18	22		Harrington D	18	24	Last Nine Holes	6
21								Higgins D	10	22		Higgins D	11	26		4
22								Hurley K	6			Hurley K	6
23								Kehily F	27			Kehily F	27
24								Kenneally M	27	24		Kenneally M	27	24
25								Kenny M	12	20		Kenny M	13	20		1
26								Kierans G	21	16		Kierans G	21	16
27								Madden W	13	25		Madden W	13	25		5
28								McDermott F	25	16		McDermott F	25	16
29								McNally G	18	14		McNally G	18	14
30								Moran G	14			Moran G	14
Competition Report (2)

 

[rpaulson]

If I understand correctly, you would like to click a button, select the correct csv file, dump the csv file data into columns A thru F. Then match up the surname and initial form the csv file to the player name that is in column L, and write the correct data into columns M thru P.

am i missing anything?

 

[Ka001]

If I understand correctly, you would like to click a button, select the correct csv file, dump the csv file data into columns A thru F. Then match up the surname and initial form the csv file to the player name that is in column L, and write the correct data into columns M thru P.

am i missing anything?

Thanks you for your offer to help.
Horefully the attached will explain what I am trying to achieve.

CSV to Tuesdays Format.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1
2	CSV Feed from External Rolf Club App						Previous Rolf Society Pata				New Petails Updated via External App
3	Player	H/C	Score	Place	Countback		Member	Handicap	Current Bonus		Member	Handicap	Adjusted Score	Place	Countback	New Bonus
4	Kenny B	13	27	1.0	Last three Holes		Allen B	15	1.0		Allen B	15				1.0
5	Gannon L	17	26	2	Last Nine Holes		Carroll A	10	0.5		Carroll A	10				0.5
6	Bergin R	15	26	3	Last Six Holes		Conway R	27	0.5		Conway R	27				0.5
7	Higgins P	11	26	4			Bergin R	14	2.0		Bergin R	15	28.0	1.0	Last Six Holes	-2.0
8	Grehan R	13	25	5			Dempsey L	23	-1.0		Dempsey L	23				-1.0
9	Harrington P	18	24	6	Last Nine Holes		Devery W	21	0.5		Devery W	21				0.5
10	Fitzpatrick R	16	22	7	Last Nine Holes		Donnelly B	24	0.5		Donnelly B	24				0.5
11							Duggan B	4	1.0		Duggan B	4				1.0
12							Duggan S	12	1.0		Duggan S	12				1.0
13							Fitzpatrick R	16	1.0		Fitzpatrick R	16	23.00	7	Last Nine Holes	1.5
14							Flynn R	22	1.0		Flynn R	22				1.0
15							Gannon L	18	1.0		Gannon L	17	27.0	2	Last Nine Holes	-2.0
16							Goff H	10	-3.0		Goff H	10				-3.0
17							Gorman L	16	1.0		Gorman L	16				1.0
18							Grealy L	22	1.0		Grealy L	22				1.0
19							Grehan R	12	1.0		Grehan R	12	26.0	5
20							Harrington P	19	3.0		Harrington P	18	27.0	3	Last Nine Holes	1.0
21							Higgins P	10	1.0		Higgins P	11	27.0	4		0.0
22							Hurley W	6	1.0		Hurley W	6				1.0
23							Kehily F	19	1.0		Kehily F	19				1.0
24							Kenneally B	20	1.0		Kenneally B	20				1.0
25							Kenny B	12	-3.0		Kenny B	13	24	6		-2.5
26							Kierans R	21	1.0		Kierans R	21				1.0
27							Madden W	13	1.0		Madden W	13				1.0
28							McDermott F	15	0.5		McDermott F	15				0.5
29							McNally R	18	0.5		McNally R	18				0.5
30							Moran R	14	0.5		Moran R	14				0.5
31
32	Purpose :-
33	To use input from a Golf Club CSV file to update a Golf Society competition data on a weekly basis. My example is for one week
34
35	Overall requirements:-
36
37	To check who played, what their Club Handicap is, what place they finished in - these are taken from the CSV data
38
39	The Golf Society competition rules
40
41	Each time a member plays in a competition he gets a bonus of 0.5 points
42	If a member does not play in a competition his "score" will remain blank and his bonus will be the same as his "Previous Scores" bonus.
43
44	The Winner of the competition will receive a prize but he will incur a penalty of 4 (bonus) points - applied to his bonuses for the coming week
45
46	2nd place in the competition will receive a prize, but he will incur a penalty of 3 (bonus) points - applied to his bonuses for the coming week
47
48	3rd place in the competition will receive a prize, but he will incur a penalty of 2 (bonus) points - applied to his bonuses for the coming week
49
50	4th place in the competition will receive a prize, but he will incur a penalty of 1 (bonus) point - applied to his bonuses for the coming week
51
52	5th place in the competition will receive a prize, but he will incur a penalty of 1 (bonus) point - applied to his bonuses for the coming week
53
54	The winner and places for the Golf Society competition are determined by adding their current bonus to their competition score (from the CSV file).
55
56	The members who played will have their handicap updated from their CSV file handicap.
57	Any member who did not play will retain their current handicap - from "the previous scores" list.
58
59	The Golf Society Member names will be updates on an annual basis - i.e. it will remain static for the playing season.
60	The number of players (from the CSV data) will change according to the numbers who play in a competition.
61
62	Steps involved in updating the Golf Society data :-
63
64	Import the CSV file data
65
66	Match the CSV player name with the "New Details" member name
67	Update the Adjusted Score i.e. CSV score + the Current Bonus. The Adjusted score will be blank if the member didn't play.
68
69	I have done the updates to the spreadsheet manually to highlight the changes to be automated.
Scores Update (2)

Cell Formulas
Range	Formula
M4	M4	=IF(LOOKUP(A4,K4:K30,A4)=K4,B4," ")
N4	N4	=IF(LOOKUP(A4,K4:K30,K4)=K4,D4," ")
O4	O4	=IF(LOOKUP(A4,K4:K30,K4)=K4,E4," ")
M7	M7	=C6+I7
O7,L7	O7	=E6
L26:L30,L22:L24,L16:L18,L14,L8:L12,L4:L6	L4	=H4
M13	M13	=C10+I13
O13	O13	=E10
M15	M15	=C5+I15
O15,L15	O15	=E5
P26:P30,P22:P24,P16:P18,P14,P8:P12,P4:P6	P4	=I4
P7	P7	=I7-4
P13,P25	P13	=I13+0.5
P15	P15	=I15-3
L20,O20	L20	=B9
M19:M20	M19	=C8+I19
M21	M21	=C7+I21
M25	M25	=C4+I25
P20	P20	=I20-2
P21	P21	=I21-1

 

[Ka001]

If I understand correctly, you would like to click a button, select the correct csv file, dump the csv file data into columns A thru F. Then match up the surname and initial form the csv file to the player name that is in column L, and write the correct data into columns M thru P.

am i missing anything?

You understand correctly what I am trying to achieve. To help you get a clearer picture I manually imported the data from a csv file to illustrate the issues that I am having - i.e. trying to populate columns M to P with the correct data from the other columns. If I could automate the import of the csv file as well, that would be great.
Thank you.

 

[rpaulson]

I have the data importing just from from the csv file.

let focus on just Kenny B,
1. Column L: his previous handicap was 12 (cells H25) write the new handicap of 13 from the import and write to cell L25.
2. Column M: (Adjusted store) is new score from import of 27, Plus current bonus of negative 3.
3. Column O: Countback is just copied straight form the import file.

4. Column N: Not sure how you got 6 when the csv file has 1
5. Column P: how did you get the negative 2.5 ????

let me know