+ Reply to Thread
Results 1 to 6 of 6

Excel, writing a formula that fits these criteria

  1. #1
    Registered User
    Join Date
    09-20-2016
    Location
    US
    MS-Off Ver
    2010
    Posts
    38

    Excel, writing a formula that fits these criteria

    I would like to start off by apologizing if I did not format this properly, thanks for helping!

    -Please see bottom for data


    I am trying to write a formula that will go through the "dates" and "points" rows on tab 1 and when it finds a date with a corresponding point it will populate that date in tab 2 under the Date column. Simple enough right? Well here's where I'm running into problems, I need a formula to include the following criteria:

    1) If the date does not have a corresponding point in the second row on tab 1 it will skip it and move on to the next date to validate it and so on

    *note- I also need the Points column on tab 2 to populate with the corresponding date, but I believe a simple HLOOKUP formula will work just fine

    2) I would like to have the formula to where it can be duplicated on tab 2 cells A3,A4,A5 and so on, but as an example: if A2 has date of 9/15/2016, when the formula in A3 is checking for dates with corresponding points in tab 1, it will skip the date in tab 2 located in A2 since it's already been used

    Below I have attached some pictures I snipped from the excel file I've been playing with. Thank you for any help!!

    all.PNG
    Attached Files Attached Files
    Last edited by bordemkills; 09-20-2016 at 03:42 PM. Reason: Updated Sample Sheet

  2. #2
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Excel, writing a formula that fits these criteria

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    “None of us is as good as all of us” - Ray Kroc
    “Actually, I *am* a rocket scientist.” - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    09-20-2016
    Location
    US
    MS-Off Ver
    2010
    Posts
    38

    Re: Excel, writing a formula that fits these criteria

    Thanks for the walk through on how to upload the file! I have attached it

  4. #4
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Excel, writing a formula that fits these criteria

    In G6, enter this array formula:

    =IFERROR(SMALL(IF('Tab 1'!$C$5:$H$5<>"", 'Tab 1'!$C$4:$H$4), ROW(A1)), "")

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

    Now copy G2 down.

    Then in H6, enter this regular formula and copy down:

    =IF(G6="", "", HLOOKUP(G6, 'Tab 1'!$C$4:$H$5, 2,0))

  5. #5
    Registered User
    Join Date
    09-20-2016
    Location
    US
    MS-Off Ver
    2010
    Posts
    38

    Re: Excel, writing a formula that fits these criteria

    Works like a charm!!! Thank you very much!

  6. #6
    Valued Forum Contributor
    Join Date
    08-07-2014
    Location
    Quito, Ecuador
    MS-Off Ver
    Excel 2016 & 365, Windows 10
    Posts
    453

    Re: Excel, writing a formula that fits these criteria

    @ bordemkills

    Check this file
    It contains matricial formulas to solve the problem.

    Vicho

    Edit**
    Sorry, I didn't see JBeaucaire's answered before
    Attached Files Attached Files
    Barriers are there for those who don't want to dream

+ 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. How to extract data from range to a new worksheet if it fits 2 criteria
    By DrewBoid in forum Excel Formulas & Functions
    Replies: 16
    Last Post: 06-15-2016, 03:36 AM
  2. [SOLVED] Finding the next date that fits multiple criteria - Complex formula.
    By JulesR in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-20-2015, 03:33 AM
  3. [SOLVED] How to test if range selected fits criteria
    By anrichards22 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-10-2015, 12:40 AM
  4. Find smallest number that fits a criteria
    By HalPlz in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-10-2014, 03:49 PM
  5. Extracting data from column that fits criteria
    By Robo25 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 10-29-2013, 11:33 AM
  6. Help - Extracting an entire row that fits a single criteria
    By Babyanne in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-21-2009, 06:40 PM
  7. Look up that fits 2 criteria
    By njexpress9 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-25-2009, 04:53 PM

Tags for this Thread

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