From a csv achieved by a badge to a ranking

[DaWebmasta]

Hi everyone,
Sorry for my bad English but I'm trying my best. I am the webmaster of an amateur cycling team with about 160 members and every Sunday we ride a ride. The riders have a badge (card) and must therefore log in every Sunday. So every Sunday I receive a csv file as in this example below.

ride01.csv (65 km)

ride01.csv
	A
1	Date,Time,Terminal ID,User ID,Name,Employee ID,Class,Mode,Type,Card Serial No.,Result,Property,External Device,Coordinate
2	2020-03-08,09:53:24,0008 : jdjd,00003002,Smith John,3002,User,Access,Card,01324047,Success,1000,,0 / 0
3	2020-03-08,09:52:15,0008 : jdjd,00004003,King Ronny,4003,User,Access,Card,01324052,Success,1000,,0 / 0
4	2020-03-08,09:51:59,0008 : jdjd,00007036,Presley Elvis,7036,User,Access,Card,01636049,Success,1000,,0 / 0
5	2020-03-08,09:49:41,0008 : jdjd,00004016,Wade Duane,4016,User,Access,Card,01636043,Success,1000,,0 / 0
ride01

Next sunday I get ride02.csv (55 km)

ride02.csv
	A
1	Date,Time,Terminal ID,User ID,Name,Employee ID,Class,Mode,Type,Card Serial No.,Result,Property,External Device,Coordinate
2	2020-03-08,09:52:15,0008 : jdjd,00004003,King Ronny,4003,User,Access,Card,01324052,Success,1000,,0 / 0
3	2020-03-08,09:49:41,0008 : jdjd,00004016,Wade Duane,4016,User,Access,Card,01636043,Success,1000,,0 / 0
4	2020-03-08,09:53:24,0008 : jdjd,00003002,Smith John,3002,User,Access,Card,01324047,Success,1000,,0 / 0
5	2020-03-08,09:51:59,0008 : jdjd,00007033,Travolta John,7033,User,Access,Card,01636046,Success,1000,,0 / 0
ride02

And so on...
Now I want to make a ranking with the following information: ranking, name (based on total points), cardnumber, total mileage and total points.
When you participate (so every time you use your badge) you should get 3 points. If you don't participate you get zero points

Now I make that ranking manually. It is the intention that everything is done automatically as much as possible. So it should look something like this after 2 stages or rides.

ranking.csv
	A	B	C	D	E
1	Rank	Name	Cardnumber	Total km	Total points
2	1	King Ronny	4003	120	6
3	2	Smith John	3002	120	6
4	3	Wade Duane	4016	120	6
5	4	Travolta John	7033	55	3
6	5	Presley Elvis	7036	65	3
ranking

So I can place The ranking CSV on the internet.

Is it possible? Can it be done?
I don't know how to do it right away. I can put a simple formula in Excel, but how it should be done with the total number of km and assigning the points there I am really stuck.

Thank you very much in advance!

 

[Trevor G]

This formula will extract the names,

=TRIM(MID(SUBSTITUTE(A2, ",", REPT(" ", LEN(A2))), LEN(A2)*4, LEN(A2)))

Incrementing the number after the * to 5 will give you the card number

=TRIM(MID(SUBSTITUTE(A2, ",", REPT(" ", LEN(A2))), LEN(A2)*5, LEN(A2)))

Because there is no miles in the data for each ride you will have to add them in manual. The formula example can be added below each CSV table or placed somewhere else. but to retain a master total will need VBA. But hopefully this gives you a start point.

 

[DaWebmasta]

Hello,

Thanks for your reaction.
I know nothing about VBA. I'll see what I can found about VBA basics on the internet.

Greetings!

 

[DaWebmasta]

Mmmm, those VBA youtube courses for beginners will not help me right away with my problem...
Is it a good idea to start a ranking file with the non-variables like the name and the card number and add them in manual. So I start with:

ranking.csv
	A	B	C	D	E
1	Rank	Name	Cardnumber	Total km	Total points
2		King Ronny	4003
3		Smith John	3002
4		Wade Duane	4016
5		Travolta John	7033
6		Presley Elvis	7036
ranking

Let's start with the points. Is it possible to see if a cardnumber appears in file (let's say in ride01.csv) it will increase the total points by 3 in cell E2?
For example: If 4003 is found in ride01.csv so Ronny King will get 3 points?

i'm such a dummie

Grtz

 

[Snakehips]

@DaWebmasta I cannot be certain but, I suspect that you perhaps do not wish nor need to utilise vba?
I do not know the exact form of your spreadsheet nor, if there are other factors that would make the below unworkable.
However, I have quickly done the below, using only standard Excel features.

In this form, each csv dataset is pasted into column A, below the the previous.
Using Data > Text To Columns, the input data is split to columns in about 8 clicks ( 10 seconds)
The distance of the ride needs to be entered in the new header row, in column P. ( Shown as green )
(Note there are formulas copied down in P. Entering a new distance will overwrite the formula in the header row but that's good!!)

The irrelevant columns are hidden.
The table on the right is essentially a list of all members.
Stats are picked up by formulas in U & V and the table can be sorted on column V as and when required.

Book1
	A				E	F										P	Q	R	S	T	U	V
1																Distance
2	Date				Name	Employee ID										65			Name	Number	Km	Points
3	08/03/2020				Smith John	3002										65			King Ronny	4003	120	6
4	08/03/2020				King Ronny	4003										65			Wade Duane	4016	120	6
5	08/03/2020				Presley Elvis	7036										65			Smith John	3002	120	6
6	08/03/2020				Wade Duane	4016										65			Travolta john	7033	55	3
7																			Presley Elvis	7036	65	3
8	Date				Name	Employee ID										55			Trump Donald	9999	0	0
9	08/03/2020				King Ronny	4003										55
10	08/03/2020				Wade Duane	4016										55
11	08/03/2020				Smith John	3002										55
12	08/03/2020				Travolta John	7033										55
13
Sheet2

Cell Formulas
Range	Formula
P3:P6, P9:P13	P3	=IF(F3="","",P2)
U3:U8	U3	=SUMIF(E:E,S3,P:P)
V3:V13	V3	=IF(S3="","",COUNTIF(E:E,S3)*3)

It may not be the perfect answer but hope it helps.

Don't hesitate to ask if you need more explanation.

 

[DaWebmasta]

Hello Snakehips,

Again, sorry for my bad English.

Thanks for your answer!!!! It's not that I don't want to use vba. It's just that I don't know anything about it. I watched some beginner videos on Youtube. It is super interesting but I couldn't take it any further for this project.
I understand the examples you show me without VBA. It's very clear and I understand what you're doing. I'll try it out in a minute.

In your example, all input is pasted into column A. Is it possible to paste the data every time into a new sheet for each cycling trip (because it concerns more than 100 participants over 30 trips and then it quickly becomes a very long and unclear file)

 

[Snakehips]

Please do not apologise for your English. It is far more better than what my English are!

The choice is entirely yours. If you want to really automate the process then you have to go vba. Then you can virtually do anything you want with any data layout.
If you do not go vba then I feel that the simplest way to extract the overall stats is from the single sheet listing.

However, there is nothing at all to stop you having a separate sheet for each event and, maybe, computing some event stats on that sheet.
So all you would do is copy the event csv to a dedicated sheet and then copy paste the data values to the bottom of the overall calculation sheet.?

The summary itself could be on a separate sheet.?