Excel Challenge: List numbers that are sum of squares of other numbers

In this Excel challenge, you are required to produce a list of numbers that are the sum of the squares of natural numbers, also the list should be pruned to numbers less than 100(or any other number in a different circumstance)
Link to workbook
docs.google.com/spreadsheets/...
00:00 Introduction
00:21 Problem statement
01:19 Breakdown of logic
02:30 Using Helper cells
07:07 Introducing TOCOL function
08:00 2nd Approach
09:18 Solution in one-cell
12:02 concluding thoughts
#excelchallenge #excelformulas

[@stevereed5776]

Thanks Victor, another masterclass in Excel

[@ExcelMoments]

Thanks Steve, I appreciate your comment

[@IvanCortinas_ES]

The matrix approach you have given is spectacular. Applause Victor!!

[@ExcelMoments]

Thank you very much for your comments

[@rtrbs8383]

@@ExcelMoments Mr.Victor May be you are THE ONE not the Neo Anderson

[@jazzista1967]

Oh my goodness Victor: What a terrific example and solution.... I used to create these multiplication tables many times when i was learning how to do mixed cell references years ago in excel 2007 from a Mike Girvin video . Brought back great memories. By the way, the division trick to get the division error was brilliant to visualize the final list of numbers . Quite elegant as always! Thanks for sharing your knowledge

[@Mark-sf4kh]

❤

[@ExcelMoments]

We go again!!

[@hagiasofia6179]

Dear good sir, I discovered your channel only 3 days ago and since then I have been watching your videos whenever possible - such is the power of (your) thinking. So, many thanks for being there! P. S. Is it possible to present an Excel challenge here?

[@ExcelMoments]

Yes, please, by all means

[@hagiasofia6179]

@@ExcelMoments Thank your so much for your answer, you are very kind! The question/problem is kind of similar to the one addressed in your "Sort "Messy" Excel Data" video but while in that case it was possible to use WRAPROWS because each part of the data consisted of the same number of rows, is there a way to proceed when this is not the case, i.e. when the data is structured in a way that each record consists of a different number of variables, i.e. it is contained in a different number of rows? E.g, the data is contained in 1 column and structured like this (where r = record or start of a new record):
column 1
r1
a
b
c
d
r2
a
b
c
d
e
r3
a
b
c
d
The desired outcome is this:
column 1 ... column 6
r1 a b c d
r2 a b c d e
r3 a b c d

[@ExcelMoments]

@hagiasofia6179 Great question. Is there something that identifies r1,r2..like is there a special character or something that tells us this is the beginning of the record or the end of the record

[@hagiasofia6179]

@@ExcelMoments To make it simple let us just say that the start of every record contains the same text string, e.g. "NEW".