generate list

Question

Good day! I have a file that has list of all names on one sheet. is there a way to generate on a different sheet list of names based on the first letter of the names? like in sheet1 would be all the names, sheet2 would be names starting in A-D, sheet3 names starting in E-J... TIA!

kidd_ip · Answer

Take this, support.microsoft.com/en-us/excel/functions/filter-function, support.microsoft.com/en-us/excel/about-power-query-in-excel

m_tarler · Answer

there are a number of possible approaches to do this. as referenced by kidd_Ip you could use the FILTER function or use Power Query or you could even use pivot tables (and there are probably more options).&nbsp; IMHO the FILTER function would be the easiest.&nbsp; A formula on sheets 2-x would be something like:=FILTER(sheet1!A1:.ZZ9999,&nbsp; (UPPER(LEFT(sheet1!A1:.ZZ9999,1))&gt;="A")*(UPPER(LEFT(sheet1!A1:.ZZ9999,1))&lt;="D"), "none found")

terio · Answer

BenjonBarachina wrote:is there a way to generate on a different sheet list of names based on the first letter of the names?Try in 2nd sheet:=LET(_names,Sheet1!A1:.A9999,FILTER(_names,ISNUMBER(MATCH(LEFT(_names);{"a";"b";"c"},0)),"No names"))modify matrix of first letters {"a";"b";"c"} in next sheets.&nbsp;Bye.

Forum Discussion

generate list