Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Every week, a new Excel file is generated by a CRM system, which has the date-stamp in it's file-name, but placed in a folder at a fixed location (a sharepoint URL).

This file is quite large, and usually contains anywhere between 25-30 thousand rows. Out of this, I need to create a new table, relevant to my purpose.

Rows relevant to my purpose, must match one (or more) keywords/key-phrases, from a list of 30-40 such keywords/key-phrases. This list of keywords/key-phrases, however also grows, but slowly, s.a. adding new keywords once every few months.

Is there some way to automate this manual, tedious and somewhat error-prone task ?

share|improve this question
    
Have you tried recording a macro of what you are presently doing 'manually'? –  pnuts Sep 25 '12 at 19:16
    
@pnuts, thanks for replying. Recording macro is certainly one possibility, I agree, but not having used it previously, I wasn't sure if records actions as diverse as Search, Copy-Paste, reorder (Sort) etc. I will explore that route. –  icarus74 Sep 26 '12 at 9:08
    
It does - every single key stroke you record is reproduced (except cancel!). The problems can be (i) that it may add a lot more than you instruct - some formatting etc it does automatically (makes for messy code) and (ii) something like Autofilter (a toggle) might not give you the same answer each time - depends on filter status when you open sheet. PS consider recording using RC type references. However, SU/SO is here to help! Good luck. –  pnuts Sep 26 '12 at 11:27
    
@pnuts, that seems like a possibly good answer. I'd definitely upvote (minimally) or even accept. Plan to keep the question open a little longer, to see if there are any other miraculously simple and reliable alternatives, although I suspect that there may not be one. –  icarus74 Sep 27 '12 at 10:19
1  
Have you tried =OR(ISNUMBER(MATCH(B1,keyarray,0)),ISNUMBER(MATCH(D1,keyarray,0)),ISNUMBER(MATC‌​H(F1,keyarray,0))) in Row1 of some spare column and copying down, where B, D and F are references to your "Account", "Reseller" and "L2 Channel" columns (with TRUE for Keep, FALSE for Delete)? –  pnuts Oct 3 '12 at 11:47

2 Answers 2

up vote 1 down vote accepted

Because three different columns (assumed below to be B, D and F) need to be checked, an OR function looks appropriate:

=OR(ISNUMBER(MATCH(B1,keyarray,0)),
    ISNUMBER(MATCH(D1,keyarray,0)),
    ISNUMBER(MATCH(F1,keyarray,0)))

so that a match for any of the three columns will return TRUE. Place this in a ‘helper’ column in the first row occupied by data (say Row 1, or adjust B1,D1 and F1 above accordingly).

MATCH checks to see whether, for example, the value of B1 exists in keyarray, where that is the given name for the range that contains the list of keywords/key-phrases (~30-40 in number) – need not be in same sheet or workbook but if not the full path must be specified and recommended that the 'other' workbook is open when applying the formula to a new batch of data.

0 forces an exact match only (alternatively -1 for smallest value that is greater than or equal to B1, or 1 for largest.)

MATCH returns the location of the found value in the array (otherwise, with parameter 0, #N/A). That is numeric so ISNUMBER tests for a number (any number) – to exclude #N/A results.

Hence provided any of B1, D1 or F1 is in keyarray the result will be TRUE – otherwise FALSE.

To copy the formula down over 25-30 thousand rows conveniently where some contain merged cells, place something (say ‘end’) in the intersection of the last occupied row and the ‘helper’ column (to prevent expanding the size of the spreadsheet unnecessarily). Copy the cell containing the formula, select the cell immediately below it then Ctrl+Shift+Down/Paste to fill the helper column downwards for all occupied rows, without continuing past last occupied row and with overwriting ‘end’.

Filter on ‘helper’ column for TRUE, select all occupied columns, copy and paste into new sheet/workbook. Delete blank rows in new sheet/workbook and save. (May choose to delete ‘helper’ column from source also.)

Ensure that when occasionally adding items to keyarray the named range covers the additions.

share|improve this answer
    
This turns out to be much simpler, and a nice compromised between what is done today, and complete automation (requiring VBA scripting, which I am not comfortable with). –  icarus74 Oct 10 '12 at 7:23
    
Thank you. Sometime I suggest when doing this 'manually', you turn on Record Macro first and later have a look at the code generated. It probably won't quite suit for future use without adjustment but should require only a little modification to 'fully' automate the process. Incidentally, the above may cover the difficult part, but it does not create your table - is that part of what you would like answered? –  pnuts Oct 10 '12 at 8:55

This is a bigger task wich may be solved in a vast variety of ways. But in short, YES, you can automate this.

Just to start you off:

Are you absolutely sure, that your CRM System cannot provide this information you want to extract directly?

Usually they are based on a Databasesystem, like SQL, and because you are already extracting data out of it, you might be able to modify this output to match your needs.


Now to the possiblities with excel:

  1. Importing the base data from your CRM

you might be able to establish a data-connection to your CRM

you can create VBA-Code to import the newest file in your folder or to update an established data-connection to this new file

you can open the newest file via VBA and copy desired data into your targeted worksheet

...

  1. If still needed, filtering of the data

I would suggest that you look into filters and advanced filters, vlookup, countifs and vba-code examples. There are quite some question here and in stackoverflow handling multicondition-filtering, but you would have to provide more specifics to solve this properly.


as a suggestion: I would split this task into getting the base-data and only if still necessary filtering it. As I see it, it should be possible to get your filtered data much erlier in the process. Or tell me otherwise :)

share|improve this answer
    
Thanks @Jook. This is in the context of a large corporation, with too many layers of need-to-know protection built around the core CRM system. For us, the Excel sheet is the best we can hope to gain access to! So, the problem is not technical, but policy. So, it looks like, it's either going to be Macro-recording of manual-actions, or some VBA Macro's that programmaticly pull this off. –  icarus74 Sep 26 '12 at 9:13
    
As I suspected - I am familiar with this situation. However, it usually is in your corporations interest to provide this data as easy as possible for you - in the end, they usually buy such a CRM-system for this cause. Someone should take this to IT, but as long as there is not other way, use your own solution. In that case, you might want to start with the filtering, and then worry about automatic data import, because this should be easier. –  Jook Sep 26 '12 at 10:03
    
Thanks @Jook, makes sense. –  icarus74 Sep 27 '12 at 10:21

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.