+ Reply to Thread
Results 1 to 7 of 7

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

  1. #1
    Registered User
    Join Date
    03-28-2022
    Location
    Singapore,Singapore
    MS-Off Ver
    NA - Google Sheets
    Posts
    3

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

    Hi,

    I'm new here - nice to meet you! I think I have attached Example 1 here to show what I need.

    I have a weekly sheet that is downloaded. It tracks open positions for hiring. Each week I create a new sheet entitled "Current Week" which is the working sheet for the week. I am wanting to see if it is possible to:

    - Pull rows of data and insert them into Current Week tab from the previous week *if* they have the same "Posting ID"
    - I am wanting the new data to show below or above the previous week

    I am including an example of what I want it to look like. Each week new applications and steps come in, so I want to be able to see in the current week's view if anything changed in the New - Active, Screen- Active, etc. categories.

    *Bonus* if it's possible to have a third row automatically added below the previous week and current weeks' data that would show any change in number (e.g. +1, -2, etc)
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    07-24-2021
    Location
    Japan
    MS-Off Ver
    Google Sheets (& Mac 2021)
    Posts
    797

    Re: 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.
    Last edited by janmorris; 03-29-2022 at 02:37 AM.
    As a gesture off appreciation, you can click * Add Reputation at the foot of any of the posts of members who helped you reach a solution.

    And finally, was your problem solved? if so, please click Thread Tools above the first post of your enquiry, then select [Solved]

  3. #3
    Registered User
    Join Date
    03-28-2022
    Location
    Singapore,Singapore
    MS-Off Ver
    NA - Google Sheets
    Posts
    3

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

    Hi,
    Thanks for the reply.
    Let me clarify!

    The current week will be downloaded from the system each week.
    The "old" current week doc will have it's label changed to the previous weeks' date.
    So the "new" data source will be in the current week, and then the data source to be pulled over will be from the previous week.
    Does that make sense?

    Regarding the dates, that is interesting and I didn't know. I'll look into this further!

  4. #4
    Valued Forum Contributor
    Join Date
    07-24-2021
    Location
    Japan
    MS-Off Ver
    Google Sheets (& Mac 2021)
    Posts
    797

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

    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
    Last edited by janmorris; 03-29-2022 at 12:52 PM.

  5. #5
    Registered User
    Join Date
    03-28-2022
    Location
    Singapore,Singapore
    MS-Off Ver
    NA - Google Sheets
    Posts
    3

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

    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.
    Attached Files Attached Files
    Last edited by cannotlah; 04-03-2022 at 09:43 AM.

  6. #6
    Valued Forum Contributor
    Join Date
    07-24-2021
    Location
    Japan
    MS-Off Ver
    Google Sheets (& Mac 2021)
    Posts
    797

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

    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.
    Last edited by janmorris; 04-04-2022 at 03:50 AM.

  7. #7
    Valued Forum Contributor
    Join Date
    07-24-2021
    Location
    Japan
    MS-Off Ver
    Google Sheets (& Mac 2021)
    Posts
    797

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

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Compare Sheets and Display the previous sheets column in current sheets
    By maddyrafi1987 in forum Excel - New Users/Basics
    Replies: 4
    Last Post: 05-09-2017, 04:03 AM
  2. 2 sheets, identify matching and non-matching rows
    By athyeh in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-10-2014, 02:40 PM
  3. Replies: 1
    Last Post: 02-04-2013, 11:40 AM
  4. Macro to sort an activity sheet by current week and current + last 1 and 2 weeks
    By engineering_excel in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-13-2011, 11:28 AM
  5. Replies: 4
    Last Post: 03-21-2011, 05:37 PM
  6. Replies: 4
    Last Post: 02-13-2011, 05:37 AM
  7. average last x weeks of data, based on current week
    By shifei in forum Excel General
    Replies: 7
    Last Post: 09-16-2010, 08:00 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1