Question

Hi everyone I need some help.

  • 30 June 2022
  • 1 reply
  • 5 views

So I have a excel file where Column AA is Reference No, AB is Commission Amt and AD is Customer name. Now for AA reference no U326165744, I have to make Column AB sum of (41.18+5.35+0) because there column AD Customer name (LARA) is same. Also for Reference U946536964 it should sum (10.44+0) based on Column AD SIGMA (not 0.52 coz its SIGMA2)How would we do that in AA?

image


1 reply

@Isaa Bella​  If you can get list/table of distinct Customer Name and their corresponding Reference, then

 

  1. connect to excel as db
  2. loop through the list/table
  3. query: select * from table where customer name = <customer name from list> (You shall get output rows for that customers only)
  4. loop through dataset rows, take sum of all amount
  5. update query: update table set Commission Amt = 'sum' where reference = <ref from list> and customer name = <customer name from list>

 

Hope this may help!

 

Reply