Using formula to count based on certain criteria.

Question

Hello, I'm trying to figure out how to count the number of times a transaction was both Bought and Sold in the same Document Number. I highlighted a few examples that occur.

As you can see, there are transactions where a product is bought then sold (Doc # 111) or it can be sold then bought (Doc # 877) and finally I included an example where a product is bought, sold, and bought again all under the same 432 document number. For Doc # 432, I'm just looking for occurrences where the product was only bought then sold as shown in the highlighted example.

Hopefully I made sense. Thank you!

Answer 1

Hi,

With online sql:

select * from Sheet2;

create temp table aa as

select (select count(*) from Sheet2 where Document_Num like a.Document_Num and Type like 'Bought') x,(select count(*) from Sheet2 where Document_Num like a.Document_Num and Type like 'Sold') y,* from Sheet2 a group by Document_Num;

select Document_Num from aa where x>0 and y>0;

Answer 2

Hi,

Based on the data that you have shared this M code in Power Query works

let

Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content], 

#"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"Quantity", Int64.Type}, {"Transaction Type", type text}, {"Transaction Date", type datetime}, {"Document Number", type number}}), 

#"Grouped Rows" = Table.Group(#"Changed Type", {"Document Number"}, {{"Q", each List.Sum([Quantity]), type nullable number}, {"C", each Table.RowCount(\_), Int64.Type}}), 

#"Filtered Rows1" = Table.SelectRows(#"Grouped Rows", each ([C] = 2)), 

#"Filtered Rows" = Table.SelectRows(#"Filtered Rows1", each [Q] = 0), 

#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Q", "C"}) 

in

#"Removed Columns"

Hope this helps.

Answer 3

Or

select * from Sheet2;

select Document_Num from (

select * from Sheet2 where Type like 'Bought' group by Document_Num) a inner join (

select * from Sheet2 where Type like 'Sold' group by Document_Num) using(Document_Num) group by Document_Num;