rodynetwork
asked on
need a formula
I need help with a formula in excel. Below is a paste of 5 columns I want to compare. 1st column invoice number, 2nd column date, 3rd column invoice amount, 4th column client payment, 5th column hotel payment. Notice invoice 22819 is in balance, invoice 22820 has no cient payment and invoice, 22821 has no hotel payment and invoice 22823 has neither client or hotel payment. Can you share a script or formula that would highlight each instance where there an invoice is missing a matching payment in column 4 and/or column 5? One more wrinkle, sometimes the payment is several partial payments totalling the amount. 22830 is an example of column 5 having several payments that sum to the invoice amount.
22819 5/22/2010 1200.22
22819 5/22/2010 1200.22
22819 5/22/2010 1200.22
22819
22819 5/22/2010
22820 5/22/2010 284.38
22820 5/22/2010 284.38
22820 5/22/2010
22820
22821 5/22/2010 687.96
22821 5/22/2010 687.96
22821
22822 5/22/2010 137.99
22822 5/22/2010 137.99
22822 5/22/2010 137.99
22822
22822 5/22/2010
22823 5/23/2010 170.64
22823
22830 5/23/2010 1398.75
22830 5/23/2010 1398.75
22830 5/23/2010 373.00
22830 5/23/2010 466.25
22830 5/23/2010 559.50
22830
22830 5/23/2010
22819 5/22/2010 1200.22
22819 5/22/2010 1200.22
22819 5/22/2010 1200.22
22819
22819 5/22/2010
22820 5/22/2010 284.38
22820 5/22/2010 284.38
22820 5/22/2010
22820
22821 5/22/2010 687.96
22821 5/22/2010 687.96
22821
22822 5/22/2010 137.99
22822 5/22/2010 137.99
22822 5/22/2010 137.99
22822
22822 5/22/2010
22823 5/23/2010 170.64
22823
22830 5/23/2010 1398.75
22830 5/23/2010 1398.75
22830 5/23/2010 373.00
22830 5/23/2010 466.25
22830 5/23/2010 559.50
22830
22830 5/23/2010
In the attached file I took a different approach:
1) I created a PivotTable to compare the payments for each invoice
<Note to Brad: You and I IM'ed a while back about when to use Calculated Fields/Items in PTs. The PT I created uses two Calc Fields, the 'Variance' fields...>
2) I used Conditional Formatting on the PT to highlight where the variances are
I also added a bit of frippery to make the PT and its source range automatically updatabale, using the approach I describe here: https://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/A_3172-How-to-Automatically-Update-Your-PivotTables-and-PivotCharts.html
Patrick
Q-26499531.xls
1) I created a PivotTable to compare the payments for each invoice
<Note to Brad: You and I IM'ed a while back about when to use Calculated Fields/Items in PTs. The PT I created uses two Calc Fields, the 'Variance' fields...>
2) I used Conditional Formatting on the PT to highlight where the variances are
I also added a bit of frippery to make the PT and its source range automatically updatabale, using the approach I describe here: https://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/A_3172-How-to-Automatically-Update-Your-PivotTables-and-PivotCharts.html
Patrick
Q-26499531.xls
A very elegant solution would be through a pivot table. See attached file for your question. It can do what u want and more! See 2nd sheet. Few columns have been added to facilitate the pivot table.
... Enjoy!
Book10.xlsm
... Enjoy!
Book10.xlsm
:)
ASKER
All-thanks. Wow.
Sorry, I should have asked to idiot proof your reply. How do I go about executing the formula? I am extreme novice at excel. Can give instruction on how to implement your formula? Thanks!
Sorry, I should have asked to idiot proof your reply. How do I go about executing the formula? I am extreme novice at excel. Can give instruction on how to implement your formula? Thanks!
rodynetwork,Download the sample file I provided and have a look :)BTW, I noticed that Brad and I took different approaches in our Conditional Formatting. Brad is basically checking to see if the sum of hotel payments is equal to the sum of client payments.In mine, I checked to see whether the sum of client payments equaled the invoice amount, and whether the sum of hotel payments equaled the invoice amount. For example, if the invoice amount were 5000, and you have a client payment for 100 and a hotel payment for 100, Brad's approach will say you are in balance, whereas mine will say you are not.Please clarify which approach was what you intended.Patrick
rodynetwork: Of course Patrick beat me to the pivot solution.
I looked at it in a different way... base being the Invoice Amount.
Hence, Client Balance = Invoice Amount - Client Amount
Hotel Balance = Invoice Amount - Hotel Amount
I looked at it in a different way... base being the Invoice Amount.
Hence, Client Balance = Invoice Amount - Client Amount
Hotel Balance = Invoice Amount - Hotel Amount
ASKER
Hi Patrick (matthewspatrick). Each of these is helpful, but I found yours is more thorough. Amazing the answers you guys give. Geniuses. How do I implement or execute yours on my sheet/datat?
Patrick,
I compared client payment to invoice, and hotel to invoice. You had a different approach, but are making the same comparison.
Brad
I compared client payment to invoice, and hotel to invoice. You had a different approach, but are making the same comparison.
Brad
ASKER
Hey Brad, I liked yours alot and would like to use it. I don't know how to implement it. What are the steps I take to execute?
I assumed that column A is the invoice number, column C is the invoice amount, column D is the client payment, and column E is the hotel payment.
The SUMIF function adds up values from rows that satisfy a criterion. For example, you might want to sum up the amounts invoiced for a given invoice number. This is returned by:
=SUMIF($A:$A,$A1,$C:$C)
If you want to compare that value to the amount paid by the client, that would be given by:
=SUMIF($A:$A,$A1,D:D)
The not equal operator <> tells Excel to compare to numbers and return TRUE if they are not equal, and FALSE if they are. We need the TRUE result for Conditional Formatting. This comparison may be written as:
=SUMIF($A:$A,$A1,$C:$C)<>S
To create Conditional Formatting:
1) Select all the cells in columns C and D that you want to check. I assume that row 1 is the top of that selection.
2) Open the Conditional Formatting menu item. In Excel 2003 and earlier, it is found on the Format menu. In Excel 2007 and later, it is found in the Home tab in the Styles group.
3) You want to choose a "Formula is" criteria for the Conditional formatting
4) Paste the formula in the field provided. Make sure that you change the reference to $A$1 to match the top row in your selection. Don't add $ in front of D:D.
5) Click the Format button and go to the Fill tab. Choose your highlight color.
The SUMIF function adds up values from rows that satisfy a criterion. For example, you might want to sum up the amounts invoiced for a given invoice number. This is returned by:
=SUMIF($A:$A,$A1,$C:$C)
If you want to compare that value to the amount paid by the client, that would be given by:
=SUMIF($A:$A,$A1,D:D)
The not equal operator <> tells Excel to compare to numbers and return TRUE if they are not equal, and FALSE if they are. We need the TRUE result for Conditional Formatting. This comparison may be written as:
=SUMIF($A:$A,$A1,$C:$C)<>S
To create Conditional Formatting:
1) Select all the cells in columns C and D that you want to check. I assume that row 1 is the top of that selection.
2) Open the Conditional Formatting menu item. In Excel 2003 and earlier, it is found on the Format menu. In Excel 2007 and later, it is found in the Home tab in the Styles group.
3) You want to choose a "Formula is" criteria for the Conditional formatting
4) Paste the formula in the field provided. Make sure that you change the reference to $A$1 to match the top row in your selection. Don't add $ in front of D:D.
5) Click the Format button and go to the Fill tab. Choose your highlight color.
Brad,My apologies. I should always remind myself: whenever I think Brad made a mistake, I'd better check it a few more times, because far more likely I'm the guy who is mistaken :)rodynetwork,Which Excel version are you using? The work steps are a tad different in Excel 2007/2010 than previous versions.Patrick
ASKER
Thanks guys. Using excel 2007. I would like to run this on the whole document. I was reading Brads instructions and got a little lost. I went to Home/Styles/Conditional formatting and didn't see a "Formula is". There is a "highlight cells rules" with several options and a "top/bottom rules" with several options. Also, in my initial example, to simplify explanation, I left out the first two rows. In my sheet the rows I am actually interested in are F and G. To use Brads as the example, the C and D are actlually F and G in my sheet. Sorry again for the need for idiot proof instruction. Thanks for the help.
ASKER
Sorry, to clarify what I mean by "whole document", I want to run it on F and G from the first row to the last row.
rodynetwork,I did the following. You can follow along on your own, adjusting columns as needed:1) In A1:E1, put these headers:Invoice Date Inv Amt Client Pmt Hotel Pmt2) In A2:E27, I pasted in the data from your question3) I then selected A1:E27, and on the Insert tab of the Ribbon I clicked Table in the Tables group to create a Table4) On the Insert tab of the Ribbon I clicked PivotTable in the Tables group. The Create PivotTable form should pick up the table you just created as the source5) In the PivotTable, drag Invoice into the row labels, and Invoice Amt into the values. Change the aggregation to sum if Excel defaulted it to count6) On the Ribbon, in the PivotTable Tools/Options tab, click Tools / Formulas / Calculated Field7) On the Insert Calculated Field form, enter Client Variance for the name, and for the formula use:='Client Pmt'- 'Inv Amt'then click Add. After doing that, put Hotel Variance in the Name field, this in the formula field:='Hotel Pmt'- 'Inv Amt'then click Add, and then Close8) On the PivotTable's worksheet, select columns A:D, and on the Home tab of the Ribbon, click Styles / Conditional Formatting / Manage Rules. In the Rules Manager, click New Rule, and for rule type choose 'Use formula...". In the formula field, use:=AND(ISNUMBER($A1),OR(
ASKER
Hi Patrick,
Thanks for the help. I hit a snag somewhere between steps 4 & 5. During step 3, with those five columns selected through out, starting from A1 to E1 and going down a few thousand rows, I clicked Tables and the cells are now shaded blue. In step 4 I cliicked Pivot Tables and the Create Pivot Table window came up and shows:
Select a table or range:
Table/Range: Table 1
or
Use an external data source
When I click "ok", it says "data source reference is not valid"
Thanks for the help. I hit a snag somewhere between steps 4 & 5. During step 3, with those five columns selected through out, starting from A1 to E1 and going down a few thousand rows, I clicked Tables and the cells are now shaded blue. In step 4 I cliicked Pivot Tables and the Create Pivot Table window came up and shows:
Select a table or range:
Table/Range: Table 1
or
Use an external data source
When I click "ok", it says "data source reference is not valid"
Perhaps you should post a sample file
In Excel 2007, the steps are:
1) Select all the cells in columns F and G that you want to check. I assume that row 1 is the top of that selection. 2) Open the Conditional Formatting menu item. In Excel 2007 and later, it is found in the Home tab in the Styles group.
3) Choose the option for "New Rule..."
4) Choose the option to "Use a formula to determine which cells to format"
5) Paste the formula in the "Format cells when this formula is true" field . Make sure that you change the reference to $A$1 to match the top row in your selection. Don't add $ in front of F:F.
=SUMIF($A:$A,$A1,$C:$C)<>S
6) Click the "Format..." button and go to the Fill tab. Choose your highlight color.
7) Click OK
The relative parts of the formula will adapt to the other cells in the selection.
Brad
1) Select all the cells in columns F and G that you want to check. I assume that row 1 is the top of that selection. 2) Open the Conditional Formatting menu item. In Excel 2007 and later, it is found in the Home tab in the Styles group.
3) Choose the option for "New Rule..."
4) Choose the option to "Use a formula to determine which cells to format"
5) Paste the formula in the "Format cells when this formula is true" field . Make sure that you change the reference to $A$1 to match the top row in your selection. Don't add $ in front of F:F.
=SUMIF($A:$A,$A1,$C:$C)<>S
6) Click the "Format..." button and go to the Fill tab. Choose your highlight color.
7) Click OK
The relative parts of the formula will adapt to the other cells in the selection.
Brad
ASKER
Hi Brad,
That worked. Thank you. Can you help with something else? Now that I have done this, I can't sort or filter. The sort and filter buttons in the data tab are greyed out. Also, in my sheet the fields are C the invoice number and E amount, F client pay in and G hotel pay out. The way this is working at the moment is that every field that is missing a F client payment is yellow. I need F to not be yellow if G is also missing a payment. It is normal for both F and G to be blank. What I need to view is all instances where F is less than E AND G has something amount in it. It is the instances where we have put money out to hotels but the client hasn't paid us. Those are the only instances I am trying to isolate and view. I would like to only have the yellow show on only the F client payments where there is a G hotel payment. It doesn't matter if G is greater or smaller than E invoice amount. I only want to see F client payments where F is less than E and where G is same or greater or less than E, but not blank. It is Ok for F and G to both be blank, as it is normal to to be waiting for both the cleint pay and the hotel pay. What I want to isolate and view are all instances of F having less than E where this is something in G, no matter if G is equal to E or greater or less than E. Sorry if I am being too wordey. Thanks for this fantastic help.
That worked. Thank you. Can you help with something else? Now that I have done this, I can't sort or filter. The sort and filter buttons in the data tab are greyed out. Also, in my sheet the fields are C the invoice number and E amount, F client pay in and G hotel pay out. The way this is working at the moment is that every field that is missing a F client payment is yellow. I need F to not be yellow if G is also missing a payment. It is normal for both F and G to be blank. What I need to view is all instances where F is less than E AND G has something amount in it. It is the instances where we have put money out to hotels but the client hasn't paid us. Those are the only instances I am trying to isolate and view. I would like to only have the yellow show on only the F client payments where there is a G hotel payment. It doesn't matter if G is greater or smaller than E invoice amount. I only want to see F client payments where F is less than E and where G is same or greater or less than E, but not blank. It is Ok for F and G to both be blank, as it is normal to to be waiting for both the cleint pay and the hotel pay. What I want to isolate and view are all instances of F having less than E where this is something in G, no matter if G is equal to E or greater or less than E. Sorry if I am being too wordey. Thanks for this fantastic help.
ASKER
I noticed that you have a bad header name in C1 of your sample file. I renamed it to just Invoice, and then recreated all of my work steps.
1) Create a Table for your data
2) Make a PivotTable based on that new table
3) Add the calculated fields to that PT
4) Add the CF to highlight rows in the PT with variances
Q-26499531.xlsm
1) Create a Table for your data
2) Make a PivotTable based on that new table
3) Add the calculated fields to that PT
4) Add the CF to highlight rows in the PT with variances
Q-26499531.xlsm
Use this version, it has the "auto update" macro from my article https://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/A_3172-How-to-Automatically-Update-Your-PivotTables-and-PivotCharts.html
Q-26499531.xlsm
Q-26499531.xlsm
ASKER
Hi Patrick,
I realy appreciate the help and I like the robust solution you have, the way it boils it down to only the fields I want to view. I apologize for my lack of proficiency in excel. I think the instructions you are giving may be higher level than my comprehension, as I keep trying to do what you are saying and keep getting hung up in place or another. What i've tried so far is to follow the instructions you gave earlier for implementing in my sheet and I have also tried just copying/pasting into the sheet you just provided. That isn't working, as the pasted info is not appearing in the pivot sheet/tab. As for implementing the instructions you had previously given, trying to implement on one of my sheets, I just keep getting hung up. Do you have any suggestions for how to approach implementing? I think the problem is that I am just too unaware or unskilled at excel?
I realy appreciate the help and I like the robust solution you have, the way it boils it down to only the fields I want to view. I apologize for my lack of proficiency in excel. I think the instructions you are giving may be higher level than my comprehension, as I keep trying to do what you are saying and keep getting hung up in place or another. What i've tried so far is to follow the instructions you gave earlier for implementing in my sheet and I have also tried just copying/pasting into the sheet you just provided. That isn't working, as the pasted info is not appearing in the pivot sheet/tab. As for implementing the instructions you had previously given, trying to implement on one of my sheets, I just keep getting hung up. Do you have any suggestions for how to approach implementing? I think the problem is that I am just too unaware or unskilled at excel?
rodynetwork,I am at a loss in terms of how else to explain what I am doing. I have dropped a line with some other Experts, hoping that maybe one of them may add a little clarity.Patrick
ASKER
Thanks Patrick. I am sorry to frustrate you. thanks for helping
ASKER
If it helps, I will be happy to pay for a solution. I realize it can be tedious and frustrating to deal with someone who isn't comprehending. I am a buinsess owner and have no problem paying for this. The excel sheet I am looking at is generated from with a system I had built. I click a button and that sheet comes up. Ideally, I would have a programmer take your formula and set up my system to generate the excel sheet with your formula applied. Or, I can simply implement it myself, if I can get up to speed on how to do that. Either way, I am happy to compensate you. Sorry for the frustration.
rodynetwork,If I was gruff in my tone, please overlook it, as that was not my intention at all. To the extent that I am getting frustrated, it is mostly at my apparent inability to communicate what I am doing :)Taking a step back: do your data always take this format, perhaps with a varying number of rows, but the columns always the same? If so, in a few hours I can write a macro to script out what I did.As far as payment goes, officially EE does not allow offers of or solicitations for paid work in questions, but if you go to my profile you will see a 'Hire Me' button, which allow us to make arrangements privately :)Patrick
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
If I may suggest an alternate solution, trying to do this in Excel is counter productive. The task is really transaction based and trying to compile this info in Excel, while doable, is a pain the butt to write and maintain. Instead, I suggest using an Access database. There are several free templates already that are probably right in line with what you want to do, or, as a business owner, you can hire someone to write something specific to your needs. You can get something much more robust with custom reports and the whole lot. I don't know what the protocol is here for offering professional services, but I have an IT company that can definitely handle this.
Thanks. Understood.
=SUMIF($A:$A,$A1,$C:$C)<>S
Invoice-matchingQ26499531.xlsx