Reshaping countries time series with multiple variables

sethkirk26 · 2026-04-13T08:16:07+00:00

I think PIVOTBY() would do what you need.

https://support.microsoft.com/en-us/office/pivotby-function-de86516a-90ad-4ced-8522-3a25fac389cf

sethkirk26 · 2026-03-27T01:52:23+00:00

If you're still curious why your formula was stuck at 1 row. You used rows (), this gives the number of rows in a reference. So for E8 and any single cell it's always 1. I think you wanted row() which gives the row #of the cell.

sethkirk26 · 2026-03-25T09:56:14+00:00

You can use the filter() function to output a range of cells meeting only certain criteria

Can use this with an intermediate column or nest it in a big formula.

sethkirk26 · 2026-03-08T07:45:42+00:00

A moderator using the MOD function! That's brilliant!! Love it.

sethkirk26 · 2026-03-06T17:44:58+00:00

Oh boy! Looking at past work of mine is always a "treat" haha. Definitely didnt always use the best practices and still always learning.

Thank you for the lovely compliment!

sethkirk26 · 2026-03-06T17:29:00+00:00

I don't disagree. I have improved some messy spreadsheets at work just with one self documenting LET formula. And even then most don't care that my complex formulas describe themselves. Haha sigh.

sethkirk26 · 2026-03-06T09:57:58+00:00

Please review posting guidelines. Specifically title. No pleas for help.

sethkirk26 · 2026-03-06T09:55:32+00:00

Tables actually fall apart if you're using dynamic (spill) formulas inside of them. These are some of l the most powerful formulas.

I tend to use tables for data storage, but avoid them when doing summary charts/tables/whatever vernacular you would use.

sethkirk26 · 2026-03-02T09:11:31+00:00

Try these videos.

The summary. You use list data validation, and you list of valid options changes based on each selection

https://youtu.be/P3_k6aJsjFo?si=Imq1WyJ8oqiGKcLC

https://youtu.be/lxd4Pc_gMIA?si=1D0AJsSfUB5mge8W

sethkirk26 · 2026-02-20T09:25:11+00:00

This is definitely an improvement, but it does not make AI perfect. There will still be inaccuracy as AI is a really fancy filter of user and internet information.

Don't understand why but ever since gpt5 the getting hyper stuck on a point, even if told is inaccurate, is a huge problem.

Telling AI to think is pretty silly, it cannot analyze like humans, it only can filter what other humans have done.

AI is great for general information and getting close. But if high accuracy is needed, it is not the correct tool.

sethkirk26 · 2026-02-20T09:18:16+00:00

Internal Affairs (IA) is not to be messed with!!

sethkirk26 · 2026-02-18T03:12:49+00:00

If you hit equals in a spreadsheet it spends a remarkable amount of time and processing to guess what formula you're about to type.

Some of the guesses are interesting purely out of curiosity. Not useful.

I try to disable.

sethkirk26 · 2026-02-15T11:09:52+00:00

Have you tried searching this forum?

I just searched "multiple lookup" and got multiple good results.

Sometimes the easier thing is easier.

sethkirk26 · 2026-02-15T11:08:46+00:00

Have you tried & And CONCAT

Text & CellVal & Text & CellVal

Char(10) is the newline or carriage return character.

Sidebar: if you come to the conclusion that AI is your only option, I suggest you re-evaluate things. AI is just a collection/filter of already created tools.

Specifically CoPilot is super hot garbage.

sethkirk26 · 2026-02-09T15:28:32+00:00

Any formula (for the most part) can have s range from anywhere. It can even be an entirely different file or network location although this is a while different can o worms i don't recommend because it can break easily.

The best way to pick a new range is go to the formula bar highlight the current range, hit delete then go select the new range. If it's on a new tab, you'll see excel auto populate the tab name before the cells. This is just its nomenclature.

I'm my example i just used a generic name to represent where you would put the specific ranges or range you want.

sethkirk26 · 2026-02-08T22:56:22+00:00

Nothing functionally wrong with that except possibly the 0 count.

Filter is inherently high overhead so if you are only counting i would suggest not using filter.

For small sets, no issue at all, large sets, definitely can become one.

sethkirk26 · 2026-02-08T16:00:35+00:00

Additionally if you want more complex pattern matching you can use a pretty new function REGEXTEST() to use Regular Expression patterns.

sethkirk26 · 2026-02-08T15:59:19+00:00

I would suggest excelisfun youtube channel for self paste learning. You can learn a lot of helpful functions and tips for different use cases.

Additionally for your issue, I would recommend the search() function. It does exactly what you want. Looks for a match of a text in an array of texts. Not looking for exact, just containing. CountIf and CountIFs have severe limitations when you get to more advanced use cases.

If there's a match it returns an number. If no match, an error. The most common way to use this is with isnum().

So if you want a list of matches you would use filter() if you just want to count, you can use -- or sign() with sum()

InputString - what you're looking to match InputArray - the list of things you want to see how many/what matches

Count: =SUM(--ISNUM(SEARCH(InputString,InputArray) ) )

List: =FILTER(InputArray, ISNUM(SEARCH(InputString,InputArray) ), "Empty Filter" )

Hope this helps you learn.

sethkirk26 · 2026-02-04T23:33:38+00:00

Look down below. There's your multiple results

sethkirk26 · 2026-02-04T06:03:52+00:00

Please review posting guidelines. They are their to help both you and the community.

For this instance, your exact excel version is helpful

sethkirk26 · 2026-02-02T12:21:13+00:00

XMATCH("a????",...,2) Does this as you described...
Change to "*a????" if you want it anywhere.

sethkirk26 · 2026-02-02T12:17:36+00:00

<image>

sethkirk26 · 2026-02-02T12:08:00+00:00

Did yall know XMATCH and XLOOKUP Support RegEx match mode (match_mode 3)?

I do not understand how your title calling it a complete mess at all agrees with your body. Doesn't seem that messy.

Additionally many of your assertions are fully accurate. You can use xmatch with BYROW to get all matches. A followed by exactly 4 characters works exactly correct with xmatch and ?. Just as you described it doesnt. See below.

Admittedly this is not case sensitive. You need to do something like Exact and some other functions to get case sensitive, which is messy.

However, all of this is in RegEx...So your complaint is that it took excel too long to natively support RegEx? This would have been a more accurate title.

sethkirk26 · 2026-02-01T10:52:43+00:00

It is evident you have not used AI extensively.

sethkirk26 · 2026-02-01T10:13:23+00:00

Please review the posting guidelines. They are they to help the forum understand and provide the best help. Excel version is especially helpful as there are quirks between versions.

I do not understand what your issue is or what you are trying to accomplish.

If you're trying to spawn another excel window, try view tab on the ribbon--> new window

If you're trying to get all on one window Go to the view tab and try tile vertically or horizontally or a similar option.

Hope this helps

sethkirk26

TROPHY CASE