generate list

Hi! This can probably be generated with a dynamic-array formula such as FILTER, UNIQUE, SORT, or a combination of them. Please add a small example showing the source rows and the exact expected output, with private data replaced. That will make it possible to give you one formula instead of a manual process.

Hi BenjonBarachina​,

You can try the following formula in Sheet2.

=LET(
     name, Sheet1!A2:A16,
       rx, REGEXEXTRACT(name, "."),
           FILTER(name, (rx >= "A") * (rx <= "D"), "No names found")
)

Use the same formula for the other Sheets but change only the letters. So instead of the letter A in the formula, put the letter E and instead of the letter D in the formula, put the letter J. Do the same for the other Sheets.

Change the range in the formula according to the real range you have in your data.

Hope this helps.

IlirU

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.

Bye.

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).  IMHO the FILTER function would be the easiest.  A formula on sheets 2-x would be something like:

=FILTER(sheet1!A1:.ZZ9999,  (UPPER(LEFT(sheet1!A1:.ZZ9999,1))>="A")*(UPPER(LEFT(sheet1!A1:.ZZ9999,1))<="D"), "none found")

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