+ Reply to Thread
Results 1 to 6 of 6

Extract data from one sheet to another (Index/Match Formula)

  1. #1
    Valued Forum Contributor
    Join Date
    04-21-2005
    Location
    Southern England
    MS-Off Ver
    Excel for Office 365
    Posts
    1,689

    Extract data from one sheet to another (Index/Match Formula)

    Hi,

    This problem has haunted me for years but I would like to find a solution...

    I use SAP and in order to get the actuals extracted from SAP I have to copy and paste them in which takes me days to do so wondered if there is an easier way?

    On the attached file I have two tabs. “Data Dump” is the extract from SAP and “P&L” which is a profit and loss template which is where I need the data input to.

    Is there any way I can use formula on the P&L tab which pulls the data from the dump based on the following:
    • MATCH Colum B on the “Data Dump” sheet with column A “P&L”
    • MATCH Colum C on the “Data Dump” sheet with column B “P&L”
    • MATCH Colum F on the “Data Dump” sheet with Row 4 on the “P&L” This would need to be grouped if this is even possible?

    All the entries on the Dump should go in to column M (Jan 13) as they are all January entries, this will not always be the case as in this example.

    I am open to any suggestions of how best to achieve this if it can be done?

    I have attached a file for reference
    Attached Files Attached Files
    Last edited by pauldaddyadams; 01-31-2013 at 12:36 PM.

  2. #2
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: Extact dtaa from one sheet to another (Index/Match Formula)

    hi there. try this in F6, to copy down & across:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  3. #3
    Valued Forum Contributor
    Join Date
    04-21-2005
    Location
    Southern England
    MS-Off Ver
    Excel for Office 365
    Posts
    1,689

    Re: Extact dtaa from one sheet to another (Index/Match Formula)

    Hi

    The formula is brill - I have added it to the attached file and it does seem to work. I do have problems with it not balancening which may be because there are some entries which appear on the dump that are not on the P&L.

    I have added column G on the "Data Dump" sheet. Is there a formula which would highlight non matching entries as everything on the dump should have a place on the P&L

    I have consolidated the data on the dump and p&l sheets to show the differences but without manually going through the dump list it is hard to identify what hasnt been "matched"

    Any ideas?
    Attached Files Attached Files

  4. #4
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: Extact dtaa from one sheet to another (Index/Match Formula)

    to use Conditional formatting based on different sheets require Named Ranges. i think it's more troublesome for you. so use this formula in "Date Dump" sheet G6 instead:
    =SUMPRODUCT(--(B6&C6='P&L'!$A$6:$A$369&'P&L'!$B$6:$B$369))>0
    copy down all the way. you can do an Autofilter or Conditional formatting now. those that are "FALSE" are not inside P&L

  5. #5
    Valued Forum Contributor
    Join Date
    04-21-2005
    Location
    Southern England
    MS-Off Ver
    Excel for Office 365
    Posts
    1,689

    Re: Extact dtaa from one sheet to another (Index/Match Formula)

    That new formula was a massive help - however it has identified a problem which until now i wasnt aware of. SAP must have a rules which groups certain GL codes and places them in the same place.

    I have uploaded an example which shows factory consumables - normally only cost centre 59413 is used however SAP must have an exception for this code to allow other cost centres to be used as in this case 58351 and 58905. I believe there is only a handful of general ledger codes like this factory consumables one that this exception exists for however I need to find a work around if possible?

    One Idea in the attached new upload is to insert additional columns to include extra cost centres - I have amended the formula be modified to include these cost centres but the formula is large - is their any other cleaner way?
    Attached Files Attached Files

  6. #6
    Valued Forum Contributor
    Join Date
    04-21-2005
    Location
    Southern England
    MS-Off Ver
    Excel for Office 365
    Posts
    1,689

    Re: Extract data from one sheet to another (Index/Match Formula)

    Hi

    Does anyone know of a way I can combine the formulas I have highlighted in yellow in the attached file in a different (cleaner) way?
    The way I have it (currently only highlighted in yellow and for jan13) it works but I fear it my crash excel when I copy it across multiple columns.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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