Excel Magic Trick 1190: Formulas To Extract Unique List County Numbers & List Zip Codes Horizontall
Download Excel Start File: https://excelisfun.net/files/EMT1190.xlsx Download File: https://excelisfun.net/ 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. 1. (00:08) Problem Set up 2. (00:50) Why use formulas and why use Array Formulas 3. (01:20) Beauty of formulas is that they update instantly when the source data changes 4. (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. 5. (02:26) Use Defined Names rather than Table Formula Nomenclature (Structured References) 6. (02:42) Create Names From Selection keyboard: Ctrl + Shift + F3 7. (03:09) Test to see if Defined Names will respect expanded ranges in an Excel table. 8. (03:32) SUMPRODUCT and FREQUENCY functions to create a Array Formula that counts unique numbers 9. (06:55) Array Formula to extract a unique list of numbers, using the functions: IF, ROWS, ROW, INDEX, FREQUENCY, AGGREGATE 10. (13:31) IF and COUNTIFS to count how many of each county codes there are. 11. (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. 12. (20:22) Test the solution by dumping a new data set into the Excel Table to see that the formulas all update instantly. 13. (20:49) Summary Sal A at YouTube: Learn Excel - Transpose for Each County - Podcast 1953 https://www.youtube.com/watch?v=8edJrF_U4J8 To learn about Array Formulas: https://www.youtube.com/playlist?list=PLrRPvpgDmw0kjL4875H36yNhWBb0f-nci Excel Magic Trick 1190: Formulas To Extract Unique List County Numbers & List Zip Codes Horizontally playlist: https://www.youtube.com/playlist?list=PL63A7644FE57C97F4
Download Excel Start File: https://excelisfun.net/files/EMT1190.xlsx Download File: https://excelisfun.net/ 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. 1. (00:08) Problem Set up 2. (00:50) Why use formulas and why use Array Formulas 3. (01:20) Beauty of formulas is that they update instantly when the source data changes 4. (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. 5. (02:26) Use Defined Names rather than Table Formula Nomenclature (Structured References) 6. (02:42) Create Names From Selection keyboard: Ctrl + Shift + F3 7. (03:09) Test to see if Defined Names will respect expanded ranges in an Excel table. 8. (03:32) SUMPRODUCT and FREQUENCY functions to create a Array Formula that counts unique numbers 9. (06:55) Array Formula to extract a unique list of numbers, using the functions: IF, ROWS, ROW, INDEX, FREQUENCY, AGGREGATE 10. (13:31) IF and COUNTIFS to count how many of each county codes there are. 11. (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. 12. (20:22) Test the solution by dumping a new data set into the Excel Table to see that the formulas all update instantly. 13. (20:49) Summary Sal A at YouTube: Learn Excel - Transpose for Each County - Podcast 1953 https://www.youtube.com/watch?v=8edJrF_U4J8 To learn about Array Formulas: https://www.youtube.com/playlist?list=PLrRPvpgDmw0kjL4875H36yNhWBb0f-nci Excel Magic Trick 1190: Formulas To Extract Unique List County Numbers & List Zip Codes Horizontally playlist: https://www.youtube.com/playlist?list=PL63A7644FE57C97F4