Results 1 to 6 of 6

Thread: Data Extraction to another xls File
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Dec 2013
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Data Extraction to another xls File

    Dear friends, I found this site (Extract all rows from a range that meet criteria in one column in excel | Get Digital Help - Microsoft Excel resource) with a very good formula and very helpful for my task that I have to complete.
    I attached these two files just to ask help because I need to use this formula to bring the valued from merged Cells, back to a different XLS file.
    So, my aim is to update automatically a spreadsheet with values from many different Excel files.

    The first problem that I have to solve, is the Merged cells that return '0' value.
    The second problem is how I will connect the "Data Extraction" xls file with the "Source File" xls file to get the results from there?


    The results that you see at image 2 are from image 1 which is at the same workbook. The formula that I am using is the following (in order to work the formula press: Shift+CTRL+Enter):
    =INDEX('2 Results'!$A$14:$F$25,SMALL(IF(($L$3<='2 Results'!$F$14:$F$25)*($L$4>='2 Results'!$F$14:$F$25),MATCH(ROW('2 Results'!$D$14:$D$25),ROW('2 Results'!$D$14:$D$25))),ROW('2 Results'!C1)),COLUMN('2 Results'!C1))


    My aim is to extract the values from image 3 to image 2.


    Could you help me with this please?


    Thank you very much!

    [IMG]X:\AXZ\COM\B2B\XLS Testing\1jpg[/IMG]
    [IMG]X:\AXZ\COM\B2B\XLS Testing\2.jpg[/IMG]
    [IMG]X:\AXZ\COM\B2B\XLS Testing\3.jpg[/IMG]

  2. #2
    Board Regular mrmmickle1's Avatar
    Join Date
    May 2012
    Location
    Charlotte, NC
    Posts
    2,453
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Data Extraction to another xls File

    The image links you have provided are broken (they don't link to anything)
    Matt Mickle
    Microsoft MVP - Excel
    Using Excel & Access 2010, 2013 & 2016 | Windows 7 | 64 Bit

    Post a screen shot with the MrExcel HTML Maker Add-In or the Excel Jeanie Add-In

  3. #3
    New Member
    Join Date
    Dec 2013
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Data Extraction to another xls File

    hmmm... Sorry! I thought that they will be uploaded. I will copy the spreadsheets and I will paste them. Thanks

  4. #4
    New Member
    Join Date
    Dec 2013
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Data Extraction to another xls File

    The following Tables displays the spreadsheet "2 Results" that has the values that we want to extract to the second spreadsheet with the name "Extract Data":
    ************************************************************************************************************************ **
    Spreadsheet "2 Results"
    (I put the column names on top of each column to be easier to understand the formula)
    ************************************************************************************************************************ **
    C D E F
    Test Execution Steps Expected Result Actual Result Test
    Result
    26. Click the "X" icon of remove button.
    Click "No" from the confirmation dialog.
    Click remove button again and get "Yes" from the confirmation dialog.
    A. Will pop up a dialog to prompt user for confirmation. A. Error F
    45. Add on the following character "shdhsj" on the table for the selected fit A. Validation message should appear like "invalid entry" or "not accepted" A. Error F
    46. 2 users used the same account A. Same result to both users A. Error F
    64. Login with user account.
    65. Open a new browser, copy the URL from first browser to new browser.
    66. Log off from the second browser.
    67 Log in with a different account.
    A. OK
    B. New browser page should ask for username and password. B. Error F
    C. OK C. Error
    D. Expected not to be able to open a second account on the same PC. D. Error F
    ************************************************************************************************************************ **

    The following table are the extract results to the spreadsheet with the name "Extract Data" from the first spread sheet named "2 Results":
    The formula used to retrieve the data from the first spreadsheet to the second one is the following:
    =INDEX('2 Results'!$A$14:$F$25,SMALL(IF(($L$3<='2 Results'!$F$14:$F$25)*($L$4>='2 Results'!$F$14:$F$25),MATCH(ROW('2 Results'!$D$14:$D$25),ROW('2 Results'!$D$14:$D$25))),ROW('2 Results'!C1)),COLUMN('2 Results'!C1))
    ***Press Ctrl + Shift + Enter in order to work***

    Spreadsheet "2 Results"
    ************************************************************************************************************************ **

    C D E F G H I J K L
    Start Value: F
    End Value: F
    Test Execution Steps Expected Result Actual Result Test
    Result
    26. Click the "X" icon of remove button.
    Click "No" from the confirmation dialog.
    Click remove button again and get "Yes" from the confirmation dialog.
    A. Will pop up a dialog to prompt user for confirmation. A. Error F
    45. Add on the following character "shdhsj" on the table for the selected fit A. Validation message should appear like "invalid entry" or "not accepted" A. Error F
    46. 2 users used the same account A. Same result to both users A. Error F
    0 B. New browser page should ask for username and password. B. Error F
    0 D. Expected not to be able to open a second account on the same PC. D. Error F
    #NUM! #NUM! #NUM! #NUM!


    ************************************************************************************************************************ **




    THE AIM of my task is to extract the data from the following Spreadsheet named "Source Spreadsheet" which belongs to different xls 2010 file. So, the data from "Source Spreadsheet" wish to be at the spreadsheet named "Extract Data" of the previous file.
    When that established, then I will be able to extract more data from other files, too.
    Thanks!

  5. #5
    Board Regular mrmmickle1's Avatar
    Join Date
    May 2012
    Location
    Charlotte, NC
    Posts
    2,453
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Data Extraction to another xls File

    I'm unsure of the task you are trying to accomplish. Are you saying that the formula you have is not providing the results that you expect? If so where is the formula failing? Can you provide a before and after. You can download this Free Add-In in order to post data on the message board in a more readable format. This will allow others users on the forum to better understand your issue:

    Ask Mr. Excel - VBA HTML Maker
    Matt Mickle
    Microsoft MVP - Excel
    Using Excel & Access 2010, 2013 & 2016 | Windows 7 | 64 Bit

    Post a screen shot with the MrExcel HTML Maker Add-In or the Excel Jeanie Add-In

  6. #6
    New Member
    Join Date
    Dec 2013
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Data Extraction to another xls File

    Thank you very much! I will do it right away and then I will wait your feedback

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •