Jan 31 2022 02:59 PM - edited Feb 03 2022 02:50 PM
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:
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...
Jan 31 2022 11:05 PM
@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.
Feb 02 2022 01:11 AM - edited Feb 02 2022 01:14 AM
Solution
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)
Feb 02 2022 01:11 AM - edited Feb 02 2022 01:14 AM
Solution
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)