Help splitting order numbers by character for reconciliation

[GrandMoffReknaw]

Hi all,

In all my years of Excel use, I've never come across such a stupid human-made issue.

I currently need to get an orders report from a supplier and reconcile them, however, they have decided to consolidate orders together on a line (which technically makes sense, since my organisation is also doing the stupid thing of submitting each line as an order). As such, I'm in the middle, and it's my problem to fix... I've attached a very basic version of the issue.

Link to example: https://docs.google.com/spreadsheets/d/1EDeFD1zlE9QVzuY30EwyYcGlFFziBq11/edit?usp=sharing&ouid=10137...

I have another report from my side, which I can use to reconcile (this will be the same as the "should look like" part of the Excel). I was thinking I'd run an Index/Match type scenario to then line up the order numbers with the product code and quantity from the supplier and us at the end. Happy for any ideas.

In summary, I need to:

• Split the order numbers containing "&" and have them double checked against my data to ensure the product and quantity are correct.
• The number of rows from theirs and the output should be the same.
• There are duplicate order numbers, which need to reconcile against my data also.

I also prefer using Formulas, but am also open to Excel 365 scripts ('new macros' as I call them), as I'm trying to build this as a bigger process in Power Automate.

Thanks guys, I have spent the last week on this, thinking I had it, only to have another issue throw itself at me in the process... I just can't understand why anyone would do it this way...

[#2]

@GrandMoffReknaw If you insist on using Excel for the web I can't think of an easy way, but if you can get your hands on the desktop (PC, not Mac) version you can fairly easily transform the "stupid" list into something more useful with Power Query. See attached.

[#3 · Accepted solution]

Hi @GrandMoffReknaw 

Assuming data stored in tblOrders:

in F4

=IF(ISNUMBER(SEARCH(" & ",[@[Order Number]])),
    LET(
      Delim,  "|",
      Value,  SUBSTITUTE([@[Order Number]], " & ", Delim),
      seqLen, SEQUENCE( LEN(Value)+1 ),
      arrStart,
        LET(
          Dvalue, Delim & Value,
          split,  MID(Dvalue, seqLen, 1),
          arr,    (split = Delim) * seqLen,
          FILTER(arr, arr > 0)
        ),
      arrEnd,
        LET(
          valueD, Value & Delim,
          split,  MID(valueD, seqLen, 1),
          arr,    (split = Delim) * seqLen,
          FILTER(arr, arr > 0)
        ),
      arrValues,  MID(Value, arrStart, arrEnd-arrStart),
      valueOccur, COUNTIF(
                OFFSET(tblOrders[[#Headers],[Order Number]],1,0,(ROW()-ROW(tblOrders[#Headers]))),
                [@[Order Number]]
            ),
      INDEX(arrValues, valueOccur)
    ),
    [@[Order Number]]
)

Corresponding sample attached (tested in Excel Web)