OmniDocHow to check the two versions of the quotation form: How to choose between Excel comparison and PDF comparison
The quotation form has been revised several times, and it is very time-consuming to check each item. This article compares the applicable scenarios of using Excel formulas to check, and first extracting PDF tables to Excel and then comparing the two methods, and gives the selection basis and operation steps.
Key points of this article
- When the two versions of quotations are in Excel and have the same structure, use auxiliary columns and formulas to compare directly item by item.
- When the new version is a PDF that can select text, first extract the table to Excel, and then compare the formulas. It is less likely to miss items than comparing with the naked eye.
- The scanned document does not have a text layer, so you need to perform OCR recognition first, and check whether the recognized numbers are consistent with the original.
- The comparison results only explain the differences. The total and tax amount still need to be recalculated, and important unit prices are sampled and reviewed.
When a supplier sends a new quotation, procurement or finance usually needs to confirm: which unit prices have changed, which quantities have been deleted, and what the total has become. It's easy to handle when both versions are in Excel, but once one of the versions is in PDF, you have to convert the table first before you can truly compare. Many people search for "tools for checking formulas in excel tables". What they actually want to know is: should they use Excel formulas to compare, or should they convert first and then compare, or simply compare two PDFs.
This article compares three ideas: using formulas to check in Excel, first extracting the PDF table to Excel and then comparing, and directly comparing two PDFs. After reading, you will be able to choose the appropriate approach based on the format of the document in your hand, and know which points are easily missed during verification.
How to check the two versions of quotations to make it more stable? One sentence conclusion
When both versions are editable tables, you can use Excel formulas to compare and control them. If one of the versions is a PDF and text can be selected, extract the table first and then compare. If you just want to quickly see where the changes have been made in the two documents, it is easier to directly compare the two PDFs.
| your situation | Recommended practices | reason |
|---|---|---|
| Both versions are Excel, with the same row and column structure. | Compare item by item using formulas in Excel | Values can be calculated directly and differences can be highlighted |
| One version of Excel, one version of PDF, and the PDF can select text | First extract the PDF table to Excel, and then compare it with formulas | After converting to data, you can do subtraction and search. |
| The PDF is a scan and text cannot be selected. | Do OCR recognition first, check the recognition results and then compare. | The scanned document does not have a text layer that can be extracted, and recognition errors require manual verification. |
| Both versions are PDF, please pay attention to format and text changes. | Directly compare two PDFs | Ability to mark text and layout differences at the page level |
| The quotation items are increased or decreased, and the line order is disrupted. | First align by item number or product name, and then compare | Once the line number changes, subtracting line by line number will cause all misalignments. |
What are the different methods?
Check with formulas in Excel
Put the two versions of data into two worksheets in the same workbook, and first sort them by item number or product name. Then, in the auxiliary column of a table, use a search function to get the unit price of the other version, and then use a conditional judgment formula to mark "the same" or "different", and directly subtract the difference column. With conditional formatting, different cells will be highlighted and total changes can be seen at a glance.
This approach is suitable for quotations with a stable table structure and a small number of rows. The shortcoming is: merging cells, multi-layer headers and changes in row order will cause errors in the formula; it only compares values and text, and changes to notes and explanatory terms require additional manual reading.
First extract the PDF table to Excel and then compare it
If the new version of the quotation is a digital PDF, you can first try to select a piece of table text in the reader. Can be selected, the description file has a text layer, and can be copied to Excel, or exported as a table with the help of online conversion websites and desktop office software. After the conversion is completed, use the formula method in the previous section for comparison.
The difficulty with this approach is the quality of the conversion. Merging cells, cross-page headers, and tables missing borders may cause column misalignment. Copied and pasted numbers are often saved as text, and direct summing will result in incorrect results, which need to be converted to numerical values first. After conversion, check at least a few rows to confirm that the columns and numbers correspond correctly.
Directly compare two PDFs
This method does not require converting tables, but directly checks the differences between the two documents in the PDF comparison function. It is suitable for situations where both versions of the quotation are delivered as PDF, the layout is basically the same, and you mainly want to know where the changes have been made.
Its limitations are also obvious: the comparison locates text and layout differences on the page. It will not calculate the total changes for you, and it is not suitable for summarizing the unit price differences of dozens of lines. If you want to calculate the increase or decrease in the total amount, you still need to put the data into the table.
📊 Core comparison
| Contrast Dimensions | Formula checking in Excel | Extract the PDF table first and then compare it | Compare two PDF directly |
|---|---|---|---|
| Do you need to convert the table first? | No need | need | No need |
| Can you calculate the unit price difference and the total change? | Can | Yes, but check the number format first after conversion | Cannot be calculated directly |
| Can you find changes in layout and page text? | Can only compare the data content | Basically, it can only compare to the content of the data. | Can |
| The difficulty of dealing with the change of row order | Need to align first | Need to align first | No need to align, press the page to view |
| Risk of conversion error | Low | Higher, merging cells and text numbers is a common problem | Low |
# which method is suitable for you?
if you have both Excel versions, the structure has not changed much
Do the auxiliary column and formula comparison directly in Excel, the checking process is completed completely in the local table, and the result is also easy to keep the file.
if the new version is PDF, but text can be selected
First extract the table to Excel, and then compare it with the formula. Be sure to check whether the numbers are saved as text and whether the columns are aligned after conversion.
if both versions are PDF, I just want to see where to change them.
Using the PDF comparison function to view the differences page by page, it is suitable to quickly determine the scope of changes; the part involving the amount, and then extract the relevant lines to calculate separately.
if it is a scanned piece, or the line items increase or decrease a lot
First do OCR recognition, and then check the recognition results. When the line items increase or decrease a lot, first sort out two aligned tables according to the item number, and then compare the formulas, which will be more efficient than flipping through the pages one by one.
# how to do it in OmniDoc
- Download and open the OmniDoc desktop client from download.omnidoc.top, and use the PDF editor to open a version of the quote.
two. If both versions are PDF, use the comparison feature of the PDF editor, select the other version of the file, and view the text and page differences between the two documents.
- If you need to put the form into Excel for formula checking, use the export function to select Excel format for export, and then align the data by item number in Excel.
- If it is a scanned version of PDF, first use the OCR function to identify the text, and then check the unit price, quantity and decimal point against the original.
- After the check is completed, recalculate the total and tax amount of the two editions, sample and compare the important unit price, and do not rely solely on the comparison results.
# frequently asked questions (FAQ)
1. Is there a tool for checking formulas in the excel form?
Excel itself does not have a button to compare the two versions of the quotation, commonly used is the auxiliary column plus search function, conditional judgment and conditional format. If you don't want to build the formula manually, you can choose office software or PDF editor with comparison function, but still check that the results are consistent with the original table.
2. Can the total of the quotation be checked with the comparison function?
The comparison function can mark the difference between numbers and words, but it does not replace calculation. The aggregate changes are recommended to be recalculated in Excel using the summation formula, and then compared with the total in the two versions of the quotation.
3. What if the numbers copied from PDF to Excel cannot be summed up?
For the most part, numbers are saved as text. Select the column first, use the sorting function or change to a numerical format, and then sum; at the same time, check whether there are spaces or hidden symbols before and after the number.
4. The line order of the two versions of the quote is different, how to align it?
Take the item number or product name as the keyword, place the two pages of data side by side, and compare the values with the search class formula. Do not subtract directly by line number, or one increase or decrease will put all subsequent lines out of place.
# Summary
To check the quotation of the two editions, the key is to judge the file format first: the editable form is compared with Excel formula, the PDF that can select the text is extracted first and then compared, and if you only want to see the change position, you will directly compare the PDF. No matter which method is used, the total and important unit prices have to be recalculated or sampled and reviewed.
