min and max date

[Imran Azam]

HI GUYS,

I am trying to get the min and max date for a customer from the data below


[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]Name[/TD]
[TD]item[/TD]
[TD]qty[/TD]
[TD]start date[/TD]
[TD]end date[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Alan[/TD]
[TD]S[/TD]
[TD]3[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl65, width: 75, align: right"]01/09/2013[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]31/12/2014[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Alan[/TD]
[TD]S[/TD]
[TD]5[/TD]
[TD]01/11/2015[/TD]
[TD]31/12/2016[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Alan[/TD]
[TD]S[/TD]
[TD]2[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl65, align: right"]01/01/2016[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]31/12/2017[/TD]
[/TR]
</tbody>[/TABLE]

i want so show the below

which is the name the item , sum of qty and min start date and max end date


[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]item[/TD]
[TD]QTY[/TD]
[TD]start date[/TD]
[TD]end date[/TD]
[/TR]
[TR]
[TD]Alan[/TD]
[TD]S[/TD]
[TD]10[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl65, width: 75, align: right"]01/09/2013[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]31/12/2017[/TD]
[/TR]
</tbody>[/TABLE]


how can i get the min and max dates?

 

[sandy666]

with PowerQuery (Get&Transform)

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Name[/td][td=bgcolor:#5B9BD5]item[/td][td=bgcolor:#5B9BD5]qty[/td][td=bgcolor:#5B9BD5]start date[/td][td=bgcolor:#5B9BD5]end date[/td][td][/td][td=bgcolor:#70AD47]Name[/td][td=bgcolor:#70AD47]item[/td][td=bgcolor:#70AD47]qty[/td][td=bgcolor:#70AD47]Min date[/td][td=bgcolor:#70AD47]Max date[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Alan[/td][td=bgcolor:#DDEBF7]S[/td][td=bgcolor:#DDEBF7]

3​

[/td][td=bgcolor:#DDEBF7]

01/09/2013​

[/td][td=bgcolor:#DDEBF7]

31/12/2014​

[/td][td][/td][td=bgcolor:#E2EFDA]Alan[/td][td=bgcolor:#E2EFDA]S[/td][td=bgcolor:#E2EFDA]

10​

[/td][td=bgcolor:#E2EFDA]

01/09/2013​

[/td][td=bgcolor:#E2EFDA]

31/12/2017​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Alan[/td][td]S[/td][td]

5​

[/td][td]

01/11/2015​

[/td][td]

31/12/2016​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Alan[/td][td=bgcolor:#DDEBF7]S[/td][td=bgcolor:#DDEBF7]

2​

[/td][td=bgcolor:#DDEBF7]

01/01/2016​

[/td][td=bgcolor:#DDEBF7]

31/12/2017​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"item", type text}, {"qty", Int64.Type}, {"start date", type date}, {"end date", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Name", "item"}, {{"qty", each List.Sum([qty]), type number}, {"Min date", each List.Min([start date]), type date}, {"Max date", each List.Max([end date]), type date}})
in
    #"Grouped Rows"[/SIZE]

 

[Joe4]

For a strictly Excel solution, see this: https://exceljet.net/formula/minimum-value-if