GoogleSheets - Pull rows from last week's sheets to current weeks' sheets if matching

Welcome to the forum!

In the "View I want" tab (which is what you want the "Current Week" to be like), you have two rows for each posting ID, and i can see where you are taking one row of each pair from the "3212022" tab.... HOWEVER... there is no source data for the other rows.

In order to compile a report you need to have a data source. Can you please provide a new file that has more of the structure (AND data) that will be used to compile the "Current Week" report?

---

Also, heres a word of advice regarding naming conventions...

MDYYYY is a poor format to use, by reason that a date of 1212022 can be either 21 Jan 2022, or 1 December 2022. Continuing to use this format will invariably lead to problems later on.

I recommend you use the standard ISO 8601 format of YYYY-MM-DD, or at the very least YYYYMMDD, so your current date of 3212022 will actually be 2022-03-21. In this way there can be no confusion to interfere with formulas or Apps Scripting.

Hi cannotlah,

Here is a solution which uses a few sheets to get the desired result.

The benefit of this system is it doesnt use Apps Scripting, so it is quite flexible to modify without requiring javascript coding skills.

Let me explain it for you.....

CONFIG sheet
Enter the date for the last reporting period into cell B1, in the format of YYYY-MM-DD.
NOTE: This value MUST match the name of the sheet otherwise the system will not work!
Cell B2 will create the range to the last row with a value, and it uses the following formula:

Please Login or Register  to view this content.

Enter the name of the current sheet into cell B3 (you have made it "Current Week", so i have followed used the same)
NOTE: This value MUST match the name of the sheet otherwise the system will not work!
Cell B4 uses the same formula as cell B2

CONSOL sheet
This is consolidated (all) data from both sheets
It uses the following formula:

Please Login or Register  to view this content.

DUPS sheet
This sheet is sorts and filters removal of unique Posting IDs, so that you are left only with rows with duplicate Posting IDs
It uses the following formula to pull data from the CONSOL sheet:

Please Login or Register  to view this content.

QUERY sheet
This is the final report sheet, it is much like your "View I want" sheet, except that it has all the details for each column, and not just the differences for columns F-K.
To make the sheet more viewer friendly i have added conditional formatting that highlights the LAST duplicated row, ie the subtotals row.
The subtotals row only gives the difference between the highest and lowest for each grouping, it does not detect if negative or positive value.
It uses the following formula to pull data from the DUPS sheet:

Please Login or Register  to view this content.

And it uses the following formula for the conditional formatting on the range A2:K100:

Please Login or Register  to view this content.

---

Instructions:

Keep the CONFIG sheet at the front so that you can easily change the date in cell B1
Put the DUPS and CONSOL sheets last, so they are out of the way
The QUERY, Current Week, and last dated sheet (eg. 2022-03-21) should be put after the CONFIG sheet and before the DUPS and CONSOL sheets.
This structure is already setup for ease of use.

Here is link to the file for you to make a copy:
https://docs.google.com/spreadsheets...it?usp=sharing

Screenshot of the final result:

Screen Shot 2022-03-29 at 11.45.26 pm.png

Wow, this is amazing. Thank you so much! One slight problem, the dummy sheet I created uses fewer rows (A-K). Looks like I will need more rows - up to Z. So I assume I replace K with Z. Then on the query sheet I can see I must need to do something with Col but I am not totally sure what. Sorry for the inconvenience, just realized more data that I thought initially.

I'm attaching a view with all the columns that will exist entitled "NEW" sheet.

Really appreciate this. So incredibly helpful, just need to figure out what I need to adjust.

yes, thats right.

you will need to change the DUPS sheet formula to something like:

Please Login or Register  to view this content.

then for the QUERY sheet you will need to either add in the "select .... Col#" for the columns that have text, or "MAX(Col6)-MIN(Col6)" if you want to find difference between highest and lowest.

and you will need to change the QUERY range so something like:

Please Login or Register  to view this content.

in order for me to write the formula i would need some sample data.

something to keep in mind... when doing the min max... the values in each column wont necessarily be from the same rows, thats because you want min max from each column... so for example on columns with dates.. you could get the earliest and latest dates.. but that wont mean that other column values are from those same rows that the dates are from. i hope that makes sense.