series determination

[arnab0711]

Hi All,
I have this sample file which has a series of serial numbers in worksheet "Total",
and list of serial numbers in workbook "damage"
Please advice if there is any function to determine the serial number is in which series,
its is not possible do manually since the list comes in thousands.

[johncena]

i think u can try vlookup function..

[JBeaucaire]

A LOOKUP() might work, perhaps a VLOOKUP() or an INDEX/MATCH(), but your sample workbook doesn't lend itself to a clear answer.

1) Does the TOTAL sheet represent each series? If so, there's no series names, perhaps add that in column D or column A?

2) None of the serial numbers on your DAMAGE sheet are in the series examples on the TOTAL sheet, so that makes testing a solution more difficult because you can't provide sample answers, or can you?

[arnab0711]

Pls the answers to your questions
1)Each row has a series starting from row 6,I have made some changes to the damaged sheet which I have attached here,now you can find the number in damaged sheet in the total sheet
2)we are also only concerned with last seven numbers,for example last seven numbers of cell c2 is 1179241,and we can find this number in the 9th row where the lot starts with
8991848000001124004 and ends with 8991848000001224003
since the last seven numbers starts with 1124004 and ends with 1224003.

[JBeaucaire]

You didn't answer question #1. There are no series "names" so it is completely unclear what you mean by "find which series a number is in". What is the result supposed to be? There are no series "names" in that sheet, just lists of numbers.

For the serial # in C2, what is the desired answer and where?

=========
If all you care about is the last 7 digits, make your list of numbers just those 7 digits. Your data should reflect your actual need. If you don't need nor care about the digits that come before, remove them from your series table.

[arnab0711]

No I haven't defined any names for the series,as I told in the example the answer for cell c2 is in the Total sheet in the series that starts with
8991848000001124004 and ends with 8991848000001224003

============
Its not that I don't care about the digits other than last 7 numbers,I mentioned abt last seven #,because they change,but the numbers before that are static.I am also attaching the revised sheet

[JBeaucaire]

For C2...WHERE do you expect this answer to appear? D2?

If you want the answer in D2, what is the desired answer? Please don't describe the answer...tell me what the answer for C2 should be, exactly what you expect to appear.

Then tell me what you expect the answer for C342 to be...and where? D342?

Don't explain it, just demonstrate sample answers, please, based on the sample data you've already provided.

[arnab0711]

ok,I want a user form kind of a thing where if you give a number it will give you the series or the name of the series (If you give a name of the series)please advice if you are able to understand this.

[JBeaucaire]

Please read post #7 and provide only the answers requested.

[arnab0711]

I want the answer in D2,the answer will be the series .

[JBeaucaire]

I know this sounds sarcastic, but since there is no entry on the Total sheet anywhere with the text string "series", then the only formula that would work in D2 is:

="Series"

Now, if you want to go down column A and put in a series name for each of the rows of series numbers, then we have something to match and draw back..