Excel - build histogram of adjacent cell text

I have an excel spreadsheet with rectangular sets of cells. Think of them as tiles. A tile has text markings on its borders, and the tiles exist in the spreadsheet with no overlaps. Consider these tiles as an example:

Also consider this example tile set, with the two tiles of the previous example arranged in a certain manner in 2D space. Note that this configures two adjacent cell text instances, namely White - Blue and Green - Gray are adjacent.

Note that it does not make a difference if one counts the adjacent text twice (for example, white and blue are adjacent, but also blue and white).

I would like to automate the creation of a histogram that, within a region of the spreadsheet, looks for adjacent text, and creates some kind of list of which colors appeared adjacent to one another. In the example above, I'd like to obtain as a result that white was adjacent to blue and green was adjacent to gray (not necessarily how many times, but at least that they do appear).

How could I achieve this?

Solution 1:

This can be done using Power Query. Here is a complete example that creates a table listing each occurrence of pairs of adjacent colors, including duplicates. It would take a few more steps to turn it into a frequency distribution table.

Here is my sample of tiles:

Select the range of cells containing all the tiles, including extra empty rows and columns is not a problem. Define a named range for this selection:

Open the Power Query Editor and open a blank query. Open the Advanced Editor and enter the following M code (I have renamed the steps to describe them better):

let
    Source = Excel.CurrentWorkbook(){[Name="Tiles"]}[Content],
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    #"Unpivoted All Columns Except Index" = Table.UnpivotOtherColumns(#"Added Index", {"Index"}, "Attribute", "Value"),
    #"Extracted Digits From Column Numbers" = Table.TransformColumns(#"Unpivoted All Columns Except Index", {{"Attribute", each Text.AfterDelimiter(_, "n"), type text}}),
    #"Changed Data Type Attribute Column" = Table.TransformColumnTypes(#"Extracted Digits From Column Numbers",{{"Attribute", Int64.Type}}),
    #"Added Custom Column Equal to Attribute-1" = Table.AddColumn(#"Changed Data Type Attribute Column", "Custom", each [Attribute] - 1, Int64.Type),
    #"Merged Query With Itself" = Table.NestedJoin(#"Added Custom Column Equal to Attribute-1", {"Index", "Attribute"}, #"Added Custom Column Equal to Attribute-1", {"Index", "Custom"}, "Added Custom", JoinKind.LeftOuter),
    #"Expanded Added Custom" = Table.ExpandTableColumn(#"Merged Query With Itself", "Added Custom", {"Value"}, {"Value.1"}),
    #"Filtered Rows To Keep Only The Color Pairs" = Table.SelectRows(#"Expanded Added Custom", each [Value.1] <> null and [Value.1] <> ""),
    #"Removed Other Columns" = Table.RemoveColumns(#"Filtered Rows To Keep Only The Color Pairs",{"Index", "Attribute", "Custom"})
in
    #"Removed Other Columns"

Here is the result in the Power Query Editor window:

You can process the table further if needed in Power Query (e.g. remove duplicate rows) or load it as it is anywhere in the worksheet.