Data Comparison Query - Comparing two tabs

lemonap618

New Member
Joined
Oct 26, 2012
Messages
33
Dear Mr. Excel,
I am currently working on comparing data between two tabs in my spreadsheet - "Merged Original" and "Merged SRO." My objective is to determine if customers from the "Merged Original" tab renewed their product ID subscriptions with my business by examining the renewals listed in the "Merged SRO" tab, specifically at the Opportunity ID (SO Line) level.

To categorize a renewal, I'm considering whether the customer bought the same products or more, based on the measure of ACV. Initially, I thought of concatenating necessary fields and comparing the tabs this way. However, I'm curious if there might be a more efficient or better approach for this analysis.

I've highlighted the pertinent fields in red for your reference if you'd like to take a closer look.
VBA Code:
Thanks for the help!
 

Attachments

  • MergedSRO.png
    MergedSRO.png
    254.6 KB · Views: 16
  • MergedOriginal_2.png
    MergedOriginal_2.png
    179 KB · Views: 15

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
I do not quite understand how you want to compare both tables.
In your "MergedSRO" sheet do you want to compare the column F ("Opportunity ID (SO Line)") and the see if you have the same entry in "Merged Original". And if there is a match you want to compare the ACV numbers and see if the new one is greater than before?

Why are the other columns marked red? How does that relate to "specifically at the Opportunity ID (SO Line) level."
 
Upvote 0
I do not quite understand how you want to compare both tables.
In your "MergedSRO" sheet do you want to compare the column F ("Opportunity ID (SO Line)") and the see if you have the same entry in "Merged Original". And if there is a match you want to compare the ACV numbers and see if the new one is greater than before?

Why are the other columns marked red? How does that relate to "specifically at the Opportunity ID (SO Line) level."
Doh! Sorry I wrote this request late last night. I would like to compare the product, customer NBR, bundled quantity and ACV between the two spreadsheets. The opportunity ID will not work as a link between the two spreadsheets.
 
Upvote 0
Ok so you only want to see if there is a match on those 4 columns. And if there is return a text that says "Product Id subscription renewed" for example?
 
Upvote 0
How about this?:

Libro1
DEFGHI
1Cust Nbr (Site)Product IDBundled QuarACVRenewed Product ID
210006312VS20530210180.79yes
310011748NX93300112295.56yes
488484888PK7575789980.1 
Merged SRO
Cell Formulas
RangeFormula
I2:I4I2=LET( Original_CustNbr, 'Merged Original'!D2:D10000, Original_ProductID,'Merged Original'!F2:F10000, Original_BundledQuar, 'Merged Original'!G2:G10000, Original_ACV, 'Merged Original'!H2:H10000, IF(COUNTIFS(Original_CustNbr,D2,Original_ProductID,F2,Original_BundledQuar,G2,Original_ACV,H2),"yes","") )
 
Upvote 0

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top