how to group data with criteria 2 columns.

[jamiguel77]

hi all

i have this data...

A1="BA01", B1="15-01-2018", C1="5"
A2="BA01", B2="15-01-2018", C2="7"
A3="BA01", B3="15-01-2018", C3="15"
A4="BA01", B4="19-01-2018", C4="8"

i need get this result in other sheet:


A1="BA01", B1="15-01-2018", C1="27"
A2="BA01", B2="19-01-2018", C2="8"


how to do this?

thanks

 

[sandy666]

using PowerQuery (Get&Transform)

ID	Date	Value		ID	Date	Count
BA01	15/01/2018​	5​		BA01	15/01/2018​	27​
BA01	15/01/2018​	7​		BA01	19/01/2018​	8​
BA01	15/01/2018​	15​
BA01	19/01/2018​	8​

example file

 

[jamiguel77]

thanks for answer, how to enable: PowerQuery or is a separated program?

thanks...

 

[sandy666]

this is free add-in from MS site for excel 2010/2013 , built-in = 2016 and higher

edit:
not for Mac

 

[jamiguel77]

downloading.....

 

[sandy666]

for future
MS Power Query for Excel Help

 

[jamiguel77]

sandy666 thanks, is possible share the steps here for get the result?

much thanks.

 

[sandy666]

You can see steps selecting Show Queries from the Data tab (or similar, I don't remeber old layout of PQ) then double click on the table there

but this is simple in this case
after load source table to PQ Editor
select two first columns
then Group and Aggregation: SUM by Value

 

[jamiguel77]

ok, thanks, downloading your excel file.

thanks

 

[sandy666]

you are welcome

nice to see you Like it
Let me know if you've any question