CODEWITHSUNDEEP
excel
Kevin E
Kevin E

Reputation: 13

Data generation in Excel

Good morning, I am new at using excel and I'll be very thankful if someone can help (and I'm sure that the answer is easy from your point of view).

https://img15.hostingpics.net/pics/785039data.png

The screen capture reprents a simplified way of explaining what I am looking for:

For each number in the first column (which can appear more than once), I want to generate a corresponding Id (See column Id to affect which is the type of data). We are only looking at the beginning of "Number", which can be up to 8 number is size. Sometimes there can be tricky case, as te example starting with 40 vs 402 which are not affected to the same Id.

As I am new on excel maybe this answer or question exist but I don't know how to search/name it. Thank you for your help, have a great day.

Upvotes: 1

Views: 87

Answers (2)

ian0411
ian0411

Reputation: 4275

You can try this array formula (click Ctrl + Shift + Enter together) from cell B2 and drag it down:

=IFERROR(INDEX(D$2:D$7,MATCH(MAX((VALUE(LEFT(A2,LEN($C$2:$C$7)))=$C$2:$C$7)*C$2:C$7),C$2:C$7,0)),"")

This will try to match the longest number (with MAX) and return the value (with INDEX/MATCH).

Upvotes: 1

ImaginaryHuman072889
ImaginaryHuman072889

Reputation: 5195

Tricky formula. Not exactly the prettiest thing ever but this formula works: (Line breaks added for readability)

= IFERROR(INDEX(D$2:D$7,MATCH(MAX((((A2-(C$2:C$7*10^(CEILING(LOG10(A2+1),1)
  -CEILING(LOG10(C$2:C$7+1),1))))=MOD(A2,10^(CEILING(LOG10(A2+1),1)
  -CEILING(LOG10(C$2:C$7+1),1))))+0)*C$2:C$7),C$2:C$7,0)),"(no match)")

A few things to note:

  1. You didn't include cell ranges so you will have to manually change the cell ranges in the formula above to whatever your particular cell ranges are.

  2. This is an array formula, meaning after you enter this formula into a cell, you must press on the keyboard Ctrl+Shift+Enter rather than just Enter.

  3. The formula looks for the "strongest" match. For example, if a number is 40200000, the formula would return "Grapefruit" because it is a stronger match than "Grape".

  4. If no match is found, I just have the formula return "(no match)" but you can obviously change that to whatever you want.

  5. I assume this won't be an issue, but the formula will not work if any negative numbers are used.

See below, screenshot that shows formula works for your data.

enter image description here

Upvotes: 1

Related Questions