Excel Formulas – Using IF/AND together

Datanology usually consider all the formula based options for your project before looking at introducing VBA or userform based projects. Formulas in Excel can be very efficient, and when you use nested (several formulas mixed into 1) formulas and especially combining the IF & AND formulas a very quick, accurate and useful outcome for your […]

Excel UDF Get the colour of the cell

Dealing with colours jn Excel became a lot more important when Office 2010 was released. Conditional formatting allowed for large ranges of slightly different shades of the same colour to be used in ranges which are not so easy to identify by sight. This UDF example returns the value of the colour in the cell. […]

Excel UDF Get Workbook name

In Excel there is no built in function to check and return a result for the workbooks name. Even though it may be in clear sight to a user at the top of the screen, there may be situations where the workbooks name is required to be referenced against, such as counting the number of […]

Excel UDF Get Worksheet name

In Excel there is no built in function to check and return a result for the worksheets name. Even though it may be in clear sight to a user at the bottom of the sheet on it’s tab, there may be situations where the worksheets name is required to be referenced against, such as counting […]

Excel VBA Delete all blank rows

This VBA snippet is very useful to have, and in most projects, it is common for us to have this code to hand, and adapt as required to carry out a specific task on a dataset. There may be situations where your large dataset has been cleaned as far as possible, but there are still […]

Excel VBA Clear all controls on a Userform

Creating userforms in Excel to carry out basic or even complex tasks is relatively straightforward. A bit of creativity and inspiration is required as well as a little bit of Excel VBA. Datanology build userforms into the majority of our projects and they are a vital ingredient in any Excel based database system you might […]

Excel UDF Identify email address

We carry out a lot of work on document and data cleansing, data capture and data blending. One of the most common request is about email addresses and how to automate grabbing just the email address from a string of text in Excel. Because an email address is a unique entity and also has a […]

Excel Match Formula

The ‘Match’ formula could be described as a versatile relative of the Vlookup and Hlookup functions. Whilst vlookup can only find corresponding data to the right of the lookup data, match can help you to return information from any direction using it’s friendly assistant, ‘Offset’. Match also has it’s own unique abilities and there are […]

Excel Column Letters to Numbers

When dealing with large databases and datasets in Excel, it is often handy to know the numbers that relate to the column letters. This is especially useful when you are using the .cells rather than .range in your VBA projects. Columns in Excel are numbered in the same fashion as rows – starting at 1 […]

Extract a specific length of numbers from a string

Recently we have been asked to provide a solution for a client who was faced with potentially thousands of cells in a range where only a specific reference number within an alphanumeric sequence was required to be extracted. This was a fixed length of six numbers but this sequence could be anywhere in the string. […]

Excel Binary vs Excel Standard

There are certainly far more benefits in saving larger database files managed in Excel as the binary format suffix of .xlsb over any drawbacks. Of course if it was 100% beneficial in every situation then Microsoft would have introduced this as the standard saving format, but the reality is that for a wider use and […]

Excel Disable Right Click

Excel is not known for it’s security or protection, but it doesn’t profess to be so either. There are many built in features to provide basic security such as password protecting worksheets, individual cells and workbooks. Unfortunately any of these security features can easily be over-ridden by an intermediate Excel user. VBA projects can also […]

UDF to locate first letter in string

In the process of data manipulation, data cleansing for instance it is a regular occurrence to identify a structure of the string in a cell for a postcode or telephone number for instance, or to facilitate splitting a cells contents up by numbers, letters etc. This example UDF is designed to be pasted into the […]

UDF Extract all comments from a cell

This UDF (User defined function) is designed to be pasted into a VB Module (ALT + F11) and will then become a normal everyday formula that you can use in Excel to get the comment text out of a particular cell. There may be occasions when for instance a small database has been designed and […]

VBA Sort a range A-Z

Sorting a range of text from A-Z or Z-A is relatively simple in Excel, whcihever version you use. Select the range and click the A-Z icon to sort in the ribbon and follow any instructions. However there may be times when you want to carry out this task in the middle of a VBA process […]

Populate a userforms combobox with unique values

Populating a ComboBox with unique values in Excel can be very useful when it comes to database building, or where a ComboBox has multiple values and it would be easier to populate the values from a range of cells that could be dynamic. This can be done in a number of ways, using advanced filter, […]

Excel VBA Auto Fill formula

Using VBA in Excel can greatly increase speed and productivity of the majority of Excel projects and systems. Even in VBA, shortcuts and usual Excel ‘quick wins’ can be used to your advantage whether that be the increased speed of doing a task, or just making something easier to do. The auto fill functions of […]

Excel task management Date highlight

Sometimes, it is necessary for Excel projects to be handled entirely by formulas as opposed to using VBA and creating a fully bespoke VBA system. This project was requested with the specifications that it must be formula based which meant it could be used, updated and amended on any mobile device such as an iPhone […]

Excel Multiple Child SKUs

Our clients product list was provided to them in Excel format but each product had a Child SKU labelled across the row, sometimes up to 200 individual SKU codes. This data needed to be structured differently in order to be updated to the clients website correctly and to do this job manually would have taken […]

Vlookup in Excel

Vlookup (and its counterpart Hlookup which we deal with later) is an incredibly powerful function in Excel. It’s ability to automate results in a workbook can save lots of time, and provide 100% accuracy when used correctly. It looks quite daunting at first but once the formula logic is broken down into plain English and […]

Excel UDF to validate UK Postcode

As part of our postcode analysis and cleansing processes, an important task is to ensure that a given postcode is actually a valid UK format postcode. It is not easy to spot errors or potentially invalid postcodes when you have a large list of addresses, especially if you have used OCR scanning that may mis-recognise […]

UK Postcode formula for Excel

When dealing with UK addresses in Excel, having a clean database of clients, potential clients addresses is important to ensure that when you make contact with them you make sure that contact happens and shows your business in a professional way. Incorrect addresses, spelling errors etc can look unprofessional to a prospective client. Using formulas […]

Bespoke CRM System – Amaani

Services Provided Excel Business Solution VBA Solution Data Analysis & Reference Library About Amaani Amaani is custom PC manufacturer. Datanology Solution Amaani approached Datanology looking for a bespoke CRM system to manage their custom PC build quality control. We developed an easy interface Excel solution, running VBA macros behind the scenes. This bespoke Excel solution […]

Bespoke CRM System – BIF

Services Provided Excel Business Solution VBA Solution Data Analysis & Reference Library About BIF Business In Focus works with the Welsh Government, to provide business start-up advice and support throughout South Wales. Datanology Solution BIF came approached Datanology for a customer database program. We developed an easy interface program which tailored to their requirements. The […]

Bespoke CRM System – Student Advantage Card

Services Provided Excel Business Solution VBA Solution Data Analysis & Reference Library About Student Advantage Card Student Advantage Card is the UK’s the biggest Student Discount Card in the UK. Datanology Solution Student Advantage Card approached Datanology looking for a bespoke CRM system to manage their student card holders. We developed an easy interface Excel […]

the web designer group uk
the webdesigner group logo

Close Button

Web Site Designed by

The Web Designer Group