Skip to main content

How can I use Airtable to compare two sets of data?

  • August 18, 2022
  • 4 replies
  • 201 views
L

Sorry in advance if this has already been asked and answered a million times.

Basic Situation -
I am a bookseller, and my POS is a POS when it comes to order integrations. So, I have to create a bulk upload in a spreadsheet of hundreds of books per week. This is fine, it’s doable, whatever. But it does require a lot of tedious data manipulation which I am hoping Airtable can reduce.

The Issue -
I need to compare one CSV file (the Purchase Order) to a different CSV file (our item database) in order to see which books are new and need to be added to our system. If they are new, then I need to format the data to match our POS’s upload template. If they are reorders, I need to check if there has been a cost or price change. I can easily do this in Google Sheets with vlookup formulas, but, again, having to copy and paste and move things around between different worksheets has gotten old very quickly.

The Question -
Can I use Airtable to create a template that allows me to just upload the two CSV files and have another table do all the comparing and sorting for me?

Thanks in advance!

4 replies

TheTimeSavingCo
Forum|alt.badge.img+31

Hi Lyn, yeah this seems like it’d be doable; I’d just have the two tables, one for the Purchase order CSV and one for the Item Database CSV, and I’d set it up so they’d be linked records via some kind of unique identifier you’ve got in your system

Could you provide details on the stuff you’d need to compare and sort?

Adam, Airtable Consultant - thetimesaving.company
L
  • Lyn_Ciurro
  • Author
  • New Participant
  • August 22, 2022

Hi Lyn, yeah this seems like it’d be doable; I’d just have the two tables, one for the Purchase order CSV and one for the Item Database CSV, and I’d set it up so they’d be linked records via some kind of unique identifier you’ve got in your system

Could you provide details on the stuff you’d need to compare and sort?


Thanks, Adam!

Here’s my current (simplified) workflow and what I’m comparing.

We order books every week, which have a unique ISBN identifier we use to compare. Currently, I take our new book order and compare it to our item product database in our Point of Sale system to figure out which books are re-orders (books we’ve already had and are receiving again) and which books are new (books we’ve never ordered before and need to add to our system). The new items then need to be reformatted using a POS specific template, and then uploaded to our POS. I then need to cost check the re-orders to make sure there haven’t been any price or cost changes.

I just want to find some way to make a template that does a lot of the grunt work of formatting and comparing for me. Is that helpful info?

TheTimeSavingCo
Forum|alt.badge.img+31

Thanks, Adam!

Here’s my current (simplified) workflow and what I’m comparing.

We order books every week, which have a unique ISBN identifier we use to compare. Currently, I take our new book order and compare it to our item product database in our Point of Sale system to figure out which books are re-orders (books we’ve already had and are receiving again) and which books are new (books we’ve never ordered before and need to add to our system). The new items then need to be reformatted using a POS specific template, and then uploaded to our POS. I then need to cost check the re-orders to make sure there haven’t been any price or cost changes.

I just want to find some way to make a template that does a lot of the grunt work of formatting and comparing for me. Is that helpful info?


Yeap that’s helpful, but I feel like without the nitty gritty details it’d be hard to tell you whether exact things were possible, you know what I mean?

In any case, if I were you I’d have two tables, Purchase Order and POS, where the POS table’s primary field is the ISBN and a linked field between the two tables

With both tables empty, I would upload their respective CSVs and then link the records by pasting the ISBN values from the Purchase Order table into the linked field to the POS table

The POS table would be set up with columns to format data into the POS specific template, and I’d pull data over from Purchase Order via lookups, and I would create fields to do the cost checks etc

Adam, Airtable Consultant - thetimesaving.company
K
Forum|alt.badge.img+3
  • KevinSch
  • New Participant
  • December 11, 2024

Yeap that’s helpful, but I feel like without the nitty gritty details it’d be hard to tell you whether exact things were possible, you know what I mean?

In any case, if I were you I’d have two tables, Purchase Order and POS, where the POS table’s primary field is the ISBN and a linked field between the two tables

With both tables empty, I would upload their respective CSVs and then link the records by pasting the ISBN values from the Purchase Order table into the linked field to the POS table

The POS table would be set up with columns to format data into the POS specific template, and I’d pull data over from Purchase Order via lookups, and I would create fields to do the cost checks etc


Hi Guys,

I have a very similar question and I am new to airtable to please also apologize if I ask a stupid question. 

I work in HR and what I am looking for is an automated way to compare two data sets and ideally produce the differences out of that.
Essentially it's about employee data with many different fields of personal data, age, hiring date, user name, personal information etc.

What I need is on a monthly basis upload a new data set and compare it to the previous month to report out on the "joiner / mover / leavers". Meaning who in the old set left the Org and is no longer there, who joined newly, and who moved within the Org to a different position . Is there any way to do that easily and explained to a serious airtable rookie? If possible this would be absolutely massive because we are talking about an everchanging data set of 4000+ employees.

Thank you so much in advance for your help!

Kind regards,
Kevin

Terms & ConditionsAccessibility statement