Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to find missing values while reconciling files?
Dear All,
Below is a link to the file with data I am trying to reconcile. The point is, I can't find 3 values. You will have noticed that I manually added 2 values that were not found by vlookup. Frankly all values from Columnd D exist in Column B as identified by vlookup function. However, there are 3 missing and can't be picked up as they are probably the same values which appear several times in Column D but not in Column B. I suspect that the disparity of 25 555,16 is a combination of some amounts. Any help appreciated with this reconciliation. I was not able to spot this at work so am trying to do this at home for tomorrow morning. Is there any function that looks up repetitive values/number e.g. the same invoice number in a row or column? Thanks in advance, Peter file hosting website: http://www.mediafire.com/?0y2t4guzjyj |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to find missing values while reconciling files?
Got this message at the page you mentioned;
"This file is currently set to private. " VLOOKUP picks up only the first match... "Piotr (Peter)" wrote: Dear All, Below is a link to the file with data I am trying to reconcile. The point is, I can't find 3 values. You will have noticed that I manually added 2 values that were not found by vlookup. Frankly all values from Columnd D exist in Column B as identified by vlookup function. However, there are 3 missing and can't be picked up as they are probably the same values which appear several times in Column D but not in Column B. I suspect that the disparity of 25 555,16 is a combination of some amounts. Any help appreciated with this reconciliation. I was not able to spot this at work so am trying to do this at home for tomorrow morning. Is there any function that looks up repetitive values/number e.g. the same invoice number in a row or column? Thanks in advance, Peter file hosting website: http://www.mediafire.com/?0y2t4guzjyj |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to find missing values while reconciling files?
Hi Sheelo,
sorry I missed that. It's been made public now. Thanks, Peter "Sheeloo" wrote: Got this message at the page you mentioned; "This file is currently set to private. " VLOOKUP picks up only the first match... "Piotr (Peter)" wrote: Dear All, Below is a link to the file with data I am trying to reconcile. The point is, I can't find 3 values. You will have noticed that I manually added 2 values that were not found by vlookup. Frankly all values from Columnd D exist in Column B as identified by vlookup function. However, there are 3 missing and can't be picked up as they are probably the same values which appear several times in Column D but not in Column B. I suspect that the disparity of 25 555,16 is a combination of some amounts. Any help appreciated with this reconciliation. I was not able to spot this at work so am trying to do this at home for tomorrow morning. Is there any function that looks up repetitive values/number e.g. the same invoice number in a row or column? Thanks in advance, Peter file hosting website: http://www.mediafire.com/?0y2t4guzjyj |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to find missing values while reconciling files?
See the second sheet (Reconciled, Cols F-M) at
http://www.mediafire.com/?eowgzwnkqmm I extracted the unique entries from Col B and D (after removing the two you had added), then compared the difference in counts for each unique value between the two cols. 1,682.60 is not in Col D Other differences in Counts are highlighted There are many entries for which counts are different. "Piotr (Peter)" wrote: Hi Sheelo, sorry I missed that. It's been made public now. Thanks, Peter "Sheeloo" wrote: Got this message at the page you mentioned; "This file is currently set to private. " VLOOKUP picks up only the first match... "Piotr (Peter)" wrote: Dear All, Below is a link to the file with data I am trying to reconcile. The point is, I can't find 3 values. You will have noticed that I manually added 2 values that were not found by vlookup. Frankly all values from Columnd D exist in Column B as identified by vlookup function. However, there are 3 missing and can't be picked up as they are probably the same values which appear several times in Column D but not in Column B. I suspect that the disparity of 25 555,16 is a combination of some amounts. Any help appreciated with this reconciliation. I was not able to spot this at work so am trying to do this at home for tomorrow morning. Is there any function that looks up repetitive values/number e.g. the same invoice number in a row or column? Thanks in advance, Peter file hosting website: http://www.mediafire.com/?0y2t4guzjyj "Piotr (Peter)" wrote: Hi Sheelo, sorry I missed that. It's been made public now. Thanks, Peter "Sheeloo" wrote: Got this message at the page you mentioned; "This file is currently set to private. " VLOOKUP picks up only the first match... "Piotr (Peter)" wrote: Dear All, Below is a link to the file with data I am trying to reconcile. The point is, I can't find 3 values. You will have noticed that I manually added 2 values that were not found by vlookup. Frankly all values from Columnd D exist in Column B as identified by vlookup function. However, there are 3 missing and can't be picked up as they are probably the same values which appear several times in Column D but not in Column B. I suspect that the disparity of 25 555,16 is a combination of some amounts. Any help appreciated with this reconciliation. I was not able to spot this at work so am trying to do this at home for tomorrow morning. Is there any function that looks up repetitive values/number e.g. the same invoice number in a row or column? Thanks in advance, Peter file hosting website: http://www.mediafire.com/?0y2t4guzjyj |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to find missing values while reconciling files?
Hi Sheeloo,
Thank you for your time and help. I had found the other way arround by extracting from a source file additional description and compared those columns eventually. Nonetheless, you table might be useful in future as I find it as a good way to look for specific values. 3 values were entered twice, hance the difference. Thank you once again. Regards, Peter "Sheeloo" wrote: See the second sheet (Reconciled, Cols F-M) at http://www.mediafire.com/?eowgzwnkqmm I extracted the unique entries from Col B and D (after removing the two you had added), then compared the difference in counts for each unique value between the two cols. 1,682.60 is not in Col D Other differences in Counts are highlighted There are many entries for which counts are different. "Piotr (Peter)" wrote: Hi Sheelo, sorry I missed that. It's been made public now. Thanks, Peter "Sheeloo" wrote: Got this message at the page you mentioned; "This file is currently set to private. " VLOOKUP picks up only the first match... "Piotr (Peter)" wrote: Dear All, Below is a link to the file with data I am trying to reconcile. The point is, I can't find 3 values. You will have noticed that I manually added 2 values that were not found by vlookup. Frankly all values from Columnd D exist in Column B as identified by vlookup function. However, there are 3 missing and can't be picked up as they are probably the same values which appear several times in Column D but not in Column B. I suspect that the disparity of 25 555,16 is a combination of some amounts. Any help appreciated with this reconciliation. I was not able to spot this at work so am trying to do this at home for tomorrow morning. Is there any function that looks up repetitive values/number e.g. the same invoice number in a row or column? Thanks in advance, Peter file hosting website: http://www.mediafire.com/?0y2t4guzjyj "Piotr (Peter)" wrote: Hi Sheelo, sorry I missed that. It's been made public now. Thanks, Peter "Sheeloo" wrote: Got this message at the page you mentioned; "This file is currently set to private. " VLOOKUP picks up only the first match... "Piotr (Peter)" wrote: Dear All, Below is a link to the file with data I am trying to reconcile. The point is, I can't find 3 values. You will have noticed that I manually added 2 values that were not found by vlookup. Frankly all values from Columnd D exist in Column B as identified by vlookup function. However, there are 3 missing and can't be picked up as they are probably the same values which appear several times in Column D but not in Column B. I suspect that the disparity of 25 555,16 is a combination of some amounts. Any help appreciated with this reconciliation. I was not able to spot this at work so am trying to do this at home for tomorrow morning. Is there any function that looks up repetitive values/number e.g. the same invoice number in a row or column? Thanks in advance, Peter file hosting website: http://www.mediafire.com/?0y2t4guzjyj |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Reconciling | Excel Worksheet Functions | |||
creating a reconciling list of items not matched between two files | Excel Worksheet Functions | |||
Find missing values in a series | Excel Worksheet Functions | |||
Disable find files error message for missing links on opening WB | Excel Discussion (Misc queries) | |||
Excel Compare values in columns & display missing values in a new | Excel Discussion (Misc queries) |