Merging lists and connecting the information in a new field but to the right row

[Lifebook_Admin]

Hey all,

I really hope you can help me with this - I imagine it to be simple but I just cant find an answer for this!

I want to move my spreadsheets to airtable.
In one, my main table, I have a list of students with their names (here ABCD), etc.

In a second table I have more information about these students, but the name list is in a random order

Now I would like to import the second table and have the data matched to the first one, so that the right data from table 2 are listed in a new field, but connected to the right student A, B, …

What would be the best way to do this?
Thank you so much for your help!

Best,
Alina

[Hannah_Wiginton]

Hey @Lifebook_Admin

Welcome to the Airtable community!

Add a ‘Joined’ column to your 1st table.

Copy the info from table 2 to table 1. Use the ‘Consolidate’ option in Excel.

SpreadsheetWeb – 17 Dec 19

How to find duplicates in Excel and remove or consolidate them

In this guide, we're going to show you how to find duplicates in Excel and also remove or consolidate them using various methods.

Est. reading time: 4 minutes

After everything is consolidated and matched, you can then import it all into Airtable in a single table.

[Lifebook_Admin]

Hey Hannah,

thanks so much for your reply!
If I understand correctly you recommend consolidating the lists before uploading ot airtable?

But how could I do that in airtable? I found the option to manually link records from table 2 to the right row in table 1 - but my lists are long so Im searching for a way to automatically connect the two lists, having the name as the dependence field.

Do you might know how to do that?
Thanks so much for your help!

[Hannah_Wiginton]

Hey @Lifebook_Admin

Yes, you can manually link everything, but best practices in Airtable means you need to have a single Students table, not duplicate tables.

If you need another table that logs each time a Student “Joined” a daily class (Attendance), then you will have 2 tables and use a linked record to the Students table to display the Student’s name. An example of this is every day, you ‘check’ that the student has attended the class.

If ‘Joined’ is a singular 1-time event, like your Student joined a civic club, then it can stay in the Students table.

My thought process on the suggestion was that you start with cleaned up data before bringing it into Airtable.

To add to Airtable:

First, create a table in Airtable labeled Students

Add all the column names from both your spreadsheet tables as Fields in the single Airtable table

• Name, Country, Contact, Joined

Copy and paste your 1st spreadsheet table into the Airtable table - be sure the columns in both are in the same order.

Now hide all the fields except Name and Joined

Copy and paste your 2nd table into Airtable

Unhide all fields

Then, sort by Name. You will be able to see your duplicate Student names. You can then go through and manually tag all the appropriate selections.

IF your Joined data is per ongoing ‘events’ like daily attendance, create a second table and turn that Joined field into a Linked Record field type.