For Fraud Examiner / Financial Investigators ·
What you'll accomplish
A duplicate-payment formula that runs without an error is not the same as one that is right. A range that stops one row short returns clean TRUE and FALSE values and misses a pair, and you may not notice until a reviewer does. In this guide you give ChatGPT a small invented payment file whose answers you already know, ask it to find the same patterns and show its steps, then compare its list with your workbook's list. Where the two disagree, one of them is wrong, and you find out which before the formula goes near a real export.
What you'll need
Data caution. This guide uses an invented sample so there is nothing to protect. When you test against your own layout, keep it that way: invented rows, or rows fully coded and redacted so no vendor, person, invoice or account can be identified. Real payment exports belong in an AI tool only if your employer's AI policy and an approved tool allow it. Either way, look at your account's data controls to see whether chats and uploaded files are kept and whether they may be used for model training, since consumer plans can differ from business ones.
Do this first, before ChatGPT sees anything, so the comparison is honest.
=COUNTIFS($B$2:$B$12,B2,$C$2:$C$12,C2,$D$2:$D$12,D2)>1
=MOD(D2,100)=0=AND(D2>=$J$1*0.9,D2<$J$1) filled down.OpenAI's help page on data analysis says ChatGPT can analyze uploaded files, answer questions about the data, and create tables or charts. It recommends structured data with clear column names and one record per row, and it lists spreadsheet formats including .xlsx, .xls and .csv.
What you should see: the file name appearing in the message box before you send. Troubleshooting: Labels and menu locations change. If you cannot find the upload control, check that your account type and the selected model support file uploads, and look in OpenAI's current help pages. If the file is rejected, try saving it as .csv.
Send this message with the file attached.
Attached is an invented file of 12 payments with columns Sheet row, Vendor_Code, Invoice_No and Amount. Header is row 1.
1. List the sheet rows where Vendor_Code, Invoice_No and Amount all repeat.
2. List the sheet rows where Vendor_Code and Amount repeat but Invoice_No differs from at least one other row.
3. List the sheet rows where Amount is a whole multiple of 100.
4. List the sheet rows where Amount is 4,500 or more and below 5,000.
5. Show the steps or code you used for each list.
6. Make a chart of payment count by Vendor_Code.
Report row numbers exactly as they appear in the Sheet row column.
What you should see: four lists of row numbers, a short description of how each was found (or the code), and a chart. ChatGPT may show its work in a data analysis panel or in the reply. Troubleshooting: If the reply gives counts but no row numbers, ask again for the row numbers. If it gives different rows than the answers above, do not edit the sample. Ask it to show how it handled the 1375.50 amounts, since decimals are a common place for a mismatch.
What you should see: a mismatch of two rows. This is the useful moment. One of the two lists is wrong, and the data is invented, so you can find out why without any risk.
$B$2:$B$13, $C$2:$C$13, $D$2:$D$13) and fill down again.What you should see: both lists match, with 4 rows in 2 pairs. Troubleshooting: If they still disagree, ask ChatGPT to show the exact steps it used for the exact-duplicate list, and read them against your formula. Sometimes the AI's list is the one with the problem, for example when it treats 1375.5 and 1375.50 differently or drops a row. Your own hand check of the rows decides.
What you should see: every list matches your pre-worked answers. Troubleshooting: Charts sometimes sort by count instead of vendor code, or group differently. The numbers matter, not the order.
Explain a mismatch:
My workbook flags rows 2 and 3 as exact duplicates and your list shows rows 2, 3, 5 and 13. My formula is =COUNTIFS($B$2:$B$12,B2,$C$2:$C$12,C2,$D$2:$D$12,D2)>1 with data in rows 2 to 13. Explain the difference.
Edge-case rows for your test file:
Suggest 10 invented payment rows (vendor code, invoice number, amount) that test a duplicate formula: a trailing space in the invoice number, a lowercase invoice letter, a decimal amount and a transposed digit. Label which rows should and should not be flagged.
Review a formula's logic:
What you should see: column E shows TRUE for rows 2 and 3 only. Column F shows TRUE for rows 2, 3, 4, 6 and 9. Column G shows TRUE for rows 2, 3, 4, 7 and 11. Troubleshooting: If your column F has extra TRUE values, check that the Amount column holds numbers and not text. Text values behave differently in MOD.
Here is a formula: [paste]. Columns: [names only]. Explain what it flags, what it misses and what happens with blank cells.
Round-amount check:
Explain what =MOD(D2,100)=0 does with amounts that have cents, with zero and with blank cells.