| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Complicated Macro to Match - Not for the faint hearted!
Hello
I have a spreadsheet which I have to reconcile between our customer orders in the system and our bank account. I dont know if this requires a complicated if statement but here is the criteria of what I need, 1. I have a list of transactions from our system and the bank account in the same sheet. Column A identifies between the system and the bank accounting using the characters SYS and BACC. Column B contains the type of order. Column C contains the order number which should also match the bank account. Column E contains the amount from the bank account and the amount from the order. IF the following criteria are met then do the below. 1. Column B match for both rows then column E matches for both rows then column B. Column A rows much have only one row that matches so the criteria must be one for sys and one for bacc. THe above criteria is the only way I think we can match the entries off completely. One the criteria has been met I want both rows copied to a new sheet called "Matched" so the data sheet just leaves entries which have not been matched up. Here is some sample data, ColA ColB ColC ColD ColE SYS S A12345LP DD 100.10 BACC S A12345LP DD -100.10 SYS D A12345LP DD 99.99 BACC D BA12345LP DD 99.99 BACC E 90222EAP DD 120.00 SYS E 444555BAP DD 12.00 SYS S B12345AA DD 89.97 BACC S B12345AA DD 89.97 If the criteria I set is applied then I would expect to see copied into the new worksheet "Matched" the below transactions which have been matched off. There must be an entry for both SYS and BACC regardless as to whether the amounts are positive or negative as long as they match to the penny. ColA ColB ColC ColD ColE SYS S A12345LP DD 100.10 BACC S A12345LP DD -100.10 SYS S B12345AA DD 89.97 BACC S B12345AA DD 89.97 Me even getting the thinking in my head correct was difficult.........I really hope someone can help with this, Thanks so much, Your all really amazing at these things and I do appreciate all your help. Andrea |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Complicated Macro to Match - Not for the faint hearted!
Hi Andrea
If I have understood you correctly, then enter in F2 =IF(E2="","",IF(SUMPRODUCT(($C$2:$C$9=C2)* ($B$2:$B$9=B2)*$E$2:$E$9)=0,"Matched","")) This will produce a Matched pair for the first 2 lines, but not for the last 2 as their sign is the same. Shouldn't one of them be negative? If you want a match regardless of sign, then =IF(E2="","",IF(OR(SUMPRODUCT(($C$4:$C$9=C2)* ($B$4:$B$9=B2)*$E$4:$E$9)=0, SUMPRODUCT(($C$4:$C$9=C2)* ($B$4:$B$9=B2)*$E$4:$E$9)=2*E2),"Matched","")) Copy down column F Apply DataFilterAutofilter and used the dropdown on column F to select Matched. Mark the filtered rows by selecting their row numbers, Copypaste to new sheet. Mark the filtered rows again on Source sheet, Delete. -- Regards Roger Govier wrote in message ups.com... Hello I have a spreadsheet which I have to reconcile between our customer orders in the system and our bank account. I dont know if this requires a complicated if statement but here is the criteria of what I need, 1. I have a list of transactions from our system and the bank account in the same sheet. Column A identifies between the system and the bank accounting using the characters SYS and BACC. Column B contains the type of order. Column C contains the order number which should also match the bank account. Column E contains the amount from the bank account and the amount from the order. IF the following criteria are met then do the below. 1. Column B match for both rows then column E matches for both rows then column B. Column A rows much have only one row that matches so the criteria must be one for sys and one for bacc. THe above criteria is the only way I think we can match the entries off completely. One the criteria has been met I want both rows copied to a new sheet called "Matched" so the data sheet just leaves entries which have not been matched up. Here is some sample data, ColA ColB ColC ColD ColE SYS S A12345LP DD 100.10 BACC S A12345LP DD -100.10 SYS D A12345LP DD 99.99 BACC D BA12345LP DD 99.99 BACC E 90222EAP DD 120.00 SYS E 444555BAP DD 12.00 SYS S B12345AA DD 89.97 BACC S B12345AA DD 89.97 If the criteria I set is applied then I would expect to see copied into the new worksheet "Matched" the below transactions which have been matched off. There must be an entry for both SYS and BACC regardless as to whether the amounts are positive or negative as long as they match to the penny. ColA ColB ColC ColD ColE SYS S A12345LP DD 100.10 BACC S A12345LP DD -100.10 SYS S B12345AA DD 89.97 BACC S B12345AA DD 89.97 Me even getting the thinking in my head correct was difficult.........I really hope someone can help with this, Thanks so much, Your all really amazing at these things and I do appreciate all your help. Andrea |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Complicated Macro to Match - Not for the faint hearted!
Does no one know how to do this in a macro to save me the risk of
making a mistake? There is a risk in doing these long excel formulas as I don't really understand where as if I have a macro that can be variable then that always works best for me , Please let me know, Thanks Andrea On 2 Jun, 10:23, "Roger Govier" wrote: Hi Andrea If I have understood you correctly, then enter in F2 =IF(E2="","",IF(SUMPRODUCT(($C$2:$C$9=C2)* ($B$2:$B$9=B2)*$E$2:$E$9)=0,"Matched","")) This will produce a Matched pair for the first 2 lines, but not for the last 2 as their sign is the same. Shouldn't one of them be negative? If you want a match regardless of sign, then =IF(E2="","",IF(OR(SUMPRODUCT(($C$4:$C$9=C2)* ($B$4:$B$9=B2)*$E$4:$E$9)=0, SUMPRODUCT(($C$4:$C$9=C2)* ($B$4:$B$9=B2)*$E$4:$E$9)=2*E2),"Matched","")) Copy down column F Apply DataFilterAutofilter and used the dropdown on column F to select Matched. Mark the filtered rows by selecting their row numbers, Copypaste to new sheet. Mark the filtered rows again on Source sheet, Delete. -- Regards Roger Govier wrote in message ups.com... Hello I have a spreadsheet which I have to reconcile between our customer orders in the system and our bank account. I dont know if this requires a complicated if statement but here is the criteria of what I need, 1. I have a list of transactions from our system and the bank account in the same sheet. Column A identifies between the system and the bank accounting using the characters SYS and BACC. Column B contains the type of order. Column C contains the order number which should also match the bank account. Column E contains the amount from the bank account and the amount from the order. IF the following criteria are met then do the below. 1. Column B match for both rows then column E matches for both rows then column B. Column A rows much have only one row that matches so the criteria must be one for sys and one for bacc. THe above criteria is the only way I think we can match the entries off completely. One the criteria has been met I want both rows copied to a new sheet called "Matched" so the data sheet just leaves entries which have not been matched up. Here is some sample data, ColA ColB ColC ColD ColE SYS S A12345LP DD 100.10 BACC S A12345LP DD -100.10 SYS D A12345LP DD 99.99 BACC D BA12345LP DD 99.99 BACC E 90222EAP DD 120.00 SYS E 444555BAP DD 12.00 SYS S B12345AA DD 89.97 BACC S B12345AA DD 89.97 If the criteria I set is applied then I would expect to see copied into the new worksheet "Matched" the below transactions which have been matched off. There must be an entry for both SYS and BACC regardless as to whether the amounts are positive or negative as long as they match to the penny. ColA ColB ColC ColD ColE SYS S A12345LP DD 100.10 BACC S A12345LP DD -100.10 SYS S B12345AA DD 89.97 BACC S B12345AA DD 89.97 Me even getting the thinking in my head correct was difficult.........I really hope someone can help with this, Thanks so much, Your all really amazing at these things and I do appreciate all your help. Andrea- Hide quoted text - - Show quoted text - |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Complicated Macro to Match - Not for the faint hearted!
Hi Andrea
The formulae are not complicated. You didn't respond as to whether two identical sums of the same sign are to be matched or not. =IF(E2="","",IF(SUMPRODUCT(($C$2:$C$9=C2)* ($B$2:$B$9=B2)*$E$2:$E$9)=0,"Matched","")) Breaking this down IF(E2="","", this is to prevent the formula writing the word matched against blank rows further down your sheet, where there are no values in column E. So if there is nothing in column E, enter a null in column F The SUMPRODUCT() part is adding together all values from column E, where rows have identical values in cells in column C (regardless of whether they are adjacent) and rows have identical values in cells in column B.(regardless of whether they are adjacent) If the sum of these values is Zero, then the column F shows the word "matched" against the relevant lines, because all of the rows are equal and opposite. i.e 100.10 + -100.10 = 0 In the second case, the OR function is merely adding to the above, the word "matched" also, if the SUM is not equal to Zero, but is equal to twice the value found in column E i.e. 89.97*2 matches 89.97+89.97 Did you try it? -- Regards Roger Govier wrote in message oups.com... Does no one know how to do this in a macro to save me the risk of making a mistake? There is a risk in doing these long excel formulas as I don't really understand where as if I have a macro that can be variable then that always works best for me , Please let me know, Thanks Andrea On 2 Jun, 10:23, "Roger Govier" wrote: Hi Andrea If I have understood you correctly, then enter in F2 =IF(E2="","",IF(SUMPRODUCT(($C$2:$C$9=C2)* ($B$2:$B$9=B2)*$E$2:$E$9)=0,"Matched","")) This will produce a Matched pair for the first 2 lines, but not for the last 2 as their sign is the same. Shouldn't one of them be negative? If you want a match regardless of sign, then =IF(E2="","",IF(OR(SUMPRODUCT(($C$4:$C$9=C2)* ($B$4:$B$9=B2)*$E$4:$E$9)=0, SUMPRODUCT(($C$4:$C$9=C2)* ($B$4:$B$9=B2)*$E$4:$E$9)=2*E2),"Matched","")) Copy down column F Apply DataFilterAutofilter and used the dropdown on column F to select Matched. Mark the filtered rows by selecting their row numbers, Copypaste to new sheet. Mark the filtered rows again on Source sheet, Delete. -- Regards Roger Govier wrote in message ups.com... Hello I have a spreadsheet which I have to reconcile between our customer orders in the system and our bank account. I dont know if this requires a complicated if statement but here is the criteria of what I need, 1. I have a list of transactions from our system and the bank account in the same sheet. Column A identifies between the system and the bank accounting using the characters SYS and BACC. Column B contains the type of order. Column C contains the order number which should also match the bank account. Column E contains the amount from the bank account and the amount from the order. IF the following criteria are met then do the below. 1. Column B match for both rows then column E matches for both rows then column B. Column A rows much have only one row that matches so the criteria must be one for sys and one for bacc. THe above criteria is the only way I think we can match the entries off completely. One the criteria has been met I want both rows copied to a new sheet called "Matched" so the data sheet just leaves entries which have not been matched up. Here is some sample data, ColA ColB ColC ColD ColE SYS S A12345LP DD 100.10 BACC S A12345LP DD -100.10 SYS D A12345LP DD 99.99 BACC D BA12345LP DD 99.99 BACC E 90222EAP DD 120.00 SYS E 444555BAP DD 12.00 SYS S B12345AA DD 89.97 BACC S B12345AA DD 89.97 If the criteria I set is applied then I would expect to see copied into the new worksheet "Matched" the below transactions which have been matched off. There must be an entry for both SYS and BACC regardless as to whether the amounts are positive or negative as long as they match to the penny. ColA ColB ColC ColD ColE SYS S A12345LP DD 100.10 BACC S A12345LP DD -100.10 SYS S B12345AA DD 89.97 BACC S B12345AA DD 89.97 Me even getting the thinking in my head correct was difficult.........I really hope someone can help with this, Thanks so much, Your all really amazing at these things and I do appreciate all your help. Andrea- Hide quoted text - - Show quoted text - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Complicated If Then / V Lookup / Match Statement... | Excel Worksheet Functions | |||
| complicated... not for the faint hearted | Excel Programming | |||
| Complicated lookup/match fuction | Excel Worksheet Functions | |||
| Complicated lookup/match fuction | Excel Worksheet Functions | |||
| Complicated lookup/match fuction | Excel Worksheet Functions | |||
![amorrison2006@googlemail.com[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Hybrid Mode
