removing duplicates and calculating the total qty that is pending in some other sheet

please attach a workbook which includes typical values, showing the "before" and "after" situation
avoids having to recreate test data and structure

thanks

ManageAttachments3.jpg

thanks - will look at it later today

thanks kev, well i got the unique records by calling a function Lookup_concatru and written a script on it even i got the pending sum. But i raised with one more query and the query is if the quanity gets zero then item shouldn't be shown in items column of sheet 1. can you help me with this.

Please Login or Register  to view this content.

please edit your previous post - to include code tags thanks

- click on Edit Post,
- select all code text,
- click on # icon

I have a solution for you

- Function added to remove duplications in ITEMS

- Formulas inserted to correctly calculte both ORD QTY and PND QTY

Thanks for including the code tags

Everything is in the attached workbook

Formulas inserted to correctly calculte both ORD QTY and PND QTY

F2 copied down:
=SUMIF(Sheet2!B:B,Sheet1!B2,Sheet2!E:E)

G2 copied down:
=SUMIF(Sheet2!B:B,Sheet1!B2,Sheet2!F:F)

Function added to remove duplications in ITEMS

The function is called from the final line in your other function, and the previous line was also amended

Please Login or Register  to view this content.

the code is in Module1:

Please Login or Register  to view this content.

I do not understand what you are saying
- none on the duplicated "Final Sec" values sheet2 have an associated 2nd value in the pending column
- where does the duplication come in?

Column G total(sheet1) matches column F total(sheet2)
If they should not match what is is the Excel logic for
- excluding an item?
- including an item?

Which values are incorrect in Sheet1 and why?
- please attach workbook with corrected sheet 1 values

thanks

I think I get it -

IF there are more than one entries for "Final Sec" (in Sheet2) THEN PND QTY(in sheet1) is the minimum of those quantities

is this correct?

Please enter this formula in cell G2 in sheet2 and copy down:
=MINIFS($F$2:$F$13,$D$2:$D$13,D2)

Does that give the correct values for each "Final Sec" to transfer to sheet1?

Are you using Excel 2010?
- I need to know, some functions in Excel2016 do not work in Excel2010

We can still make it work - formulas need writing differently

Workbook is attached
- please test with more examples
- it works with the data supplied, but there are not enough values to be sure

Expected results obtained with some helper columns in sheet2 (columns G and H)
- this meant that the DeDup function is no longer required and has been removed

Formulas used on sheet2:
in G2:
=IF(F2<>0,MAX(IF(D:D=D2,F:F)),0) - see *note below
in H2:
=IF(G2=0,"",D2)

Formulas used on sheet1:
in E2:
=lookUpConcat(B2,Sheet2!$B$2:$B$13,Sheet2!$H$2:$H$13,",")
in F2:
=SUMIF(Sheet2!$B$2:$B$13,Sheet1!$B2,Sheet2!$E$2:$E$13)
on G2:
=SUMIF(Sheet2!$B$2:$B$13,Sheet1!$B2,Sheet2!$G$2:$G$13)

*note
The formula in G2(sheet2) is an ARRAY formula
- if it is not entered correctly it will not work and return wrong results
- it is entered with {CTRL}{SHIFT}{ENTER}

- if it is entered correctly it looks like this
{=IF(F2<>0,MAX(IF(D:D=D2,F:F)),0)}

- you cannot input the special brackets {} - the formula will not work

Thanks for the rep

If you are satisfied with the solution
-please mark the thread as solved (top of thread, under thread tools)

thanks

[From private message] now am facing a issue regarding the processor calculation. its taking much time for every calculation. And even i went for manual option but when i switch again to automatic i need to wait for hours to get that done. So, can you please suggest me some optimizing techniques. so that i make it very smooth. Thanks

Unfortunately array formulas are very "hungry" and eat up a lot of processor capacity if there are many lines

Here are 2 alternative formulas:
- they are not array formulas
- they give the correct answer for all items with (limited) test data

(sheet2) cell G2 copy down:
=IF(F2=0,"",F2)

(sheet2) cell H2 copy down:
=IF(F2=0,"",D2)


- do they give the correct results for all your data?
- is calculation time acceptable?

Yes
- it is caused by the volume of formulas in the workbook and
- the speed\memory size of your PC is a likely factor

How many rows are there
- in sheet1?
- in sheet2?

Is there anything else in the workbook?
Are you leaving other applications open at the same time?


I am busy today.Tomorrow I will create a VBA procedure to do this
- it will be quicker I think and
- it will only use resource when you press the button to run it

- in attached file most formulas have been overwritten with values (sheet 1 and sheet 5) and
- several columns removed from sheet 5
- this speeds up everything in testing
- I do not want to wait for the sheet to recalculate every time I change something )

With over 50,000 records, a pivot table would be much quicker
- formulas take a lot of memory which slows everything down
- pivot tables work much faster
- a pivot table gives you the "result" without any formulas in any cells
- a pivot table's range grows automatically with your data

Test it for speed:
- click on sheet5
- amend the value in M2 from 2,465 to 5,000
- click on sheet "pivot"
- click on any cell in pivot table
- click on Pivot Table Tools (at top of worksheet)
- click on "Refresh"
- value in C4 now reflects new value and all totals updated
- time taken with current data = almost instant (less than 1 second)

Refresh Pivot.jpg
The Pivot Table:
- everything except columns D is extracted from sheet5
- column D is a calculated field
- Pivot Table results could be converted via VBA to look like sheet1
- at the moment all values are included in the pivot table, but can exclude those Pending = 0 later

What do you think?

Ok - that is what I expected you to say
I am busy today, but will look at this for you tomorrow

First I must understand your data properly

see picture - customer D02198, and tell me about section 7656 ONLY

total for 7656 QTY = 103 + [753] + 1351 = 264 = 2,471
Total for 7656 Prd = 79 + [239] + 1226 + 254 = 1,798
Pending for 7656 = 2,471 - 1,798 = 673

Now assume only row 1298 changes

Today (row 1298 ONLY!)
Qty = 753, Prd = 239, pending = 753 - 239 = 514

after Update1:
if Prd increases to 550
Qty = 753, Prd = 550, pending = 753-550 = 203

after Update2:
if Prd increases to 753
Qty = 753, Prd = 753, pending = 753 -753 = 0

What should sheet1 totals for 7656 include after update1?
perhaps?
total QTY = 103 + [753] + 1351 = 264 = 2,471 (same as before)
Total Prd = 79 + [550] + 1226 + 254 = 1,798 =2,109
pending = 2,471 - 2,109 = 362

What should sheet1 totals for 7656 include after update2
perhaps?
total QTY = 103 + [753] + 1351 = 264 = 2,471 (same as before)
Total Prd = 79 + [753] + 1226 + 254 = 2,312
pending = 2,471 - 2,312 = 159
OR
total QTY = 103 + [0] + 1351 = 264 = 1,718
Total Prd = 79 + [0] + 1226 + 254 = 1,798 = 1,559
pending = 1,718 - 1,559 = 159
OR
something else?
thanks

ExampleItem.jpg

thanks will look at this tomorrow

I am busy for rest of today
- so cannot modify further until tomorrow

In the meantime, you may check progress so far:
- run the macro in the attached workbook with {CTRL} t
- it adds a helper column to sheet5 to concatenate CoNo and Section Number (needed in pivot table)
- pivot table created frrom sheet 5
- extra columns added to PivotTable
- PivotTable turned into normal range
- items with Pending = 0 are excluded
- concatenation formula used to concatenate Section Number values

Further questions:
1 when you run this you should notice it is much faster. Is it?
2 are values correct?
3 which columns should be added to the sheet PivotTable (Company Name? etc)
4 How do you select the values for your report?
( at the moment it is everything in Sheet5 - I am sure you will not want that!!)

This is what the output looks like at the moment:

Table096.jpg

NOTE:
if you want to test with more values:
- it will be easier to test in attached workbook by pasting more values into sheet 5
- I am not sure how easy it will be for you to adapt the code to use in your workbook at the moment
- it will be easier when finalised

1. Yes offcourse its prety fast than before. I appreciate you work
2. Well values are not correct its only taking for a single section as you can see in your attached for D00406/a the ordr qty must be 88573.9 and the remaining valuees are also getting wrong.
3.customer column and i also have one more column which is similar like section no. column (i.e here we have 9 PRESS categories and the sections are divided with the corresponding Press no's. And in this also we have duplicates like the section column but we have to eliminate those and get only unique values like the one we have done now with section column. And i do have one more column which is the reference name which is not shown in below image but this we can see in Sheet 5 and data must get sorted based on this in Pivot table. Re.date and App Dispatch date are shown in Sheet 5 but i have removed those things,Prod Qty and Remarks are only two which are entered manually.The exact report is shown below.
Untitled.jpg
4.This question is not clear for me can you please brief it again.

Apart from this can you please explain the code for me, everything is fine well the speed is also upto the mark. But only thing is the sum of ordr qty,prd qty,pnd qty has to be changed as its not summing the total sections of same order and few modifications need to be done.

Please select Thread Tools from the menu link above and mark this thread as UNSOLVED so that members read it.

I will explain all the VBA to you after we get the values correct

I agree - quantities are wrong - will investigate today
(I forgot to tell VBA to sum all the values onto one row - oops!)

I now know the answer to Q4 - in something you sent at the beginning there were dates - those are in the missing columns you mention in reply to Q3

3.customer column and i also have one more column which is similar like section no. column (i.e here we have 9 PRESS categories and the sections are divided with the corresponding Press no's. And in this also we have duplicates like the section column but we have to eliminate those and get only unique values like the one we have done now with section column. And i do have one more column which is the reference name which is not shown in below image but this we can see in Sheet 5 and data must get sorted based on this in Pivot table. Re.date and App Dispatch date are shown in Sheet 5 but i have removed those things,Prod Qty and Remarks are only two which are entered manually.The exact report is shown below.

The above has an impact on how I write the VBA
- I do not want any more "simplified" workbooks
- I need to see what you are working with and exactly what you want
- make customer name anonymous (remove the values, but leave the column in place)

Please attach a workbook where
- sheet5 contains ALL the columns (with 1000 rows of data as before)
- also include "Results" sheet - include all the columns you expect to see in your Pending report
- include some example results (from the 1000 rows)
(I need to fully understand how to arrive at all unique values)

thank you

I agree - quantities are wrong - will investigate today
(I forgot to tell VBA to sum all the values onto one row - oops!)

Please satisfy yourself that the values are now correct
- run in attached workbook with {CTRL} t

I will wait for info requested in post#36 before doing anything else

Thanks Kev_ after sitting for few hours on the data i removed much data from it and sending you the file. let me know if the data sent to you is clear. And i made a note what actually i need in report even i showed it here. TEST.xlsm. Well the data which i used only less but as i said before we normally use 50k records. And report must get compiled from all these records. I took 10 records as example but am facing issue with the results which i marked in file with Red.

well its my mistake that i havent informed you regarding report what actually i needed from it. Sorry for that. Hope you get an idea what actually i need.

Note:-
1 Don't need duplicates for both "SECTION NO." & "PRESS"
2 For Press if Pnd Qty gets Zero then the Press Has to be vanished just like Section gets disappeared when the qty gets a zero value. And moreover duplicates are to be eradicated similar like Section. But here we don't use any arithmetic functions this should depend on qty of section that is pending (PND-Qty Kgs).
3 If "PND-Qty Kgs" gets Zero in Orders sheet then order must vanish in Results Sheet.
4 S.No. must be automatically generated
5 Total Sum of Ord Qty, Prod Qty, Pnd Qty must be seen at the bottom of all the orders. I need subtotal of these three.
6 Headers must be like this
7 Data in Results must be sorted according to Reference column
8 I have values from 2sheets. One is Customer and the other is Orders. Customer name must be dragged from Customer sheet.
Thanks

Thank you - the data you provided is perfect
I will update the thread tomorrow

I am still testing different solutions to handle your extra request re "Press"
I will update the thread this afternoon (UK)

Ok - thanks

I still have a couple of things to do
- but try it in the attached workbook with {CTRL} t

I noticed right at the end that the "Press" numbers are wrong
- I will sort that out tomorrow
- it was correct but I have amended something and now they are not correct!!
- it is all to do with when these numbers are concatenated

I would welcome any comments that you have

Thanks kev_
as per the attached file that was sent in recent post i noticed few errors
1. S.no is not starting from '1'
2.As you said PRESS no's are getting wrong. Yes even i noticed that.
to give an idea how the PRESS plays is shown in image below:-
Untitled.jpg
3.Sub Total of ORD QTY,PROD QTY,TO PROD is not happening in Pending orders.
note:-
i have observed the above ones and apart from this my question is if PRD Kgs is beyond QTY Kgs why is it still showing in Pending orders?
the order must get disappear even if the PRD Kgs is beyond the QTY Kgs.

see attached file

The quantities and remaining Section numbers are all correct based on what I have told the VBA to do

Look at sheet "manual proof" and compare it with sheet "Pending"
- Order Qty, Production Qty and Pending QTY - AGREE
- Section numbers listed -AGREE
- grand totals agree

Let me know what is incorrect and why
thanks

(as previously mentioned Press values are not yet correct)

also see comments on post#45

1) "Press" fixed
- "Press" formula values were being affected by the next instruction
- copy & paste values added immediately after concatenation "froze" the correct values

2) S.No is now starting at 1

3) Grand totals added to sheet Pending Orders

@Tusher029
- that is exactly what the VBA is doing.
- it includes a pivot as the first step (which summarises by order)
- it includes SUMIF
- it includes de-duplicate
- it includes lookups to get other required values from another table

The requirement is to
- concatenate "unique" values of both "Section No" and "Press" and
- eliminate those values where..
- the "total PENDING" for a "section" for an "order" = 0

If you have a more efficient solution that would be wonderful
- please help the OP (and me) by posting a workbook showing all the stages to get to the "final" solution

AH - got it

Totals = totals for order (including everything)
Section = only "Section" where Pending > 0
Press - only "Press" where Pending > 0

Previously, Press 6 & 7 was included because section 92324 was included
- needs a test to see if Press Pending = 0
- if so include section, but exclude press

92324 section press is 2 only thats my mistake i showed it as 6 7 7 in others. the press will be unique for a section i made it wrong. i think the change is not required for press. I tested for the press its perfect when i made 92324 section press to 2.

I want to be 100% sure!

Press.jpg


Press = 2,6,7

Logic:
IF Section 92324 Pending > 0 THEN
include all Press for Section 92324
ELSE
do not include Press
END IF

Please confirm

Post #55 explains why rest of wording deleted on this post
see post#59 instead

HI kev_

Why is the attached file same as before
why the ORD QTY,PROD QTY,TO PROD getting wrong totals???

Because I am stupid today!

I am off for a beer and then I will fix ..

Ok - I think this now should give you the results you are looking for

1 Pending Orders sorted by Reference
2 "Press" left unmodified as you suggested
- I agree that this will give correct result IF section is unique to press (by order)
3 Annoying green cell warning triangles dealt with (in Section & Press columns)
(invisible character added at beginning of string so that Excel now thinks that the cells contain text)
4 Headings match what you wanted
5 All totals are now ORDER level totals

Please test and confirm that all values are now correct
Also please test on a COPY of your complete file - I hope it runs quickly for you

NOTE - the code places values in column O in sheet "ORDERS" (and deletes those values later)
- if your file contains anything in column O, then those values will be overwritten
- let me know if this will cause you a problem
- you must tell me which column is the next available column
- for the code to work columns A to M in sheet "Orders" must be IDENTICAL in layout to attached file
- same for sheet CUSTOMERS
- the text in J1 and M1 is used in defining the pivot table Calculated column for Pending quantities
(so for example - IF you amend "Qty Kgs" to "ORD Kgs" THEN code fails )
- the VBA requires the data to be stored in sheet named ORDERS and lookup data to be stored in sheet named CUSTOMERS

After you have tested and agreed everything is ok.....
- I will create detailed notes explaining the VBA
- I will "translate" the references
etc...

That suits me - I am busy for the next few days
So I will not update the thread before Monday