How to do this task with Excel? Recognising "groups" or "categories" of numbers?

Question

I have ranges of numbers (e.g. 1234, 12345, 12346, 12347) which identify products. In each number the first 4 digits identify a group of products, for example 1234 means "sneakers", and 12345 is a product that is part of that group. How can I make excel automatically tell which product is part of that group (e.g. it will tell 12345 as being a part of group 1234, whereas 32445 will not be)? I guess it has to somehow "read" only the first 4 digits of the number and ignore the rest then compare those 4 digits to the identifier and see if it matches or not. I know it can be done with the custom filter with "begins with" 1234 but how can I do so on multiple ranges at the same time and from different databases?

Answer 1

To 'do so on multiple ranges at the same time and from different databases' would necessitate the use of a VBA macro or macros.

Answer 2

What you need is a look up table or alternatively add an exta column identifying the ranges and sorting your data appropriately (Data, Sort) and then you can subtotal each group or alternatively just use autofilter.

Answer 3

it's a little hard to tell from your description and without seeing the layout of your sheets/databases but sounds like you could use a vlookup & left() formulas to do this.  Assuming that at one of your sheets is a master list of all the product groups (with the group # and names) you could do something like

say A1 contains your product 12345

and C1 to E100 is your "master" list of product groups (1234/sneakers, 4567/shirts, 7890/pants, etc)

in B1 enter

=VLOOKUP(LEFT(A1, 4), C1:E100, 2, FALSE)

to break this down, the vlookup formula takes 3 criteria.  1st is what you want to look up, 2nd is where do you want to look it up, 3rd is if it finds a match, what column should we return the data from.  The "false" at the end is optional but tells excel to find exact matches only.

within the vlookup, we use the LEFT formula which simply copies X number of characters of a cell, starting from the left.  In your case, you only want the first 4 characters, starting from the left.

so we end up with a formula that will get the 1st 4 characters of A1 (1234 in this example), then attempt to find a match of 1234 in the master list, and if it finds a match, returns the description from the 2nd column of the master list ("sneakers").

This may not be exactly what you need for your particular data layout but maybe it'll give you some ideas on how you could accomplish what you need.

there are also formulas such as RIGHT() which do the same as LEFT but starts from the right side.  And MID() which allows you to extract characters from any starting point and any length (such as extracting 3 characters starting from 4th position).  You can find more info on these formulas under the help sections in excel (F1 for help).

Answer 4

to just look at the 1st 4 digits of a number

assuming your 1st number is in cell A1

use this formula

=LEFT(A1,4)

then copy&paste the formula down the column.

if you need the number to be a value (it doesnt look like you do) but here it is anyway:

=VALUE(LEFT(A1,4))

what do you mean by"how can I do so on multiple ranges at the same time and from different databases?" do you have data on several different sheets/files?

maybe if you add a column called "group" to all your data, and use this formula...then you can use the new column for your formulas.