Comparing to Colums [Closed]

Report
-
 alshear -
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

Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
789
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
Posts
191
Registration date
Sunday April 12, 2009
Status
Member
Last seen
February 16, 2010
210
Just check the below thread may be it will solve your problem .....

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


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.
mubashir aziz
Posts
191
Registration date
Sunday April 12, 2009
Status
Member
Last seen
February 16, 2010
210
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,"")}



Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
789
can you give a small extract of your sheet(s). I do not know whether in this form a sheet can be posted.
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.
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.