i need to create a random 3 letter prefix form eg. British Head Office, how would you pick letters out and compare the 3 letter prefix back to a column with existing prefix. and if u can help me even more how can i automate this method beacuase i for eg. get like 50 company names at a time and dont have time to do and check them 1 by 1 lol.
I need to extract (and then use for SumIfs) only item numbers from the long description. Please see the attached list where item number column shows existing list & next column shows what i want to extract. The exrtacted part if has any trailing or succeeding letters, characters between numbers should stay. for example from "SGA:RV-SVA:PEPPERS/PEPPERONCINI:SV9176001/232034" I need to extract " SV9176001/232034" or from " SPICES:BULK SPICES 7100:9054B" I need to extract " 7100:9054B". Can some one please urgently help me on this.
View 9 Replies View RelatedI know how to use RAND for numbers, but is it possible to use RAND for letters?
View 9 Replies View RelatedI'm looking for is a way to sum a bunch of numbers using a sum if statement that start with PRJ and are followed by any combination of five numbers. Like this
=sumif( Range, PRJ#####, SUm range)
I just want to show a letter and change it to any letter and show it again in seconds. Can it be done with formula, maybe with RAND formula or any other formula.
for example when i press F9 the letter A change B, and then i press F9, it changed to C, and then when i press F9 and it changed to A again, and i press F9 and it changed to B, and so on...
(Is it possible too..with a little variation if i press F9 continually, it changed every 5 second?)
I have 1200 doctor/patient records to input into an excel spreadsheet for import to an online EHR database. I can set up all the normal formulas and formatting but for the life of me not figure out how to create a custom formula to take the first letter of the patient first name and last name and add 6 figures to create a unique patient identifier.
ie. James + Smith+ random 6 figures = JS245318.
In my spreadsheet the first name is under Column 1, Last name Column 3 and the unique number generated in column 4.
I'm having a bit of a block with an index/match formula that I am trying to create for the attached spreadsheet. i.e. I need to populate cell J3 with the info the corresponding letter contained in the table to the far left. The numbers of reference to match are the 'zones' in H3 and J2.
View 2 Replies View RelatedUDC 01_001 template black.jpgBatch 01_385 template white.jpg
UDC 01_001 template blue.jpgBatch 01_385 template blue.jpg
UDC 01_001 template grey.jpgBatch 01_385 template grey.jpg
UDC 01_001 template pink.jpgBatch 01_385 template pink.jpg
UDC 01_001 template white.jpgBatch 01_385 template black.jpg
I have 100s of urls with prefix as the left column. I need to rename them to the right column format. UDC 01_001 will take number Batch 01_385, UDC 01_002 will use Batch 01_386 and so on.
I cant figure out how to do this in one quick macro instead of replace function.
I am using the Column() Function for referencing purposes... what I want this function to do is return the "letter form" of the column position rather than the "number Form"... is there anyway to change this without switching to R1C1 form?
i.e. Column(B4) yields "2"
I want it to give me "B"
I have a user form that has a combo box "City" two text boxes one called "Flight" and the other "Date". What I'm trying to do is to prevent the user from saving the data input from the user form if any of those three fields is left blank. The code that I have so far checks all of the required fields, if any are left blank a message notifys which field(s) is left blank and return the focus back to that field. But the rest of code also fires.
What I really need is either to stop the code if any fields are blank and return the focus back to the blank field, the user completes the field(s) and clicks the save again, or better yet, pause the code until all the required fields are completed and then complete the save. (There is actually another 200+ lines of code in this sub, but I deleted it to keep the post a little shorter.)
When I type a single lower case letter into a cell, what formula or conditional formatting should I use to always convert it to a capital letter automatically?
View 9 Replies View RelatedFor the below formula is it possible to replace the B's (column location) with a cell Say Z146 which contains the letter B (or a number if thats easier and someone can tell me the numbers for each column).
When the formula is dragged into the next cell (down) it takes its column reference from Z147 and then my life becomes so much easier.
=IF(INDEX('Overs-Unders'!B:B,MATCH($C145,'Overs-Unders'!$A:$A,0))"",INDEX('Overs-Unders'!B:B,MATCH($C145,'Overs-Unders'!$A:$A,0)),"")
I have a 2010 excel sheet containing 14 columns and 45082 rows in total. I am quite illiterate when it comes to writing macros but I know that what I need can be achieved with a set of codes.
To be more clear, I inserted two tables below. The first one represents the current data structure, and the second one is the way I want my data to look like.
Current data structure looks like
Variable 1
Variable 2
Variable 3
[Code].....
I need to change a few hundred cells (one column) where the first letter may be a capital letter to a lowercase letter.
So:
GetAwardfromBid to getAwardFromBid
or
SmallLertter to smallLetter
I am having trouble filling a formulae series to the left on one spreadsheet, the fomulae being references to another sheet.
For example, I have two sheets 'Mtce Options' and 'Base Case'. In 'Mtce Options' I have the following formulae
A B C
1='Base Case'!A15='Base Case'!D15='Base Case'!G15
I want to fill to the left, incrementing the column references by a factor of 2 each time, eg. next two should be ='Base Case'!J15 and ='Base Case'!M15.
However, if I autofill to the left by highlighting A1, B1 and C1 or just B1 and C1 all I get is an inappropriate reference such as ='Base Case'!D15 or ='Base Case'!F15, respectively, in D15.
I am working on a Random Cycle Count Generator that provides random SKU#s based on 3 separate columns of SKU listings. The user clicks a button to generate the SKU#s to cycle count for that day. What I would like to see is a date stamp in the columns next(B,D,F) to the referenced SKU listing(A,C,E) based on which SKU#s are generated. This will let me see the last date that the SKU was generated. I would also like it to automatically save after generating.
View 2 Replies View RelatedG1 = will be one of these 12 choices: 01 January or 02 February or 03 March...or 12 December
K2 = Date I performed the task
D2 = Name of Inspector
I usually manually input on G2 the name of the report.
Example:
G1 = 12 December
K2 = 12/05/2013
D2 = Chris Mart
Therefore, I need to manually input on D2 = 13-12-MARTCL
Where....
MARTCL comes from a table where I have all the inspectors and their corresponding username.
That table is O2:P63. O1= Title of row (Username), P1 = Title of row (Name of inspector)
13 = year extracted from 12/05/2013
12 = Month of inspection (which can be extracted from G1 or K2)
How do I use cell reference AND VLOOKUP to create 13-12-MARTCL on G2?
I have a colummn of data (text data). For my purposes I would need to add a prefix text to every single cell contained in that column; for instance, if I have a cell with a text "hellohowareyou" ,
I would need to change it by adding to it a constant prefixation text "es_ES@" so that the final result would be displayed like "es_ES@hellohowareyou" , and so on for the rest of the cells under the same column.
Say I have a workbook called test1.xls, where the number 1 at the end of the file name is variable. Tomorrow that character could be 2 so the file would become test2.xls etc (or could even be a letter for what it matters).
Is there a way in Visual Basic to recognize that the final character is not fixed but can get different values each time? Or in other words to recognize that the file starts with "test", and that whatever follows that prefix is not relevant?
For example....
If ActiveWorkbook.name = "'test*.xls" Then...
where * (star) could be any character.
As a Windows analogy, I am thinking of the * (star) character when one searches a certain directory for, say, Excel files (*.xls etc). Obviously, in VBE that doesn't work, and the star is just a character like any other.
I’m making a database to forklifts. Most used code I have found from this forum. I haven’t found a way how to copy existing formulas on summary sheet so that sheet name is also changed in formula. I attached also my workbook, but it’s not in English language but in Estonian. I hope that isn’t problem.
If pressed button on summary sheet (“Pealeht”) then existing blank sheet are copied and renamed. On userform1 is textbox3 which value added to summary sheet, also combobox4 value added to summary sheet. Now I want that if pressed commandButton1 on userform1 then all formulas what exist on summary sheet from F6 to CW6 are also copied to next row and in formula sheet name changed. My second question is what to write that if new sheet created then it goes always to end of existing sheets. Right now I’m using code what copies new sheet after blank sheet but it’s not satisfying me.
i have a cell "h23" that must always start with a "j" or a "J"
the trouble is some of my users are only putting in the number
ie 2345 when it should be j2345 or J2345
I'm not a novice but this might be something simple I didn't realize. I am creating a quoting sheet for a steel fab company. I have created dopdowns for the different materials and sizes but the dropdown is way too big. I need to have a column that helps me filter the dropdown more so I don't have to scroll through litterally hundreds of steels to find the one I need. The good news is that most steels have prefixes that make them easier to filter.
For example:
L-3x3x1/2
L-2x2-1/2x1/4
C6@8.2#
C8@11.5#
W12@16.0#
W21@44.0#
What I'm looking for is a way to have a dropdown that has all of the prefixes (L, C, MC, W, PL, etc) in it and when I select one of them the next column (the actual description of the steel as shown above) will only give me the specific steels for that steel shape (L,C,W,etc)
Currently I'm putting one row of say the MC shapes and one row of the C shapes and one row of the L shapes then copying them as needed. It's a lot of work and you can easily make a mistake.
I have a table like below
A B
1 x1234 value
2 y1234 value
If I want to look for all values that start with "x", is there a way to do it within a vlookup formula? Something like: vlookup("x*", A1:B2, 2, False). I know there's other ways to do this, but I want it all contained in one formula instead of splitting the A column using LEFT(A1, 1).
I read the thread below on how to utilize the Subsitute function to remove periods and thought about being able to use it for this. However, I have some phone #'s in my list that contain multiple 1- scenarios in them because the area code or 3-digit prefix sometimes include a 1- also. How do I make the formula only look at the 1- for long distance and not any other 1- found in the phone #? I want to remove all of the 1- for long distance because we are trying to use a new autodialer that is pre-programmed with the 1-.
View 8 Replies View RelatedVBA code to add prefix "HR" in cell H11. So no matter what I type in cell H11, for example 123456 when I hit enter I would like that in that same cell stands HR123456.
View 4 Replies View RelatedI have several excel workbooks with many worksheets (over 500 in some). Around a third of these worksheets are named "Exp1", "Exp2", "Exp3", etc.
I would like a macro that will delete all worksheets that do not have names starting with the letters "Exp". I do not want any confirmation dialogue, and as the workbooks vary in size, I would like it to finish when there are no sheets left (except the Exp ones of course).
I have often used Data Validation List to create selection lists for cells in a worksheet. The problem is the list has to be on the same sheet you want to use it on. Is there any other way to do this so one common list can be used for all sheets? I have a list of accounts I want to use on 12 different Monthly tabs.
View 5 Replies View Relatedfor a small online database I have a column that lists nationalities:
French
Spanish
American
Latin American
(etc etc)
I need to add a prefix to all nationalities, for example:
Artist Nationality///French
Artist Nationality///Spanish
Artist Nationality///American
Artist Nationality///Latin American
(etc etc)
Is there a way I can select the 700 cells and do this in one shot? I can't add formulas because I will have to paste all this in text pad and then upload it.
I want to add "CR" to the beginning of every cell that already has data. I know I could do =a1 and it would copy all the way down. However each cell has different data and I'd like to keep it that way but add 2 letters in the beginning.
View 3 Replies View RelatedI have lists of numbers in column A. Based on the number in the cell, I would like to insert RO_ or RP_ before the number. I currently have two buttons for this, one named RO and the other RP and have simply recorded a macro of what I wish to do. However, instead of inserting RO_ or RP_ before the number the macro replaces the hole lot with "RP_10" or "RO_10" as this was the cell i recorded the macro on. How do I make it so it just inserts the Prefix rather than replace the contents of the cell.
Currently my code for one of the buttons is as follows;
[Code] .....
