Formula to Auto Recognize New Data From The Previous Data Record

[garrett.grillo]

I’m in the process of automating my project cost reports that I have to produce every month. As a part of this automation, I need my Cost Report spreadsheet for each new month to run an automated check against every new month’s SAP Invoice Report that I’m just paste into another sheet tab. So, for every new month (as seen between the Invoice Report for May 2014 and June 2014 in cells L6 and L17), there is the possibility for a new Service Order Number and along with it a new Service Order Value (budget) and Invoiced Actual Cost that can pop-up in the SAP Invoice Report, but it may not be in my existing Cost Report. With all the projects I maintain, I run a high risk of not noticing a new service order and as a result may not add it into my monthly Cost Report.

What I need is a formula to do this automatically for me by recognizing the change between what is already there in the previous Service Order Numbers, locate the new Service Order Number from the new month’s SAP Invoice Report, and from that report automatically add itself and its related cost information to my Cost Report (as seen between the Cost Report for May 2014 and June 2014 in cells D7:H7 and D17:H17). And it would be the same for the next month and the next (rows D18:H18, D19:H19, etc.) as this formulas would be hidden hidden in my Cost Report awaiting for any new appearance of a Service Order Number.

Can somebody please help me on this one?

Please let me know if you have any questions or if I can provide any additional information to help in figuring out this formula.

Example.xlsx

Thank you,

Garrett

[Punnam]

Hi Garrett,

I shall try before that i need some clarification , specify me the following data
1) which data has to be auto populated
2) A source copy of SAP generated format
3) Show a before and after sheet which means how is u r current , and what u r expecting to be happen manually
4) how the data should flow, like which table to which table Co-May204 to Cost - Jun- 2014 , or inv -June-2014 like that

Punnam

[garrett.grillo]

Hi Punnam,

I hope the answers to your questions below with the new arrow diagramed spreadsheet example will help in figuring out what the formula is.

1) Q: Which data has to be auto populated?
A: Column E (For Example E4:E9).

2) Q: A source copy of SAP generated format?
A: The source copy of SAP is in the example between columns H and K (H:K).

3) Q: Show a before and after sheet which means how is u r current, and what u r expecting to be happen manually?
A: As you look horizontally each set of rows contain the next months Cost Report (For Example B2:F9, B11:F18, B20:F27, B29:F36) and its progression as each month passes. See the May 2014 Cost Report and SAP Source Report for how the formula would possibly work.

Step 1:The formula contained in cell E14 checks for the unique identifying number that is our Company Number. Then also checks the like formula above it in cell E13 that displays last months (April 2014) Service Order Number (151726). The formula contained in cell E14 basically says, I’m looking for one specific Company Number (purple circle) that directly relates to multiple Service Order Numbers (dark green circle) that are located in the right SAP Source Report for that same corresponding month.

Step 2: The formula in E14 sees that the like formula above in in cell E13 already contains the older preexisting months Service Order Number (151726) and says ok, this Service Order Number already exists, look for any and all NEW Service Order Numbers contained in the right SAP Source Report that match the one unique Company Number that is also contained in the SAP Source Report.

Step 3: The formula in cell E14 then Ignores all preexisting Service Order Numbers (dark green circles) and finds its NEW Service Order Number in cell J14 (orange circle) and populates itself with this New Service Order Number (151265). As for all the remaining cell formulas below that are alike one ones above it, they will remain blank until any NEW Service Order Numbers are found in the SAP Source Report. So, for any given month contained in the SAP Source Report, there may be no new Service Order Numbers just the previously found numbers or there maybe new multiple Service Order Number additions to the already preexisting numbers. No matter what month it is, all the formula has to do is see what Service Order Numbers were already there and only populate with the new Service Order Numbers.

4) Q: How the data should flow, like which table to which table Co-May204 to Cost - Jun- 2014 , or inv -June-2014 like that?
A: I think I answered it in the above question.

Example.xlsx

Thank You Again,

Garrett

[Punnam]

hi Garrett,

Thanks explanation .Few more query.

1) Do u have only one Company number at any time of year or multiple , if so provide a example .
2) At end of every month u a re going to copy and paste the "SAP Source Report for April 2014" in Column H - K ? Please clarify me
3) Approximately No of expected Rows on monthly data .

Punnam

[Punnam]

Hi Garret ,

I have updated a sample for u reference, if u are able to provide the details as requested in Post 4.
I will try to make it more simple.

Punnam

[garrett.grillo]

Hi Punnam,

1) Q: Do u have only one Company number at any time of year or multiple , if so provide a example?
A: There can be multiple Company Numbers at any time of the year.

2) Q: At end of every month u a re going to copy and paste the "SAP Source Report for April 2014" in Column H - K ? Please clarify me.
A: Yes, but as each new month passes those months will also be in the SAP Source Report.

3) Q: Approximately No of expected Rows on monthly data?
A: Leaving room for 10 rows of Service Order Numbers per 1 Company Number should allow enough room for the entire lifespan of the projects.

I have also attached the original SAP Source Report (the actual SAP report I use at work) to help in answering any additional questions you may have about the organization of the data.

I hope this continues to help.

SAP Source Report.xlsx

Thank You Again,

Garrett

[Punnam]

Hi,

Check this file and let me know weather it match u r requirement ?

Punnam

[garrett.grillo]

You almost have it solved!

The last and only question I have (fingers crossed, lol) is in the “Expected” sheet I need to have rows under each unique “Company Name” (colored yellow) that will sum all the project costs above it related to that “Company Name”. The “Total” rows need to be a SUM formula that will just add up the automated cells (colored white) that you provided and cannot be any type of Match and/or Index formula reading off the SAP export.

So, the last problem to solve is that in the “Sheet1” sheet, new Service Order Numbers are added each month to the SAP export that’s added (cut and paste text) into this sheet. This causes the list to grow and as the number of rows increase, in the “Expected” sheet it causes all the automated cells (colored white) to shift downwards passing by the “Total” rows and as result the Sum formulas contained in these “Total” rows aren’t able to capture all the numbers related to the “Company Name” totals and as a result will just produce a summation of only part of the “Company Name” costs. If you add an additional row of information into the “Sheet1” you’ll see the results in the “Expected” sheet of what I’m trying to describe.

In the Excel attachment, you’ll see a sheet I added called “Expected (Example)”. Like in this example, is there any way to possibly have a few extra of your Match Index formula cells that have yet (if future months) to access any new information from the SAP export “Sheet1” sheet and at the same time used as buffer between the yellow “Total” rows and the last cost data that was automatically added to the rows above it?

Also, let’s say for example for the, “Company Name” DWRRI-TE061-122 rows increase as the automated cell formulas locate and add the unique cost data rows related to DWRRI-TE122. As the once blank formulas will now provide the cost data, it will not cause the other unique “Company Name” rows to shift downwards as it did before.

I really appreciate all your help and patients.

Thank you again,

SAP Source Report (Rev.1).xlsx

Garrett

[Punnam]

hi,
I HAVE DOUBT,
DO ONE COMPANY NAME HAVE DIFFERENT COMPANY NUMBERS ?
1) DWRRI-TE061-123 50009217
2) DWRRI-TE061-210 50009217
PUNNAM

[garrett.grillo]

Hi Punnam,

Through your previous help, I just finally figured out a working solution and formula.

Thank you for all your help my friend! :-)

Garrett

[Punnam]

HI garrett.grillo,

Please update the sheet with revised formula .I need to know how was it need to be done.

Punnam