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

HansVogelaar 

Waybill cost always be included in sum even duplicate rows. But Freight column not.

Explanation: One of our computer system is generating this data and including freight as duplicate. How? (A lorry hired may have multiple waybills for one customer for same destination).

(Invoice issued to customer and this invoice may have multiple waybills and for those invoice appearing duplicate freight. We want to get rid of duplicate freight in our summary.)

amjadinsaudia 

Just to make sure: should waybill cost be included for duplicate rows or just counted once?

HansVogelaar 

How is your pivot calculated, that is not taking duplicate value in freight column? please explain. As I have large set of data and I have to make pivot on it.

Second thing, waybill cost duplicate is also not calculated but we need this to be calculated even duplicate. Can you please see this possibility also.

Regards,

Amjad

amjadinsaudia 

See the attached version.

HansVogelaar 

Hi Mr. Hans.

I am writing to you after long time.

I have data as attached. Three columns are important for me. lhc no., freight, waybill cost.

In same LHC no. if freight is appearing duplicate then not to sum otherwise sum.

Please advise how to do this?

I have created a pivot and its sum is including with duplicate value. Duplicate value is yellow highlighted in the data.

Regards,

amjadinsaudia 

You're welcome!

HansVogelaar 

mathetes 

I have achieved my goal 100% with the help Mr. Hans. I would like to thanks to him and Mr. Mathetes for the kind support on this tech forum.

With my regards,

Amjad

HansVogelaar 

noted the message and ack with thanks.

amjadinsaudia 

According to Define and solve a problem by using Solver :

"You can specify up to 200 variable cells."

So your problem is too complicated for Solver.

HansVogelaar 

Now I added the actual data which goes to the ending range of line no. 231.

In this scenario Excel is replying "Too many variable cells"

File is attached for quick reference please.

Awaiting for a reply.

HansVogelaar 

Thanks for detailed reply and I solved it.

I did and accomplish it with a name error.

I am using office 2016 and might be filter function is not available this version.

However I am able to corresponding value in A with the help of 1 in column B.

Any comments?

amjadinsaudia 

Open the workbook that I attached to my previous reply.

Select cell D2.

On the Data tab of the ribbon, click Solver.

Select the 'Value of' option button, and enter 3232,83 in the box next to it.

Click in the 'By Changing Variable Cells' box, then point to B2:B22.

Click the Add button.

In the 'Add Constraint' dialog, click in the 'Cell Reference' box, then point to B2:B22

Select bin from the drop-down next to it.

Click OK.

In the 'Select a Solving Method' drop-down, select 'Simplex LP'.

The Solver Parameters dialog should now look like this:

Click Solve.

After a while, the 'Solver Results' dialog should appear.

Make sure that 'Keep Solver Solution' is selected, then click OK.

In column B, a 1 indicates that the corresponding number in column A is included in the sum.

Column E displays the values that are included.

amjadinsaudia 

Macros don't work in Excel Online.

The macro runs in the desktop version of Excel, but as I told you before, it doesn't work correctly. SolverAdd doesn't add a constraint. I don't know how to solve this.

Running Solver manually does work.

HansVogelaar

Is this file to be open in excel online as the function filter is part of online version only and then how to run macro in it?

Or if this to be open in offline 2016 then I am facing with an name error in column E.

Awaiting for a reply please!