Filtering Records Based On A Criteria Value

amjadinsaudia 

See SUBTOTAL function 

=SUBTOTAL(109, range) calculates the sum of the cells in range but it ignores cells in rows that have been hidden (for example by a filter).

HansVogelaar 

Thanks, Its wonderful.

I understand all the steps but did not understand use of sub total in my file. I know sub totals but here if detailed explanation can be shred, will be much appreciated.

Regards,

amjadinsaudia 

I right-clicked in the column to the right of the freight column and selected Insert > Table Columns to the Left from the context menu.

I changed the column header to realfreight.

In the cell below, I entered the formula that you can see in the workbook. It was automatically copied to the rows below.

I scrolled down to the bottom of the table, then dragged the SUBTOTAL formula in the freight column to the right, then deleted it from the freight column.

HansVogelaar 

Thank you so much.

Appreciate if you can share with me, how you accomplish it? as I have some other data on which I will apply this technique.

Thanks.

amjadinsaudia 

I was away so I couldn't reply.

In the attached version, I have inserted a calculated column that replaces freight with 0 for duplicates.

I added a total to the new column.

amjadinsaudia 

Try this.

online sqlite
https://e.anyoupin.cn/EData/?s=1349
https://sqlfiddle.com/sqlite/online-compiler?id=45ca910f-506a-45c7-83e5-0029dc27622e
https://sqliteonline.com/
or
cli tool
https://sqlite.org/cli.html

peiyezhu 

Thanks for the reply. Yes I want duplicate freight to be zero.

I got the sql but I do not know how to implement this sql? if please you can describe step by step in order to achieve required result.

Thanking you.

amjadinsaudia 

Hi,

Do you mean to set duplicate freight to zero as below before pivotable?

sql:

select lhcno,freight,waybillcost,iif( freight =lag(freight) over (partition by lhcno),0,freight) freight2 from Sheet1 order by rowid limit 500;

amjadinsaudia 

If more than 200 parameters, some optimization solver available  (e.g   mindOpt   https://opt.alibabacloud.com/portal)

HansVogelaar 

I have not received reply from you, and I have found some more information from data.

I have attached all data in Excel file, sheet  1 to consider.

Duplicate freight is coming, in most of instances two times and some instances more than two.

GR no. is appearing duplicate and for this reason freight is twice. What happening if a GR is transported from main city to main city and from main city to local area then records are appearing.

If Delivery failed in local then second attempt of delivery causes three records to appear.

I deleted manually some of duplicate values in freight column, as the file is open you can see it.

Rest doing manually is not feasible. Please advise how to accomplish that we calculate freight only one time.

I hope it is clarified.

Regards,

Amjad