Solved

Extraction Routine for Excel Range

Posted on 2014-03-27
15
211 Views
Last Modified: 2014-04-03
EE Pros.,

I have a file that comes to me weekly.  Within the massive amount of data, I need to extract specific rows of data containing a text string.  What I need is a macro that takes a defined Range name (i.e. the raw data in the columns/rows) and finds the specific text within a particular column or row.  Once it is found, it exports the specific row to another worksheet and names the worksheet with the range name.

There may be an easier way to do this (dataslicers?) but I'm thinking "macro" so that I can easily take an incoming data file, name a range, run the routine and then perform all other operations on the extracted data.

Thank you in advance.

B.
Extract-Routine.xlsx
0
Comment
Question by:Bright01
  • 9
  • 6
15 Comments
 
LVL 29

Expert Comment

by:gowflow
ID: 39959124
Hi
You forgot to mention from what sheet we lookup the info (in this case Page1_1) but will it allways be that or ?

Presume you can have as many If as already designed ? or more ?

When you say: 4.) You designate the and/or or blank to stop next criteria...
what if one has put the Column ref and criteria but forgot to put the and/or ... ? it will not take that column into consideration ?


gowflow
0
 

Author Comment

by:Bright01
ID: 39959501
gowflow,

greetings!

1.) The Sheet will have a different name. I was hoping that by defining the range, it wouldn't have to point to the Sheet where the data was coming from.  If so, could we add a cell where the Range is named to name the sheet?

2.) I think 5 If statements are enough.  If you choose a blank in the in Column/Row option, then the comparison is completed.  In other words, you can do less then 5 If Statements.

3.) The If Statements mean that I can "look" for the text string in up to 5 Columns and the result will be that it will produce the output for the entire rows where it finds the string.  If you only use two columns, it will only look in those two columns for the match.

Hope that answers your questions.

B.

3.)
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39959638
Hello,

1.) The Sheet will have a different name. I was hoping that by defining the range, it wouldn't have to point to the Sheet where the data was coming from.  If so, could we add a cell where the Range is named to name the sheet?

I know that the sheet will have a different name you are talking about the Extracted sheet that is fine with the value of cell F4 it holds that there. I am talking about the sheet that we need to lookup the data for ! and for sure if you have a sheet name every time, then you ought to have a cell to refer to that sheet same as F4 in this case which cell would you pick so I incorporate it in the file I have?

As far as point 2) and 3) understood that 5 would be enough that is no problem.

I am now concerned with the alternative you have put (Column or Row) what if you pick Row say in Cell E7 what would you then put in Cell F7 ??? For columns it is ok to put the Column label but for Row ??? what do you have in mind ?

gowlfow
0
 

Author Comment

by:Bright01
ID: 39959896
1.) The source file/sheet will always have the same name. (I'll either rename the file each time or copy and paste the source data).

2.) I really only need "column".   I thought I might use row later.... but to keep it simple, let's just identify the columns for the identification.

Thank you,

B.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39960357
1.) The source file/sheet will always have the same name. (I'll either rename the file each time or copy and paste the source data).

I think you are not understanding what I am asking.
The file you posted is called:
Extract-Routine.xlsx
I am not talking about this name.

the file contains 3 sheets:
Page1_1
Extract
March3

I presume that when you run the Macro that will extract data the file naturally should contain only 2 sheets:
Page1_1
Extract

and the button that activate the macro is in the sheet Extract that is a template for extraction criterias. amoung these criterias you have a cell F4 where you put the name 'March3' or 'abc' or whatever that will be the name of the output result of extraction.

My question was and still is:
What is the name of the sheet that contain the data that we need to pull the records from in this case obviously it is Page1_1 but ultimately as we are sitting in sheet Extract the code will not know from where to pull the data unless we tell it:

Either by specifying a certain name: like always 'Page1_1 or Data or Sheet1 or ...

OR
by creating like F4 an other cell in your sheet Extract where you can then put the name Page1_1 and the code will read that cell to get the sheet name.

Got it ???
gowflow
0
 

Author Comment

by:Bright01
ID: 39961143
Yes.  Thank you for the explanation.  I would appreciate it if we could point (like in F4) to the right sheet where the data is.

Thank you!

B.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39961617
F4 ?????????????????????????????????????????????/
and where do we put the reference to the new created sheet ?????????????? March3 for that matter ??? it is already used for the output sheet !!!


gowlfow
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:Bright01
ID: 39961668
gowflow,

March3 is only a named WS because I created it to show what I meant for the output (the Macro creates the WS).  So until the macro runs, it doesn't actually exist.

I have modified the original view so you can see what it looks like before the macro runs.

Hope this helps.

B.
Extract-Routinev2.xlsx
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39961702
duuuuhhh !!!

You added F5 the worksheet reference and that is what I was trying to get to and was fighting for to get you to understand. !!! Let me make the macro (so we agree to remove the 'row' thing as non sense in that specific context.

Right ??
pls confirm

tks
gowlfow
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39961722
One last thing, you noticed that the output in March3 you have the data but no header !!!!

so Question:
in Page1_1 the header is on row 27, presume this is not standard and the row is volatile so please confirm correct.

then if above statement is correct, then can we assume that the string:
Customer Name indicate that this is a Header ???? or if not what would be the string that can without any doubt confirm that this is a header ??

As frankly believe it is uncomplete to just save a bunch of data without a header it is not 'professional like' !!! :)

gowflow
0
 

Author Comment

by:Bright01
ID: 39961737
gowflow,

I left the header out so as not to complicate the macro.  I was simply going to copy it in afterwards.  If you want to add it then great.  Row 27 is stable.

Yes.  We can take "row" selection out.

Thank you,

B.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39961820
ok no sweat. pls allow me sometime.
gowflow
0
 
LVL 29

Accepted Solution

by:
gowflow earned 500 total points
ID: 39963550
ok I have been looking at these criterias and trying to make some sense out of all this 'chaos' and as we removed the Row and left with only columns there is no need anymore for the dropdown in Col E to choose from Row or Column.

Also by the same token I see under Col H you labeled it Text or Number which brought me to the following that I believe could be best appropriate in the scope of what you requested.

I introduced the list of operators > < = >= <= <> Like that you may choose from in Column G and also did some data validation in Col F to restrict any input that will not lead to a column reference and removed this Row/Column choice.

Please check the attached file only for these criteria's and if all ok then I will proceed to developing the macro accordingly.

The operators have been introduced in the event you want to get data extracted for say sales >= 1000 or flag <> "" kind of things that wouldn't hv been possible in the previous layout.

gowflow
Extract-Routine-V01.xlsm
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39966731
Any chance to have looked at the previous post ?
gowflow
0
 

Author Closing Comment

by:Bright01
ID: 39976627
Thanks Gowflow!  You continue to do great work.

Appreciate it.

B.
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
excel file 5 47
How to Auto-fill data in Database 2 19
Excel VBA - open a DataValidation dropdown 6 21
TT Column Arrange 10 27
Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now