PDA

View Full Version : Database Help (Excel)



Jesus
26-07-15, 10:36 PM
Okay so here's the situation...

- I have extracted data from update A (second name, first name, common name, date of birth)
- I have extracted data from update B (second name, first name, common name, date of birth)
- Each set of data is in it's own separate column (column A = first name, column B = second name, etc)

What I want to do is cross check update B against update A so that I can see if there are any common players. I then want excel to return their date of birth from update A. So, for example, I know Sergio Agüero exists in both sets of data, however I can't get excel to check in both sets of data for "Sergio Aguero" and then return his date of birth from update A.

Example Update A





First Name

Second Name
Common Name
dd
mm
yyyy




Sergio
Agüero

2
6
1975


Igor
Akinfeev

8
4
1973


David
Alaba

24
6
1979


Francisco Román
Alarcón Suárez
Isco
21
4
1979


Jordi
Alba Ramos
Jordi Alba
21
3
1976


Thiago
Alcántara

11
4
1978


Xabi
Alonso

25
11
1968


Diego
Alves Carreira
Diego Alves
24
6
1972


Daniel
Alves da Silva
Dani Alves
6
5
1970


Mikel
Arteta

26
3
1969




Example Update B





First Name

Second Name
Common Name
dd
mm
yyyy




Oliver
Agüero

25
5
1972


Igor
Akinfeev

8
9
1973


Greg
Alaba

11
12
1971


Francisco Román
Alarcón Suárez
Isco
16
7
1976


Jordi
Alba Ramos
Jordi Alba
12
8
1977


Thiago
Alcántara

8
5
1968


James
Alonso

20
6
1974


Diego
Alves Carreira
Diego Alves
19
4
1972


Daniel
Alves da Silva
Dani Alves
25
2
1977


Mikel
Arteta

8
4
1977




What I have done in the examples above is change the first names of Aguero, Alaba and Alonso. What I am hoping is that someone can write a function so that Excel will return a "NA" when it tries to look for "Sergio Aguero", "David Alaba" and "Xabi Alonso" in both updates (because those players only exist in one set of data).

Hope this makes sense and I hope some Excel wizard can help me.

Dermotron
27-07-15, 08:46 AM
vlookup is the function you are after

Using an if statement would return yes/no or whatever parameters once it finds aguero. So if there were 10 aguero's it wouldn't be specific

Jesus
27-07-15, 09:35 AM
Yeah I tried to use VLOOKUP but it was limited to what I wanted it to do. Would I have to combine the First and Second name columns in order to give me one set of data to look up? If so... Is there a way I can do that without having to manually combine them? One set of data has over 110,000 entries where as the other set of data only contains 200 entries.

Dermotron
27-07-15, 09:56 AM
if you done something like this

=concatenate(A1,"*",B1) in a blank column and then do the same to the other sheet (forget common name for the minute). So from above you would have Oliver*Aguero in the new column. Why use the *? Because you want to be able to use Text to Columns when finished to separate the names out again and the default options like space would create extract columns for Isco's first and second names. Use Text to Columns > Delimited > Other and * to return them to originals.

Then use vlookup on the new concatenates column (means less searching as there is now only one column to reference in each sheet)

If us vlookup use them in sheets on the workbook as opposed to on different workbooks, can make it easier to manage

Fods
27-07-15, 02:19 PM
Is this for DTL you sneaky dog

Jesus
27-07-15, 02:20 PM
Is this for DTL you sneaky dog
Hahaha. Not at all. :D

Jesus
27-07-15, 08:53 PM
if you done something like this

=concatenate(A1,"*",B1) in a blank column and then do the same to the other sheet (forget common name for the minute). So from above you would have Oliver*Aguero in the new column. Why use the *? Because you want to be able to use Text to Columns when finished to separate the names out again and the default options like space would create extract columns for Isco's first and second names. Use Text to Columns > Delimited > Other and * to return them to originals.

Then use vlookup on the new concatenates column (means less searching as there is now only one column to reference in each sheet)

If us vlookup use them in sheets on the workbook as opposed to on different workbooks, can make it easier to manage

Dermo, thank you! This worked to an extent. The only issue I came up against was that if a player has a common name in Update A and it has been separated out in to First Name and Second Name in Update B then it doesn't pick it up (this happened with Cristiano Ronaldo for example).

Dermotron
28-07-15, 08:46 AM
If you give me a full rundown of what you are trying to do it might be easier to figure out. Or failing that you can send me the workbook and I'll write the formulas for you

Jesus
31-07-15, 05:35 PM
So I've just run in to a problem..

I inserted Lionel Messi in to my database and added him to the Barcelona team:

http://i.imgur.com/2jnP7Ag.png

However when I load a game, he doesn't appear in their squad and instead appears as a free transfer:

http://i.imgur.com/jnkltux.png

This has also happened to some of the other players I've created. I went in to the official editor and validated the database and also used CMDBSort to see if that would help but every time I create a new game the problem is still the same. Anyone have an idea as to why?

milo
31-07-15, 07:42 PM
are you running as administrator?

Jesus
31-07-15, 07:57 PM
are you running as administrator?

Yep. Other players I've inserted are at the clubs I chose, it's just some players end up being a free transfer.

Craig Forrest
31-07-15, 08:00 PM
Only happen with teams that have B teams by any chance?

Or teams that have a squad of 50?

Jesus
31-07-15, 08:08 PM
Only happen with teams that have B teams by any chance?

Or teams that have a squad of 50?

Think you might be on to something there, it happened for players from Barcelona and Atlético de Madrid.

Both teams have less than 50 players in their squad though.

Craig Forrest
31-07-15, 08:10 PM
I know with loans and such, you have to list them twice to get them to work... Once to the senior squad and once to the reserve squad.

Try inserting Messi on Barca and an identical Messi on Barca B and see what happens

Dermotron
31-07-15, 08:57 PM
Teams with B Teams can only have 48 players spread over the 2 sides idb