Comparing Two Worksheets

[Frances_Cromwel]

I have two worksheets where I need to compare numerous data fields.
Each table has the same common denominator in column A (invoice number)
I want to compare numerous fields in the primary worksheet pulling from worksheet 2.

For example:
I want to pull the record data from worksheet 2 into worksheet 1 to compare them.
Tax amount, total invoice, ship-to address, ship to city, ship-to state.
I then need to see via a formula if one doesn’t match the other. So if the ship-to city doesn’t match it returns an “X”

I also need to pull in data (ship via) from worksheet 1 into worksheet 2 (where it doesn’t exist

I currently do all of this in Excel with vlookup.

Is this possible to do in Airtable?

[Justin_Barrett]

Welcome to the community, @Frances_Cromwell! :grinning_face_with_big_eyes: This is doable, but will require some manual work. Because Airtable is a database and not a spreadsheet, it doesn’t allow arbitrary perusing of the contents of other tables without building links to specific records first. That’s the manual work you’ll need to do, but based on what you described, it should be fairly easy.

Because the primary field (what you called “column A”) in both tables is the invoice number (I’ll refer to this field as {Invoice} going forward), you can add a link field to [Table 1] that points to [Table 2], then copy the entire contents of {Invoice} into this link field, and Airtable will build links to the appropriate records in [Table 2], even if they’re in a completely different order. With this link field built, you’ll notice that Airtable makes a reciprocal link field in [Table 2], meaning that you can now add lookup fields in either table to pull in data that you want to compare.

As for making the comparisons, I suggest naming your fields in such a way that you know where each data point is coming from. For example, say you’re in [Table 1], and are comparing the ship-to city values. I recommend naming [Table 1]'s field {Ship-to City 1}, and the lookup field pulling the equivalent value from [Table 2] as {Ship-to City 2}. Then your formula field could be named something like {Ship-To City Match?}, with the formula:

IF({Ship-to City 1} != {Ship-to City 2}, "❌")

Notice that I used an emoji :x: instead of a plain X. Airtable supports emoji characters in strings, which will let you spice up your field contents as you wish.

Does this give you enough ideas to run with?

[Frances_Cromwel]

Justin_Barrett:

add a link field to [Table 1] that points to [Table 2] , then copy the entire contents of {Invoice} into this link field

Hi Justin,

First question: I’m confused about creating a link field to tie to the two tables together. If I create a column that is a link field in table 1, and I link to table 2, I want to link on the invoice. How do I do that when I need to select an invoice? Do I match the invoice # up? The how do I fill the rest of the cells without doing it automatically?

I’m confused by this: " add a link field to [Table 1] that points to [Table 2] , then copy the entire contents of {Invoice} into this link field"

[Justin_Barrett]

Frances_Cromwell:

If I create a column that is a link field in table 1, and I link to table 2, I want to link on the invoice. How do I do that when I need to select an invoice?

The link field simply tells Airtable that you want to link to one or more records from another table. Making these links is normally done manually by clicking in the field to activate it, clicking the + button in the field to add a link, and choosing a record from the target table in the popup list. The other way to make a link is to paste text into the link field, in which case Airtable will attempt to find a record in the linked table with a primary field matching the pasted text.

That’s why I suggested copying the entire contents of the {Invoice} field into the link field you made in the first table. Record by record, Airtable will take each record number and search the linked table for a matching record, building a link to it if it’s found. Because you said that both tables have the same invoice numbers in their respective primary fields, everything should match.

To get more specific about how you copy and paste an entire field, single-click on the field header (where you see the field name). The entire column should be highlighted. Press CTRL-C (or Command-C, depending on your OS) to copy it. Click on the header of the link field that you made to highlight the entire column, then press CTRL-V / Command V to paste the values.

Is that more clear?

[Bill_French]

Frances_Cromwell:

I have two worksheets where I need to compare numerous data fields.

@Frances_Cromwell, you raise an interesting topic that has piqued my attention. I don’t have a solution per-se, but I am interested in understanding the use case. How is it that you have arrived at a point where two tables contain seemingly near-duplicate information, thus requiring you to perform this audit process?

[Frances_Cromwel]

So I have a column in table 1 that links to the {Invoice} column in table 2.
I highlighted the {Invoice} column in table 2, copied and pasted into table 1 link column. Is this correct? Airtable did not match up the {Invoice} to table 2.
I know this can’t be difficult but I’m missing something.

[Frances_Cromwel]

Hey Bill,

I am comparing invoice data from Netsuite to the invoice data in Avalara, our tax software. There are issues currently with the correct ship-to not being pulled correctly into the tax software so I have to make corrections for sales tax purposes.

I’m looking for a solution that only requires me to drop in the new month’s data, has the outcomes automatically calculated. Do the ship-to addresses match is the key. How much taxable revenue was collected by state and by city, how much sales tax was collected. I then have to compare the order data to what was remitted to the states to make sure there were no errors.

I’m not sure everything I need to do can be done in Airtable.

[Justin_Barrett]

Sorry if I wasn’t clear. All the copying and pasting happens in table 1. For example, say that your invoice column—the primary field in table 1—looks like this (a short example for clarity):

You make a link field next to it like this:

Copy the primary field contents, paste them into this link field, and you have this:

Looking in table 2, you’ll see the matching links coming from table 1:

Frances_Cromwell:

I’m not sure everything I need to do can be done in Airtable.

Can it be done in Airtable? I’m pretty sure it can. Is Airtable the best place to do it? That’s still up in the air. After reading the details of what you’re trying to ultimately accomplish, and now knowing that you need to do this monthly, I feel that it’s still doable in Airtable with minimal manual intervention. Once the full setup is built, the only manual part of the process after importing the new month’s data would be doing the invoice number copying that I mentioned above to automatically build links between the two tables. However, even this could be automated with the help of external tools like Zapier or Integromat.

Long story short, Airtable won’t make links between tables without some kind of intervention, either by you making them yourself (even via copy/paste) or by an external process that you kick off.

[Bill_French]

Frances_Cromwell:

comparing invoice data from Netsuite to the invoice data in Avalara

Yep, I get it. As I suspected, this is about auditing to maintain compliance. I like to dive a little deeper to understand why the sought-after solution has cascaded into focus within Airtable and I appreciate the time you took to give us the bigger picture.

As you point out, Avalara is forcing your hand to perform this auditing process. But such an exercise in Airtable is also an interesting and compelling task that @Justin_Barrett has addressed to the best of Airtable’s capacity.

Have you considered using the Avalara Tax API to create an audit assessment and then perhaps perform the corrections without human intervention?

Depending on the volume, this would seem a bit more sustainable than running all the data into Airtable to compensate for the missteps of Avalara.

While code-free integration platforms like Zapier and Integromat are sometimes helpful in cases like yours, I always wince when I see misstep #1 triggering the need for platform #3 whose technical ceiling triggers the need for another human process that then uncovers the need for yet another collection of human tasks. Before you know it, you’re shaving a Yak!

There is at least one other option - the new Pipedream service - it seems to hold great promise and would surely work will to make this a silent audit/corrective process that works in near-real-time.

But back to Avalara itself - one must ask, why isn’t it super-precise about ship-to addresses since that is a key attribute in determining which tax rates apply? One would/should assume their solution must be relentlesly accurate or risk mistating tax and reporting to tax authorities. I would think they have great incentive to fix this issue.