saranblade
New Member
- Joined
- Oct 30, 2013
- Messages
- 1
Hello all. I'm new here - thanks for having me and checking out my post.
I work for an organization that uses an Excel-based product application. I am the point man redesigning our entire application process so that the application is tailor-made to fit customers' needs based on a number of creiteria they provide us with at the point of inquiry.
I have already constructed a workbook that includes a "master list" of possible application questions which I desire to filter out based on the customer's applicable criteria. Each question is assigned a question code for programming purposes, so questions will be called "GI1" for General Info 1, or "FI1" for Financial Info 1. I'm not able to post attachments (yet?) so here's a general look and what the list looks like.
-----
...A B C D E F G H I
1
2 G1 G1. Question text blah blah
3 G1 question text continued
4 G1 [answer here]
5 F1 F1. Question Text blah blah
6 F1 blah blah blah
7 F1 blah blah
8 F1 [answer here]
-----
For the purpose of making the application more aesthetically pleasing, the range contains merged cells(!).
I already have logic programmed into my workbook that spits out a list of question codes applicable to the particular customer's request. This list populates a single "Question Results" column with about 120 rows. For each potential question, if the question is applicable based on logic I've put into the workbook, one of the cells in that column will populate with the applicable question code. If the question is not applicable, the corresponding cell will show 0 instead.
This approach provides me with a workable criteria range for an Advanced Filter; the issue with that approach is that Advanced Filter obviously does not play nicely with the merged cells that management is requiring. For whatever reason, filter in place is not working for me and, at any rate, it won't copy the list to the separate sheet where I want it.
Therefore, instead of building an application by selecting questions that apply to the particular situation, I want to use a macro to do the following "alternative filter":
1). Copy the master list of questions to a separate sheet titled "CustomApp". The top-left cell of this range would ideally be CustomApp!A11.
2). Based on the "Question Results" list which is populated with question codes such as G1 and F1, delete all of the rows where CustomApp!A11:A1100 does not include a question code from the list. For this example, let's say the criteria range is located at =Sheet2!$F$1:$F$120.
3). If the Delete Entire Row function would not otherwise do so, shift remaining rows so that they are all together with no spacing in between them.
If possible, since I do not know VBA, if you could also point me to a place where I can learn to trigger this macro by clicking a shape, I would appreciate it.
Thanks a lot for your time. Please let me know if there's anything you need me to clarify.
I work for an organization that uses an Excel-based product application. I am the point man redesigning our entire application process so that the application is tailor-made to fit customers' needs based on a number of creiteria they provide us with at the point of inquiry.
I have already constructed a workbook that includes a "master list" of possible application questions which I desire to filter out based on the customer's applicable criteria. Each question is assigned a question code for programming purposes, so questions will be called "GI1" for General Info 1, or "FI1" for Financial Info 1. I'm not able to post attachments (yet?) so here's a general look and what the list looks like.
-----
...A B C D E F G H I
1
2 G1 G1. Question text blah blah
3 G1 question text continued
4 G1 [answer here]
5 F1 F1. Question Text blah blah
6 F1 blah blah blah
7 F1 blah blah
8 F1 [answer here]
-----
For the purpose of making the application more aesthetically pleasing, the range contains merged cells(!).
I already have logic programmed into my workbook that spits out a list of question codes applicable to the particular customer's request. This list populates a single "Question Results" column with about 120 rows. For each potential question, if the question is applicable based on logic I've put into the workbook, one of the cells in that column will populate with the applicable question code. If the question is not applicable, the corresponding cell will show 0 instead.
This approach provides me with a workable criteria range for an Advanced Filter; the issue with that approach is that Advanced Filter obviously does not play nicely with the merged cells that management is requiring. For whatever reason, filter in place is not working for me and, at any rate, it won't copy the list to the separate sheet where I want it.
Therefore, instead of building an application by selecting questions that apply to the particular situation, I want to use a macro to do the following "alternative filter":
1). Copy the master list of questions to a separate sheet titled "CustomApp". The top-left cell of this range would ideally be CustomApp!A11.
2). Based on the "Question Results" list which is populated with question codes such as G1 and F1, delete all of the rows where CustomApp!A11:A1100 does not include a question code from the list. For this example, let's say the criteria range is located at =Sheet2!$F$1:$F$120.
3). If the Delete Entire Row function would not otherwise do so, shift remaining rows so that they are all together with no spacing in between them.
If possible, since I do not know VBA, if you could also point me to a place where I can learn to trigger this macro by clicking a shape, I would appreciate it.
Thanks a lot for your time. Please let me know if there's anything you need me to clarify.
Last edited:
