+ Reply to Thread
Results 1 to 7 of 7

Need help with IF formula

  1. #1
    Registered User
    Join Date
    06-19-2018
    Location
    RVA
    MS-Off Ver
    Excel 2010
    Posts
    3

    Need help with IF formula

    Hi Everyone,

    I have been visiting this forum but this is my first time posting. I need help with a formula. I have a spreadsheet with many columns and rows but I am only concerned with two columns here. One is the Employee ID ((Column C) and the other is Device Type (Column P). Employee ID column can have duplicate employee Ids since an employee can have multiple device types. The device type has seven different options (Aircard, PDA Voice/Data, Telemetry, Cell Phone, Satellite, PDA Data, Pager). I am trying to do create two new columns with formulas:

    1. New column that will indicate "Yes" for employee ids that have "PDA Voice/Data" and "Cell phone"

    2. New column that will indicate "Yes" for employee IDS who have more than two device types


    Thanks in advance!!

  2. #2
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Need help with IF formula

    A workbook with examples and expected results would help.
    but... for #1, it seems this may not be dependent on the ID but rather what is in column P, if so then this would work... =IF(OR(P2="pda voice/data",P2="cell phone"),"yes","")
    for #2, again it seems to not be dependent on what is in column P but rather how often the employee appears in column C, if so then this would work...
    =IF(COUNTIF($C$2:C2,C2)>1,"yes","")
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  3. #3
    Registered User
    Join Date
    06-19-2018
    Location
    RVA
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Need help with IF formula

    Thank you Sambo Kid for the reply! I have attached the file. Removed bunch of columns so the column letters have changed now.


    For #1, it is dependent on the Employee ID (Column A). I am looking at the employee IDs in column A that have both in Device Type (Column B): "PDA Voice/Data" AND "Cell Phone". For Example Employee ID 954 in the file.

    Number #2 works great, thanks!
    Attached Files Attached Files

  4. #4
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Need help with IF formula

    this will work IF cell phone is in the cell immediately preceding pda voice/data.
    =IF(AND(A2=A3,B2="cell phone",B3="pda voice/data"),"yes","")
    I'm trying to figure out a different solution in case they are separated or in a different order.

  5. #5
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Need help with IF formula

    Rather than do a repeating summary of each ID (more than 48,000 rows of calculations ) there is a summary section F:H

    In column F unique IDs using remove duplicates.

    These take quite a bit of time to calculate, but they were the fastest I could come up with.

    For Yes/No ID having both "PDA Voice/Data" and "Cell phone" entered in G3. Fill down.
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    For Yes/No ID have more than two device types entered in H3. Fill down.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Dave

  6. #6
    Registered User
    Join Date
    06-19-2018
    Location
    RVA
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Need help with IF formula

    Thank you both for your help! Really appreciate it!

  7. #7
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Need help with IF formula

    You are welcome. Thank you for the feedback.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

    Edit Sambo kid mentioned it ...

    If it is possible to sort source data on IDs final calculations would only have to address those sections of data rather than check the whole 24,000+ range each time (for each column).

    It would use a lookup table that defines lower and upper boundaries of relevant data.

    If that is doable at your end and you are interested please let us know.
    Last edited by FlameRetired; 06-21-2018 at 06:20 PM.

+ 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. Replies: 5
    Last Post: 02-16-2018, 06:50 AM
  2. Replies: 8
    Last Post: 09-22-2017, 05:41 AM
  3. Excel formula bar to display the result of the formula , not the formula?
    By max_max in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-18-2016, 07:15 PM
  4. Replies: 11
    Last Post: 06-06-2014, 03:34 PM
  5. how to hide formula in formula box, view lookup result in formula box?
    By vengatvj in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-14-2013, 04:06 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