Good Afternoon
I was wondering if i could have some support resolving the follow problem
On the attached spreadsheet i have some data under report tab, i need to break that data and transfer across to the master tab, so it will show me every department qty order and despatched rtc by date.
Support with this issue would be much appreciate
If you have Ms Access, it can be done in a single query.
or
you can try Excel query...
https://support.office.com/en-sg/art...rs=en-SG&ad=SG
HiOriginally Posted by ranman256
Thank you for your answer, but never have worked with nay of the above, is anyway that you could explain how this could be done by ms access
Thanks
Please this would be much appreciated as i have been trying to get this for long time without joy
See if the attached will work for you. It will take a little bit of manual adjusting when you add a new date - you will need to insert a row and copy some stuff down.
I used a helper column (N) to overcome the merged cells in B, you can hide this if you like
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
If you don't like the helper, you could try this instead:
=SUMIFS(Report!J:J,Report!$A:$A,Master!$C3,Report!$F:$F,INDIRECT("'Master'!N"&INT((ROWS($1:1)-1)/4)*4+3))
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
Glen, any idea how to have `1 copy-downable formula for the dates?
Ford - eh? Did I screw up somewhere? It's not been my best ever day!!
I think I now know what you meant at post 7. No is the answer. To be honest, I doubt if I would have come up with the neat solution that you used!!
Yes, I mean the date-extract formula. You cant just copy it down, it is extracting unique dates, but they need to repeat for evert code, and thanks for the compliment
Thank you very much for the help on this, it worked very well.
If you guys allow me i would like to get that same data but based on picks, ie if they are blank picks or if they are CBO picks, ignoring all other picks, is this possible?
i have add some columns on the attached spreadsheet and i have highlighted what i mean
your support would be much appreciated
Thanks
The nice thing about sumifS() is that you can just keep adding criteria to it.
I'm not sure what the "blank" picks are, but this shows how to add to my original formula for the CBO...
=SUMIFS(Report!J:J,Report!$A:$A,Master!$C4,Report!$F:$F,Master!$O4,Report!$O:$O,"CBO")
You could use that same method to add any other column/s to the fiormula
Thank you for your response, but this formula wont give me what im looking for, so i wont to know how many cases and units ordered for each department where the order type is CBO, or the order type is blank,
Help with this would be appreciated
Thanks
give me a few sample answers please, as I said...
I'm not sure what the "blank" picks are,
Hi
In the spreadsheet tab report column O Pick column if the cells are empty means blank pick, and what i need is exacle the same that have been done but only referring to the data from Picks CBO and blanks, ignoring all the other dqata
EX
T01 20 cartons orders and 50 units ordered in total, i just need to know how many of that ones are from blank picks and how many are from CBO picks
Hope i manage to explain it
Thanks
Well, like I said, you can adapt the SUMIFs() function to add up data in all sorts of ways - you just need to pick the criteria range and the criteria,and include them in the formula. I have given you the basis, the rest should be straight forward. To count the blankl cells, you would do it like this...
=SUMIFS(Report!J:J,Report!$A:$A,Master!$C4,Report!$F:$F,Master!$O4,Report!$O:$O,"")
thank you for your response
I do not want to count the blank cells, i would like to do the same extraction of data that we have done earlier but i would like to do based on the empty cells from that column
I fail to see the difference between BLANK cells and EMPTY cells? I have shown you how to build the formula, so you should be able to do it nowI do not want to count the blank cells, i would like to do the same extraction of data that we have done earlier but i would like to do based on the empty cells from that column
no difference between blank and empty cell, i trying the say is that i need to do the same sort that you have done early in the spreadsheet but instead of being based in all the data in the spreadsheet will need to be based on the blank or empty cell and cells with CMB on column O
So if we have 20 [rows of data and 6 of the them on column o are empty we will sort the data only based on that 6
Sorry but my English is not great and sometimes i find difficulty in explain myself
Thanks
I have explained to you what you need to do, try it yourself and see how you make out
i wil trty it again but it onli give me the total of empty cells not any data elated to the empty cells
Yes this is only counting the qty of empty cells for each one of the days, i would like it to count the qty of cases and single against each date but only for the data related with the empty cells or CBO, exactly the same as the original problem but this time will need to be related only to the data relevant to the empty cells and to CBO cells
Thanks
Construct the formula like I showed you. Include whichever columns (criteria ranges) and criteria you want...
=sumifs(sum-range,criteria-range1,criteria1,criteria-range2,criteria2,criteria-range3,criteria3...............................etc
ok i got it thank you
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks