Forum Discussion
amjadinsaudia
Oct 10, 2023Copper Contributor
Filtering Records Based On A Criteria Value
My requirement is to filter records based on a criteria value. List of records in Column A (Filter range) Criteria in Column B Output in Column C Criteria is sum of three different ranges i...
HansVogelaar
Oct 18, 2023MVP
See the attached version.
amjadinsaudia
Oct 18, 2023Copper Contributor
- HansVogelaarOct 03, 2024MVP
=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).
- amjadinsaudiaOct 03, 2024Copper Contributor
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,
- HansVogelaarOct 02, 2024MVP
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.
- amjadinsaudiaOct 02, 2024Copper Contributor
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.
- HansVogelaarOct 02, 2024MVP
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.
- peiyezhuOct 01, 2024Bronze Contributor
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 - amjadinsaudiaOct 01, 2024Copper Contributor
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.
- peiyezhuOct 01, 2024Bronze Contributor
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;
- peiyezhuOct 01, 2024Bronze Contributor
If more than 200 parameters, some optimization solver available (e.g mindOpt https://opt.alibabacloud.com/portal)
- amjadinsaudiaSep 30, 2024Copper Contributor
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
- amjadinsaudiaSep 29, 2024Copper Contributor
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.)
- HansVogelaarSep 28, 2024MVP
Just to make sure: should waybill cost be included for duplicate rows or just counted once?
- amjadinsaudiaSep 28, 2024Copper Contributor
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
- HansVogelaarSep 27, 2024MVP
See the attached version.
- amjadinsaudiaSep 26, 2024Copper Contributor
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,
- HansVogelaarOct 19, 2023MVP
You're welcome!
- amjadinsaudiaOct 19, 2023Copper Contributor
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
- amjadinsaudiaOct 19, 2023Copper Contributor
- HansVogelaarOct 18, 2023MVP
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.
- amjadinsaudiaOct 18, 2023Copper Contributor
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.
- amjadinsaudiaOct 18, 2023Copper Contributor
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?
- HansVogelaarOct 18, 2023MVP
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.
- amjadinsaudiaOct 18, 2023Copper ContributorHow to run solver manually? any guidance will be appreciated.
- HansVogelaarOct 18, 2023MVP
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.
- amjadinsaudiaOct 18, 2023Copper Contributor
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!