Forum Discussion

rodsan724's avatar
rodsan724
Brass Contributor
Oct 27, 2023

Align the column values with each other so you can easily see what's missing

I have some data on the attached worksheet. This is a side by side comparison of file contents. Column1 is the control, it has everything. The desire is to automate aligning the data so you can easil...
Macros and VBA
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Oct 28, 2023

rodsan724 

I sort of got there with a 365 formula but its not what I would call 'pretty'!

I used a Lambda function to fill the section id down and created an identifier for each data item that could be filtered and sorted.

SetCodeλ(code, line)
=IF(
    ISNUMBER(SEARCH("Section", line)),
    "§" & RIGHT(line, 1) & ".0",
    IF(
        ISNUMBER(SEARCH("Field", line)),
        IF(ISERROR(SEARCH("Data", line)), LEFT(code, 3) & RIGHT(line, 1), LEFT(code, 4) & "xxx"),
        NA()
    )
)

Having set up the identifiers, I VSTACKed them and created the control list by filtering and sorting.  The final step was to look up these controls in each file column and return the original content using XLOOKUP.

Worksheet formula
= LET(
    identifier, DROP(REDUCE("", {1,2,3}, LAMBDA(acc,f,
      HSTACK(acc, SCAN("100", CHOOSECOLS(data, f), SetCodeλ))
    )),,1),
    control, SORT(UNIQUE(TOCOL(identifier,3))),
    result,  DROP(REDUCE("", {1,2,3}, LAMBDA(acc,f,
      LET(
        file,     CHOOSECOLS(identifier, f),
        dataset,  CHOOSECOLS(data, f),
        HSTACK(acc, XLOOKUP(control, file, dataset, "x"))
      )
    )),,1),
    HSTACK(control, result)
  )

I should be able to tidy up the REDUCE / HSTACK which are simply ugly workarounds for a MAP version that returns arrays of arrays, but I have rather run out of steam!

Resources