Bright01
asked on
Extraction Routine for Excel Range
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
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
ASKER
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.)
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.)
Hello,
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
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
ASKER
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.
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.
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
ASKER
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.
Thank you!
B.
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
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
ASKER
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
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
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
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
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
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
ASKER
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.
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.
ok no sweat. pls allow me sometime.
gowflow
gowflow
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Any chance to have looked at the previous post ?
gowflow
gowflow
ASKER
Thanks Gowflow! You continue to do great work.
Appreciate it.
B.
Appreciate it.
B.
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