Find column numbers or letters where cells exceed a value

[jamesb]

I have looked at @JohnSUN’s approach in this link, but my requirement is slightly different, and I don’t seem to be able to make anything work:

List of ONLY values greater than the value in referenced Cell

Just try Advanced Filter

I have a large sheet, many columns and rows.

For simplicity, I’ve shown a simple section:

Column A : date
Column B : the criteria
Columns C to K : the data

I need to find the column numbers (or letters) in which the data in the cells in the row exceed that of B, and place the results at the end of the same row.

Therefore in cell L2 I’d have : “5,11” (or “E,K”), since these are greater than B2.

Any help would be most gratefully received.

Many thanks.

[karolus]

@jamesb

Therefore in cell L2 I’d have : “5,11” (or “E,K”), since these are greater than B2.

what do you want to do with this »5,11« or »E,K« then?

[jamesb]

I need to identify the cells in the row. From that point I can extract the data.

[karolus]

@jamesb

From that point I can extract the data.

That’s what I was afraid of…why don’t you extract the data straight away?

[jamesb]

Well, at the moment I’m simply considering how best to move forward. Extracting all the data isn’t in the plan, as some of it is “noise”.

[dscheikey]

Hi Jamesb,

this function will give you the desired result:

=TEXTJOIN(",",1,IFERROR(AGGREGATE(15,6,COLUMN(C:K)/(C2:K2>B2),COLUMN(A:I)),""))

Jamesb.ods (10,9 KB)

The formula checks the values in columns C to K of the current row (e.g., row 2). It compares each of these values with the value in column B of the same row. For each cell that meets this comparison (C2 to K2 > B2), the corresponding column number is taken. These column numbers are concatenated into a comma-separated list. If no values meet the comparison, the formula returns an empty string.

However, it is somewhat simpler as a matrix formula (end with CTRL and ENTER):

=TEXTJOIN(", ", 1, IF(C2:K2 > B2, COLUMN(C:K), ""))

LibreOffice 24.8 and higher also works:

=TEXTJOIN(",",1,FILTER(SEQUENCE(,9,3),C2:K2>B2))

[jamesb]

@dscheikey Thank you so much!

[Lupp]

@jamesb

From that point I can extract the data.

How would you do that? Having cell designators as a list of (say) column numbers would require any kind of split to get the values, but there is no TEXTSPLIT() in Calc.
Well, the REGEX() function can be used to get a workaround.
See also:
alternativeAsk106599_jamesb_dscheikey.ods (13.8 KB)

[jamesb]

Hi @Lupp. Thank you so much for your thoughts. They’re much appreciated.

I separated out the comma separated values using the “Text to Columns” function.

[jamesb]

Hi @dscheikey
Thank you again for this.
In hindsight (a wonderful thing!) it would have been better if I’d asked for the formula to return the column letter specifically.
I’ve used the following previously to show letters:

=SUBSTITUTE(ADDRESS(1,COLUMN(),4),“1”,"")

What I can’t figure out is how to make this work in the formulae you so kindly provided. Could I trouble you again, please?

Sorry, and thank you!

[dscheikey]

Hi James,

I’ll come back to @karolus what do you actually want?
You write once that you want to extract the data. If you need the cell content, then it would not be wise to first create the column number or the column letter in a comma-separated list.

This brings you the cell content with your conditions: Matrixformula!

{=IFERROR(INDEX(A2:K2,0,AGGREGATE(15,6,COLUMN(C:K)/(C2:K2>B2),COLUMN(A:I))),"")}

[jamesb]

Hi @dscheikey,

Thank you so much for your reply.

The solution you gave me right at the start was exactly what I needed, except that I should have asked for the letters of columns, and not their numbers. So the result would have read, “E,K” in this case.

Thank you so much for your kind help. It’s most appreciated, and I’m sorry to be a nuisance.

James.

[dscheikey]

The following formula gives you the desired result “E,K”

{=TEXTJOIN(",",1,IF(C2:K2>B2,REGEX(ADDRESS(2,COLUMN(C:K),4,1),"\d*","","g"),""))}

You must enter the formula as a matrix formula by completing the entry with CTRL + SHIFT + ENTER.
You can also use your suggestion with SUBSTITUTE().

Jamesb.ods (12,0 KB)

[jamesb]

@dscheikey

{=TEXTJOIN(",",1,IF(C2:K2>B2,REGEX(ADDRESS(2,COLUMN(C:K),4,1),"\d*","","g"),""))}

Thank you so much!
All the best,
James.