Excel: Dividing a column into a table with multiple criteria

Question

I have some barcode scanner which lists out the codes on a column in Excel.

The goal is to divide the original column into a table as per the test cases below. Does anyone have an idea in how to achieve it?

Table Before:

A
XXID0081
45011
654000
2
654001
3
654002
4
XXID0082
45012
785902
2
3
XXID0083
45013
888981
2
888982
3

Table After:

A	B	C	D
XXID0081	45011	654000	2
XXID0081	45011	654001	3
XXID0081	45011	654002	4
XXID0082	45012	785902	5
XXID0083	45013	888981	2
XXID0083	45013	888982	3

I have tried using the Power query but wanted to do it with excel functions. Tried using the WRAPROWS

=WRAPROWS(A2:A300,4)

not working. There is one more problem when the qty is twice for A,B,C column it will be added up. for example as in the screenprints.

Please ask away if you have any questions. I will post the power query code if needed. TIA.

let
    Source = Excel.CurrentWorkbook(){[Name="Table6"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"A", type text}}),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Custom", each if Text.StartsWith([A], "XXID") then [A] else null),
    #"Filled Down" = Table.FillDown(#"Added Conditional Column",{"Custom"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each not Text.StartsWith([A], "UID")),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Custom.1", each if Text.Length([A]) = 5 then [A] else null),
    #"Filled Down1" = Table.FillDown(#"Added Custom",{"Custom.1"}),
    #"Added Custom1" = Table.AddColumn(#"Filled Down1", "Custom.2", each if Text.Length([A]) = 6 then [A] else null),
    #"Filled Down2" = Table.FillDown(#"Added Custom1",{"Custom.2"}),
    #"Filled Up" = Table.FillUp(#"Filled Down2",{"Custom.2"}),
    #"Added Custom2" = Table.AddColumn(#"Filled Up", "Custom.3", each if Text.Length([A]) < 4 then [A] else null),
    #"Filtered Rows1" = Table.SelectRows(#"Added Custom2", each [Custom.3] <> null and [Custom.3] <> ""),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows1",{"Custom.3"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Custom", "Custom.1", "Custom.2", "A"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Reordered Columns",{{"Custom", type text}, {"Custom.1", Int64.Type}, {"Custom.2", Int64.Type}, {"A", type number}}),
    #"Grouped Rows" = Table.Group(#"Changed Type1", {"Custom", "Custom.1", "Custom.2"}, {{"A", each List.Sum([A]), type nullable text}}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Grouped Rows",{{"Custom", type text}, {"Custom.1", Int64.Type}, {"Custom.2", Int64.Type}, {"A", type number}})
in
    #"Changed Type2"

I would advise using data identifiers in the barcode if possible, so you know which data is which by the identifier. — P.b, May 21 '23 at 21:36
yeah. not able to make that work using the wraprows. any ideas how to make that work — Γουλιέλμος, May 21 '23 at 21:39
Will `A` always start with the same characters and same length?, Will `B` always be 5 digits, will `C` always be 6 digits? Could `D` add up to 5 or 6 digits? We need more info to be sure we can help — P.b, May 21 '23 at 21:43
Thats correct.x for now A is always 8, B is always 5, C is always 6 but d can be add up to 1 to 6 digits; mostly it will be 1 till 3. — Γουλιέλμος, May 21 '23 at 22:15
If the code is going through the list of values, and the previous value was a D, then the next value is a 5 or 6 digit number, how would you know if it's D vs B or C? — JSmart523, May 22 '23 at 07:12
I think the Power query assumes length of any individual D is less than 4? — Tom Sharpe, May 22 '23 at 07:47

score 4 · Accepted Answer · answered May 22 '23 at 10:47

4

Try:

=LET(arr,VSTACK(TOCOL(A:A,1),"Finish"),n,ROWS(arr),seq,SEQUENCE(n),seqA,SEQUENCE(n-1),
WRAPROWS(DROP(REDUCE("",seqA,LAMBDA(all,curr,
LET(length,LEN(INDEX(arr,curr)),lenNext,LEN(INDEX(arr,curr+1)),IF(AND(length<=4,lenNext>4),VSTACK(all,
      XLOOKUP(1,(LEN(arr)=8)*(seq<curr),arr,,,-1),
      XLOOKUP(1,(LEN(arr)=5)*(seq<curr),arr,,,-1),
      XLOOKUP(1,(LEN(arr)=6)*(seq<curr),arr,,,-1),
      SUM(FILTER(arr,(seq<=curr)*(seq>XMATCH(1,(LEN(arr)>4)*(seq<curr),,-1))))
),all)))),1),4))

answered May 22 '23 at 10:47

Tom Sharpe

30,727
5
24
37

1

This almost looks simple, the way you posted this. Very inventive, especially the part where you don't update the 'REDUCE' value if the length of the current Reduce value is not up to 4 characters and the next is no greater than 4, otherwise find the first values from that position and up by length conditions. Inspiring. – P.b May 22 '23 at 11:46

JSmart523 · Answer 2 · 2023-05-26T17:59:08.263

(Holy moly Tom Sharpe's answer is great! And already accepted. But since I had written this already I thought I'd contribute it.)

I would use List.Accumulate.

let
  // Table6's A column as a list
  Source = Excel.CurrentWorkbook(){[Name="Table6"]}[Content][A],
  #"Accumulate Values" = List.Accumulate(
    Source,
    // NextRow is the row we're building
    // Rows is a list of rows we've built
    [
      NextRow = {},
      Rows = {}
    ],
    (_, v) => let
      vnum = try Number.From(v) otherwise null,
      NextRowCount = List.Count([NextRow]),
      ThisColNum = if (vnum is null) then
        // not a number so must be column A
        0
      else if vnum < 10000 then
        // Assume Qty (column D) if less than 5-digit number
        3
      else if vnum < 100000 then
        // Column B if 5-digit number
        1
      else
        // Column C
        2,
      ret = if ThisColNum = NextRowCount then
        // Append v to row we're building
        _ & [NextRow = [NextRow] & {vnum ?? v}]
      else if NextRowCount <> 4 then
        // if v isn't the next column to append to the row we're building then we should have a complete row!
        error Error.Record("Bad Values", "NextRow incomplete", [state=_, value=v])
      else if ThisColNum = 3 then
        // another quantity. Add to column D
        _ & [
          NextRow = List.FirstN([NextRow], 3) & {[NextRow]{3} + vnum}
        ]
      else
        // Commit row we've built, start building new row
        [
          NextRow = List.FirstN([NextRow], ThisColNum) & {vnum ?? v},
          Rows = [Rows] & {[NextRow]}
        ]
      in
        ret
  ),
  // Get all the rows we've built
  Rows = if List.Count(#"Accumulate Values"[NextRow]) = 4 then
    #"Accumulate Values"[Rows] & {#"Accumulate Values"[NextRow]}
  else
    error Error.Record("Bad Values", "Last NextRow incomplete. Last value not quantity?", #"Accumulate Values"),
  Table = #table(
    type table [A=text, B=Int64.Type, C=Int64.Type, D=Int64.Type],
    Rows
  )
in
  Table

Edited after debugging. I'd originally entered it via phone at night, which is why I originally had ended with "Caveat: not debugged", since I was just showing how I would have done it. Also changed code to not assume that first four Table6 values would be "A", "B", "C", and "D" values in that order. — JSmart523, May 26 '23 at 18:03
(Also lied to StackOverflow by specifying code was JavaScript, because mostly-correct syntax highlighting of code is better than no highlighting at all.) — JSmart523, May 26 '23 at 18:06

Excel: Dividing a column into a table with multiple criteria

2 Answers2