Comparing to Colums

Closed
Alshear - Jul 2, 2009 at 07:16 PM
 alshear - Jul 7, 2009 at 06:19 PM
Hello,
I have a dilemma. I am trying to compare two sets of data to make sure they match. I have a bill with names and the amount for each name; then i have a sheet that has names and the amount they paid toward the bill. I am trying to find a way to see if for example the names in column A are the same as in column C ; as well if the amounts associated with the name are correct on the bill verses moneys collected. Is there a way to do this my bill has around 3,000 people on it.

6 replies

venkat1926
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
810
Jul 2, 2009 at 07:46 PM
assume the name and the bill amount are in column A and B of sheet 1. where are the names and amoutn paid in sheet 2 (that is in wich columns). you talk about some column C

If you find the bill amount and amount paid what do you want to do and if not what do you want to do.

claiffy
0
mubashir aziz
Posts
190
Registration date
Sunday April 12, 2009
Status
Member
Last seen
February 16, 2010
159
Jul 2, 2009 at 11:17 PM
Just check the below thread may be it will solve your problem .....

http://ccm.net/forum/affich 116250 compare and match


0
The sheet is set up as such:
Column
A B C D
Employee Amount Employee Employee
Name (A2) Billed (B2) Name (C2) Amount Paid (D2)

What I need to do is Compare the employee name (a2) and amount billed (b2) to employee name (c2) and employee amount paid (b2). The names are sorted alphabetically but in some cases there is someone on the bill that is not paying for the services so there name does not show up on my listing with employees name and employee amount paid. When this occurs it messes my list up so if the names and amounts do not match up; so I have to go through the list of people and find who is the oddity and causing the list not to match. I am trying to find a way to match the name columns so that the employee name on the bill and the employee name from my paid out listing are in the same row. Once I have the names matching I also want to see if amount billed is the same as amount paid out.
0
mubashir aziz
Posts
190
Registration date
Sunday April 12, 2009
Status
Member
Last seen
February 16, 2010
159
Jul 6, 2009 at 10:07 PM
If you are just want to show up the name which are in column A but not in column C then below formula will work for you and will show you the name of employees which have not paid. If you need any thing else do let me know ...... in my last post i give you the one referenced link and i used the same approach .... you can use vlookup as well in this case ........just try this formula and do let me know .......
E2=IF(ISERROR(INDEX(C:C,MATCH(A2,C:C,0))),A2,"")

Drag this formula to downward to compare other cells as well .........

I've also made below formula and you can copy this formula in Cell F2 then press Ctrl+Shift+Enter ...

Actually this formula will check if one employee is in column A and also in column C but he has paid less amount then billed.
e.g. if jenny billed amount is 500 and she paid only 200 then her name will be appear in column F and you can check her amount ...... But remember one thing that names should be unique and occur once else this formula will always check the first occurrence of employee and will give you the result ......


Array formula which needs to be entered as by pressing Ctrl+Shift+enter. After pressing Ctrl+Shift+Enter {} will occur around the formula. Don't try to write {} manually
F2={IF(ISERROR(INDEX($C$2:$C$150,MATCH(1,IF($C$2:$C$150=A2,IF($B$2:$B$150=D2,1)),0))),A2,"")}



0
venkat1926
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
810
Jul 6, 2009 at 08:50 PM
can you give a small extract of your sheet(s). I do not know whether in this form a sheet can be posted.
0

Didn't find the answer you are looking for?

Ask a question
Column A /Column B / Column C / Column D

Row 1 / Name / Amout Billed / Name / Amount Paid

Row 2 / John Smith / $5.00 / John Smith / $5.00

Row 3 / Tom wright / $6.75 / Ben Cooper / $7.24

Row 4 / Tim James/ $4.25 / Tim James / $4.25

Row 5 / Matt heart / $4.00 / Mary Smith / $5.00

Row 6 / Mary Smith / $5.00 / Bill Troop / $8.25


This is a sample of what the sheet would look like I want to know of the employees on both sheets if the $ amounts matach and want to know which employees are not in both collums.
0
Column A /Column B / Column C / Column D

Row 1 / Name / Amout Billed / Name / Amount Paid

Row 2 / John Smith / $5.00 / John Smith / $5.00

Row 3 / Tom wright / $6.75 / Ben Cooper / $7.24

Row 4 / Tim James/ $4.25 / Tim James / $4.25

Row 5 / Matt heart / $4.00 / Mary Smith / $5.00

Row 6 / Mary Smith / $5.00 / Bill Troop / $8.25


This is a sample of what the sheet would look like I want to know of the employees on both sheets if the $ amounts matach and want to know which employees are not in both collums.
0