← back

Vibe Coding, Hierarchy from Formulas

Created on: 2025-11-25

We are giving an subset of an Excel table and our objective is to "extract" a hierarchy relationship among rows based on provided formulas.

The input is an Nx4 table with the following columns:

We will assume that formulas will involve nested operations, ranges (within the same sheet), and sums.

Example formulas:

Hierarchy Definition

We say that Row X is the parent of Row Y if Y is included in Row X formula. Formulas might use different columns A1:A10, C5 - C6. For hierarchy purposes, we only care about rows, and can disregard the column name. If Row X has parents Row Y and Row Z but Row Y is the children of Row Z, then Row X has parent Row Y and Row Y keeps Row Z as it parent. A row can have only one parent, if multiple parents are found, and there is no relationship between them (as above), we should raise an error.

Examples:

Output

The output should be a Nx1 table where each row has the row number of the parent, or is empty if no parent was found.

Implementation Details

  1. Have a function that given a formula, calculates all of its children (1 function for this)
  2. Map each row number to a list of all of its children
  3. Propagate potential parents to each row (1 function for this)
  4. Resolve any potential "multi-parent", start for the "top of the hierarchy" to prevent issues.

Return the list of parents (if any) for each row.

We expect this to be wrapped as a single function: "detect_hierarchies_from_formula"

OBS: Beware of mixing "the row number column" with the actual row number, which are potentially different!

Testing Guidelines

  1. Create 2 sample tables, one with 5 rows, another with 10. Both should have formulas with mixed letters (B1:B5) and (D7 + D8) in the same table.
  2. One table should have a children with 2 parents, but those parents should be one children of another. Therefore, a solution should exist.
  3. Use pytest and place the tests in tests/transformations/detect_hierarchy
  4. Don't mock any of the functions.

After the fact notes