+ Reply to Thread
Results 1 to 9 of 9

Formula question - how can i pull the data i am looking for?

  1. #1
    Registered User
    Join Date
    05-21-2018
    Location
    Rhode Island
    MS-Off Ver
    365 ProPlus
    Posts
    4

    Formula question - how can i pull the data i am looking for?

    I have an excel report that shows account numbers and a subtotal for the department. Each department has the same account number, so the detail looks like this:


    Account 0001
    Account 0002
    Account 0003
    Department 1234 subtotal


    Account 0001
    Account 0002
    Account 0003
    Department 5678 subtotal


    I am trying to use a formula to pull the amount for account 0002 for department 5678, but I am not sure how to do this without doing a 'sumif' and picking up all account 0002 instances, or by manually adding the department number to the account number so that I have a unique identifier. there are 60k rows of data, so combining the account number and department number would be time consuming. Is there an easier formula that would pull the balance for account 0002 in department 5678 on a monthly basis, when there may be accounts or departments added so the rows change?

  2. #2
    Registered User
    Join Date
    05-21-2018
    Location
    Rhode Island
    MS-Off Ver
    365 ProPlus
    Posts
    4

    Re: Formula question - how can i pull the data i am looking for?

    Is there a way to create a formula that looks for 'department 5678 subtotal' and then pulls the data for the first 'account 0002' preceding it?

  3. #3
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    35,967

    Re: Formula question - how can i pull the data i am looking for?

    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.
    Trevor Shuttleworth - Excel Aid

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  4. #4
    Registered User
    Join Date
    05-21-2018
    Location
    Rhode Island
    MS-Off Ver
    365 ProPlus
    Posts
    4

    Re: Formula question - how can i pull the data i am looking for?

    Attached is an example. Each month, I would pull the same information on the "after" tab that is in yellow, however the data is downloaded from PeopleSoft so there may be additional rows of data added each month.
    Attached Files Attached Files

  5. #5
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    22,614

    Re: Formula question - how can i pull the data i am looking for?

    Do the number of accounts vary by month and/or department?

  6. #6
    Registered User
    Join Date
    05-21-2018
    Location
    Rhode Island
    MS-Off Ver
    365 ProPlus
    Posts
    4

    Re: Formula question - how can i pull the data i am looking for?

    They do change, there may only be 1-2 new accounts added in a month, but it would be added for each department, so the impact on the report may be about 10-12 new lines.

  7. #7
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    22,614

    Re: Formula question - how can i pull the data i am looking for?

    Assuming the data is as shown in "Before"

    in A1 of "After"


    =COUNTIF(Before!$A$3:$A$1000,"Account*")/COUNTIF(Before!$A$3:$A$1000,"Subtotal*")

    this a count of the accounts (10 in this case)

    in F5

    =INDEX(Before!$B$3:$B$37,MATCH("*"&After!$E5,Before!$A$3:$A$37,0)-1-($A$1-RIGHT($D$3,4)*1))

    This currently assumes data for only one month

    Attached has formula in H: change account in D3
    Attached Files Attached Files
    Last edited by JohnTopley; 05-21-2018 at 05:03 PM.

  8. #8
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365, rarely 2007
    Posts
    12,616

    Re: Formula question - how can i pull the data i am looking for?

    Another way. Array entered and filled down. If you arenít familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Dave

  9. #9
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365, rarely 2007
    Posts
    12,616

    Re: Formula question - how can i pull the data i am looking for?

    This version would not have to be array entered. My apologies for not posting the simpler way.
    Formula: copy to clipboard
    Please Login or Register  to view this content.

+ 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] Excel does not pull value from the cell - appears to pull formula
    By enjoyexcel in forum Excel General
    Replies: 2
    Last Post: 01-06-2015, 11:42 AM
  2. Need formula to pull data based off a data validation (please help!!)
    By Thanks4helping in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-20-2014, 11:08 PM
  3. Formula to Pull data
    By cartica in forum Excel General
    Replies: 3
    Last Post: 11-26-2013, 01:25 PM
  4. formula to pull data
    By bryan469 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-24-2013, 03:03 PM
  5. Need a formula pull data
    By cgayle in forum Excel General
    Replies: 2
    Last Post: 02-27-2012, 03:07 PM
  6. Formula to pull data out of a cell
    By SEMMatt in forum Excel General
    Replies: 9
    Last Post: 09-28-2011, 09:27 AM
  7. Replies: 2
    Last Post: 07-01-2005, 02:05 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