• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to identify decimal numbers in a free-text cell with a formula?

Thanks a lot everyone,

I got the message, and I promise to use it wisely! :)

In my little project I have an excel template - first is a data formatted worksheet with entry fields, second worksheet staffed with formulas validating data entry "on the fly" & providing error messages and a third one with reference data lists.
At the end, file size is quite large, and as I am not concern with a speed, the size is a problem as it submitted through email (it's a "must").
I reworked formulas and format to minimise the size, but initially I was wondering if VB code would "weight" less.
I am very rusty on VB and it will take time (wich I don't have) to transform this saga into decent code even to test it, so I was curious on other people experience.
 
Formula and VBA are not separate routes that you should be thinking of going down. Some things are easier via VBA and some are through Native features in Excel. So in a sense these features can compliment each other where the other one falls short.

You probably have to ask questions like:
1. Do formulas contribute so much to your file size?
2. What part of data is static and what is dynamic or keeps adding?

Then perhaps, my rough guess would be.
1. You need to create a common format input sheet which you probably have.
2. Main data processor workbook [maybe VBA add-in if you need to] which pulls in the data from Input format from step 1 and does some processing if need be e.g. adding formula rows etc.

Then it will be simply sending input data to all concerned and the data processor shall be distributed only in case of updates to main agreed model.

Of course, I could still be off the mark with your situation but it should get you thinking :D
 
Hi, Rediska!

If you're willing to work a little, you can do as follows:
a) You build a sample workbook, with all the required worksheet & names structure.
b) You enter the input data and place all the formulas that you could, in a worksheet "WSFormula".
c) You copy the worksheet "WSFormula" to "WSMacro"
c) You specify your additional requirements (complex formulas or cells to be filled by code), with examples.
d) You (not me!) ask Sajan (or another Yoda-Formulator) to develop a solution for worksheet WSFormula.
e) You (not me!, and neither you Sajan) ask me??? (nahhh, don't be evil!) to develop a solution for worksheet WSMacro, which should produce the same output that in WSFormula.
f) You test both solutions with:
1K rows
10K rows
100K rows
1000K rows
g) Then it'll be up to you to choose the most appropriate solution for your actual data set.

Regards!
 
Hi SirJB7,

My sample workbook is now complete and ready to go testing (thanks to all!), but! due to the specifics of my work I can't go wagging it, so...and.... thanks for the advice... :)
 
I did it on my first post, thanks.
and this is why my data samples looks like "flip flap flop" in the other posts.
Yoda-Saj solved most complex cases, the rest was easy.
I've got all I needed!
Cheers!
 
You must log in or register to reply here.
Back
Top