Sorting Products to Raw materials and add the weight and the week produced

Hello Experts!

I got a problem to solve and it is out of my expertise to do that. Thanks to everyone trying to help me with it!!

PLEASE SEE THE SAMPLE FILE ATTACHED!!



I have a file with two tables. One holds all the Raw material and in what Product it is used, when and with what weight.

Now I need to fill table 2 with the weight data for every week of the year per Rawmaterial and Product.



Raw material 1 / Product 1 Week1 weight / Week 2 Weight / Week 3 Weight and so on.....
/ Product 2 Week1 weight / Week 2 Weight / Week 3 Weight and so on.....
/ Product 3 .... Week1 weight / Week 2 Weight / Week 3 Weight and so on.....

Raw material 1 / Product 1 Week1 weight / Week 2 Weight / Week 3 Weight and so on.....
/ Product 2 Week1 weight / Week 2 Weight / Week 3 Weight and so on.....
/ Product 3 .... Week1 weight / Week 2 Weight / Week 3 Weight and so on.....

I hope I made myself clear, but I guess you get the idea in the sample file.

Thank you very much in advance!!


JP

Hi
please post your file on the forum as described in the yellow banner.
External links are unsafe and not all members can access them.
Thanks

There is, you just haven't spotted it yet!!

Unfortunately the attachment icon doesn't work at the moment. So,... instead click on Go Advanced (below the Edit Window) while composing your reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

Make sure confidential information is removed first!!!!

Hi. Is it allowed to fill in the relevant blank cells in column A??

If YES, this is easy. If NO, it will be horrible.

Hi Glenn,

thank you for looking into my problem

yes that is no problem, they can be filled accordingly.

Hi. there is a lot going on here. I will explain, as needed. However, I have had to make further changes to the layout (no blank rows) to make this deliverable without VBA.

Take a look and see if it does what you need.

Hi Glenn,

I just experience a mistake. In the Helper file it does not list all Articles, ONE is missing?

Any idea?

Yes, but I would not be able to do it.

The helper lists the Rohstoffs, not Artikels. There are 15 Rohstoffs in your raw data. There are 15 on the helper column. So I am a little confused.

Please try this formula

at C8

=SUMIFS(Tabelle1!$D:$D,Tabelle1!$B:$B,LOOKUP(2,1/($A2:$A8<>""),$A2:$A8),Tabelle1!$C:$C,$B8,Tabelle1!$A:$A,">="&C$5,Tabelle1!$A:$A,"<="&C$6)

Note : max row is allow only 7 rows per A column,
for unlimited at 7 per A column formula may changed to

=SUMIFS(Tabelle1!$D:$D,Tabelle1!$B:$B,LOOKUP(2,1/($A$1:$A8<>""),$A$1:$A8),Tabelle1!$C:$C,$B8,Tabelle1!$A:$A,">="&C$5,Tabelle1!$A:$A,"<="&C$6)

Regards.

After seen Glenn's file , seem I mis understood OP requirement again. >_<

Sorry for the mistake.

Regards.

In tabelle2 C8 = 0 because Tabelle1 A2 = 02.01.2019 it's not a date in my local. (Glenn's file already fixed it)
Article 2 is in U column , Article3 is not match with Rohstoff 1


Regards.

This is (may be) another solution,
by adding helper sheet and use non-Array formula to count and re-order combind text.

Please review in the file.

Weak point of this file,
1. In helper sheet need to have formula row more than data in Tabella1 (it's use Index to rebuild table of data)
2. In Tabella2 (output) name are sorted , it might not as you want.


Regards.

If you want to retain the unsorted order in Tabelle 1 it all becomes much simpler!!

See file. Helper gone...

No. Now I am totally confused. All the Rohstuff are in alphabetical order in your raw data. They are in the same order in table 2.

Please upload a small, representative sample and show your expected results, calculated manually. 10 rows, maximum, is all that is needed.

Rather than start all over again (!!!) I copied 20 rows into my original effort and re-worked it. I have now copied all of the sample data in and it is OK.... now... I think. I have spent an awful hour staring at one formula which refused to work.

A disaster caused by Excel's inability to count (floating point arithmetic... don't ask). Now sorted.

Issues:

Dates in raw data are not dates. They're text. Select Table 1 column A. Data/Txt to columns/Next/Next/Date DMY/Finish.

Helper column A. This is an array formula.


Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

My main concern now is that it might be too slow... especially if the file you uploaded is only a small fraction of the real thing. I do have one possible workaround up my sleeve....

let me know!!

I do not own a crystal ball. Unless I can SEE what YOU have done, how can I tell what is wrong??

The problem was resolved as the OP has mis-transcribed dsome ranges. A pivot Table was finally needed to sort the unique Artikel names.

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.