Excel Formula - Compare two columns

PE 2900
March 26, 2008 at 15:20:36
Specs: XP/2003 office, Dual Quad/4 GB Ram
We have a bill that is sent to us every month and it has an invoice number and a total. I then have to run a sql query to give me a range of invoice numbers and the amount. We need to check to compare invoice number and amount. I have a formula to compare one column but I need a way to compare the 2 columns, that way I can compare the invoice number and the amount.

Thanks,

Jr. Sys


See More: Excel Formula - Compare two columns

Report •


#1
March 27, 2008 at 06:05:24
Could you be a little more specific as to what you are trying to compare and what the result should be?

Perhaps a brief example of what's in the 2 columns and what you are looking for would help.


Report •

#2
March 27, 2008 at 09:21:29
Sure. An example would be:

A B
1 65434 237.87
2 65467 456.98
3 65597 1008.89

So the above spreadsheet is emailed to me and the sheet will have about 800 rows and I then have to run a sql query and I do a range of invoices, from the lowest to the highest and then I will paste my two columns in let's say column D and E and it will have the same information. The big difference will be that my two columns will have about 5,000 rows so I need to match up all my invoices with the ones we received and then I need to match up the amount as well. I would be happy to email a copy of the file if you are interested.

It appears when I post my msg that the columns do not match up well. Column A is the invoice number and B is the amount.

Jr. Sys


Report •

#3
March 27, 2008 at 12:42:39
First, I don't see how the mention of the sql query relates into your question. Is that just something you felt like sharing or is relevant to the question about comparing the 2 columns?

Second, when you say you need to "match up the amount as well" does that mean you might have the same invoice number multiple times, but with different amounts for each? Kind of like partial payments?

Like so, with the -, *, # indicating matches:

5000 Invoices..Amt....800 Invoices..Amt
65434.........125.12....65434-----237.87
65434.........285.15....65467****456.98
65434------237.87....65597###1008.89
65467*****456.98
65467.........123.45
65467.........562.36
65597.........895.32
65597####1008.89
65597..........23.26


Report •

Related Solutions

#4
March 27, 2008 at 15:42:08
Sorry I did not explain myself very well. The invoice spreadsheet we get is for all the invoices that we processed to a company. They in turn send us a bill for all the invoices that they processed and sent to the eventual customer. They do not send them immediately so they might be giving us invoices from several months back, so I run a sql query that I then transfer the data to excel. All our information is in a SQL database and I can compare and highlight and create views, whatever I need to find what I'm looking for. However, in excel I'm not sure how I can get the needed end result.
So, I have a spreadsheet with invoice numbers and the associated amount. Then I get all the same invoice numbers with an associated amount, my amount might be off by a dollar from the final amount that we receive from our 3rd party vendor. So the issue is that they give us about 800 invoices with an amount and I have to match up our invoices with our amount and make sure they match. So I would like to find a way to highlight or something like that, any invoice numbers and amounts that don't match within let's say $2.

Jr. Sys


Report •

#5
March 28, 2008 at 08:39:35
I'm going to assume that only the second part of your latest post, starting from the word "So", contains the actual question.

You state that you get "all the same invoice numbers" from the vendor, but the amounts might not match.

Assuming that's the case, try this, adjusting columns to best suit your needs.

- Place one list of invoices in Column A.
- Place the associated amounts in column B.
- Place the other list of invoices in Columns C
- Place the associated amounts in Column D.
- Sort your data so that the invoice numbers line up.
- Highlight column D, the column with one set of your amounts.
- Click Format...Conditional Formatting
- Use the dropdown menu to choose Formula is
- In the formula box, enter this formula:

=IF(ABS(B1-D1)>2,TRUE,FALSE)

- Click the Format button and pick a format for Column D, such as Pattern...Red.

The formula will calculate the difference between the amounts in Columns B and D and format (highlight) the amount in Column D if it differs by more than 2.


Report •

#6
April 2, 2008 at 10:33:58
Thank you for the help, although I think I messed it up and it does not seem to be working in the manner that I hoped. The main issue is that the invoice we receive has about 800 invoice and the ones that we have is about 30,000 invoices, so I can't just match them up, I need to first remove the 800 needed invoices from the 30,000 invoice along with the amounts and then match up the amounts. I know that it might have seemed that the information I was giving was fluff, but I was hoping to describe the situation so it could be understood what I'm trying to accomplish. I have pasted a sample of the worksheet. I have an excel file I would be happy to send.

Thanks,

Invoice # Total Invoice # Total
688100 213.35 688100 213.35
688066 1046.76 688099 100
688065 741.06 688098 273.2
688035 702.98 688097 656.85
688009 534.91 688096 290.58
688008 417.6 688095 798
688007 417.6 688094 845.46
688006 557.05 688093 1195.2
687997 782.82 688092 638.55
687982 1237.02 688091 926.68
687981 245.94 688090 962.85
687980 604.24 688089 465.18
687979 197.97 688088 597.68
687978 245.44 688087 538.96
687977 309.6 688086 367.2
687976 1297.04 688085 1077.24
687974 245.13 688084 131.36
687973 62.55 688084 960.22

Jr. Sys


Report •

#7
April 2, 2008 at 20:58:18
Based on the sample workbook you sent via email, I think the following Conditional Formatting formula should work.

Select the cells that contain the "Company Invoices" and put this in the Formula Is field of the Conditional Formatting dialog box and choose a Format.

Obviously you'll have to adjust it for the actual cells used in the real workbook.

=IF(ABS(INDEX($A$3:$C$1074,MATCH(E3,$A$3:$A$1074,0),3)-F3)>2,TRUE,FALSE)

Let me know how it works for you.


Report •


Ask Question