Excel Magic Trick 1190

Formulas To Extract Unique List County Numbers & List Zip Codes Horizontally

DESCRIPTION

See how take two columns of county numbers and kip codes and show unique list of county codes vertically, and then for each county code, list zips codes horizontally.
(00:08) Problem Set up
(00:50) Why use formulas and why use Array Formulas
(01:20) Beauty of formulas is that they update instantly when the source data changes
(01:36) Convert Data Set to Excel Table so that we have dynamic ranges that will allow formulas to update automatically when we dump new data into table.
(02:26) Use Defined Names rather than Table Formula Nomenclature (Structured References)
(02:42) Create Names From Selection keyboard: Ctrl + Shift + F3
(03:09) Test to see if Defined Names will respect expanded ranges in an Excel table.
(03:32) SUMPRODUCT and FREQUENCY functions to create a Array Formula that counts unique numbers
(06:55) Array Formula to extract a unique list of numbers, using the functions: IF, ROWS, ROW, INDEX, FREQUENCY, AGGREGATE
(13:31) IF and COUNTIFS to count how many of each county codes there are.
(14:29) Formula to extract zip codes and list horizontally (that depends on the county codes being sorted) using IF, OR COLUMNS, INDEX and SUM functions. Also see some expandable ranges in the formula. This is not an array formula – and we can use it because the county code column is sorted.
(20:22) Test the solution by dumping a new data set into the Excel Table to see that the formulas all update instantly.
(20:49) Summary




Be the first to comment

Leave a Reply