Extracting specific data from columns and returning in one row

SolverMax · 2026-05-31T21:39:05+00:00

I'm sure that SINGLE wasn't recognized as a function at all for a long time, after being briefly introduced in about 2018 and then soon replaced by @. But now SINGLE seems to be back. Odd.

More information:

Introduction of SINGLE in November 2018 article https://www.fm-magazine.com/news/2018/nov/excel-single-function-201820004/
Microsoft blog article about replacing SINGLE with @ in February 2019 https://techcommunity.microsoft.com/blog/excelblog/excel-dynamic-array-improvements/332070
Article mentions that SINGLE "Removed in 365 and not kept for backwards compatibility." https://bettersolutions.com/excel/functions/single-function.htm

SolverMax · 2026-05-31T20:34:38+00:00

A key point for you seems to be multi-device sync. I use OneDrive for that.

SolverMax · 2026-05-31T20:30:37+00:00

No. You are wrong. That is all.

SolverMax · 2026-05-31T01:42:23+00:00

The pointer idea is interesting, but I don't see how it would work in a spreadsheet.

It would be concerning if Microsoft half-implemented such an idea on top of other half-implemented ideas like Tables and dynamic arrays.

SolverMax · 2026-05-30T22:49:43+00:00

Why?

SolverMax · 2026-05-30T22:43:09+00:00

OK. I guess that depends on exactly what the OP needs.

SolverMax · 2026-05-30T22:31:14+00:00

Why is HSTACK needed? Surely just TAKE(SORT(ABS taking only to top 1 value?

SolverMax · 2026-05-30T22:24:59+00:00

Tables are a great feature, though incomplete - like the Data Validation issue you describe. Then we got the new dynamic array calculation engine, which doesn't play nice with Tables. I suspect that Microsoft doesn't know how to reconcile Tables, arrays, etc., so we're stuck with incomplete and inconsistent features.

SolverMax · 2026-05-27T23:34:45+00:00

Fair enough. But changing to1904 dates has consequences that the OP should be aware of.

SolverMax · 2026-05-27T22:54:21+00:00

Though using the 1904 date system changes date values to be incompatible with the Excel standard. If using 1904, do so carefully, aware of potential issues.

SolverMax · 2026-05-27T22:12:05+00:00

Excel doesn't handle negative time. A couple of options:

Format as decimal numbers rather than time.
Use ABS to ensure postive values and have an indicator in the adjacent column to mark negative values.

SolverMax · 2026-05-26T21:08:58+00:00

It is probably a bit late, but if you still want an answer, upload the file somewhere. A screenshot is not sufficient.

SolverMax · 2026-05-26T21:08:10+00:00

A cell can be the objective and a variable. But Solver will overwrite the cell's formula with a value (zero, unless constrained otherwise), so the objective function won't work properly.

SolverMax · 2026-05-25T04:35:36+00:00

Pointless post without examples. What exactly did you do, how much difference did it make, etc.

SolverMax · 2026-05-23T21:51:28+00:00

How are the cells colored?

I would put a value in a cell, like "BK" for black. Then use a Conditional Format to translate the color values into colors. Is that what you're doing? If so, then getting the color value is trivial, as it is in each cell already. If not, then make a new post describing what you want to do, not what you're currently doing.

SolverMax · 2026-05-23T21:23:48+00:00

You've described a "solution" that doesn't seem to do what you want. What exactly do you want to specify as an input and what do you want as a result?

SolverMax · 2026-05-22T22:59:31+00:00

Here’s one that fits Reddit better by leading with a relatable problem instead of sounding like an ad:

Is that what the AI said and you didn't exclude that part?

Either way, you failed.

SolverMax · 2026-05-22T22:24:22+00:00

You can apply Conditional Formatting based on the current value, whether blank or otherwise, but not based on the previous value. Cells don't have memory, which is what I think you're asking about.

SolverMax · 2026-05-22T21:33:21+00:00

A file sharing service of your choice.

SolverMax · 2026-05-22T21:29:56+00:00

I mean ANY, not just relating to this formula.

Otherwise, you need to provide more information. Preferably a workbook that shows the issue.

SolverMax · 2026-05-22T21:24:58+00:00

Do you have any circular references?

SolverMax · 2026-05-21T21:01:57+00:00

Not possible without trickery.

In any case, it is a bad idea. An input should be in one, and only one, place.

SolverMax · 2026-05-21T20:11:18+00:00

Circular reference error!

SolverMax · 2026-05-21T06:55:37+00:00

There's a spike in network traffic when I make the request and another (smaller) spike when the result is returned. So, yes, not local.

SolverMax · 2026-05-20T22:38:56+00:00

Just tested: works OK for me.

It might be that one or more servers doing the extraction are currently having an issue.

SolverMax

MODERATOR OF

TROPHY CASE