I've looked everywhere for this. I have a sheet that splits a message into columns. It goes something like this.

On the left column, the message to be split is placed. On the columns to the right the message is split so the person is in 1 column and their number is in the column to the right of that.

In another sheet, I have a UNIQUE() function to gather all the unique names but without the numbers. I do this by doing UNIQUE(FLATTEN()) and manually typing all the ranges that don't have the numbers. My question is: is there a way for me to instead of skipping the columns with numbers and stringing a bunch of ranges together, can I use one range that includes the numbers and the names but also ignores cells that are only numerical values?

CodePudding user response：

try:

=UNIQUE(QUERY(FLATTEN(C2:H), "where not Col1 matches '^\d. '", 0))

update:

=QUERY(UNIQUE(FLATTEN(FILTER(LOGGING!D2:Z, 
 MOD(COLUMN(LOGGING!D2:Z), 2)=0))), "where Col1 is not null", )

CodePudding user response：

Looks like player0 already provided you with a working solution. I'll add another, the results of which I don't currently see in your sheet.

I added a new sheet ("Erik Help") with the following formula in A1:

=ArrayFormula(QUERY(IFERROR(SPLIT(FLATTEN(SPLIT(PROPER(LOGGING!B3:B), ", ", 0)), ":")), "Select Col1, SUM(Col2) WHERE Col1 Is Not Null GROUP BY Col1 LABEL Col1 'Name', SUM(Col2) 'Total'"))

This produces two columns with headers and all results. It uses only the original raw data from LOGGING!B:B and skips the intermediate data break-downs, going directly from raw data to result in one step.