how to sum value in delimeter on a single cell with date condition selected

pomzung125 · Aug 27, 2023

Hi, Everyone

I would to sum stock count corresponding to sku number with the selected bank tranfer date as a condition.

If I select 10-May-23, it should represent sum stock with corresponding SKU number. Sometime there are 2 SKUs and quantity in a single cell separated with comma. The expected result will be like this

.

If I choose 17-may-23 as bank transfer date, it shall show like this.

I'm struggling with this problem. I don't really know how to deal with.
If anyone could help me, i'm so appreciated.

Thank you so much

alansidman · Aug 28, 2023

Power Query Solution

Book3

A

B

C

D

E

F

G

H

I

1

transfer date

channel

price

begin date

end date

order number

customer payment date

SKU

Quantity

2

10-May-23

Tiktok

2,877.87

5-May-23

9-May-23

577237460215826000

5/5/2023

TK002-03

1

3

577234250735192000

5/5/2023

TK002-02, TK001-02

2,3

4

577234241907755000

5/5/2023

TK002-02, TK001-02

2, 3

5

577234229344897000

5/5/2023

TK002-02, TK001-02

2, 3

6

577249257838906000

5/9/2023

TK002-03

2

7

10/17/2023

Tiktok

463.68

12-May-23

577261085446801000

5/12/2023

TK002-03

1

8

577258955718101000

5/12/2023

LM005-01

1

9

10

11

transfer date

Column1

Sum

12

5/10/2023

TK002-03

3

13

5/10/2023

TK002-02

6

14

5/10/2023

TK001-02

9

15

10/17/2023

TK002-03

1

16

10/17/2023

LM005-01

1

Sheet1

Power Query:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Filled Down" = Table.FillDown(Source,{"transfer date", "channel", "price", "begin date", "end date"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Filled Down",{{"transfer date", type date}, {"channel", type text}, {"price", type number}, {"begin date", type datetime}, {"end date", type datetime}, {"order number", Int64.Type}, {"customer payment date", type datetime}, {"SKU", type text}, {"Quantity", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Table.FromColumns({Text.Split([SKU], ","),Text.Split([Quantity], ",")})),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Column1", "Column2"}, {"Column1", "Column2"}),
    #"Trimmed Text" = Table.TransformColumns(#"Expanded Custom",{{"Column1", Text.Trim, type text}, {"Column2", Text.Trim, type text}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Trimmed Text",{{"Column2", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type1", {"transfer date", "Column1"}, {{"Sum", each List.Sum([Column2]), type nullable number}})
in
    #"Grouped Rows"

pomzung125 · Aug 28, 2023

alansidman said:
Power Query Solution

Book3
A B C D E F G H I
1 transfer date channel price begin date end date order number customer payment date SKU Quantity
2 10-May-23 Tiktok 2,877.87 5-May-23 9-May-23 577237460215826000 5/5/2023 TK002-03 1
3 577234250735192000 5/5/2023 TK002-02, TK001-02 2,3
4 577234241907755000 5/5/2023 TK002-02, TK001-02 2, 3
5 577234229344897000 5/5/2023 TK002-02, TK001-02 2, 3
6 577249257838906000 5/9/2023 TK002-03 2
7 10/17/2023 Tiktok 463.68 12-May-23 577261085446801000 5/12/2023 TK002-03 1
8 577258955718101000 5/12/2023 LM005-01 1
9
10
11 transfer date Column1 Sum
12 5/10/2023 TK002-03 3
13 5/10/2023 TK002-02 6
14 5/10/2023 TK001-02 9
15 10/17/2023 TK002-03 1
16 10/17/2023 LM005-01 1
Sheet1

Power Query:

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Filled Down" = Table.FillDown(Source,{"transfer date", "channel", "price", "begin date", "end date"}), #"Changed Type" = Table.TransformColumnTypes(#"Filled Down",{{"transfer date", type date}, {"channel", type text}, {"price", type number}, {"begin date", type datetime}, {"end date", type datetime}, {"order number", Int64.Type}, {"customer payment date", type datetime}, {"SKU", type text}, {"Quantity", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Table.FromColumns({Text.Split([SKU], ","),Text.Split([Quantity], ",")})), #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Column1", "Column2"}, {"Column1", "Column2"}), #"Trimmed Text" = Table.TransformColumns(#"Expanded Custom",{{"Column1", Text.Trim, type text}, {"Column2", Text.Trim, type text}}), #"Changed Type1" = Table.TransformColumnTypes(#"Trimmed Text",{{"Column2", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type1", {"transfer date", "Column1"}, {{"Sum", each List.Sum([Column2]), type nullable number}}) in #"Grouped Rows"

You're so kind. I'm so appreciated with your solution. Thank you so much.

Anyway I'm still wondering about if there is no power query how we handle this kind of situation.

JEC · Aug 28, 2023

Here a VBA solution which should work on a Mac.

VBA Code:

Sub jec()
 Dim ar, xp, sp, j As Long, jj As Long, jjj As Long, st As Long
 ar = Cells(1).CurrentRegion.Value2
 ReDim sq(UBound(ar), 3)
 
 For j = 2 To UBound(ar)
   st = IIf(ar(j, 1) > st, ar(j, 1), st)
   sp = Split(ar(j, 8), ", ")
   For jj = 0 To UBound(sp)
     For jjj = 0 To UBound(sq)
       xp = Val(Split(ar(j, 9), ", ")(jj))
       If sq(jjj, 3) = st & "|" & sp(jj) Then
         sq(jjj, 2) = sq(jjj, 2) + xp
         Exit For
       ElseIf sq(jjj, 0) = "" Then
         sq(jjj, 0) = st
         sq(jjj, 1) = sp(jj)
         sq(jjj, 2) = xp
         sq(jjj, 3) = st & "|" & sp(jj)
         Exit For
       End If
     Next
   Next
 Next
 
 Cells(23, 1).Resize(UBound(sq), 3) = sq
End Sub

Used this layout

Book1

A

B

C

D

E

F

G

H

I

J

1

transfer date

channel

price

begin date

end date

order number

customer payment date

SKU

Quantity

2

10-5-2023

Tiktok

2877.87

45051

45055

5,77E+17

5-5-2023

TK002-03

1

3

5,77E+17

5-5-2023

TK002-02, TK001-02

2, 3

4

5,77E+17

5-5-2023

TK002-02, TK001-02

2, 3

5

5,77E+17

5-5-2023

TK002-02, TK001-02

2, 3

6

5,77E+17

5-9-2023

TK002-03

2

7

17-10-2023

Tiktok

463.68

45058

5,77E+17

5-12-2023

TK002-03

1

8

5,77E+17

5-12-2023

LM005-01

1

9

10

Sheet1

pomzung125 · Aug 29, 2023

JEC said:
Here a VBA solution which should work on a Mac.

VBA Code:

Sub jec() Dim ar, xp, sp, j As Long, jj As Long, jjj As Long, st As Long ar = Cells(1).CurrentRegion.Value2 ReDim sq(UBound(ar), 3) For j = 2 To UBound(ar) st = IIf(ar(j, 1) > st, ar(j, 1), st) sp = Split(ar(j, 8), ", ") For jj = 0 To UBound(sp) For jjj = 0 To UBound(sq) xp = Val(Split(ar(j, 9), ", ")(jj)) If sq(jjj, 3) = st & "|" & sp(jj) Then sq(jjj, 2) = sq(jjj, 2) + xp Exit For ElseIf sq(jjj, 0) = "" Then sq(jjj, 0) = st sq(jjj, 1) = sp(jj) sq(jjj, 2) = xp sq(jjj, 3) = st & "|" & sp(jj) Exit For End If Next Next Next Cells(23, 1).Resize(UBound(sq), 3) = sq End Sub

Used this layout

Book1
A B C D E F G H I J
1 transfer date channel price begin date end date order number customer payment date SKU Quantity
2 10-5-2023 Tiktok 2877.87 45051 45055 5,77E+17 5-5-2023 TK002-03 1
3 5,77E+17 5-5-2023 TK002-02, TK001-02 2, 3
4 5,77E+17 5-5-2023 TK002-02, TK001-02 2, 3
5 5,77E+17 5-5-2023 TK002-02, TK001-02 2, 3
6 5,77E+17 5-9-2023 TK002-03 2
7 17-10-2023 Tiktok 463.68 45058 5,77E+17 5-12-2023 TK002-03 1
8 5,77E+17 5-12-2023 LM005-01 1
9
10
Sheet1

Thank you so much for your solution

It works like a charm on my MacOS.

JEC · Aug 29, 2023

You're welcome. Cheers!

how to sum value in delimeter on a single cell with date condition selected

pomzung125

New Member

Excel Facts

alansidman

Well-known Member

pomzung125

New Member

JEC

Well-known Member

pomzung125

New Member

JEC

Well-known Member

Similar threads

Forum statistics

Share this page

how to sum value in delimeter on a single cell with date condition selected

pomzung125

New Member

Excel Facts

alansidman

Well-known Member

pomzung125

New Member

JEC

Well-known Member

pomzung125

New Member

JEC

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock