My works can be regarded as stations along my life's way. All my writings may be considered tasks imposed from within; their source was a fateful compulsion. Carl Jung ‘Memories, Dreams, Reflections’

I first read in Memories, Dreams, Reflections as an introduction to the work of Carl Jung as a part of David Tacey’s class in university. I am not sure what it says about me, but I had not realised that the book was actually ‘recorded and edited’ by Aniela Jaffé. (Clearly, I skipped the introduction?) It was therefore interesting to return to it again all these years later.

Memories, Dreams, Reflections is not an autobiography that captures the events of life, rather it is better consider as Jung’s psychic reflection on the his journey with the unconscious. Edward Glover sums it up as follows:

The title is an apt one, for the main purpose of his recollections, dreams and visions is to illuminate his inner development, to trace the effect of his “confrontation of the unconscious” at various stages of his life, in a form unhampered by the necessity for scientific presentation and valuation. It is a personal testament and, above all, a religious testament.

Source: Illuminations From Within by Edward Glover

Glover also highlights the point that Jung requested that the book was not included in the official edition of his collected works. Eugene Kernes describes the book as a ‘detached autobiography’ that allowed Jung to share details that he would not usually have shared.

This is an autobiography, but a detached autobiography which enabled Jung to relate personal details that otherwise Jung would not have wanted to share.

Source: Review of Memories, Dreams, Reflections by Carl G. Jung by Eugene Kernes

I wonder if the request to have it excluded related to the fact that the book was co-written with Jaffé?

It had been proposed that the book be written not as a “biography,” but in the form of an “autobiography,” with Jung himself as the narrator. This plan determined the form of the book, and my first task consisted solely in asking questions and noting down Jung’s replies. Although he was rather reticent at the beginning, he soon warmed to the work. He began telling about himself, his development, his dreams, and his thoughts with growing interest.

Source: Memories, Dreams, Reflections by Carl Jung (and Aniela Jaffé)

With this dual writing experience, it was interesting to read the analogy raised in the Prologue to life as being ‘rhizome’:

Life has always seemed to me like a plant that lives on its rhizome. Its true life is invisible, hidden in the rhizome. The part that appears above ground lasts only a single summer. Then it withers away–an ephemeral apparition. When we think of the unending growth and decay of life and civilizations, we cannot escape the impression of absolute nullity. Yet I have never lost a sense of something that lives and endures underneath the eternal flux. What we see is the blossom, which passes. The rhizome remains.

Source: Memories, Dreams, Reflections by Carl Jung (and Aniela Jaffé)

For me, I was reminded of the work of Gilles Deleuze and Félix Guattari.

We are writing this book as a rhizome. It is composed of plateaus. We have given it a circular form, but only for laughs. Each morning we would wake up, and each of us would ask himself what plateau he was going to tackle, writing five lines here, ten there. We had hallucinatory experiences, we watched lines leave one plateau and proceed to another like columns of tiny ants.

Source: A Thousand Plateaus: Capitalism and Schizophrenia by Gilles Deleuze and Félix Guattari

Grant Maxwell suggests that their concept is at least partially derived from Jung’s discussion of this concept.


Memories, Dreams, Reflections is split into distinct points of reflection, whether it be childhood, coming to analytical psychology, the meeting with and diversion from Freud, confrontation with the unconscious, the Bollingen Tower, experiences of other cultures, and thoughts on the afterlife. It is not necessarily about the people in Jung’s life, whether it be family or those he may have met throughout his life. Although they are a part of the story, at no point are they placed on centre stage. The book touches on this when we are told:

The finest and most significant conversations of my life were anonymous.

Source: Memories, Dreams, Reflections by Carl Jung (and Aniela Jaffé)

Instead, we get a unique insight into Jung and his world. This includes the idea of being two persons:

Somewhere deep in the background I always knew that I was two persons. One was the son of my parents, who went to school and was less intelligent, attentive, hard-working, decent, and clean than many other boys. The other was grown up–old, in fact–skeptical, mistrustful, remote from the world of men, but close to nature, the earth, the sun, the moon, the weather, a living creatures, and above all close to the night, to dreams, and to whatever “God” worked directly in him.

Source: Memories, Dreams, Reflections by Carl Jung (and Aniela Jaffé)

Something that has me wanting to re-read Donald Winnicott’s work on the true and false self, especially after reading Winnicott’s review.

Writing as guided by the unconscious:

The work is the expression of my inner development; for commitment to the contents of the unconscious forms the man and produces his transformations. My works can be regarded as stations along my life’s way.
All my writings may be considered tasks imposed from within; their source was a fateful compulsion.

Source: Memories, Dreams, Reflections by Carl Jung (and Aniela Jaffé)

This reminds me of something that Jon Hopkins said about writing music. In a conversation with Jamie Lidell, he explained that that he wished that he could ‘choose’ the music he writes. Instead, he argues that we have no choice over what we do, the choice is about what our body gives energy for.

Mistakes involved in the path of individuation:

When one follows the path of individuation, when one lives one’s own life, one must take mistakes into the bargain; life would not be complete without them. There is no guarantee not for a single moment that we will not fall into error or stumble into deadly peril. We may think there is a sure road. But that would be the road of death. Then nothing happens any longer at any rate, not the right things. Anyone who takes the sure road is as good as dead.

Source: Memories, Dreams, Reflections by Carl Jung (and Aniela Jaffé)

This is a reminder of the importance of mistakes and forgiveness.

Life after death through the unconscious:

If there were to be a conscious existence after death, it would, so it seems to me, have to continue on the level of consciousness attained by humanity, which in any age has an upper though variable limit.

Source: Memories, Dreams, Reflections by Carl Jung (and Aniela Jaffé)

I wonder what Jung would make of the modern world and artificial intelligence? Does our data consumed and sitting on serves somehow add or augment the ‘collective unconscious’?

Loneliness as the inability to communicate what is important:

Loneliness does not come from having no people about one, but from being unable to communicate the things that seem important to oneself, or from holding certain views which others find inadmissible.

Source: Memories, Dreams, Reflections by Carl Jung (and Aniela Jaffé)

With so many spaces to broadcast, I wonder if the challenge with ‘communication’ is our ability to not only speak, but also to listen?


It is interesting reading some of the reviews and comments about Memories, Dreams, Reflections online, which explain that some of the medical practices have become obsolete. In addition to this, I was not sure what to make of some of the cultural observations.

Jung does not though come across as a man who is simply enjoying going to these countries, but sometimes can come off as a bit presumptious about the way he deals with other cultures. He can seem like he thinks he is above them, or their better and this does nothing for the autobiography.

Source: Book Review: “Memories, Dreams and Reflections” by Carl Jung by Annie Kapur

All in all though, this book offers an insightful introduction to Jung. Although it does not necessarily provide a clear summary of his work, it does provide context for it. It also includes some letters in the appendix between Jung and Freud, which I am going to assume had not been published before.

The power of query in sorting out data in Sheets

The team I am a part of was recently asked if there was any work that we could possibly automate to save time and effort. Leaving aside the strange question of asking the boiling frog how the boiling process might be sped up, I was left thinking about the limits of automation when what is trying to be automated is not always straight-forward and often involves contextual knowledge. My suggestion in response was not to automate the task, but possibly automate the process.

Ever since I have stepped into my new role in the technical team, I have made a conscious effort to capture everything that I have learnt and store it in a canonical space as one of the issues I had early on was that it was too difficult to find anything. This then allows me to produce repeatable processes. (In some respects I imagine that organising information in this manner actually makes it easier for an AI assistant to support?)

In addition to automating the day-to-day, I recently built out a spreadsheet to help with a particular task of cleaning up duplicate records across the database. Given a list of hundreds of records to review, I felt that saving any time and effort was going to make a difference. My thought was to create a series of DECLARE statements to use to query the database, then copy the raw data, review it, create tasks for the relevant teams and produce a summary of the records changed and those that remained the same.

Doubling Down on the Declare

One of the issues I initially found when producing my query was updating all the variables each time. It therefore occurred to me that rather than changing the names and various IDs each time, which was always fiddly and prone to error, I could create a series of declare statements that I updated with the SQL statement each time based on a list of users and issues presented in a spreadsheet. This allowed me to leave the rest of the query the same:

="
DECLARE @Sur1 VARCHAR(30) = '"&D2&"'
DECLARE @Sur2 VARCHAR(30) = 'SUR2'
DECLARE @Pre1 VARCHAR(30) = '"&E2&"'
DECLARE @Pre2 VARCHAR(30) = 'PRE2'
DECLARE @Login1 VARCHAR(30) = '"&H2&"'
DECLARE @Login2 VARCHAR(30) = 'CECVIDS\UPN2'
DECLARE @Login3 VARCHAR(30) = 'CECVIDS\UPN3'
DECLARE @CNUM1 VARCHAR(15) = 'CX'
DECLARE @CNUM2 VARCHAR(15) = 'CX'
DECLARE @CNUM3 VARCHAR(15) = 'CX'"

In addition to this, I stripped out any additional information that was included within a text string of possible information and had this represented on unique lines after the initial list:

--"&IFNA( JOIN( CHAR(10) & "--", ARRAYFORMULA( LEFT( QUERY( TRIM( MID( SUBSTITUTE(J2, ":", REPT(" ", LEN(J2))), LEN(J2) * ROW(INDIRECT("1:" & LEN(J2))) - LEN(J2) + 1, LEN(J2) ) ), "SELECT * WHERE Col1 LIKE 'C%'" ), 16 ) ) ) )&""

The output then looks something like this:

--"
DECLARE @Sur1 VARCHAR(30) = 'Davis'
DECLARE @Sur2 VARCHAR(30) = 'SUR2'
DECLARE @Pre1 VARCHAR(30) = 'Aaron'
DECLARE @Pre2 VARCHAR(30) = 'PRE2'
DECLARE @Login1 VARCHAR(30) = 'CECVIDS\01234567'
DECLARE @Login2 VARCHAR(30) = 'CECVIDS\UPN2'
DECLARE @Login3 VARCHAR(30) = 'CECVIDS\UPN3'
DECLARE @CNUM1 VARCHAR(15) = 'C0000001'
DECLARE @CNUM2 VARCHAR(15) = 'CX'
DECLARE @CNUM3 VARCHAR(15) = 'CX'

--CECVIDS\30000001
--C1000001"

I commented the additional information out so that I could use it if required or simply run the updated script as it was. It also meant that I did not have to delete the quotations at the start and end that come across when copying an output from Google Sheets with multiple lines) every time. I did tinker with how I could get the additional logins and numbers to prepopulate within the declare statement, but hit a wall as the number of additional values was not consistent.

Streamlining the Analysis and Fix

Once I had all the records in the system, I then had to organise them into those to be retained and those to be updated. The first step was to create a conditional formulas using the following custom formula that quickly highlighted possible duplicate records:

=COUNTIF(C:C,C1)>1

This allowed me to then reorder the list and review the rest of the details.

Once I had cleaned up the data, whether this be sorting between records to be merged / deleted and retained, or deleting records that were not actually duplicates, I then created a summary of the data.

Often with my spreadsheets I create a config tab where I create various formulas that I can reference. This is particular important when using variable NAMED RANGES.

I’d always believed that Named Ranges in Google Sheets would only accept static ranges and not formulas like Excel, which makes them less powerful of course. (Check out the Named ranges sidebar and you’ll see what I mean. No place to add formula-based dynamic ranges in there.)

However, there is a clever trick using the INDIRECT function that DOES allow dynamic named ranges!

Source: How to create dynamic named ranges in Google Sheets by Ben Collins

As I wanted to make this spreadsheet somewhat self-contained, I decided to just use the first row of the sheet, which I then hid. This included a summary of the different community names in a single cell:

=LEFT( JOIN("/", UNIQUE(QUERY(F2:F, "SELECT F WHERE NOT F = 'NameExternal'"))), LEN(JOIN("/", UNIQUE(QUERY(F2:F, "SELECT F WHERE NOT F = 'NameExternal'"))) - 1 )

A summary of staff numbers in a single cell:

=IFNA( JOIN(",", UNIQUE(QUERY(G2:G, "SELECT G WHERE G LIKE 'C%' ORDER BY G ASC"))) )

And a summary of login records in a cell

=IFNA( JOIN(", ", UNIQUE(QUERY(H2:H, "SELECT H WHERE H LIKE 'CEC%'"))) )

Each was given a Named Range that could be easily referred to in other formulas.

Initially, I created a formula which combined these different attributes, but I soon realised I then had to explain what sort of duplicate it was. Overall, there are four types of duplicate records:

  1. User with multiple staff numbers and login records
  2. User with multiple login records
  3. User with multiple staff numbers
  4. User with multiple community records

To accommodate these differences, I created a formula using the named ranges to combine the IF and AND formulas:

=IF( AND( IFNA(COUNTUNIQUE(QUERY(G2:G, "SELECT G WHERE G LIKE 'C%' ORDER BY G ASC"))) > 1, IFNA(COUNTUNIQUE(QUERY(H2:H, "SELECT H WHERE H LIKE 'CEC%' ORDER BY H"))) > 1 ), F1 & " has multiple staff numbers: " & G1 & " and multiple login records: " & SUBSTITUTE(H1, "IDS\\", ""), IF( AND( IFNA(COUNTUNIQUE(QUERY(G2:G, "SELECT G WHERE G LIKE 'C%' ORDER BY G ASC"))) = 1, IFNA(COUNTUNIQUE(QUERY(H2:H, "SELECT H WHERE H LIKE 'CEC%' ORDER BY H"))) > 1 ), F1 & " ( " & G1 & " ) has multiple login records: " & SUBSTITUTE(H1, "IDS\\", ""), IF( AND( IFNA(COUNTUNIQUE(QUERY(G2:G, "SELECT G WHERE G LIKE 'C%' ORDER BY G ASC"))) = 1, IFNA(COUNTUNIQUE(QUERY(H2:H, "SELECT H WHERE H LIKE 'CEC%' ORDER BY H"))) = 1 ), F1 & " ( " & G1 & " / logins: " & SUBSTITUTE(H1, "IDS\\", "") & " ) has duplicate community records", IF( AND( IFNA(COUNTUNIQUE(QUERY(G2:G, "SELECT G WHERE G LIKE 'C%' ORDER BY G ASC"))) > 1, IFNA(COUNTUNIQUE(QUERY(H2:H, "SELECT H WHERE H LIKE 'CECV%' ORDER BY H"))) = 1 ), F1 & " logins: " & SUBSTITUTE(H1, "IDS\\", " ") & " has multiple staff numbers: " & G1, FALSE ) ) ) )

Using the IF and AND combination allowed me to quickly and easily produce a summary of the issue in one sentence to raise in our incident management system.

In addition to this, I created a SWITCH formula to provide the server listener name, as opposed to the IP address provided in the data export:

=IFNA( SWITCH( B2:B, "IP Address 1", "Listener Name 1", "IP Address 2", "Listener Name 2", "IP Address 3", "Listener Name 3", "IP Address 4", "Listener Name 4", "IP Address 5", "Listener Name 5" ) )

Summarising the Changes

In addition to the summary of issue, I also created a summary of changes that I could easily copy and paste into the incident notes once I had cleaned up the various duplicates. Using a series of NAMED RANGES, I initially started with this query:

={ "", "", "", "", "", ""; "Record(s) updated / merged:", "", "", "", "", ""; "", "", "", "", "", ""; SORT(Indirect(Table1)); "", "", "", "", "", ""; "Record(s) retained:", "", "", "", "", ""; "", "", "", "", "", ""; SORT(Indirect(Table2)) }

However, I found this to be cumbersome to copy and paste a number of cells. Therefore, on further investigation, I found this guide for combining columns.

So if we wanted to get all the elements within our column wrapped into one row we would just need to change that third parameter to the maximum height of our columns. We could use the

ROWS()

function to capture this number.

Merge Two Columns Using QUERY: Google Sheets (Step By Step Example) by Ryan Sheehy

In order to display all the results in one cell, I transposed the data to get the outcome I was after. I also added a few SUBSTITUTES to update some of the headings.

=SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( JOIN(CHAR(10), TRANSPOSE( QUERY( TRANSPOSE({ "Record(s) updated / merged:", "", "", "", "", ""; "", "", "", "", "", ""; SORT(Indirect(Table1) & REPT(" ", 3)); "", "", "", "", "", ""; "Record(s) retained:", "", "", "", "", ""; "", "", "", "", "", ""; SORT(Indirect(Table2) & REPT(" ", 3)) }), , ROWS(TRANSPOSE({ "", "", "", "", "", ""; "Record(s) updated / merged:", "", "", "", "", ""; "", "", "", "", "", ""; SORT(Indirect(Table1)); "", "", "", "", "", ""; "Record(s) retained:", "", "", "", "", ""; "", "", "", "", "", ""; SORT(Indirect(Table2)) })) ) )), "NameExternal", "Name" ), "ExternalSystemRegistrationNumber", "CNumber" ), "IdamLogin", "IDAM/UPN" )

As the number of records varied, I had to create a NAMED RANGE for records updated / merged and records retained and used the INDIRECT formula to refer to them.

The first range Table1 contained the records to be updated / merged. The formula used was:

="C2:H"&MATCH("Database Name", Indirect(DThreeD), 0) + ROW(INDIRECT(DThree)) - 2

The second range Table2 contained the records to be retained. The formula used was:

=ADDRESS( MATCH("Database Name", INDIRECT(DUPLICATE!DThreeD), 0) + ROW(INDIRECT(DUPLICATE!DThree)) - 1, COLUMN(INDIRECT(DUPLICATE!DThree)) ) & ":I" & COUNTA(QUERY(D2:D, "SELECT D WHERE D IS NOT NULL")) + 1

As I was continually moving and deleting rows around in the spreadsheet, I found that these formulas kept on breaking these named ranges, so that is why I created two further NAMED RANGES to make the cell references static.

This was formula used for DThree:

"$D$3"

And this was formula used for DThreeD:

="$D$3:$D"

Again, all this ‘magic’ with NAMED RANGES was hidden in the top row of the spreadsheet.


There has been a lot spoken at my workplace lately about what it means to be proud. Personally, fixing 500+ duplicate records is not necessarily something I am ‘proud’ of or excited by, certainly not something that I would post on LinkedIn. (Note, I never post anything on LinkedIn.) However, I am proud of my ability to improve processes and identify ways in which my actions can make it easier for others to do their job. The challenge I often have is being able to hand over such solutions to others. Most prefer their own way of working, even if that means complaining that they do not have time for this or that. In the end, I feel that the challenge is finding the right balance between improving a process and creating more work.

For those interested, here is a link to the spreadsheet.

The only path to expertise, as far as anyone knows, is practice. Daniel Willingham - Why Don't Students Like School

A few years ago, I wrote a post exploring the act of finding the right method for the moment and working on approaches like they were ‘pedagogical cocktails‘:

Every teacher is different – we just choose to deny it. Even though we may practise a certain pedagogy, it does not necessarily mean that it will be the same as the next person. Rather, everyone has their our own intricacies and twists on the way they do things. What then starts to matter more is the practitioner rather than the pedagogy.

Source: So Which Pedagogical Cocktails Are Drinking Today? by Aaron Davis

My thought was that what mattered is actually going beyond the what and how to address the why. I was thinking about this a bit after reading Daniel Willingham’s book Why Don’t Students Like School?: A Cognitive Scientist Answers Questions About How the Mind Works and What It Means for the Classroom. I was recommended the book by someone and noticed that it was available in Audible so I dived in.

The book is broken into ten principles:

  1. People are natually curious, but they are not naturally great thinkers. Therefore, think of to-be-learned material as answers, and take the time necessary to explain the students the questions.
  2. Factual knowledge prcedes skill. Therefore, it is not possible to think well on a topic in the absence of factual knowledge about the topic.
  3. Memory is the residue of thought. Therefore, The best barometer for every lesson plan is “Of what will it make the students think?”
  4. We understand new things in the context of things we already know. Therefore, always make deep knowledge your goal, spoken and unspoken, but recognise that shallow knowledge will come first.
  5. Proficiency requires practice. Therefore, think carefully about which material students need at their fingertips, and practice it over time.
  6. Cognition is fundamentally different early and late in training. Therefore, strive for deep understanding in your students, not the creation of new knowledge.
  7. Children are more alike than different in terms of learning. Therefore, think of lesson content, not students differences, driving decisions about how to teach.
  8. Intelligence can be changed through sustained hard work. Therefore, always talk about successes and failures in terms of process, not ability.
  9. Technology changes everything … but not the way you think. Therefore, don’t assume you know how new technology will work in the classroom.
  10. Teaching, like any complex cognitive skill, must be practiced. Therefore, improvement requires more than experience; it also requires conscious effort and feedback.

Willingham explains that each of these principles was selected based on four criteria, that they are true all all the time, based on a great deal of data, have a sizable impact on performance and had to be clear about what was involved for a teacher. This focus on practicality is emphasised by the implications for the classroom provided at the end of each chapter.

There were a number of ideas that left me thinking and reflecting. This includes that logical thinking is often really just memory retrieval:

When we see someone apparently engaged in logical thinking, he or she is actually engaged in memory retrieval.

Source: Why Don’t Students Like School by Daniel Willingham

The more dots you have, the more you can retain new information in the future:

This final effect of background knowledge – that having factual knowledge in long-term memory makes it easier to acquire still more factual knowledge – is worth contemplating for a moment. It means that the amount of information you retain depends on what you already have.

Source: Why Don’t Students Like School by Daniel Willingham

Unstructured ‘discovery’ learning can lead to incorrect discoveries:

An important downside, however, is that what students will think about is less predictable. If students are left to explore ideas on their own, they may well explore mental paths that are not profitable. If memory is the residue of thought, then students will remember incorrect “discoveries” as much as they will remember the correct ones.

Source: Why Don’t Students Like School by Daniel Willingham

Effortless is a myth, “practice makes progress:”

Replace the mantra “practice makes perfect” with “practice makes progress.”

Source: Why Don’t Students Like School by Daniel Willingham

Everyone makes mistakes, the question is often how you respond.

I didn’t see the Big Boss very often, and I was pretty intimidated by him. I remember well the first time I did something stupid (I’ve forgotten what) and it was brought to his attention. I mumbled some apology. He looked at me for a long moment and said, “Kid, the only people who don’t make mistakes are the ones who never do anything.” It was tremendously freeing – not because I avoided judgment for the incident, but it was the first time I really understood that you have to learn to accept failure if you’re ever going to get things done. Basketball great Michael Jordan put it this way: “I’ve missed more than nine thousand shots in my career. I’ve lost almost three hundred games. Twenty-six times I’ve been trusted to take the game-winning shot and missed. I’ve failed over and over and over again in my life. And that is why I succeed.”

Source: Why Don’t Students Like School by Daniel Willingham

It is interesting reading through the reviews online for the book. Many praise its practicality, which I found helpful too. The issue I had was with the research and the statement of fact. For example, one commentator put it as follows:

The strength of this book is that it contains good educational advice (mostly); its weakness is its constant reference to a theory in science that I find fundamentally unconvincing.

Source: All it is cracked up to be? Some notes on Daniel Willingham’s ‘Why Don’t Students Like School?’

At the very least, I felt that it was a useful book to read as a provocation in and out of the classroom. As with various instructional models, these books offer a reminder of aspects to stop and consider. However, it also left me wondering about the rise of science as discussed by Hannah Arendt in The Human Condition:

Nothing indeed could be less trustworthy for acquiring knowledge and approaching truth than passive observation or mere contemplation. In order to be certain one had to make sure, and in order to know one had to do. Certainty of knowledge could be reached only under a twofold condition: first, that knowledge concerned only what one had done himself—so that its ideal became mathematical knowledge, where we deal only with self-made entities of the mind—and second, that knowledge was of such a nature that it could be tested only through more doing.

Source: The Human Condition by Hannah Arendt