Hello, guys
I was wondering if I could get a 2nd opinion on something I did. You might remember something better.
There's this activity that I perform on a monthly basis that basically consists of extracting a report from SAP, copy it to excel and compare it with another sheet on the same excel file. This report gives me data really raw. So far, I was using a pivot table to organize it. However, I need to perform a few other checks on the side and if I change the data the pivot table changes as well and the checks get out of place which makes me lose time getting them set again.
In order to solve this, I replicated the pivot table's structure and got the same output out of formulas. The downside of this is that whenever I change the data it won't get new entries, only old ones.
I will provide an example below so you can understand better.
The report I extract gives me something like this:
From there, I want to know how much of each item I have in stock in each location. Therefore, I replicated the pivot table's structure.
I used SUMIFS to get the values. However, let's say a new item is allocated to a new location. Is there any way it would automatically insert the item's line under the location I have set? Meaning, let's say the report now has every line above plus this one:
BBB 191 100€
Would it be possible to set the excel in any way it that it would add this line below the location BBB?
Thank you very much for any input
I was wondering if I could get a 2nd opinion on something I did. You might remember something better.
There's this activity that I perform on a monthly basis that basically consists of extracting a report from SAP, copy it to excel and compare it with another sheet on the same excel file. This report gives me data really raw. So far, I was using a pivot table to organize it. However, I need to perform a few other checks on the side and if I change the data the pivot table changes as well and the checks get out of place which makes me lose time getting them set again.
In order to solve this, I replicated the pivot table's structure and got the same output out of formulas. The downside of this is that whenever I change the data it won't get new entries, only old ones.
I will provide an example below so you can understand better.
The report I extract gives me something like this:
Code:
Location Item Value
AAA 123 1000€
BBB 231 500€
AAA 489 1500€
ABC 391 250€From there, I want to know how much of each item I have in stock in each location. Therefore, I replicated the pivot table's structure.
Code:
AAA
123 1000
489 1500
BBB
231 500
ABC
391 250I used SUMIFS to get the values. However, let's say a new item is allocated to a new location. Is there any way it would automatically insert the item's line under the location I have set? Meaning, let's say the report now has every line above plus this one:
BBB 191 100€
Would it be possible to set the excel in any way it that it would add this line below the location BBB?
Thank you very much for any input
Last edited:
