• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Food reports

Hello
The attachment is a daily report of the same shape, shown below
The blue part is feeding the flock species
I want to collect the data for this part throughout the year in a separate table, such as the table in the "Total Table" that was collected manually.
Noting that the feed mixture varieties can differ from one report to another, as well as herd types
I only attached a sample because the data is very big per year
Thank you
 

Attachments

Peter Bartholomew

Well-Known Member
To do anything other than a manual calculation your data must be presented in an absolutely regular manner.
If the 'reports' have to vary in terms of the column used for a given food stock the you will need to extract the data from each to populate a master table which combines the reports. The lookup can be achieved using INDEX/MATCH (or, most recently, XLOOKUP) on the column headers from each report. Only once that is done are you in a position to aggregate the data from each column, typically using SUMIFS:
= SUMIFS( INDEX( Data, 0, columnNum ), HerdTypeColumn, HerdType )
 
Thanks, Peter
I tried as follows:
I made a title for each cell in the reports (like in P2: AC129)
I made a database of these addresses (like in columns from AF to AI) so a pivot table can be used to get the data
But: It has a problem. In an equation in column AH it failed to make number 18 change to number 60 in cell AH60 and number 104 in cell AH104
The same problem is in the AI column
I would be very grateful if you tried to find a solution
Thank you
 

Attachments

p45cal

Well-Known Member
In the attached I've added a formula version of your table on the Total Table sheet and the values exactly match your values.
However, the Reports sheet (or a copy of it) needed the blue sections you've highlighted (less the top merged cells, and only columns D: P (I've highlighted them light blue in the attached) to be sorted left to right (a built-in option for sorting under Options…)by the headers, alphabetically.
 

Attachments

Last edited:
Top