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

Topic Labels: Formulas
2325 3
Showing results for 
Search instead for 
Did you mean: 
4 - Data Explorer
4 - Data Explorer

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!

3 Replies 3

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?

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