Same customer moving from one platform to another causes overlapping month

[fischelr01]

Hello,

I have a list of all customers transactions. These customer's are queried as "legacy" and "evolution". Once I have all of their purchases searched, I back into their revenue with another index(Match(),Match() formula. The backing into works fine.

The problem is, on the month that they switch, I'm duplicating revenue because they technically have searches on both platforms and my index(Match()Match()) is giving me revenue on both my legacy and evolution lines.

My initial thought was to recreate another table that says if these customers match, and if there is a value on both searches lines, then only take the top one. I can't seem to pen out a formula to make this happen.

Any ideas or input is greatly appreciated. I've attached an example of what I'm running into.

Thanks!

[Richard Buttrey]

I can't tell from your workbook what you are expecting. Neither are there any formulae.

Please upload a workbook and show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.

[fischelr01]

Thanks for the feedback, updated workbook is attached. Please note there is a "current" and "ideal" scenario on each tab, except revenue.

Usage:
Currently, when I pull legacy and evolution usage data for the same customer, there is an over-lap (the month where they moved from the legacy product to the evolution product). The example is November.

Ideally, I'd like to delete the Evolution usage (cell M4) when there is an overlap.

All Revenue:
This is simply the revenue pull I use to back into the following 2 tabs.

Legacy Revenue
When there is usage in the correlating month, the Index(Match(),Match()) formula then searches in All Revenue to return Customer A's legacy revenue. For Legacy revenue, no change to this tab.

Evolution Revenue

When there is usage in the correlating month, the Index(Match(),Match()) formula then searches in All Revenue to return Customer A's evolution revenue. Because there won't be a Usage value, this should return as a blank.


I hope this clarifies. Thanks for the help!

[JeteMc]

It would seem that on the 'Evolution Revenue' sheet you could use a formula (starting with cell C3) similar to:

Please Login or Register  to view this content.

Let us know if you have any questions.