+ Reply to Thread
Results 1 to 9 of 9

Multiple formulas needed please help

  1. 01-28-2014, 03:12 PM #1
    WimpieOosthuizen
    WimpieOosthuizen is offline
    Forum Contributor
    Join Date
    01-14-2014
    Location
    South Africa
    MS-Off Ver
    Excel 2010
    Posts
    109

    Exclamation Multiple formulas needed please help

    Good day

    Please assist I am Stumped
    I have attached the File I am working with
    I have information in the "Data" Sheet (this is for multiple staff members)
    I have created a sheet called "Requirement"
    I need to extract the information into the "Requirement" sheet for each employee on the list in the "Data" sheet
    Each employee will have a unique number Column A (B069 or M1030 each employee number will start with an Alphabetic charter with 3 to 4 numeric number)
    I have highlited the information needed in the "Data" file and profided a example of what is needed in the "Requirement" sheet
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 01-28-2014, 03:43 PM #2
    WimpieOosthuizen
    WimpieOosthuizen is offline
    Forum Contributor
    Join Date
    01-14-2014
    Location
    South Africa
    MS-Off Ver
    Excel 2010
    Posts
    109

    Re: Multiple formulas needed please help

    I would even appreciate a Macro if that would work better
    Reply With QuoteRegister To Reply
  3. 01-28-2014, 04:36 PM #3
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,933

    Re: Multiple formulas needed please help

    Hi Wimpie, hoe gaan dit?

    OK try this approach.

    on sheet1, use this in H3, copied down...
    =IF(A3="name",INT(H2)+1,ROUND(H2+0.01,2))

    Then for the name, use this...
    Name=INDEX(Sheet1!$C:$C,MATCH(ROWS($A$1:$A1),Sheet1!$H:$H,0))
    Not sure how you would pull the surname from that if they have 2 or more "last names", but this will pull the last "name"...
    =MID(B3,FIND("@@",SUBSTITUTE(B3," ","@@",LEN(B3)-LEN(SUBSTITUTE(B3," ",""))),1)+1,99)
    Then for the rest, use these...
    ID=INDEX(Sheet1!$C:$C,MATCH(ROWS($A$1:$A$1)+0.05,Sheet1!$H:$H,0))
    Gender=INDEX(Sheet1!$C:$C,MATCH(ROWS($A$1:$A1)+0.08,Sheet1!$H:$H,0))
    Race=INDEX(Sheet1!$C:$C,MATCH(ROWS($A$1:$A1)+0.33,Sheet1!$H:$H,0))
    etc. You will note that in each of these, the +.05 changes to 0.08 to 0.33 etc
    each of these represents the decimal part of the value in the helper in sheet1
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford
    Reply With QuoteRegister To Reply
  4. 01-28-2014, 05:45 PM #4
    Xx7
    Xx7 is offline
    Forum Contributor
    Join Date
    01-30-2011
    Location
    Vancouver, Canada
    MS-Off Ver
    Excel 2010
    Posts
    604

    Re: Multiple formulas needed please help

    Check the attached,

    1) I created 3 helper columns in the Data TAB in "BLUE"
    2) I'm not sure which field you are referring to for "Basic Salary"
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  5. 01-28-2014, 06:23 PM #5
    WimpieOosthuizen
    WimpieOosthuizen is offline
    Forum Contributor
    Join Date
    01-14-2014
    Location
    South Africa
    MS-Off Ver
    Excel 2010
    Posts
    109

    Re: Multiple formulas needed please help

    Xx7

    How did you manage to get the individual list of names into the requered sheet?
    I can not see a formula
    Reply With QuoteRegister To Reply
  6. 01-28-2014, 06:28 PM #6
    WimpieOosthuizen
    WimpieOosthuizen is offline
    Forum Contributor
    Join Date
    01-14-2014
    Location
    South Africa
    MS-Off Ver
    Excel 2010
    Posts
    109

    Re: Multiple formulas needed please help

    Hi FDibbens

    Gaan goed hier in SA net lekker warm.

    Tried to use your approach but not geeting it right, i am trying Xx7 one as he sent me an updated file, i have to redo it as i could not disclose the income
    Reply With QuoteRegister To Reply
  7. 01-28-2014, 07:10 PM #7
    WimpieOosthuizen
    WimpieOosthuizen is offline
    Forum Contributor
    Join Date
    01-14-2014
    Location
    South Africa
    MS-Off Ver
    Excel 2010
    Posts
    109

    Re: Multiple formulas needed please help

    Xx7

    I see that brought the names in with a Pivot.
    Let me just say Thank you to all who assisted, including FDibbins
    This is a great solution to my problem and it saved me hours of manual work
    Reply With QuoteRegister To Reply
  8. 01-28-2014, 07:19 PM #8
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,933

    Re: Multiple formulas needed please help

    OK that was working fine until the format of your data changed If the PT offered by xx7 works, we can leave it at that
    Reply With QuoteRegister To Reply
  9. 01-29-2014, 01:14 AM #9
    arlu1201
    arlu1201 is offline
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Multiple formulas needed please help

    WimpieOosthuizen,

    Please take a moment to read the forum rules and then amend your thread title to something descriptive of your problem. Once you have done this please send me a PM and I will remove this request.

    To change a Title on your post, click EDIT POST then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Macro needed in a file with lookup formulas that will freeze formulas if value is found
    By Elgreco in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-25-2013, 02:51 PM
  2. [SOLVED] Multiple IF formulas help needed
    By Zozika in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 06-28-2013, 01:00 PM
  3. [SOLVED] multiple formulas needed in one cell to create a value in a new cell
    By jamietofs in forum Excel General
    Replies: 13
    Last Post: 11-18-2012, 06:40 PM
  4. multiple formulas needed -
    By mheinmiller in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-16-2008, 12:17 PM
  5. Help needed on formulas
    By busterbrown885 in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 08-06-2005, 02:05 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1