Using excel for a chessgame database - Please help!

[mr_bono]

Good afternoon all,
I'm new here, so first of all, welcome.

A few weeks ago I started to play chess, actively, online and in real-life. And I thought that it would be a good idea to 'store' all my games, so I could analyze them whenever I want.

I use MS Excel for this and the way the chessgames are stored is the so-called "PGN-style". It is simply a long textstring with all the moves, ie: 1. d4 d5 2. Nf3 e6 3. Nc3 Nc6. (Each 'move' consists of two moves, one for white and one for black). So far it's easy.

But, as some of you might know, there are standard 'ECO' chess openings. For instance: 1. b4 Nh6 is called the Polish opening, 1. g3 e5 2. Nf3 is called the Benko's opening.. I have one sheet full of all the openings in the world (2018 in total).

On a other sheet I put the games I played. The date, place, time and all the moves in PGN-style. So, what I wanna know is: what opening did I play in a particular game of chess. The problem (for me) is that some games have 30 moves, others 90 moves. On the other hand, some standard 'ECO' openings exists of 2 moves, other of 8 moves. So for me it's to difficult to compare my games to the standard openings.

How the hell am I going to solve this problem?? I really, really hope someone can help me. If something is unclear, feel free to ask. Thanks in advanced.

Mr_bono

 

[etaf]

i would be very interested in a tutorial on the formula - had a search around and did not make a lot of sense of it , if you know of a good link

 

[Marcelo Branco]

i would be very interested in a tutorial on the formula - had a search around and did not make a lot of sense of it , if you know of a good link

See Aladin Akyurek excellent explanation at #9
http://www.mrexcel.com/forum/excel-questions/99621-lookup-value-unsorted-data.html

M.

 

[Snakehips]

Does this illustrate it?

Below is the made up, shortened data set I was messing with.
On the Openings sheet I have a similar formula to M that concatonates the openings but in column A

I have then sorted the whole Openings table on column A

Excel 2007
	A	B	C	D	E	F	G	H	I	J	K
1	Moves	Opening
2	1. b4	ww		1.	b4
3	1. b4 Nh6	Polish		1.	b4	Nh6
4	1. b4 Nh6 2. 12bc	ANOther		1.	b4	Nh6	2.	12bc
5	1. b4 Nh6 2. 12bc f3 3. s22	xxx		1.	b4	Nh6	2.	12bc	f3	3.	s22
6	1. b4 Nh6 2. s3	snake		1.	b4	Nh6	2.	s3
7	1. b4 Nh6 2. s3	snake		1.	b4	Nh6	2.	s3
8	1. g3 e5 2. Nf7	Benko's		1.	g3	e5	2.	Nf7
9
Openings

Result....

Excel 2007
	A	B
1	Moves
2	1. b4 Nh6 2. abc 123 3. dhg24	Polish
3	1. b4 Nh6 2. abc 123 3. dhg22 4. hdhdh wyw43 5. hdshdjeu 6. gdtwe43 dd 7. wwhh 8. yuuu	Polish
4	1. g3 e5 2. Nf7 nd3 3.and645	Benko's
5	1. b4 Nh6 2. 12bc f3 3. s22	xxx
6	1. b4 Nh6 2. s3 123 3. dhg23	snake
7	1. b4 Nh7 2. s3 123 3. dhg24	ww
8	1. b4 Nh6 2. 12bc f3 3 s22	ANOther
PGN

Cell Formulas
Range	Formula
B2	=LOOKUP(2,1/((Openings!$A$2:$A$10<>"")*(ISNUMBER(MATCH(Openings!$A$2:$A$10&"*",A2,0)))),Openings!$B$2:$B$10)

 

[mr_bono]

Here is the link to the file: Chess statistics mr bono - Online Spreadsheets - EditGrid

 

[Marcelo Branco]

Try this

1.Copy the UDF (User Defined Function) below and Paste in a Standard Module

Function aconcat(a As Variant, Optional sep As String = "") As String
' Harlan Grove, Mar 2002
    Dim y As Variant
    If TypeOf a Is Range Then
        For Each y In a.Cells
            aconcat = aconcat & y.Value & sep
        Next y
    ElseIf IsArray(a) Then
        For Each y In a
            aconcat = aconcat & y & sep
        Next y
    Else
        aconcat = aconcat & a & sep
    End If
    aconcat = Left(aconcat, Len(aconcat) - Len(sep))
End Function

2. Create a helper column in sheet All Eco
Put this formula in AW4
=TRIM(aconcat(C4:AU4," "))
copy down till the end of data

3. Create a helper column in Sheet Openings
Put this formula in B4
=TRIM(aconcat(C4:AR4," "))
copy down

4. Put this formula in Openings A4
=LOOKUP(2,1/(('All ECO'!$AW$4:$AW$2100<>"")*(ISNUMBER(MATCH('All ECO'!$AW$4:$AW$2100&"*",$B4,0)))),'All ECO'!$B$4:$B$2100)
copy down

I got this in A4:A35


[TABLE="class: grid"]
<TBODY>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

Openings​

[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]

A40 Queen's pawn​

[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]

D02 Queen's pawn game​

[/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]

A40 Queen's pawn​

[/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]

A10 English opening​

[/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD]

A40 Queen's pawn​

[/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD]

D00 Queen's pawn game​

[/TD]
[/TR]
[TR]
[TD]

10​

[/TD]
[TD]

C41 Philidor's defence​

[/TD]
[/TR]
[TR]
[TD]

11​

[/TD]
[TD]

A10 English opening​

[/TD]
[/TR]
[TR]
[TD]

12​

[/TD]
[TD]

C02 French, advance variation​

[/TD]
[/TR]
[TR]
[TD]

13​

[/TD]
[TD]

D02 Queen's pawn game, Chigorin variation​

[/TD]
[/TR]
[TR]
[TD]

14​

[/TD]
[TD]

C00 French defence​

[/TD]
[/TR]
[TR]
[TD]

15​

[/TD]
[TD]

A40 Queen's pawn, English defence​

[/TD]
[/TR]
[TR]
[TD]

16​

[/TD]
[TD]

D02 Queen's pawn game​

[/TD]
[/TR]
[TR]
[TD]

17​

[/TD]
[TD]

C00 French, King's Indian attack​

[/TD]
[/TR]
[TR]
[TD]

18​

[/TD]
[TD]

A03 Bird's opening​

[/TD]
[/TR]
[TR]
[TD]

19​

[/TD]
[TD]

C10 French, Marshall variation​

[/TD]
[/TR]
[TR]
[TD]

20​

[/TD]
[TD]

D00 Queen's pawn game​

[/TD]
[/TR]
[TR]
[TD]

21​

[/TD]
[TD]

A13 English opening​

[/TD]
[/TR]
[TR]
[TD]

22​

[/TD]
[TD]

A00 Grob's attack​

[/TD]
[/TR]
[TR]
[TD]

23​

[/TD]
[TD]

A40 Queen's pawn​

[/TD]
[/TR]
[TR]
[TD]

24​

[/TD]
[TD]

C00 French defence​

[/TD]
[/TR]
[TR]
[TD]

25​

[/TD]
[TD]

C00 French defence​

[/TD]
[/TR]
[TR]
[TD]

26​

[/TD]
[TD]

D00 Queen's pawn game​

[/TD]
[/TR]
[TR]
[TD]

27​

[/TD]
[TD]

D00 Queen's pawn game​

[/TD]
[/TR]
[TR]
[TD]

28​

[/TD]
[TD]

A00 Anderssen's opening​

[/TD]
[/TR]
[TR]
[TD]

29​

[/TD]
[TD]

C40 Damiano's defence​

[/TD]
[/TR]
[TR]
[TD]

30​

[/TD]
[TD]

D10 Queen's Gambit Declined Slav defence​

[/TD]
[/TR]
[TR]
[TD]

31​

[/TD]
[TD]

D11 Queen's Gambit Declined Slav, 4.e3​

[/TD]
[/TR]
[TR]
[TD]

32​

[/TD]
[TD]

B22 Sicilian, Alapin's variation (2.c3)​

[/TD]
[/TR]
[TR]
[TD]

33​

[/TD]
[TD]

A45 Queen's pawn game​

[/TD]
[/TR]
[TR]
[TD]

34​

[/TD]
[TD]

D00 Queen's pawn game​

[/TD]
[/TR]
[TR]
[TD]

35​

[/TD]
[TD]

D00 Queen's pawn, Chigorin variation​

[/TD]
[/TR]
</TBODY>[/TABLE]


Hope this helps

M.