Data Comparison Query - Comparing two tabs

[lemonap618]

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.

Thanks for the help!

 

[felixstraube]

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."

 

[lemonap618]

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.

 

[felixstraube]

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?

 

[lemonap618]

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?

Yes, that would be super helpful!

 

[felixstraube]

How about this?:

Libro1
	D	E	F	G	H	I
1	Cust Nbr (Site)		Product ID	Bundled Quar	ACV	Renewed Product ID
2	10006312		VS20530	2	10180.79	yes
3	10011748		NX93300	1	12295.56	yes
4	88484888		PK75757	8	9980.1
Merged SRO

Cell Formulas
Range	Formula
I2:I4	I2	=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","") )