I recently made the post here, in which I presented a solution to the problem of returning a value based upon matching a single criterion in a given column across multiple worksheets.
In this follow-up post I will look at the analogous case in which we are not matching a single criterion, but several. As mentioned in the first instalment, I will look at two solutions to this problem, one in which we make use of an extra “helper” column in each of the relevant worksheets, and one in which we do without such aids.
Most people with an average level of ability in Excel are perfectly capable of using VLOOKUP when this operation is performed over a range within a single worksheet.
But what happens when we wish to extend our search to multiple worksheets, and so return the first match from whichever sheet happens to be the first which contains our search value(s)?
In this post I will present a solution for such cases in which we have a single criterion to be matched in a given column across multiple worksheets.
In the next instalment (to follow shortly) I will also look at cases in which we are not matching a single criterion, but several. In this situation by far the simplest method is to use an extra “helper” column in each of the relevant worksheets in which we first perform a concatenation of the fields of interest. By doing this we ensure that it is a relatively straightforward case of extending the solution designed for one criterion to work also with multiple criteria.
This is the second in a series of discussions on the techniques available for extracting numbers from an alphanumeric string. In the first instalment in this series (which can be found here) I looked at extracting consecutive numbers which appear at the start of the string, e.g. 123ABC456.
In this post I will concentrate on techniques for extracting numbers from a string where:
- The numbers are consecutive
- The consecutive string of numbers is found at the very end of the string
- The desired result is to have those consecutive numbers returned to a single cell
As previously, for each of the given solutions, we need to test its soundness in two separate cases: firstly, where there are no numbers elsewhere in the string, e.g. ABC456 and secondly, where there are some numbers elsewhere in the string, either at the start, e.g. 123ABC456, or in the middle, e.g. ABC123DEF456.
This is the first in a series of discussions on the techniques available for extracting numbers from an alphanumeric string. Since we often have many different solutions at our disposable for such tasks, I will attempt to present what I feel are the principal candidates and, for each of these set-ups, discuss the merits and potential drawbacks inherent in each.
In the next instalment in this series I shall look at extracting consecutive numbers which appear at the end of the string, e.g. ABC123. In later posts I will deal with cases in which the desired numbers to be extracted are interspersed within the string in groups of one or more, e.g. ABC12DE345-FG6H789, in which case we may be interested in extracting either the number 123456789 into a single cell or each of 12, 345, 6 and 789 into four separate cells.
I shall also consider in future posts cases in which there may be several numbers within a string, though from which we wish to extract perhaps only one (or more) of these numbers, and for which our choice of extraction is based upon one or more criteria. For example, given a string of the form X12-X34-X56-X78-X90 we may wish to develop a technique which extracts the number immediately preceding the fourth occurrence of a hyphen within that string.