Creating A Sequence From A List Of Codes And Values
Oct 29, 2009
I'm trying to write some VBA code to do the following (don't think its possible using formulas only). In column A i have a list of codes (A1:A32) in column B i have a value (between 1 and 32 in this case, but feasibly could be any integer) I am trying to achive an output in column C which would take the code from col A and then "count up to" the value in col B;- for example "AAA" in col A, and "4" in column B would return AAA1, AAA2, AAA3, AAA4 in column C. For the next code say BBB and corresponding value, would start its sequence in the next empty cell below AAA4 in col C.
I am using Excel 2011 on a mac. I have a selection of data that lists a repeating code (say group 1, group 2, group 3 etc)and a value (-1,-2.3,3,6 etc) and need to find a quick way group together all the codes and next to that a value that counts how many values who share the same code hold a value greater than -1? I can do this myself in a pivot chat with count totals but my issue is how I can show the number of codes greater than -1.
Attached is an example of the data I want to sort.
I am trying to create a number counter which runs up to a value which is determined by a COUNTIF Statement in the spreadsheet. I can get the number counter to run to the given value. However, due do formatting reasons I do not want the counter to start in cell A1 but A3 instead.
Essentially my code looks like this: ... ActiveSheet.Cells(3, 1).Select Dim i As Integer For i = 1 To Range("A1") Cells(i, 1) = i Next i ...
I am creating an excel worksheet. Every time a user opens the worksheet (from a template), I want to get the next numeric sequence number as a reference, showing it in the sheet. The template is available to multiple users but the sequence number should be unique, ie if user Tom has sequence 1, Jerry will get 2, then Anne will get 3 and Tom's next one will be 4. I considered an ASCII text file with a number in, eg 1. Upon opening get OPEN text file, READ text file, Convert to numeric.. add 1.. use this. Convert to string.. WRITE text file. It seems clumsy and since I can only open with READ or WRITE (append no good, since I want only 1 value held), the READ allows multiple users to access simulataneously, and potentially get the same sequence.
Is it possinle to: LOCK file OPEN file for read READ file CLOSE file OPEN file for write WRITE file CLOSE file UNCLOCK file
There are likely to be several users accessing the template and the chance of them opening it at the same time is limited, but possible.
In the attached spreadsheet I would like to create a list of part numbers if column D E & F all have a #N/A value. Right now I'm using a True/False but this list contains around 3000 items and I don't want to have to scroll down to find the True values. Is there a way I can just create a list of corresponding part numbers to the True Values?
I've created a Excel spreadsheet. In the Spreadsheet I've got one worksheet which has columns and list of codes.
I would like my other worksheet to look through find any products I've ordered and list them with their codes and quantities, creating a summary of just what I want so that I can e-mail just the important bits.
I have a large sheet of data I'm trying to sort through. Each row of data has a column that contains a long string description. Each of these strings contains a three letter codes in all caps that I want to be able to pull out. I have a seperate sheet that has all of the possible three letter codes in one column. Is there a formula that can compare each string of text to this list of three letter codes and if it find a match put that code in a column on the original sheet?
Doc Number String Code
33333 This is an example string of text. An example three letter code would be HCB. The location of the code within the string varies. HCB
33332 This is an example string of text. An example three letter code would be CDQ. The location of the code within the string varies. CDQ
33331 This is an example string of text. An example three letter code would be RCA. The location of the code within the string varies. RCA
I'm building a work schedule based on 12 hour days and I would like to separate a full weeks hours, 84 total, into the Regular hours and the Overtime hours that the employee will be paid. The way it is set up is I have 1 spread sheet that has the schedule for a full year (using "IN" as a working day and leaving the cell blank as a day off) and on a 2nd page I've set up with a COUNTIF formula to total the number of days worked (IN) during each pay period and then it multiplies the number of days by 12 to get the total hours.
Where I am having trouble is that the employees are paid Regular hours and OT hours in a single day and I don't know how to separate the total hours automatically into those two types of pay. For the first 5 days of their shift they are paid 8 hours of Regular time and 4 hours of OT time, and the last 2 days of the shift they are paid 12 hours of overtime. In a total of 7 days they will get 40 hours of Regular time and 44 hours of OT time.
To break this down into simple formulas, ie. (84 -40 = 44), won't work because if an employee works more than their 7 days the pattern they are paid restarts again with the 8 hours of Regular time and 4 hours of OT time and etc. (8 total days would be 48 hours of Regular time and 48 hours of OT time, max of 14 days per pay period).
Is there a formula out there that can break a number down in sequence (8-4-8-4-8-4-8-4-8-4-12-12) and total them in two different cells?
I am trying to group the same values in columns together and i was thinking about having a number count. What's the function that might be able to display this (display whats in Column B in the example below)?
I call prospects to generate business. I want to respect their time by calling before 8:00 PM so I need to know their time zone. I have a column in Excel that lists their area code, but I don't know their state.
I have tried everything imaginable to duplicate the results of the previous post to no avail. I'm using Excel 2011 on a Mac.
I'm using =VLOOKUP(B2,$I$2:$J$364,10) for my formula, where
B2 is the area code $I$2:$J$364 is the range where the area code/time zone data lives (I = area codes, J = Time Zone) 10 is column J, i.e. the Time Zone column that "feeds" the answer.
I've attached a screen shot to show what I'm doing.Screenshot at Feb 05 15-33-01.jpg
I am looking for a better way to determine if a column of values contains a sequence of consecutive matches. For example, column A contains single words. To determine if three consecutive cells contain the word "neuron", I enter in cell B3 the formula:
And copy down. I then test column B to see if there are any non-blank cells.
If we want to change the word or the number of consecutive occurrences, I have to re-edit all the formulas in column B. I want to eliminate the helper column. I would like to put the word in B1, the number of consecutive occurrences in B2 and have a formula in B3 that will return either True or False.
I can do this with a UDF, but the user has rejected this option.
I've written a procedure that loads a list of country selections in a dropdown (see attached/vb editor/ThisWorkbook).
The problem is the valid values for this column need to be just the two digit country codes, not the country code +dash+country name. The reason the dropdown selections include both the two digit country code and the country name is because most users will not know the two digit codes. Presenting the selections in this fashion will make t much easier for the user to make the proper selection.
So here is what I need: When the user makes a selection from the dropdown list, the resulting value needs to be the two digit country code only. Is there a way to incorporate this formula .... =MID(B2|1|2) ... in the vba procedure which runs after the user makes a selection? Or, is there another way to accomplish this?
I'm creating a spreadsheet to keep track of my costs of production in an online game. Within the game there are a range of spawned resources that appear for only a short time before being unobtainable these resources have specific types that is shared between multiple spawns of the resource but each resource spawn has a unique name.
My first worksheet lists all the resources and their various qualities and the later worksheets are meant to allow me to choose from a list resources matching the requirements of the item I'm looking to craft. The example i have shown in the second picture requires Tatooinian Fiberplast and Lokian Wild Wheat to craft so in the Chosen Resource column I would like to have a drop down list allowing me to select the named resource type i would like to use - for Tatooinian Fiberplast the only thing on the list should be Omnitwixi and for the Wild Wheat it should show Fizi and Krad
I am aware there are people with more pressing problems than computer games and as such
I would like to be able to compare the "standards" in Column A with the Assessment status in Column B and the Assessment period in Column C in order to generate a compact list in another workbook (ideally) or tab (if not). A list might typically contain all the standards that have been M (mastered) during a given Assessment period. I realize that filters would achieve this to a large extent but I was hoping to automatically populate another worksheet or tab.
I am looking for a Macro that would randomly select 20 items from my inventory list for performing spot checks. Column F of Active Coil Log tab is where the data would need to pull from, however I would like all data in the row to go along with it. I've explored the RAND functions, but they don't seem to be the right fit .
I'm trying to convert various codes from one column of an excel spreadsheet into different codes in another column. I was able to accomplish this with "If" statements, however I'm only able to string together seven of these statements in one command. Is there a better way to add formulas for more than seven conversions? Below is a copy of what I've done so far with the seven converts:
I've got a worksheet with five columns of data. One of the columns contains country codes. I have a list of countries that the codes relate to. I would like a new column in the worksheet that contains the country name. I'm not sure if this is possible, but it seem that most things are with VBA! The countries and their codes are on 'Sheet2' I relalise I might have to do some boring re-ordering of the data on 'Sheet2'
I have data on 400 rows. Each row has a maximum of 10 cells with data, but many have empty cells with no data. I would like to sort each row to show values of cells in sequence and eliminate empty cells. I can use the sort row function but its a long process for 400 individual rows. Is there an easier way?
I thought this would be a simple task, but i cant seem to figure out how to do this. I have a list with names and its a fixed number of rows, say A1:A200. In the next column, B1:B200, there is either TRUE or FALSE. I want a make a new list with all names with "TRUE" on the same row, without any blanks. How can I do this?
I am trying to create a list from an array. Said array contains formulas that return numbers from input contained in an other table. I would like the list to list the numbers in ascending numerical order.
I have got a worksheet wherein Column A contains the Customer Code, & Column B contains the Customer name. I want to create new worksheets based on the Customer Codes in Column A i.e for each Customer Code, new worksheet should be created wherein I would later on enter some formatting & data in the worksheets. When I select Delete worksheets, all the work sheets other than the 1st worksheet should be deleted.