Sheet4 in my book looks like this:
.............A.......................B................................C..................
I......Invoice No…..…Starting Serial No……...……Ending Serial No
2…………ND12………..6011000000013652……. ..6011000000103652
3…………ND13………..6011000000116526……....6011000000246977
4…………RF4……….….6006000001255724……..…6006000011249564
5.........RF4...........6005000000023652.........6005000000095624
note that the last 4 numbers of the series are randomly generated for security reasons and are not part of the range of the the serial No’s
So Invoice ND = 10 consecutive numbers, Invoice ND13 = 14 and Invoice RF4 = 1000 and RF4(again) = 8
The list on this sheet is a mile long
What I need is a “search box” with two windows in it
In one box I will type a serial number and in the second box I will get the Invoice number
The challenge is that the serial number could be between Starting and Ending serial No’s thus if I were to type serial number
6006000001086598 in the first box the answer in the second box will be : RF4
I have a dynamic list of items which I have to assign serial numbers to. If the serial numbers go in sequence, it would have been easy. But the problem is, they don't.
They go from A1 - A9, B1 - B10, C1 - C10 .... Is there a way I can assign an array to ensure that once the macro hits A9, it starts from B1, and once it hits B10, start from C1?
I have in my sheet from C13 to C350 many serial numbers which I manually type them in. These serials are 5 digit numbers, and I was just wondering if there's a function or formula that will not allow duplicate numbers in that column.
I am trying to get Excel to write a series of incremented serial numbers to an external program. I need 10 per line, then for the code to send ENTER and start writing on the next line. Following is my current code:
For i = 0 To qty - 1 SendKeys prefix_sn & next_sn + i & "," Next i
I have a spreadsheet with a list of about 3800 lines of dollar values in "K" that correspond to various serial numbers in "E".
These serial numbers come up multiple times at different place throughout the 3800 lines.
I know by using SUMIF I can calculate the total value of each serial number, but is there a way to bring the serial numbers across to correspond to the sum of the dollar values?
I have a userform for keeping records and would love to incorporate a new feature. I would want to generate a random serial number for each entry made with the userform.
I've modified this macro I found on this forum, all it does is compares 2 lists of 6 digit serial numbers and and then tells me which numbers are in list 1 that are not in list 2.
However, I want to modify it so that it also gives me the numbers in list 2 that are not in list 1 (put into column F). I then need it to cut the matching serial numbers (in columns A and B) from sheet 1 and paste them into sheet 2.
my spreadsheet has duplicate serial numbers in column A and the corresponding row in column B has mutliple descriptions for the same serial number. I need to combine those descriptions into 1 cell rather than having multiple rows for the same serial number. is there an IF formula that I can combine with a concatenate that will capture what I need?
I want to make continuing serial numbers in excel in order to print labels to be able to track my deliveries. i know i can drag the fill cursor to automatically make it go the next number. however i also want my serial number should represent the month and the day the invoice was printed.
for example i want the serial number should be
column a (Jan 01) column b (Jan 02 ) column c (Jan 03) a1-01-01 a1-02-01 a1-03-01 a1-01-02 a1-02-02 a1-03-02 a1-01-03 a1-02-03 a1-03-02
The first number represents the month (example -a1 for Jan) The second number represents the day and The third number represents the invoice
How can i make that when i pul the cursor down it should continue with the invoice number
However when i continue right it should move the day number?
i have a list of serial numbers in a column, say column a, for example, and i have corresponding data in columns to the right of this, say just column B for example- I also have a header on column B which counts the data in column b. (just a COUNTA, counting occurences of 'x' )
my issue is this: i need to limit the range of the COUNTA dynamically- by the number of serial numbers in column A. for example: if i have 10 serial numbers, i want my COUNTA range to be B1:B10 (excluding header) and... i need this to be a built-in formula, NOT a VBA macro. i need it to update automatically, as soon as another serial number is entered. i've tried using references to named ranges and all sorts of language tricks, and i cant seem to get it to work. (such as:
' =counta(b1:namedrange1)
i'm going to have to do the same thing with COUNTIF's, so if that is also easily explainable,
i Am trying to get this project underway and can attempt this manually in excel but will take ages. I was wondering if there is a code I can use. I am trying to get a 7 character alphanumeric serial number for units as a 4(alpha) and 3 numeric number. like say AAAA001 , i want to list on a row all the numbers on a sheet right upto ZZZZ999
I need to compare a S/n from a huge amount of datarows in sheet 1 with the s/n in a second worksheet.
If the s/n matches i need to copy the datarow from sheet 1 to sheet 2 next to the data allready existing in sheet 2. This removing the row from sheet 1.
If there is no match found in sheet 2, the row ha's to be copied to sheet 3, this also removing the datarow.
I have a range of values in a1:f500 the values are all numbers between 1-25. I then have a value in cell h1. what i need to do is find all numbers that equal to 9 in the range a1:f500 and replace all these 9's with the formula =$h$1 so that i can simply change the values in the range a1:f500 by changing the value to say 12 in cell h1. could someone please text me the vba code that needs to be entered for sheet 1?
I'm looking for a formula to find the minimum value in a non successive range of numbers (excluding zero values).
My sheet contains four fields of numbers (#1, #2, #3 and #4) and than start over again with number #1, #2, #3 and #4 etc. This goes on for approx. 25 4-column blocks. A simple MIN-formula or SMALL-formula doesn't seem to do the trick as it takes all values into account, not just the #1 or #2 or #3 or #4 values.
Example (cells A1..A24): 999-999-999-999-110-120-130-140-111-121-131-141-112-122-132-142-0-0-0-0-113-123-133-143
The minimum value of #1 (excluding the zero's) would be 110, the minimum value of #2 would be 120, #3 would be 130 and #4 would be 140.
I have the following columns and rows that I need to MOD by 15. Once they have been MOD, I need to set a range and find all numbers in that range of say, eg; all number between 9.6 and 9.8. Really hope someone can help, have been working on it day and night. I can do them one at a time, but is very time consuming. Is there a formula that will do the whole worksheet in record time?
2/18/1975, 156.7519, 175.8083, 235.4675, 253.765 4/11/1973, 237,7642, 346.5342, 113.3145, 321.435 5/12/1985, 342.7625, 123.4233, 253.4321, 357.234 etc. etc. etc.
I have two columns of numbers (this week - Column A and last week - Column B). What I need to do is look at the numbers and pull two lists out in Columns C and D. In columns C a list of the numbers that are missing from last week in column D numbers that are missing that were added from the previous week. The two lists consist of about ten thousand rows of data.
******** language="JavaScript" ************************************************************************> Microsoft Excel - Book3___Running: 14.0 : OS = (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA1=ABCD1This WeekLast WeekMissingAdded 21002103110311002310271032103210274103310331034104051035103410361055610371036 165571038   81040   91055   101655   Sheet1 [HtmlMaker 2.42]
I'm going to be using a spreadsheet to keep track of where different people are at. So if Person 1 is in Room 3, I will stick a 3 in the box next to their name and then can look at the spreadsheet whenever I need and see what room they are in. When I'm deciding what room to put a person in, though, I need to be able to quickly glance at a list of Room #'s and see what one's are still available. So I have a bank of Room #'s in the spreadsheet....1,2,3, etc.
What I'd like, is some way to set this up so that when I put, for example, "3" in the cell next to "Person 1" the spreadsheet automatically removes "3" from the bank of available Room #'s and when I delete the "3" because the person has left, it adds "3" back to the bank of available Rooms.
I have a mass of data which look something like this:
table removed
and I require the ranges of reference numbers to be listed in a column one above the other, which requires inserting new rows. I also need the date & description columns copied down into the newly inserted rows.
So basically for example I would want the top row to now read:
table removed
and then apply the same procedure to the other ranges below this.
These are some data that I have ( inputs and outputs). I was wondering if there is anyway to find this equation. I can get more data if that helps in finding the equation.
Have exported oracle DB data in an Excel sheet. There is a column with text and numbers in it. I need to get out the serial numbers of this text columns of up to 12000 data fields.
My problem is that the serial number is not always the same format. There might be also two serial numbers in one text field.
One cell can look as the following: GEYSER BURST - SERIAL NO:102 15712, 10 AUGUST 2005,150 LITRE SUPA-FLOW, EASILY ACCESIBLE. App made for today as per Victor ( Notes by Pamela on the 18/07/2008 13:30)
Out of this cell the Serial No is 102 15712. There are other formats like: 550501 009879 H 100107452 H100/110230
Sometimes there are only four or five numbers or there are special characters between the numbers like - or /.
I would like to seperate these serial numbers in a seperate cell. There might be also a cell without any serial number in it.
Is there any other way to get these numbers out except of doing it manually?
i have spent 40 hours, and still didn't find a solution. Please help is need it!! example: i have to find all articles with same code (222). first one has Q =100 second one Q=250. soled Q=150 (i am talking about 5000 rows, 400 different or same articles per month- 12 months)
columnA-----columnB---columnC 222 ----------100---------0 111-----------50 333-----------70 222----------200---------200 333---------- and so on first i have to deduct from the first one it finds (max. till 0 ...it can not be negative) ....after finding another one it deducts the rest---it means 50 Is there any kind of formula with this possibility. if it is poorly writen please let me know for more info. i am not an expert in excell, but i have tried variations of sumif, vlookup functions, but i always get stuck deducting the whole Quantity from all of the same (222) articles .
I have a mastersheet that once I open I would like to generate a 3 digit serial number in cell C22. Once this file is opened and the number is generated I do not want the number to change at that point. I have a macro to save the file with data in certain cells. Once this macro is run and the file is saved I will need to be able to open that file to view info but the serial number can't change.
I would like to do this without an add-in so that any client can use file no matter there settings if possible
