|
Multiple column comparisons
hi. I have a spreadsheet with names, ID#s for those names, and direct
deposit info. For each person, they have multiple rows. One column indicates A or B, which tells if the amount going into an account is a part of the whole, or the balance. Another column indicates the priority of said amount. In order for payroll to run correctly, all "B" amounts MUST have a greater priority number than all "A" accounts, as when it runs, if it first sees a B amount, it just takes the whole balance, sweeps it into the account, and doesn't leave anything for any of the "A"s I'm at a loss for how to return the values of people with B balances with a priority lower than their A balances. Any ideas in Excel or Access? |
Multiple column comparisons
Gary - PeopleSoft, actually.
Thanks for the advice, but I'm actually auditing PPS to show errors - just sorting won't fix the problem, as I'm trying to find the people who have improper coding so we can go back and change their priority numbers for them.... If I just use a sort, I have to manually look through 10,000 records and visually find the ones where the B values are less than the As..... Any advice? "Gary L Brown" wrote: You using SOLOMON? Anyway, sort by ID # (Ascending) and then Priority (Ascending) so that the Employee information stays contiguous and yet the 'A' will always precede the 'B'. This can easily be done using DataSort in Excel or using a query in Access. Good Luck form a Payroll specialist for 35 years (God! did I really say 35 years!!!???) :O -- Gary Brown If this post was helpful, please click the ''Yes'' button next to ''Was this Post Helpfull to you?''. "J. Catz." wrote: hi. I have a spreadsheet with names, ID#s for those names, and direct deposit info. For each person, they have multiple rows. One column indicates A or B, which tells if the amount going into an account is a part of the whole, or the balance. Another column indicates the priority of said amount. In order for payroll to run correctly, all "B" amounts MUST have a greater priority number than all "A" accounts, as when it runs, if it first sees a B amount, it just takes the whole balance, sweeps it into the account, and doesn't leave anything for any of the "A"s I'm at a loss for how to return the values of people with B balances with a priority lower than their A balances. Any ideas in Excel or Access? |
Multiple column comparisons
Always have advice :O)
Sort by ID # (Ascending) and then Priority (Ascending) so that the Employee information stays contiguous and yet the 'A' will always precede the 'B'. This can easily be done using DataSort. Assumptions for illustration purposes: A B C D 1 id# type priority test 2 54 A 1 3 54 B 2 4 2911 A 2 Review 5 2911 B 1 Review 6 4139 A 2 Review 7 4139 B 1 Review 8 4298 A 1 9 4298 B 2 10 4442 A 2 Review 11 4442 B 1 Review 12 5075 A 1 13 5075 B 2 Create the following formula in Column D, copy it down your list, then filter on Column D. =IF(OR(AND(A2=A1,C2<=C1),AND(A2=A3,C2=C3)),"Revie w","") After creating the formulas and letting them calculate, you might want to PasteSpecialValue Column D to save time and memory. Especially if you intend to re-sort the data later! HTH, -- Gary Brown If this post was helpful, please click the ''Yes'' button next to ''Was this Post Helpfull to you?''. "J. Catz." wrote: Gary - PeopleSoft, actually. Thanks for the advice, but I'm actually auditing PPS to show errors - just sorting won't fix the problem, as I'm trying to find the people who have improper coding so we can go back and change their priority numbers for them.... If I just use a sort, I have to manually look through 10,000 records and visually find the ones where the B values are less than the As..... Any advice? "Gary L Brown" wrote: You using SOLOMON? Anyway, sort by ID # (Ascending) and then Priority (Ascending) so that the Employee information stays contiguous and yet the 'A' will always precede the 'B'. This can easily be done using DataSort in Excel or using a query in Access. Good Luck form a Payroll specialist for 35 years (God! did I really say 35 years!!!???) :O -- Gary Brown If this post was helpful, please click the ''Yes'' button next to ''Was this Post Helpfull to you?''. "J. Catz." wrote: hi. I have a spreadsheet with names, ID#s for those names, and direct deposit info. For each person, they have multiple rows. One column indicates A or B, which tells if the amount going into an account is a part of the whole, or the balance. Another column indicates the priority of said amount. In order for payroll to run correctly, all "B" amounts MUST have a greater priority number than all "A" accounts, as when it runs, if it first sees a B amount, it just takes the whole balance, sweeps it into the account, and doesn't leave anything for any of the "A"s I'm at a loss for how to return the values of people with B balances with a priority lower than their A balances. Any ideas in Excel or Access? |
Multiple column comparisons
Ok, great idea, BUT (sorry), people can have more than one A value (more than
one account they're sending a percentage or amount into each pay), or no A value at all....so that makes it much more complicated. Apologies, I should have been more clear in my original post; any other ideas? It looks like this... You'll note all these people are ok, but again, I have 10,000 total rows. Fun! ID Priority Type 271258 1.00 A 271258 999.00 B 271266 999.00 B 271267 0.00 A 271267 999.00 B 271270 999.00 B 271271 100.00 A 271271 999.00 B 271272 999.00 B 271273 1.00 A 271273 999.00 B 271310 100.00 A 271310 999.00 B 271320 999.00 B 271321 0.00 A 271321 3.00 A 271321 4.00 A 271321 999.00 B 271323 999.00 B 271326 999.00 B "Gary L Brown" wrote: Always have advice :O) Sort by ID # (Ascending) and then Priority (Ascending) so that the Employee information stays contiguous and yet the 'A' will always precede the 'B'. This can easily be done using DataSort. Assumptions for illustration purposes: A B C D 1 id# type priority test 2 54 A 1 3 54 B 2 4 2911 A 2 Review 5 2911 B 1 Review 6 4139 A 2 Review 7 4139 B 1 Review 8 4298 A 1 9 4298 B 2 10 4442 A 2 Review 11 4442 B 1 Review 12 5075 A 1 13 5075 B 2 Create the following formula in Column D, copy it down your list, then filter on Column D. =IF(OR(AND(A2=A1,C2<=C1),AND(A2=A3,C2=C3)),"Revie w","") After creating the formulas and letting them calculate, you might want to PasteSpecialValue Column D to save time and memory. Especially if you intend to re-sort the data later! HTH, -- Gary Brown If this post was helpful, please click the ''Yes'' button next to ''Was this Post Helpfull to you?''. "J. Catz." wrote: Gary - PeopleSoft, actually. Thanks for the advice, but I'm actually auditing PPS to show errors - just sorting won't fix the problem, as I'm trying to find the people who have improper coding so we can go back and change their priority numbers for them.... If I just use a sort, I have to manually look through 10,000 records and visually find the ones where the B values are less than the As..... Any advice? "Gary L Brown" wrote: You using SOLOMON? Anyway, sort by ID # (Ascending) and then Priority (Ascending) so that the Employee information stays contiguous and yet the 'A' will always precede the 'B'. This can easily be done using DataSort in Excel or using a query in Access. Good Luck form a Payroll specialist for 35 years (God! did I really say 35 years!!!???) :O -- Gary Brown If this post was helpful, please click the ''Yes'' button next to ''Was this Post Helpfull to you?''. "J. Catz." wrote: hi. I have a spreadsheet with names, ID#s for those names, and direct deposit info. For each person, they have multiple rows. One column indicates A or B, which tells if the amount going into an account is a part of the whole, or the balance. Another column indicates the priority of said amount. In order for payroll to run correctly, all "B" amounts MUST have a greater priority number than all "A" accounts, as when it runs, if it first sees a B amount, it just takes the whole balance, sweeps it into the account, and doesn't leave anything for any of the "A"s I'm at a loss for how to return the values of people with B balances with a priority lower than their A balances. Any ideas in Excel or Access? |
Multiple column comparisons
A B C D
1 ID Priority Type Test 2 271258 1.00 A 3 271258 999.00 B 4 271266 999.00 B 5 271267 0.00 A 6 271267 6.00 A Review 7 271267 5.00 B Review 8 271267 999.00 B The formula... =IF(OR(AND(A2=A1,B2<B1),AND(A2=A3,B2B3)),"Review" ,"") Will take care of multiples as shown in ID 27167. 1) Sort on ID then Type then Priority. 2) Apply this formula 3) Set filter 4) Filter on D HTH, -- Gary Brown If this post was helpful, please click the ''Yes'' button next to ''Was this Post Helpfull to you?''. "J. Catz." wrote: Ok, great idea, BUT (sorry), people can have more than one A value (more than one account they're sending a percentage or amount into each pay), or no A value at all....so that makes it much more complicated. Apologies, I should have been more clear in my original post; any other ideas? It looks like this... You'll note all these people are ok, but again, I have 10,000 total rows. Fun! ID Priority Type 271258 1.00 A 271258 999.00 B 271266 999.00 B 271267 0.00 A 271267 999.00 B 271270 999.00 B 271271 100.00 A 271271 999.00 B 271272 999.00 B 271273 1.00 A 271273 999.00 B 271310 100.00 A 271310 999.00 B 271320 999.00 B 271321 0.00 A 271321 3.00 A 271321 4.00 A 271321 999.00 B 271323 999.00 B 271326 999.00 B "Gary L Brown" wrote: Always have advice :O) Sort by ID # (Ascending) and then Priority (Ascending) so that the Employee information stays contiguous and yet the 'A' will always precede the 'B'. This can easily be done using DataSort. Assumptions for illustration purposes: A B C D 1 id# type priority test 2 54 A 1 3 54 B 2 4 2911 A 2 Review 5 2911 B 1 Review 6 4139 A 2 Review 7 4139 B 1 Review 8 4298 A 1 9 4298 B 2 10 4442 A 2 Review 11 4442 B 1 Review 12 5075 A 1 13 5075 B 2 Create the following formula in Column D, copy it down your list, then filter on Column D. =IF(OR(AND(A2=A1,C2<=C1),AND(A2=A3,C2=C3)),"Revie w","") After creating the formulas and letting them calculate, you might want to PasteSpecialValue Column D to save time and memory. Especially if you intend to re-sort the data later! HTH, -- Gary Brown If this post was helpful, please click the ''Yes'' button next to ''Was this Post Helpfull to you?''. "J. Catz." wrote: Gary - PeopleSoft, actually. Thanks for the advice, but I'm actually auditing PPS to show errors - just sorting won't fix the problem, as I'm trying to find the people who have improper coding so we can go back and change their priority numbers for them.... If I just use a sort, I have to manually look through 10,000 records and visually find the ones where the B values are less than the As..... Any advice? "Gary L Brown" wrote: You using SOLOMON? Anyway, sort by ID # (Ascending) and then Priority (Ascending) so that the Employee information stays contiguous and yet the 'A' will always precede the 'B'. This can easily be done using DataSort in Excel or using a query in Access. Good Luck form a Payroll specialist for 35 years (God! did I really say 35 years!!!???) :O -- Gary Brown If this post was helpful, please click the ''Yes'' button next to ''Was this Post Helpfull to you?''. "J. Catz." wrote: hi. I have a spreadsheet with names, ID#s for those names, and direct deposit info. For each person, they have multiple rows. One column indicates A or B, which tells if the amount going into an account is a part of the whole, or the balance. Another column indicates the priority of said amount. In order for payroll to run correctly, all "B" amounts MUST have a greater priority number than all "A" accounts, as when it runs, if it first sees a B amount, it just takes the whole balance, sweeps it into the account, and doesn't leave anything for any of the "A"s I'm at a loss for how to return the values of people with B balances with a priority lower than their A balances. Any ideas in Excel or Access? |
Multiple column comparisons
Sorry, Gary, thanks. I didn't think it through that it would comare two at a
time; I didn't need to group by ID number. This worked great. Thanks! "Gary L Brown" wrote: A B C D 1 ID Priority Type Test 2 271258 1.00 A 3 271258 999.00 B 4 271266 999.00 B 5 271267 0.00 A 6 271267 6.00 A Review 7 271267 5.00 B Review 8 271267 999.00 B The formula... =IF(OR(AND(A2=A1,B2<B1),AND(A2=A3,B2B3)),"Review" ,"") Will take care of multiples as shown in ID 27167. 1) Sort on ID then Type then Priority. 2) Apply this formula 3) Set filter 4) Filter on D HTH, -- Gary Brown If this post was helpful, please click the ''Yes'' button next to ''Was this Post Helpfull to you?''. "J. Catz." wrote: Ok, great idea, BUT (sorry), people can have more than one A value (more than one account they're sending a percentage or amount into each pay), or no A value at all....so that makes it much more complicated. Apologies, I should have been more clear in my original post; any other ideas? It looks like this... You'll note all these people are ok, but again, I have 10,000 total rows. Fun! ID Priority Type 271258 1.00 A 271258 999.00 B 271266 999.00 B 271267 0.00 A 271267 999.00 B 271270 999.00 B 271271 100.00 A 271271 999.00 B 271272 999.00 B 271273 1.00 A 271273 999.00 B 271310 100.00 A 271310 999.00 B 271320 999.00 B 271321 0.00 A 271321 3.00 A 271321 4.00 A 271321 999.00 B 271323 999.00 B 271326 999.00 B "Gary L Brown" wrote: Always have advice :O) Sort by ID # (Ascending) and then Priority (Ascending) so that the Employee information stays contiguous and yet the 'A' will always precede the 'B'. This can easily be done using DataSort. Assumptions for illustration purposes: A B C D 1 id# type priority test 2 54 A 1 3 54 B 2 4 2911 A 2 Review 5 2911 B 1 Review 6 4139 A 2 Review 7 4139 B 1 Review 8 4298 A 1 9 4298 B 2 10 4442 A 2 Review 11 4442 B 1 Review 12 5075 A 1 13 5075 B 2 Create the following formula in Column D, copy it down your list, then filter on Column D. =IF(OR(AND(A2=A1,C2<=C1),AND(A2=A3,C2=C3)),"Revie w","") After creating the formulas and letting them calculate, you might want to PasteSpecialValue Column D to save time and memory. Especially if you intend to re-sort the data later! HTH, -- Gary Brown If this post was helpful, please click the ''Yes'' button next to ''Was this Post Helpfull to you?''. "J. Catz." wrote: Gary - PeopleSoft, actually. Thanks for the advice, but I'm actually auditing PPS to show errors - just sorting won't fix the problem, as I'm trying to find the people who have improper coding so we can go back and change their priority numbers for them.... If I just use a sort, I have to manually look through 10,000 records and visually find the ones where the B values are less than the As..... Any advice? "Gary L Brown" wrote: You using SOLOMON? Anyway, sort by ID # (Ascending) and then Priority (Ascending) so that the Employee information stays contiguous and yet the 'A' will always precede the 'B'. This can easily be done using DataSort in Excel or using a query in Access. Good Luck form a Payroll specialist for 35 years (God! did I really say 35 years!!!???) :O -- Gary Brown If this post was helpful, please click the ''Yes'' button next to ''Was this Post Helpfull to you?''. "J. Catz." wrote: hi. I have a spreadsheet with names, ID#s for those names, and direct deposit info. For each person, they have multiple rows. One column indicates A or B, which tells if the amount going into an account is a part of the whole, or the balance. Another column indicates the priority of said amount. In order for payroll to run correctly, all "B" amounts MUST have a greater priority number than all "A" accounts, as when it runs, if it first sees a B amount, it just takes the whole balance, sweeps it into the account, and doesn't leave anything for any of the "A"s I'm at a loss for how to return the values of people with B balances with a priority lower than their A balances. Any ideas in Excel or Access? |
| All times are GMT +1. The time now is 01:11 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com