Data Extraction to another xls File

A

atzerv

New Member
Joined
Dec 10, 2013
Messages
9
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!



 
Sort by date Sort by votes
hmmm... Sorry! I thought that they will be uploaded. I will copy the spreadsheets and I will paste them. Thanks
 
Upvote 0
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)
**************************************************************************************************************************
[TABLE="width: 709"]
<colgroup><col span="3"><col></colgroup><tbody>[TR]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]Test Execution Steps[/TD]
[TD]Expected Result[/TD]
[TD]Actual Result[/TD]
[TD]Test
Result[/TD]
[/TR]
[TR]
[TD]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.
[/TD]
[TD]A. Will pop up a dialog to prompt user for confirmation.[/TD]
[TD]A. Error[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]45. Add on the following character "shdhsj" on the table for the selected fit[/TD]
[TD]A. Validation message should appear like "invalid entry" or "not accepted"[/TD]
[TD]A. Error[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]46. 2 users used the same account [/TD]
[TD]A. Same result to both users[/TD]
[TD]A. Error[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]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.[/TD]
[TD]A. OK[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]B. New browser page should ask for username and password.[/TD]
[TD]B. Error[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]C. OK[/TD]
[TD]C. Error[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]D. Expected not to be able to open a second account on the same PC. [/TD]
[TD]D. Error[/TD]
[TD]F[/TD]
[/TR]
</tbody>[/TABLE]
**************************************************************************************************************************

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"
**************************************************************************************************************************

[TABLE="width: 1059"]
<colgroup><col><col span="2"><col><col><col span="2"><col><col><col></colgroup><tbody>[TR]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Start Value:[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]End Value:[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Test Execution Steps[/TD]
[TD]Expected Result[/TD]
[TD]Actual Result[/TD]
[TD="colspan: 2"]Test
Result[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]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.
[/TD]
[TD]A. Will pop up a dialog to prompt user for confirmation.[/TD]
[TD]A. Error[/TD]
[TD]F[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]45. Add on the following character "shdhsj" on the table for the selected fit[/TD]
[TD]A. Validation message should appear like "invalid entry" or "not accepted"[/TD]
[TD]A. Error[/TD]
[TD]F[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]46. 2 users used the same account [/TD]
[TD]A. Same result to both users[/TD]
[TD]A. Error[/TD]
[TD]F[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD]B. New browser page should ask for username and password.[/TD]
[TD]B. Error[/TD]
[TD]F[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD]D. Expected not to be able to open a second account on the same PC. [/TD]
[TD]D. Error[/TD]
[TD]F[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: center"]#NUM![/TD]
[TD="align: center"]#NUM![/TD]
[TD="align: center"]#NUM![/TD]
[TD]#NUM![/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]


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




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!
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
Cancel merged columns and arrange structure across files in the folders
2 3
Replies
20
Views
393
Joe4
Joe4
Excelwhatsthat
  • Solved
Finding a cell value that is a few columns away on the same row, based on most recent value.
Replies
3
Views
108
Fluff
Fluff
G
  • Question Question
Need help with the Filter function
Replies
4
Views
101
Eric W
Eric W
A
  • Question Question
referencing pivot table in another workbook
Replies
0
Views
119
ajm
A
R
  • Question Question
Could I ask for VBA (or python) code to create a new workbooks?
Replies
2
Views
177
Radoslaw Poprawski
R

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top