Related:
- Comparing to Colums
- How to compare two excel sheets - Guide
- Comparing columns in two different excel sheets and if they match copying third column - Excel Forum
- Which function is used to compare a cell value to an array of cells and return a value that matches the location of the value in the array, and is used when there are more than two columns in the array ✓ - Excel Forum
- Comparing two excel files - Programming Forum
- Comparing two spreadsheet data - Excel Forum
6 responses
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Jul 2, 2009 at 07:46 PM
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
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
mubashir aziz
Posts
190
Registration date
Sunday April 12, 2009
Status
Member
Last seen
February 16, 2010
165
Jul 2, 2009 at 11:17 PM
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
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.
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
190
Registration date
Sunday April 12, 2009
Status
Member
Last seen
February 16, 2010
165
Jul 6, 2009 at 10:07 PM
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 .......
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
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,"")}
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Jul 6, 2009 at 08:50 PM
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.
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.
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.
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.
