Help with populating cells with existing data

[Skompy]

Hello

I have a worksheet with multiple pages and the worksheet is to do with horse racing.

One of my pages (I will call this the main page) has a column of birth dates for each horse.
I have another page ( I will call this the 2nd page) which has a list of horses, all of which will have come from the main page.
This 2nd page contains other information not pertaining to the main page (my main page already has many columns and so I do not wish to add more columns to it).
I would like the birth date for each horse in the 2nd page, to be located in the main page and then have this information added to the 2nd page, in a column I have ready for it (which happens to be column A). This would be a one time task.
I thought that I might need to create a VLOOKUP table to do this task but I am not sure. I have found it difficult to follow the YT tutorials on VLOOKUPS that I have found and also, the text based tutorial on this site.
I really need to be with a person to learn something properly but a forum seems to be a good second option.

So I hope someone can guide me on how I go about doing this.

I will eventually create a database for all this information, but for now, I am keeping it all as separate pages in one worksheet.

Thanks

[FJCC]

Yes, the VLOOKUP() function seems like the right choice but it is hard to give you specific help without knowing how the data are laid out. On the main page, which column are the horse names in and which column has the birth dates? Better than trying to explain it would be to upload a small example file. Just a few rows of data is enough. To upload a file, click Post Reply and look for the Upload Attachment tab just below the box where you type a response.

[Skompy]

I don't think attaching a file would help as it doesn't seem necessary to me. And the main page contains several data columns that I do not wish to show.
The DOB column could be any column and so could where the DOB dates are going to be added to in the 2nd page.
If you just pretend the DOB in the main page is column A and the same for the second page, I will make the adjustments to the formula.

[FJCC]

Having the DOB data in column A will not work for a VLOOKUP function since the value to be returned has to be to the right of the lookup value. I will assume that on the Main sheet the names are in column A and the DOB is in column B, with headers in row 1 and the data running from row 2 to row 50. On the second page, the names are in column A from row 2 to row 50. Put this formula in B2 of the second page

Code: Select all

=VLOOKUP(A2; Main.$A$2:$A$50;2;0)

You can then copy that down the rest of the column.

It is easy enough to delete confidential or irrelevant data from a copy of your file and upload that. Working with an example file can save a lot of time. It is common that important details are left out of written explanations because people are using a new feature or function and don't know what is important.

[Skompy]

I can't adjust the formula (because I don't understand it) so here are the columns info.

Main page
Horse name is column L
DOB column is column AK
Number of rows in main page is 12412 (starting from row 9)

2nd page
Horse name is in col D (starting from row 6)
DOB col is col A

[FJCC]

Put this in A6 on the second sheet.

Code: Select all

=VLOOKUP(D6;Main.$L$9:$AK$12412;26;0)

What that means is:
First parameter D6 -> take the value in D6 of the current sheet
second parameter Main.$L$9:$AK$12412 -> look for the value of D6 in the first column of the array Main.$L$9:$AK$12412 (i.e. look in column L, rows 9 - 12412 of the Main sheet)
third parameter 26 -> When a match is found, return the corresponding value from the 26th column of the given array (i.e. column AK)
fourth parameter 0 -> look for an exact match only

[Skompy]

OK, this has worked. Thanks. Thanks also for explaining what the formula is doing.

But I have to also account for something which I made an error in my original question about.
I erroneously stated (because I forgot), that all horses on the 2nd page exist in the main page. This is not so and so some horses in the 2nd page do not exist in the main page. Some of those horses have their DOB entered on the 2nd page already and so I do not want to wipe out that info. There are about 300 of them and so I would have to re-find all that info again and its a lot of time to do that.
So I need a way of testing if the horse in the second page does or does not exist in the main page before going ahead with the adding of the DOB.
Is there a way to do this?

[FJCC]

You can get a True/False result on the presence of a name on the Main sheet with a formula like

Code: Select all

=ISNUMBER(MATCH(D6;Main.$L$9:$L$12412;0))

I am not sure how to use that because I don't understand what is in column A of your second sheet. Some cells have the DOB for horses that do not appear on the Main sheet. What is currently in the other cells where I expected you would put the VLOOKUP function?

[Skompy]

You can get a True/False result on the presence of a name on the Main sheet with a formula like

Code: Select all

=ISNUMBER(MATCH(D6;Main.$L$9:$L$12412;0))

I am not sure how to use that because I don't understand what is in column A of your second sheet.

As above, column A of the 2nd page does/will contain the horse's DOB. Some of the cells in column A have dates in them already (manually entered). Those DOB dates that pertain to horses that are not in the main page need to kept as copying the formula to all cells in col A will delete these dates and it will be a pain to have to go and get this information, piecemeal.

Some cells have the DOB for horses that do not appear on the Main sheet. What is currently in the other cells where I expected you would put the VLOOKUP function?

I am not clear what you mean with this question.
Column A on the 2nd page is for the horse's DOB. Some cells in column A already contain some dates (manually entered). But there are lots of cells in column A which are empty (hence my seeking of a way to populate these cells where possible). But for those horses that appear in both the main page and the 2nd page, the manually entered dates will be overwritten by the VLOOKUP function, which does not matter.

[FJCC]

You can use the fomula

Code: Select all

=ISNUMBER(MATCH(D6;Main.$L$9:$L$12412;0))

with a filter to put the VLOOKUP formula only the cells where the horse name appears on the Main sheet. On the second sheet, either insert a new column next to D or use an empty column somewhere. Give the column a header in row 5 and then fill the column with the formula above. Click on the header and then select the menu Data -> Filter -> AutoFilter. The header cell will now have a drop down list from which you can select the value 1, which is the value of TRUE. Over in column A, you will now see only those cells in which you can use the VLOOKUP function, since the horse name does appear on the Main sheet. When you paste the formula, you may get a warning about some cells already containing data. It is safe to proceed with the paste. As always, keep a back up copy of the file before trying this.

[Skompy]

You can use the fomula

Code: Select all

=ISNUMBER(MATCH(D6;Main.$L$9:$L$12412;0))

with a filter to put the VLOOKUP formula only the cells where the horse name appears on the Main sheet. On the second sheet, either insert a new column next to D or use an empty column somewhere. Give the column a header in row 5 and then fill the column with the formula above. Click on the header and then select the menu Data -> Filter -> AutoFilter. The header cell will now have a drop down list from which you can select the value 1, which is the value of TRUE. Over in column A, you will now see only those cells in which you can use the VLOOKUP function, since the horse name does appear on the Main sheet. When you paste the formula, you may get a warning about some cells already containing data. It is safe to proceed with the paste. As always, keep a back up copy of the file before trying this.

I do not understand what this does or where do I put the formula. And what does "with a filter" mean, etc?

[Veda]

Hi Skompy

I'm just writing to say hello because I bet on horses every day (UK) using primarily my racing spreadsheet to make decisions so it's nice when I also see someone else having a go. We probably have different criteria for deciding what to bet on but I hope you are getting good results for all the work
Cheers
Rob

[FJCC]

I do not understand what this does

I first presented that formula on Sunday: "You can get a True/False result on the presence of a name on the Main sheet with a formula like =ISNUMBER(MATCH(D6;Main.$L$9:$L$12412;0))". It will return TRUE if the name in D6 appears in the cell range Main.$L$9:$L$12412.

or where do I put the formula

On the second sheet, either insert a new column next to D or use an empty column somewhere. Give the column a header in row 5 and then fill the column with the formula above. To fill the column with the formula, write the formula I provided in row 6 then use copy and paste to put the formula into all of the other rows that have horse names in column D.

And what does "with a filter" mean, etc?

I tried to explain how to filter the column in my last post starting at "Click on the header and then select the menu Data -> Filter -> AutoFilter." Did you try the steps I set out there? What happened?