Mr. Excel SSN Challenge: Import last 4 of SSN | Process vs. Formula | Dynamic Arrays & Power Query

/in /by

Bill Jelen, Mr. Excel, recently posted a video requesting input on ways to retrieve specific data while hiding sensitive data.

The challenge: how can you use a person’s last initial and the last-4 of their social security number to retrieve relevant details. HOWEVER! The full social security number should be available but not easily revealed.

In this video I use HSTACK, Power Query, Dynamic Arrays, LEFT and RIGHT to achieve this. But, rather than use formulas, I use simpler Excel features and focus on a process that imports the relevant data into the workbook where the lookups will be performed.

Using 2 workbooks, adds some friction to help separate the fully sensitive data and the daily use data.

#ExcelChallenge #ImportData #PowerQueryTutorial

For a list of my Excel courses at Lynda/LinkedIn:
https://www.linkedin.com/learning/instructors/oz-du-soleil
There are courses on Power Query, Good spreadsheet habits, and a weekly Excel challenge that comes out every Friday.

Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 3rd Edition
https://www.mrexcel.com/products/guerrilla-data-analysis-3rd-edition/
My old blog: http://datascopic.net/blog-2-2