Awesome Compare Data Trick
Wyn Hopkins
![Wyn Hopkins]()
Wyn Hopkins
Leading a team that solves data challenges for companies and people Power BI | Excel | Fabric | Solver Budgeting & Consolidation. Control costs, reduce risk and assist growth Microsoft MVP ??5 Million+ YouTube views
Creating a magic formula to compare 2 ranges of data for non matching items.
I'm loving Dynamic Array Excel, I now can't imagine a time before it.
Here's the formula
Without LET
=HYPERLINK("#"& TEXTJOIN(",",TRUE, IF( dblClickRange1 <> dblClickRange2, ADDRESS( ROW( dblClickRange2 ), COLUMN( dblClickRange2 ) ),"") ) )
With LET
=LET(_Range1, dblClickSelectRange1,
_Range2, dblClickSelectRange2,
HYPERLINK("#"&
TEXTJOIN(", ",TRUE,
? IF( _Range1 <>??_Range2,
? ADDRESS(?ROW(_Range2),?COLUMN(_Range2)? ?),
"")
)
)
? )
And here's the explanation
Wyn
Business Management BASc | Data Analytics Enthusiast | Power Query | Power BI
3 年OMG, what a Tip/Trick, I will give it a go - Thank you
#StandWithUkraine????
3 年Fully agree Wyn Hopkins! By the way, really looking forward to “getting to know you” at the Global Excel Summit this week :)
#twinztalk | Consultant | Speaker | Instructor | Course Developer | | It's time for BETTER. MBA, CPA (Inactive) #chatgpt #genai #Digital #Data #Analytics #Excel #powerquery #powerbi #powerpivot
3 年Fantastic Wyn! ?? I will have to give that a try - definitely a lot of use cases for that. Thanks for sharing! Bill Tomoff