merging two spreadsheets

Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

Contact Us About Resources Consent preferences Sign in

TrustPilot

starstarstarstarstar Excellent

Request Callback We will call you back  0207 987 3777 Call for assistance Your Basket Basket is empty
Callback Sign in Basket
Menu

Call us on 0207 987 3777 or Search our website

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Merging two spreadsheets

Merging two spreadsheets

resolvedResolved · Medium Priority · Version 2016

Ross has attended:
Excel Introduction course
Excel Intermediate course

Merging two spreadsheets

I have two files of data the make up my horse racing data base. One is for 'horses', the other for 'races'. These are downloaded daily from a horseracing data service called 'Raceform Interactive'
Each file has between 25-30 columns of data. They are conneccted by a unique race number that identifies the race and the horses that ran in it.
I have created the headings (only) for a 'mastersheet' that has some, but not all, of the combined columns of data.
My question is, what is the most efficient way to move the relevant historic data (stretching back 5 years) to the mastersheet.
I have attempted it using 'IF' function, but have got v confused.
Thanks for your help
Ross

RE: merging two spreadsheets

Hi Ross,

Thank you for your question. A good function to use here would be Vlookup. I actually prefer Index and Match because it is lighter on Excel's memory but Vlookup is easier to build.

Vlookup will match the unique race number in two separate tables and then bring across data into the master table from another column in the original table.

Vlookup needs the following info:
Lookup value - the unique number in your current table (normally in the first column)
Table Array - here you select the other table
Column Index Number - the column from which you want to bring across the data
Range Lookup - Finding matches for unique numbers works best if you type FALSE here.

The function's structure is:
=VLOOKUP(Lookup Value, Table Array, Col Index Num, Range Lookup)

Remember to lock the Table Array with $ symbols because you are going to copy the function down the column.

I hope this helps!

Kind regards
Marius Barnard
STL

RE: merging two spreadsheets

I'm having a problem using VLOOKUP. It may be because each race has a unique number, but each horse that runs in the race also has that number i.e. not unique.
I think because of this, I'm finding VLOOKUP doesn't work when I try and merge the two files. races and horses: as an example I will want to have on the merged speadsheet, the identity of the race, the conditions of the ground, the distance of the race etc etc for each horse in the race, all from the races file, along with details of how the horse ran, its odds, the weight it carried etc etc from the horse file.
I seem to get error messages whenever I try to use the function.
Do you think this is the reason, and is there a
way round it?
Thanks
Ross

RE: merging two spreadsheets

Hi Ross,

Lookup functions work best when the identifiers in the data sets are exactly the same. If there is one tiny difference between the two numbers you are trying to match, there will be a #N/A error. The hard bit will be to get the IDs exactly the same. You could try copying and pasting one set of IDs next to the other and then changing one set to be exactly like the other. Afterwards, paste the corrected IDs back into the other database.

I hope this helps.

Kind regards
Marius

RE: merging two spreadsheets

Thanks Marius, but my problem is each horse in a particular race has the same race identifier number. So VLOOKUP only transfers the first horse in each race as it assumes that the information attached to that number will be the same for every horse. But of course the data for each horse is different (finishing position etc etc). How can I get it to transfer the data for every horse in each race.
Ross

RE: merging two spreadsheets

Hi Ross,

It might help us answer your question better if you could send some sample data in an Excel file to forum@stl-training.co.uk, together with an example of how you would like the master sheet to be laid out.

Sometimes it's hard to give advice without seeing the source data.

Kind regards
Marius

RE: merging two spreadsheets

Hi Marius
I have tried to send the relevant files 4 times to forum@stl-training.co.uk. Each time it bounces back.
Is there another email address I could send it to?
Thnaks
Ross

RE: merging two spreadsheets

Hi Ross

Attached is your file with an example of how to create the Master Sheet.

I have used the INDEX MATCH function to pull your two sheets together - the reason for using this instead of a VLOOKUP is that rather than having to manually count across to find the column index number, you can just insert the name at the top of the column you want to return information from (you can see in your example this is just Column 19 etc).

By using the Runner ID to search you can overcome the problem of not pulling in all the information (this will mean that the Race ID is repeated many times, but this is normal).

You also had a formatting problem - the Race ID numbers were formatted as text. I've fixed this in your example but you can do this yourself by selecting the column, going to the Data ribbon, selecting Text to Columns, then clicking finish. The formatting of your lookup value and your lookup column must be EXACTLY the same, so this is another reason you were having issues.

Hopefully this answers your questions. Please let us know if you have any further problems.

Kind Regards,

Sarah Reid
Excel Trainer

Attached files...

January Races 2018 With Master Sheet.xlsx

RE: merging two spreadsheets

Thanks Sarah.
That's brilliant!
Ross

 

Training courses

 

Recommend training98.7 %
recommend us
More live stats...

Training information:

See also:

Active questions
Advanced/consultancy questions
Today's questions
Unanswered questions
Popular forum topics

See also:
Hints and tips
Forum topics

Welcome. Please choose your application (eg. Excel) and then post your question.

Our Microsoft Qualified trainers will then respond within 24 hours (working days).

Frequently Asked Questions
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.


 

Excel articles

Please browse our web site to find out more about
excel consultants and other training courses.

Excel tip:

LARGE and SMALL functions and their uses

Two of Excel's most common functions are the MAX and MIN functions which will display the largest (MAX) or smallest (MIN) value in a series. What if you need the 2nd or 3rd largest or smallest values instead of the largest or smallest?

The =LARGE(array,n) returns the nth largest value of a series.

The =SMALL(array,n) function returns the nth smallest value of a series.

In both functions, 'n' represents the order of the number you want to display. For example, putting in 2 as n will give you the second highest number; putting in 3 as n will give you the third highest number.

View all Excel hints and tips


Request Callback

We will call you back

0207 987 3777

Call for assistance

Security verified Microsoft Partner

Formerly Best Training
© 2024 STL. All Rights Reserved.
All prices offered for business users and exclude VAT. E&OE

2nd Floor, CA House, 1 Northey Street, Limehouse Basin, London, E14 8BT. United Kingdom

Terms & Conditions Privacy & Cookie Policy Consent preferences

Server loaded in 0.1 secs.