Solved

Extraction Routine for Excel Range

Posted on 2014-03-27
15
212 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

863 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

27 Experts available now in Live!

Get 1:1 Help Now