MS Excel / VBA help thread

WorksheetFunction.Substitute(lower(sourceString), "rec", "")

Nest it to get rid of colons / spaces

WorksheetFunction.Substitute(WorksheetFunction.Substitute(lower(sourceString), "rec", ""),":","")

Chuck in "isNumeric" so you only mess around with non-numeric values, depending on how strict your variable declaration is.

e.g.

if (isNumeric(myVar)) then mySubstituteFunction(myVar)
else killEveryone
end if

I don't think I fully understand, there's a two column table that looks something like this:

replace value | replace with

REC1 | 1
REC2 | 2
REC3 | 3
REC4 | 4

And instead of the first column going up to RECx (where x is high) you'd like to wildcard it out... is that correct? But how would we know what to replace x with if it's not written down anywhere?

oooh - sorry for shouting - was attempting to format stuff badly to look a bit like a table...

Okes. Here goes.
I take data from selectsurvey every week.
I save this twat of a csv as an xlsx file. Because format funtimes.
I then run a macro which says "open export" and "open cleaning book".
In cleaning book i have a table of two columns, find and replace.

Macro then says for this range whenever you find x replace it with y.

User populates data with REC and all the bastard variations thereof.
So if i could wildcard and guess their logic that would be f a b.

Bit annoying.

Also: How do i know what the problems are? I don't.
Those I spot, I sort out in the find and replace list.

What I'd like to do is clean historic data and then lock up the survey tool to prevent stuff, i thought it was. Turns out it wasn't.

aaah - so stuff in the big range (LR) in your code contains 'REC1->1000' (or whatever).

In this loop:

For x = LBound(myArray2, 1) To UBound(myArray2, 2)

You loop through your replace table. If you loop through the LR range instead, you'd only need one entry in the replace table with

REC* | ""

As its values (then use TWs substitute, or replace)

Can you protect the input spreadsheet and lock the cells to numeric (with data validation)?

Can you just remove all non numeric characters to give you what you want. That's easy to do.

Data is entered via SelectSurvey.net.
Data comes out as SPSS condensed, csv format.

Data gets saved to xlsx format. Macro runs through and cleans, adds columns etc.
Data then gets transferred to a dashboard sheet.

Prizes awarded for :
Can you connect directly to select survey
Can you use BI
Have you thought about x y z

Might be easier to do the transform / validation in excel rather than vba? Have a 'input table' which contains the dirty stuff from selectsurvey, then a 'cleaned table' which gets exported to your dashboard.

In between you could use spreadsheet formulae to vlookup from your replacement table and do any other transforms. Might be quicker and easier to spot mistakes in than doing it via vba

That's what I do!

(this is good, it confirms i'm doing something sort of right)

The whole routine in vba opens the two, cleans, etc, closes both (after saving the data and splitting it by local area). I then have a look at the cleaned data and transfer it to the dashboard sheet.
If i spot errors there, i try and work out how and correct for them for the next time in the vba.

Find whoever put together the input screen, then kill them in the face for not restricting the input variable types.

Looks in mirror.

Blames template.

Why not fix the survey instead of trying to clean up the results?

I'm doing both!
The data stays in the survey, so it has to be cleaned each extract. There are things I can fix, variable inputs, and things I can't: ban free text, pin specific fields for each user. I join a dev queue for that....

I'd suggest regular expressions. Great for pattern matching and quick to update what you're looking for. Looks like it's easy to implement in Excel. Just tried the example here
http://analystcave.com/excel-regex-tutorial/

and using the string

[A-Za-z]*w*([0-9]+)

will return 456 from any of
456
REC456
Rec456
erc :456
REC 456
Rec. 456
rEc :..:. 456 wtafigo

when using the RegexExecute function they show on the page
The demo spreadsheet is safe to download too, and you don't have to put in a real email address, it'll still take you to a download link

Brilliant. Link looks great.

Anyone good with PowerPivot/DAX and familiar with various aspects ERP systems (including MRP)? I've sort of built an ERP in Excel (don't ask) and would love someone to double check what's happening. Some of the faux-many-to-many aspects appear to be working but I'm not exactly sure why.

Any suggestions on an alternative to advanced filtering? Want to take the source table and then show a limited number of columns for rows that have an identifier (simple 0/1 for selection).

Used advanced filters before for this and worked well but I need this to be as simple as possible for a non-excel person to be able to update and the workbook cannot be macro enabled...

Pivot table with identifier column as Filter?

Pivot and slicer

Unfortunately not, I don't want to summarise the data, rather just show an extract of the table- some cells are text/comments and I need this pulled through. e.g columns A-C,F and K where K=1.

The advanced filters aren't a problem and have worked well for me in the past, but refreshing them is. I really don't want to be tied to updating this workbook so want to hand it over for self service.

It's likely possible to recreate the table elsewhere using some combination of IFs, v/hlookups, Offset and Indirect.

I'd probably just go with a helper sheet though with pictures, etc on how to reapply the filter. The standard answer is a macro (covered somewhere in here I think) but it seems that's not an option.

Yep, I have lots of other books with the filter and macro setup, first time looking for an alternative. Formulaic approach would be interesting, will ave a look.