Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Extraction Routine for Excel Range

Posted on 2014-03-27
15
Medium Priority
?
223 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 6
15 Comments
 
LVL 31

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 31

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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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 31

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 31

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
 

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 31

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 31

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 31

Expert Comment

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

Accepted Solution

by:
gowflow earned 2000 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 31

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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

610 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