Hi
Hope someone can advise me if there is a better way to do different worksheet comparisons instead of my current convoluted method. Instead of comparing 2 different worksheets each time, is there a more efficient way to compare all 7 worksheets at one go to extract what I need? Thanks for the help.
Below is what I am currently doing.
I have 7 months (Dec to Jun) of customer data (on different worksheets) which I need to do month on month comparison to sieve out customers whose payment conduct have deteriorated. The info in each worksheet contains month, customer name, payment conduct (Prompt or Late) and amount owing etc.
1) I begin by combining Dec and Jan data into a single worksheet and sort them by customer name followed by month. Then I insert a new column “Prompt to Late” where I input a nested IF function formula to compare the Dec and Jan data of same borrower to flag out the change in payment conduct.
= IF (AND (Name in current cell =Name in previous cell, Payment Conduct in current cell <> Payment Conduct in previous cell, True, False).
If a borrower’s payment conduct for Dec differs from Jan, the formula will return True for the cell.
2) Next, I create another new column “>=1000” and input the following formula:
= IF amount owing >=1000, True.
3) Then I highlight both columns “Prompt to Late” and “>=1000” and select Conditional Formatting to sieve out cells which are True.
4) I scroll down and highlight in yellow the customer’s names for Dec and Jan rows if there is True in both columns for this customer.
5) I filter the highlighted names. [This list contains customers whose payment conduct have deteriorated from prompt to late as well customers whose payment conduct is late for both months]
6) As my purpose is to sieve out only those that show deterioration in payment conduct, I create another new column “Final Late”. The formula is applied to the highlighted accounts in (5) above:
= IF (AND (Month=later month*, Payment Conduct <>Prompt, True, False))
* E.g. for Dec and Jan, later month refers to Jan. For Jan and Feb, later month refers to Feb.
7) Under column “Final Late”, I filter True to get the final list of customers with payment conduct that have deteriorated.
8) The above steps are repeated for Jan and Feb, Feb and Mar, Mar and Apr, Apr and May and May and June. The list of filtered customers in (7) for each comparison will then be combined together.
In addition to above, I need to extract a list of customers whose payment of conduct is late for both months of comparison and amount owing is >=1000 in later month. This is because customers with deterioration in payment conduct (Prompt to Late) and amount owing less than 1000 is not captured in the first listing.
9) After combining Dec and Jan data into a single worksheet, I sort them by customer name followed by month. Then I insert a new column “Late to Late” with the following formula:
= IF (AND (Name in current cell* = Name in previous cell, Amount owing in current cell >=1000, Amount owing <1000, True, False)).
* Current cell refers to data in later month. E.g. for Dec and Jan comparison, Jan row of data will be known as current cells.
10) Filter column “Late to “Late” by True. This list of customers is those that have late payment conduct for 2 months and amount owing of more than 1000 in the later month.
Hi and welcome to our forum!
You can automate this with Power Query. Alternatively, you can probably use some more formulas to make things a bit less reliant on Conditional Formatting, after all, Conditional Formatting is just performing another logical test, but you have to write those formulas each time, whereas with Power Query, you create the query once and then just refresh to update it with new data.
If you'd like to learn Power Query, please consider my Power Query course.
If you need help adding more formulas, please provide a SMALL sample Excel file that covers all scenarios and your desired results so we can see what you're working with.
Mynda
Thanks Mynda for your prompt reply. I have another report that uses Power Query created by my ex colleague. Problem is that every month, we will need to update the new file name in the source. And I notice that if the new file is not named in a particular format, errors will occur. In addition, trouble shooting is not that clear cut for Power Query unless you already have knowledge. Unlike the conventional excel way, you can retrace your steps to identify the problems. As the reporting will be handled by staff with basic excel knowledge, I think Power Query at this point is too high level for us. I will try to create a small sample excel file in the next few days. Thanks again.
Hi Mynda
I have created a small sample. Hopefully you can help. Thanks
Thanks for the file. What version of Excel do you have? If you have Microsoft 365 or Office 2021 then you have the new dynamic array functions, which can make this task easier.
Otherwise, I think learning what you need to do in Power Query is the best approach. I appreciate it's not obvious how you update the query for the new file, but it's not too difficult to learn the few steps that are required.
Mynda
Hi Mynda
It's Office standard 2019. Thanks
Thanks for clarifying. Power Query it is then.
1. Format the data on each sheet in an Excel table (CTRL+T). The tables will be named Table1, Table2...., Table7. Note: By leaving the table names generic, it should reduce the amount of editing you need to do to reuse this query in future.
2. Create a blank query
3. In the formula bar enter: =Excel.CurrentWorkbook()
4. Filter the Name column to only include names that begin with 'Table'. This is to ensure you don't double count your final query table when it's loaded to the file.
5. Click the Expand icon on the Content tab. Note: the Payment Conduct column has a space after each text string. I used Trim to remove them.
6. Transform tab > detect data type
7. Add conditional column that converts the payment conduct text to numeric equivalents of 1 for Prompt and 0 for Late.
8. Add conditional column for the >=1000 amount owing
9. Close & Load to table in Excel
10. Insert a PivotTable (see example in attached file). Note: the PivotTable Options have been set to show blanks as zeros which is required for the SUMPRODUCT part of the formula (mentioned in the next step) to work.
11. Insert IF formula to check if trend of conduct is prompt or improving over time.
Note: I stopped at this point as I wasn't sure whether the >=1000 criteria was only relevant for the last month AND if they had poor conduct. If not, how is it interpreted? e.g. if a person had poor conduct but didn't owe >=1000 at the time of the poor conduct and now their conduct is improved and they owe >=1000, vs a person who had poor conduct and still owed >=1000 in the latest period etc., etc. for each possible scenario.
Anyhow, I hope that gives you some alternate ideas of how to handle this analysis.
Mynda
Thanks Mynda for the suggested solution and attachments. I will go through this weekend and see if I can apply to my work. Thanks again.
