Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
matching
I have two lists of data to compare against one another and find the unique
transactions (a transaction that is on one list but not on the other). I am reconciling one set of transactions against another set of transactions to find the differences. I would like to compare the "Date" in column a,row 2; the "Auth Num" in column d, row 2; and "TF Gallon" in column e, row 2, all on a worksheet titled "SIMONS", against the "Date" in column a,row 2; the "Auth Num" in column d, row 2; and "TF Gallon" in column e, row 2 on a worksheet called "TA". Unfortunately what is supposed to be a unique identifier, the five digit "Auth Num" is not really unique enough. Sometimes there are duplicate numbers and supposedly unique Auth Num's that have really just been transposed so that it is unique but is actually for a transaction on the other worksheet. This company's accounting software is bad. I would like to compare both worksheets and, using the auth num first, find the transactions that are the same. 98% of the transactions will be eliminated as being unique using the Auth Num to compare data. Most but not all. For the transactions that come back unique (there will be about 40 at most and usually ten), compare the Date and TF Gallons. If they match on the Date and TF gallons then they are probably the same transaction and then can be eliminated as being unique. The TF gallons is an amount of gas that was purchased. After the data is run matched by Auth Num and then Date and TF Gallons, whatever is left over should be unique and I can then add/subtract from the proper list to accomplish my reconcile. How can I do this? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
matching
hi, take a look at:
http://www.j-walk.com/ss/excel/usertips/tip073.htm hth -- regards from Brazil Thanks in advance for your feedback. Marcelo "OfficeNDN" escreveu: I have two lists of data to compare against one another and find the unique transactions (a transaction that is on one list but not on the other). I am reconciling one set of transactions against another set of transactions to find the differences. I would like to compare the "Date" in column a,row 2; the "Auth Num" in column d, row 2; and "TF Gallon" in column e, row 2, all on a worksheet titled "SIMONS", against the "Date" in column a,row 2; the "Auth Num" in column d, row 2; and "TF Gallon" in column e, row 2 on a worksheet called "TA". Unfortunately what is supposed to be a unique identifier, the five digit "Auth Num" is not really unique enough. Sometimes there are duplicate numbers and supposedly unique Auth Num's that have really just been transposed so that it is unique but is actually for a transaction on the other worksheet. This company's accounting software is bad. I would like to compare both worksheets and, using the auth num first, find the transactions that are the same. 98% of the transactions will be eliminated as being unique using the Auth Num to compare data. Most but not all. For the transactions that come back unique (there will be about 40 at most and usually ten), compare the Date and TF Gallons. If they match on the Date and TF gallons then they are probably the same transaction and then can be eliminated as being unique. The TF gallons is an amount of gas that was purchased. After the data is run matched by Auth Num and then Date and TF Gallons, whatever is left over should be unique and I can then add/subtract from the proper list to accomplish my reconcile. How can I do this? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
matching
Thanks it looks like what I will need.
"Marcelo" wrote: hi, take a look at: http://www.j-walk.com/ss/excel/usertips/tip073.htm hth -- regards from Brazil Thanks in advance for your feedback. Marcelo "OfficeNDN" escreveu: I have two lists of data to compare against one another and find the unique transactions (a transaction that is on one list but not on the other). I am reconciling one set of transactions against another set of transactions to find the differences. I would like to compare the "Date" in column a,row 2; the "Auth Num" in column d, row 2; and "TF Gallon" in column e, row 2, all on a worksheet titled "SIMONS", against the "Date" in column a,row 2; the "Auth Num" in column d, row 2; and "TF Gallon" in column e, row 2 on a worksheet called "TA". Unfortunately what is supposed to be a unique identifier, the five digit "Auth Num" is not really unique enough. Sometimes there are duplicate numbers and supposedly unique Auth Num's that have really just been transposed so that it is unique but is actually for a transaction on the other worksheet. This company's accounting software is bad. I would like to compare both worksheets and, using the auth num first, find the transactions that are the same. 98% of the transactions will be eliminated as being unique using the Auth Num to compare data. Most but not all. For the transactions that come back unique (there will be about 40 at most and usually ten), compare the Date and TF Gallons. If they match on the Date and TF gallons then they are probably the same transaction and then can be eliminated as being unique. The TF gallons is an amount of gas that was purchased. After the data is run matched by Auth Num and then Date and TF Gallons, whatever is left over should be unique and I can then add/subtract from the proper list to accomplish my reconcile. How can I do this? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Otto M. - Data Matching | Excel Worksheet Functions | |||
sumif function when left 4 texts are matching | Excel Discussion (Misc queries) | |||
Need to Improve Code Copying/Pasting Between Workbooks | Excel Discussion (Misc queries) | |||
Count matching cells | Excel Worksheet Functions | |||
Matching cells | Excel Discussion (Misc queries) |