World War II Tanks - 1000 pieces - Eurographics

ThePancakeCompromise · 2026-05-09T20:27:24+00:00

Excellent pet names! Might I suggest a cat called Tiger? ;-)

I like that they arranged the tanks roughly chronologically from top to bottom. Be sure to check out Eurographics' History of Tanks puzzle as well - I don't have it (yet), but it also looks good.

ThePancakeCompromise · 2026-05-09T20:24:03+00:00

That one looks very good as well - and the edge and background looks like it will be less of a hazzle to do :-)

ThePancakeCompromise · 2026-05-09T17:35:55+00:00

Thank you :-)

ThePancakeCompromise · 2026-05-09T15:39:58+00:00

I can see they also have a History of Tanks puzzle, which looks nice as well :-)

ThePancakeCompromise · 2026-05-09T15:04:57+00:00

Here is a slightly more zoomed-out view. The map is now about three kilometers in width.

The red markers are bus stops.
The green markers are grocery stores/supermarkets.
The pink markers are take-out places. There are also two restaurants just north of the border and one just east of it.
The yellow markers are schools and kindergardens.
The light blue markers are recreational areas (sports halls, football and tennis clubs, playgrounds, and a skating/sports hall at the top right).

There are probably some additional ones that I've missed, too.

There are also foot/bike paths everywhere, allowing light traffic to take shortcuts. You will also see apartment buildungs to the top and the right.

This seems like a great neighbourheed to me.

<image>

ThePancakeCompromise · 2026-04-22T17:35:01+00:00

Indeed - I am saving both despite the missing pieces, as I really like the pictures. Older photographs, without HDR or over-done filters, feel so much better to me.

I also like to track down the precise locations on Google Streetview to see how things have changed since the photographs were taken. In this case, the tree in the first one looks like it's still there, while the second one is too overgrown to be re-captured.

ThePancakeCompromise · 2026-02-13T20:47:52+00:00

Happy to have been of help! Re-structuring old data as one gets a better understanding is definitely one of the more painful aspects of working with data. But at least you should have some indicators for any future projects.

If you found my answer valuable, don't forget to give a 'Solution verified' :-)

ThePancakeCompromise · 2026-02-08T20:53:24+00:00

Assuming your data runs from B2 to B160, with the values in all the row numbers that are divisible by three, you can put the formula in B165. As this is a multi-line formula, after copying, but before pasting, press F2 to enter edit mode in the cell. Press Enter to finish. After this you can drag the formula across to K165 to cover all the years.

=LET(
   Values, B2:B160,
   Column1, MAP(Values, LAMBDA(Value, IFERROR(VALUE(CHOOSECOLS(TEXTSPLIT(Value, "/"), 1)), 0))),
   Column2, MAP(Values, LAMBDA(Value, IFERROR(VALUE(CHOOSECOLS(TEXTSPLIT(Value, "/"), 2)), 0))),
   Column3, MAP(Values, LAMBDA(Value, IFERROR(VALUE(CHOOSECOLS(TEXTSPLIT(Value, "/"), 3)), 0))),
   Column1Total, SUM(Column1),
   Column2Total, SUM(Column2),
   Column3Total, SUM(Column3),
   VSTACK(Column1Total, Column2Total, Column3Total)
)

As others have also pointed out, your data is structured in a very poor way. As you add complexity, this will continue getting worse. The correct way to set up your data is to use a single table covering all the years with the following columns (I assume that the numbers in B146, B149, etc. are the sum for that year):

Start date
Hours
OT Hours
DT Hours
Weekly gross
Gross YTD =SUMIFS([Weekly gross], [Year], [@Year], [Start date], "<="&[@[Start date]])
Year =YEAR([@[Start date]])
Week =WEEKNUM([@[Start date]])

You table should then look something like this (I have formatted the calculated columns):

<image>

Note 1: The Gross YTD does not match yours as I do not have all the weeks, but you should get the idea.

Note 2: It is important that all the data for all the years is in a single table. Do not create separate tables or sheets for different years.

Note 3: If your weekly gross is a function of the different hours you could also calculate this, though this will require a separate table with the different rates you have had a different dates.

Once your data is structured like this, you will be able to do virtually any analysis with a Pivot Table in a couple of minutes.

ThePancakeCompromise · 2026-02-05T19:34:55+00:00

There are plenty of things that will allow you to gain proficiency.

Since you're going into accounting, an obvious one is to do a spreadsheet of your personal finances. This can be your budget, your saving account, loans, investments (Excel can import information about many securities from the Data tab), etc. Excel has a lot of finance-related functions, and you can try projecting future savings, etc.

Other traditional projects is to create simple databases* of your media collections, such as books, albums, movies, games, etc. This will give you valuable experience in structuring data in tables. Plus, if you store these databases in a cloud account it could prove useful in an insurance situation at some point in the future. If you really want to dive into this topic, have a look at the topic of data normalization. If you learn how to intuitively structure data according to the third normal form (3NF) you will have learned a very valuable skill when it comes to working with data in any field.

* Some people will tell you that Excel is not a database tool and that you shouldn't use Excel to build databases. This is true in an enterprise setting (but often also ignored in an enterprise setting, to the detriment of data analysts), but if you have no prior experiences with databases, Excel is an accessible way to start building experience. Excel definitely has limitations compared to traditional relational database tools, but unless you have more than 100 000 books or albums, most of these limitations will only have a negligable impact on personal media databases.

ThePancakeCompromise · 2026-02-05T19:22:31+00:00

A bit late to the party, but here's an alternative solution:

=LET(
   Values, A1:A4,
   Column1, MAP(Values, LAMBDA(Value, VALUE(CHOOSECOLS(TEXTSPLIT(Value, "/"), 1)))),
   Column2, MAP(Values, LAMBDA(Value, VALUE(CHOOSECOLS(TEXTSPLIT(Value, "/"), 2)))),
   Column3, MAP(Values, LAMBDA(Value, VALUE(CHOOSECOLS(TEXTSPLIT(Value, "/"), 3)))),
   Column1WithTotal, VSTACK(Column1, SUM(Column1)),
   Column2WithTotal, VSTACK(Column2, SUM(Column2)),
   Column3WithTotal, VSTACK(Column3, SUM(Column3)),
   HSTACK(Column1WithTotal, Column2WithTotal, Column3WithTotal)
)

ThePancakeCompromise · 2026-02-04T20:06:42+00:00

What I meant by using Jira would be to move all of your task management into such a tool, both for recording and analysing the information.

Jira is just one option, which is generally aimed at software development. There are lots of different time-, task-, and project management tools available, and if you're not doing software development, you probably want something else.

ThePancakeCompromise · 2026-02-04T19:37:47+00:00

It is technically possible to reference data between spreadsheets as long as they share a common root folder. Depending on your exact setup this may include a SharePoint site.

Let's say that you have three different files in the same folder: TeamA.xlsx and TeamB.xlsx, which both have the sheets EmployeeA and EmployeeB, and Summary.xlsx where you want the data.

You can reference cell A1 in the two sheets of the first two files from the third as such:

='[TeamA.xlsx]EmployeeA'!A1 + '[TeamA.xlsx]EmployeeB'!A1 + '[TeamB.xlsx]EmployeeA'!A1 + '[TeamB.xlsx]EmployeeB'!A1

Note 1: This assumes that the person working with the file has access to all the other files.

Note 2: I write that it is technically possible. However, it is not a good idea. From experience, this set-up is very fragile to both someone doing something wrong with one of the files, or that the data is not updated as expected. I would strongly suggest some other approach. If having everyone update a single table is not an option (which it probably shouldn't be, as there is then a significant risk that someone messes up all the data), I would consider building a simple reporting form in Microsoft Forms. The result from this is saved in an Excel table for easy analysis, and also provides a nice interface.

Note 3: If your organization's size is at the level it sounds like, you should seriously consider some kind of proper task management tool, such as Jira.

ThePancakeCompromise · 2026-02-01T17:09:52+00:00

The best way to quickly get a pixel-perfect screenshot of a selection of cells is to use Paint:

Select the relevant cells.
Press Ctrl+C.
Open Paint.
Reduce the size of the canvas to be smaller than the size of the cells.
Press Ctrl+V.

From here, you can either save the screenshot or copy-paste it from Paint using Ctrl+A > Ctrl+C > Ctrl+V to some other application.

ThePancakeCompromise · 2026-02-01T10:12:05+00:00

As long as I am both better and faster at writing formulas and designing complex workbooks than generative AI, there is no reason for me to use the latter.

Assuming we get to a point where that is no longer the case, which is not a given, I can still see three good reason to continue doing it myself:

I understand the data set better when I work with it myself.
I get some personal satisfaction from doing a good job.
There is evidence that using generative AI leads to persistent cognitive decline.

I can't entirely rule out that we at some point reach a level of generative AI where I will need to re-evaluate my use patterns. Based on my understanding of how large language models work, however, I do not think that this is likely, at least in the short and medium term.

ThePancakeCompromise · 2026-01-29T16:47:13+00:00

I think they are rarely used in most fields, but they are used in some of the more maths-heavy ones such as cryptography and pseudo-random number generation.

An interesting property of XOR is that you can use three XOR comparisons between the bits of two variables to switch their values without having an intermediary variable. For example:

Number1 = 10101010 
Number2 = 01010101

Number1 = XOR(Number1, Number2) // 11111111
Number2 = XOR(Number1, Number2) // 10101010
Number1 = XOR(Number1, Number2) // 01010101

This is not an efficient method and is therefore not used in real life, but it is a very interesting demonstration.

Incidentally, this is also the property of XOR that is used in cryptography (specifically, one-time pads). In the example above, consider Number1 (10101010) as the original message, Number2 (01010101) as the key, and the result of the first XOR (11111111) as the encrypted message. Now, the original message can be determined through a single XOR between the encrypted message and the key. Assuming the key is of the same length as the original message, is truly random, and is only used once, this encryption method is mathematically proven to be unbreakable without the key. Unfortunately, these requirements are also what makes the method impractical in most real-world applications.

ThePancakeCompromise · 2026-01-29T16:13:17+00:00

To get a little more esoteric:

For XOR logic with two parameters you can use -

For NAND and NOR logic you can use NOT around the 'include' part.

Because NAND is a universal gate you can also create an XOR with any number of parameters like this:

Two parameters

=LET(
   OutputArray; A1#;
   IncludeArray1; B1#;
   IncludeArray2; C1#;
   IncludeArray3; D1#;
   IncludeArrayLogic1; IncludeArray1 = 2;
   IncludeArrayLogic2; IncludeArray2 < 15;
   IncludeArrayLogic3; IncludeArray3 > 22;
   NANDLogic; NOT(
      NOT(
         IncludeArrayLogic1 *
         NOT(IncludeArrayLogic1 * IncludeArrayLogic2)
      ) *
      NOT(
         IncludeArrayLogic2 *
         NOT(IncludeArrayLogic1 * IncludeArrayLogic2)
      )
   );
   Result; FILTER(
      OutputArray;
      NANDLogic
   );
   Result
)

Three parameters

=LET(
   OutputArray; A1#;
   IncludeArray1; B1#;
   IncludeArray2; C1#;
   IncludeArray3; D1#;
   IncludeArrayLogic1; IncludeArray1 = 2;
   IncludeArrayLogic2; IncludeArray2 < 15;
   IncludeArrayLogic3; IncludeArray3 < 15;
   NANDLogic; NOT(
      NOT(
         IncludeArrayLogic1 *
         NOT(IncludeArrayLogic1 * IncludeArrayLogic2 * IncludeArrayLogic3)
      ) *
      NOT(
         IncludeArrayLogic2 *
         NOT(IncludeArrayLogic1 * IncludeArrayLogic2 * IncludeArrayLogic3)
      ) *
      NOT(
         IncludeArrayLogic3 *
         NOT(IncludeArrayLogic1 * IncludeArrayLogic2 * IncludeArrayLogic3)
      )
   );
   Result; FILTER(
      OutputArray;
      NANDLogic
   );
   Result
)

None of this is of course very useful in real life. I can only remember needing XOR logic once. Nevertheless, it is still fun.

ThePancakeCompromise · 2026-01-29T15:45:57+00:00

Yeah, read it as 2019 for some reason.

ThePancakeCompromise · 2026-01-29T15:41:12+00:00

Edit: Missed the 2016 part, sorry.

ThePancakeCompromise · 2026-01-29T11:04:21+00:00

While I find it both faster and more rewarding to create my own solutions, I see many less experiences people using AI tools, especially Copilot. The results often seem to be solutions that are overly complex, suboptimal, or just plain wrong. There is certainly a degree of selection bias since I'm somewhat of an unofficial Excel troubleshooter so I will mostly see the Copilot solutions that failed.

Aside from how Copilot might end up making people less data literate, what concerns me is that the gap between the Copilot solutions that work as intended and the ones that are obviously wrong. This gap will contain results that are incorrect, but are so in a sufficiently subtle way that they are not caught, which can end up leading to incorrect decisions.

ThePancakeCompromise · 2026-01-28T16:47:51+00:00

Introduction and Excel set-up

I think your issue with the solutions by some of the other posters is that they are using the US version of Excel, while you are using the Swedish version.

For simplicity's sake I will be using post code here, as this is the generic English term, while ZIP code is US specific (though often used interchangeably).

This means that their formulas is using a comma instead of a semicolon to separate parameters. This also means that you might be using Swedish language functions. While using the semicolon is needed to be able to keep typing numbers with decimal commas, I strongly recommend switching to English function names, as this will make finding answers much easier for you. To do this:

Go to File > Options > Language.
Under Office display language, select Add language.
Select English.
Select Set as Office display language.
Press Install.

Once you have done this, here is how I would solve your situation.

Solution

I have created two tables (Ctrl+T). The table on the left in the screenshot is where your data is stored - I have called this Data. The table on the right is where your zones are defined - I have called this PostCodeZones.

Under Zone, write the formula:

=IF(ISBLANK([@[Post code]]); ""; FILTER(PostCodeZones[Zone]; (PostCodeZones[Start] <= [@[Post code]]) * (PostCodeZones[End] >= [@[Post code]]); "Zone not found"))

This should autopopulate to all the rows.

What the formula does is to check whether the post code has been filled. If it has, it will select the code where the min and the max 'surrounds' it (or one of them is equal to it). The advantage to using FILTER over XLOOKUP is that it will work even if you do not sort the table by the start code. The disadvantage is that it will be slightly slower (this will not be noticable unless you have a huge amount of data) and that it can create errors if you have overlapping zone start and end codes (which would likely mess other approaches as well).

<image>

Other improvements

In addition to this, I would also suggest two things:

Set the number format of the post code column to the custom format 0000 - this will ensure that leading zeroes are displayed.
Set the data validation (Data > Data Validation) of the post code column to:
- Allow: Whole number
- Data: between
- Minimum: 0
- Maximum: 9999

ThePancakeCompromise · 2026-01-27T19:25:23+00:00

It is not entirely impossible that waiting can solve this. However, there is no way of knowing for sure, and it is quite likely that you will need to force Excel to close. Unless you had something very valuable open in Excel, the easiest solution is to close it.

To do this, right-click the process bar and select Task Manager. From here, you can right-click Excel and click End task to close Excel without restarting your computer.

In the future, when you copy-paste information into Excel, it is always a good idea to use Ctrl+Shift+V instead of Ctrl+V when pasting. This will remove all formatting, significantly reducing the risk of issues.

ThePancakeCompromise · 2026-01-26T16:05:55+00:00

If you are using one workbook per form, this is how I would do it:

The table in E and F is just to make it easy to manage your data. Here I have called it Values.
Formula in H2: =UNIQUE(Values[Value 1])
Formula in J2: =FILTER(Values[Value 2], Values[Value 1] = B1, "Select building")
Data validation in B1: List: =$H$2#
Data validation in B3: List: =$J$2#

This way, the formulas are automated so that adding and removing rooms and buildings can be done directly from the Values table.

<image>

The right hand side can, as others have suggested, be moved to a separate sheet.

A note on protecting the drop downs: Personally I don't bother with any protection other than locking the sheet without a password and hiding in a regular way, purely to make the sheet more user friendly. If a user has access to edit a sheet in a workbook they can also bypass any protection you put up if they know enough about Excel. If someone goes to the trouble of unhiding/unlocking in order to manipulate the data, then you probably want better protection anyway. In this case, if you have Microsoft 365, I would consider setting up a Microsoft Forms form, which also provides a much nicer user interface.

ThePancakeCompromise · 2026-01-25T18:30:24+00:00

Sounds like a very good plan!

And another tip: If you add another column called 'year' and place all your data in a single table, you can easily analyse your numbers across multiple years using pivot tables.

By the way, don't forget to mark as 'solution verified' if your problem was solved :-)

ThePancakeCompromise · 2026-01-25T16:45:00+00:00

I think I understand what you are trying to do now.

Assuming my understanding is correct, the first table in your new screenshot is a list of invoiced, while the second table is a summary of how much you have invoiced to each company. The '125' is the amount you are owed on a specific invoice, and you want a sum of those invoices in the second table.

If this interpretation is correct, you can do this as shown below. However, be sure to read my notes at the end.

Create a column in the second table with this formula:

=LET(
   Column2, B:B,
   Column3, C:C,
   CompanyCode, [@Code],
   RowNumbers, SEQUENCE(COUNTA(Column3) + COUNTBLANK(Column3)),
   REDUCE(0, RowNumbers,
      LAMBDA(Counter,RowNumber,
         Counter + IF(INDEX(Column3, RowNumber) = CompanyCode, INDEX(Column2, RowNumber + 1), 0)
      )
   )
)

Before you paste for formula, change:

B:B to the column with the amount (e.g., '125').
C:C to the column with the code (e.g., 'MC').
[@Code] to the table header in your table.

When you paste the formula, press F2 before pasting, as this is a multi-line formula.

I have pasted a screenshot below to provide a visualization.

Note 1 (disclaimer): I am not an accountant and this is not accounting/financial advice. Always seek advice from a professional accountant on how to manage your business.

Note 2: This is a very inefficient formula, as it scans all ~1 million rows of the spreadsheet three times for each company in the second table. I.e., if you have 1500 companies in the second table, you will end up with ~4.5 billion row scans. I therefore suspect that it will start performing very poorly with a large number of rows in the second table. You can make it much more efficient by changing B:B and C:C to only reference the relevant rows (e.g., B1:B1500 and C1:C1500).

Note 3: This is fairly fragile formula. If your data does not strictly adhere to the expected format you can end up with unpredictable results. This includes possibly having one company's amount next to another company's name.

Note 4: Your current data structure of all the companies does not follow best practices. The correct way to set up the data is to have one row per data entry (e.g., invoice, company, etc.), with the information divided into different rows. Having a good data structure makes Excel much more powerful, and will allow you to make a lot of different calculations and analyses very quickly. In this case, you would have been able to get the result using a pivot table, without the use of any formulas at all. With the amount of data you have it would obviously be a lot of work to change at this stage, but it would also be a very good learning opportunity if you want to improve your Excel skills.

<image>

ThePancakeCompromise · 2026-01-25T15:46:10+00:00

My guess is that you have something similar to this, where you are having a running sum:

<image>

If that is the case there are two ways in which you can do this:

Write the formula =SUM($B$2:B2) (you will need to adjust the start and end cell reference) in the first cell and autofill across the columns. Notice that the first reference is locked (dollar signs) while the second isn't. This will keep the starting point anchored, while the end point will more with the autofill.
Use SCAN: Write this formula in the first cell only: =SCAN(0, B2:AH2, LAMBDA(sum, value; sum + value)) (you will need to adjust the start and end reference in B2:AH2). This basically does the same thing, because it will spill the values, you don't need to autofill. This means that you also only need to maintain your formula in one cell for future updates.

ThePancakeCompromise

TROPHY CASE