Complex extracting data with VBA

pookie62

New Member
Joined
Oct 20, 2005
Messages
21
Hi,
This workbook contains 4 sheets
Exportwedstrijd, ExportInschrijving, ExportScore and ExportDeelnemer

Exportwedstrijd has the id, name, date and location of the match.
Exportinschrijving has all competitors for all 4 different "Klasse" (categories)
ExportScore has all scores from all competitors from all Klasse's.
ExportDeelnemer has the ID, and names from the competitors.

What I've done so far:
Adding 4 new sheets for each Klasse (OKP,SKP,OKR,SKR)
Putting the names of the competitors on each sheet (if they joined that Klasse)

Now the mainpart.. and the most complex..

What is the score (smallest of (Max)possibly 5 series) for each person?
Who (from all competitors in each seperate Klasse) has the fastest score and is the winner of this Klasse ?
All data is linked by ID's, but I can't figure it out how to do this..
Kopie van Export.xls
ABCDE
1IdDeelnemerIdWedstrijdIdKlasseId
2-20232126832111305961381
3-19122679462211305961381
4-5533958652211305961384
5-19007815713311305961383
6-137072825618111305961384
7-188380857440117511305961382
8-144100314840117711305961383
9-184332162040120611305961382
ExportInschrijving
Kopie van Export.xls
ABCDE
1IdDeelnemerIdWedstrijdIdKlasseId
2-20232126832111305961381
3-19122679462211305961381
4-5533958652211305961384
5-19007815713311305961383
6-137072825618111305961384
7-188380857440117511305961382
8-144100314840117711305961383
9-184332162040120611305961382
ExportInschrijving
Kopie van Export.xls
ABCD
1IdInschrijvingIdSerieNrTijd
2-1451897967-202321268337,119999886
3-72564697-202321268356,590000153
41712696171-202321268316,78000021
51838236624-202321268327,320000172
61842941566-202321268349,970000267
7-1705980600-191226794639,369999886
8-1566390871-191226794646,630000114
9-1317890890-191226794659,770000458
10-1251738173-191226794625,71999979
11606535690-191226794616,670000076
12-1467832898-1900781571513,86999989
13337000594-1900781571113,48999977
ExportScore
<SCRIPT language=JavaScript src="http://www.interq.or.jp/sun/puremis/colo/popup.js"></SCRIPT><CENTER><TABLE cellSpacing=0 cellPadding=0 align=center><TBODY><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid" bgColor=#0c266b colSpan=5><TABLE width="100%" align=center border=0><TBODY><TR><TD align=left><FONT color=white>Microsoft Excel - Kopie van Export.xls</FONT></TD><TD style="FONT-SIZE: 9pt; COLOR: #ffffff; FONT-FAMILY: caption" align=right>___Running: xl2000 : OS = Windows Windows 2000</FONT></TD></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; HEIGHT: 25px" bgColor=#d4d0c8 colSpan=5><TABLE width="100%" align=center border=0 VALIGN="MIDDLE"><TBODY><TR><TD style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: caption">(<U>F</U>)ile (<U>E</U>)dit (<U>V</U>)iew (<U>I</U>)nsert (<U>O</U>)ptions (<U>T</U>)ools (<U>D</U>)ata (<U>W</U>)indow (<U>H</U>)elp (<U>A</U>)bout</TD><TD vAlign=center align=right><FORM name=formCb658016><INPUT onclick='window.clipboardData.setData("Text",document.formFb431795.sltNb305190.value);' type=button value="Copy Formula" name=btCb074442></FORM></TD></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid" bgColor=white colSpan=5><TABLE border=0><TBODY><TR><FORM name=formFb431795><TD style="WIDTH: 60px" align=middle bgColor=white><SELECT onchange="document.formFb431795.txbFb428726.value = document.formFb431795.sltNb305190.value" name=sltNb305190><OPTION value="" selected>A1</OPTION></SELECT></TD><TD align=right width="3%" bgColor=#d4d0c8>=</TD><TD align=left bgColor=white><INPUT size=80 value=Id name=txbFb428726></TD></FORM></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>A</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>B</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>C</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>D</CENTER></TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>1</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: MS Sans Serif; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">Id</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: MS Sans Serif; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">Naam</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: MS Sans Serif; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">Voornaam</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: MS Sans Serif; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"> </TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>2</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: MS Sans Serif; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right">21</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: MS Sans Serif; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">Hofs</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: MS Sans Serif; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">Harry</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: MS Sans Serif; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"> </TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>3</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: MS Sans Serif; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right">21</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: MS Sans Serif; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">Hofs</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: MS Sans Serif; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">Harry</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: MS Sans Serif; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"> </TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>4</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: MS Sans Serif; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right">22</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: MS Sans Serif; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">Borrel</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: MS Sans Serif; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">Harry de</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: MS Sans Serif; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"> </TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>5</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: MS Sans Serif; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right">22</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: MS Sans Serif; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">Borrel</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: MS Sans Serif; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">Harry de</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: MS Sans Serif; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"> </TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>6</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: MS Sans Serif; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right">22</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: MS Sans Serif; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">Borrel</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: MS Sans Serif; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">Harry de</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: MS Sans Serif; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"> </TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>7</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: MS Sans Serif; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right">22</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: MS Sans Serif; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">Borrel</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: MS Sans Serif; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">Harry de</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: MS Sans Serif; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"> </TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>8</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: MS Sans Serif; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right">33</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: MS Sans Serif; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">Maat</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: MS Sans Serif; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">Mark</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: MS Sans Serif; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"> </TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>9</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: MS Sans Serif; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right">33</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: MS Sans Serif; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">Maat</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: MS Sans Serif; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">Mark</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: MS Sans Serif; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"> </TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; B
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

pookie62

New Member
Joined
Oct 20, 2005
Messages
21
Hmm I see now that half of what I wanted to show is not visible..
darn..
Anyway, I hope someone sees the challenge in this..
Please ask me if you need more info..
Basic question is, how to link the ID's on the different sheets..
I.e
A=ExportDeelnemer!Id
B=ExportInschrijving!Id
C=ExportScore!InschrijvingID

C must match B
In ExportInschrijving we can find the deelnemerId which matches with the ID on ExportDeelnemer sheet.
So ther must be a way to find the name of deelnemer and his score (tijd) for each klasse

Anyone ??

[edit] Is it me or is all text aligned to the right.. ?? Why ?!?!?[edit]
 

Watch MrExcel Video

Forum statistics

Threads
1,113,861
Messages
5,544,723
Members
410,630
Latest member
JFORTH97
Top