+ Reply to Thread
Results 1 to 16 of 16

Formula to extract Data based on Month & Year

  1. #1
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2021
    Posts
    2,169

    Formula to extract Data based on Month & Year

    I have source Data (Profits BR1.xlsx) that contains a heading in A1


    I would like to extract the net profit for each Dept on workbook (Profit by Month.xlsx) where the Year and month matched the month and year in the source data file (Profits BR1.xlsx)



    It would be appreciated if someone could assist me


    I have attached sample data for Sep 2020
    Attached Files Attached Files
    Last edited by Howardc1001; 10-16-2020 at 09:05 AM.

  2. #2
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    2,907

    Re: Formula to extract Data based on Month & Year

    Hi,
    Do you mean like in the attached? I copied the tabs to one file for convenience.
    so - your left tab is the source data, right?

    and sheet2 would collate your data?
    Do you have different tabs/sheets for each month? where will you take the rest of data from to fill in sheet 2?


    The formula I applied in Sheet2 cell B2 and across:
    =IF(AND(MONTH(B$1)=MONTH('Source Data'!$F$1),YEAR(Sheet2!B$1=YEAR('Source Data'!$F$1))),INDEX('Source Data'!$B$3:$B$6,MATCH(RIGHT($A2,4),'Source Data'!$A$3:$A$6,0)),"")
    Attached Files Attached Files

  3. #3
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2021
    Posts
    2,169

    Re: Formula to extract Data based on Month & Year

    Hi Belinda


    what you have done is perfect. I will teat on actual data nd if I need any further help, I will let you know


    I am familiar with Index/match, but your formula is awesome

    I fully understand the logic since going through the entire formula

  4. #4
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    2,907

    Re: Formula to extract Data based on Month & Year

    Great, just notice I have changed the text in Sheet 2 to say "Dep1" instead of "Dept1", so that when searching on source data it will find a match, meaning the text in both sheets should be the same.

  5. #5
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2021
    Posts
    2,169

    Re: Formula to extract Data based on Month & Year

    Thanks, I did pick that up


    Hope you have a great weekend

  6. #6
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    2,907

    Re: Formula to extract Data based on Month & Year

    And another thing
    (I noticed another thing that might interrupt the formula) I don't know how many departments you have to cover, but the formula is good for a dep number that don't exceed 1 figure (1-9), if the dep number contains 2 figures (Dep10 and so on) - you'd better use the below :

    b2 and down:
    =IF(AND(MONTH(B$1)=MONTH('Source Data'!$F$1),YEAR(Sheet2!B$1=YEAR('Source Data'!$F$1))),INDEX('Source Data'!$B$3:$B$6,MATCH(MID($A2,SEARCH("-",$A2)+1,99),'Source Data'!$A$3:$A$6,0)),"")

    Thank you and have a nice weekend too.

  7. #7
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2021
    Posts
    2,169

    Re: Formula to extract Data based on Month & Year

    Thanks for the heads up

  8. #8
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2021
    Posts
    2,169

    Re: Formula to extract Data based on Month & Year

    Hi Belinda

    I tried to adapt your formula to a similar problem, but get #ref!


    Kindly check & amend the formula
    Attached Files Attached Files

  9. #9
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    8,641

    Re: Formula to extract Data based on Month & Year

    Notice that I have change all "mmm yyyy" text to be real date (1st date of month), formatted as "mmm yyyy"
    And create next month by using EOMONTH than drag accross.

    In B2:
    Please Login or Register  to view this content.
    Attached Files Attached Files

  10. #10
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2021
    Posts
    2,169

    Re: Formula to extract Data based on Month & Year

    Thanks for the Help. the Attached file is corrupt, but used your formula on my original workbook I uploaded

    What is the significance of +1 in formula

    Please Login or Register  to view this content.
    Last edited by Howardc1001; 10-17-2020 at 01:35 AM.

  11. #11
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, Suffolk (England), but grew up in Sawley, Derbyshire
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2211 (Windows 11 22H2 64-bit)
    Posts
    65,473

    Re: Formula to extract Data based on Month & Year

    The attachment is not corrupt, but you need to download it then remove the extraneous .xlsx suffix before opening it, leaving just .xls. This happens with files in the old format.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  12. #12
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2021
    Posts
    2,169

    Re: Formula to extract Data based on Month & Year

    Thanks for letting me know Ali. File now opens perfectly

    Hope you have a great weekend

  13. #13
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    2,907

    Re: Formula to extract Data based on Month & Year

    Hi,
    please use the below adjusted formula in B2 and down:

    =INDEX('Source Data'!$B$2:$M$5, MATCH(MID($A2,SEARCH("-",$A2)+1,99),'Source Data'!$A$2:$A$5,0),MATCH(MONTH(B$1)&YEAR(B$1),MONTH('Source Data'!$B$1:$M$1)&YEAR('Source Data'!$B$1:$M$1),0))
    Attached Files Attached Files

  14. #14
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2021
    Posts
    2,169

    Re: Formula to extract Data based on Month & Year

    Thanks for the amended formula Belinda

  15. #15
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    2,907

    Re: Formula to extract Data based on Month & Year

    You're welcome.
    The "+1 " in the below formula for example is for excel to find the placement of the sign "-" and bring all characters after this sign (from left to right).
    so if you have this expression "Net Profit Achieved Month-Dept4" - The formula will bring "-Dept4" , but if you want to lose the "-" (which is what we need here) - you will add 1 to instruct the formula to start 1 character after the "-" sign, so that the result will be "Dept4".

    MID($A2,SEARCH("-",$A2)+1,99)

    Hope this makes sense....

  16. #16
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2021
    Posts
    2,169

    Re: Formula to extract Data based on Month & Year

    Thanks for the explanation. It makes perfect 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. [SOLVED] Formula to extract the last or highest month and year
    By Howardc1001 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-01-2020, 01:33 AM
  2. [SOLVED] Sum Data Based on a GL Code and Month in Year
    By tommpalmer123 in forum Excel Formulas & Functions
    Replies: 31
    Last Post: 04-28-2020, 04:33 AM
  3. [SOLVED] Macro to extract values based on Month and year
    By Howardc1001 in forum Excel Programming / VBA / Macros
    Replies: 23
    Last Post: 04-18-2020, 02:19 PM
  4. [SOLVED] formula to extract month and year
    By Howardc1001 in forum Excel Formulas & Functions
    Replies: 18
    Last Post: 07-25-2019, 01:24 PM
  5. [SOLVED] Formula to extract the year from the date based on a condition ?
    By donny007 in forum Excel Formulas & Functions
    Replies: 29
    Last Post: 10-26-2018, 02:06 AM
  6. Extract data based on month/year to a simple table.
    By maax555 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-15-2014, 09:55 AM
  7. Get Data based on Month and Year
    By karstens in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-01-2006, 07:50 PM

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