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.
View 5 RepliesI've been a lurker on this forum for a long while and it's always been able to provide me with lots of excel tips, and for that I am grateful! But this time I have a question that I can't find the answer to here, or anywhere else on the web after a few hours of looking. As a note, I'm not very experienced with Excel, probably somewhere between novice and intermediate.
What I need to create is a 2-dimensional array of data. The vertical (y-axis) are the tools, and the horizontal (x-axis) are the jobs. Where the y-axis and the x-axis intersect, will be the quantity of tools needed for that specific job. There are almost 1500 tools, and 100 jobs.
I'm new to arrays. They seem promising for what I want to do though...
Heres where I'm at. I have some data like this:
items freq 1 0.5 2 0.5 3 0.5 4 0.25 5 0.25 6 0.125
Now, I've created a 2 dimensional array, as such:
Code:
Sub testarray()
Dim arr1 As Variant
Dim rng1 As Range
Dim lngX As Long
Set rng1 = [A2:A6]
ReDim arr1(1 To rng1.Count, 1 To 2)
For lngX = 1 To rng1.Count
arr1(lngX, 1) = rng1.Cells(lngX, 1)
arr1(lngX, 2) = rng1.Cells(lngX, 2)
Next
End Sub
Now, what I need to do, is create an array for each freq of all the items that share that freq. Essentially I need this:
0.5 {1,2,3} 0.25 {4,5} 0.125 {6}
Now, I was thinking, if I could create a dictionary object and make the key the freq (so my keys would be 0.5,0.25,0.125) then I could assing the "item" an array (or another dictionary) holding the items that apply to that freq.
I need to export this to Xcelsius which doesn't support any macros/vba. Btw I can;'t use Row() in xcelsius too.
[Code].....
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
[URL]....
[URL]....
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.
View 3 Replies View RelatedI want to create a multidimensional array. Basically, I want to tie a file to a sheet. I want to import the following files:
"byemployee.csv",
"byposition.csv",
"Status report.xls",
"bydepartment.csv",
"byband.csv"
to the following sheets in my workbook:
"byEmployee",
"byPosition",
"statusReport",
"byDepartment",
"byBand"
Basically, I would like to pass the array by reference. Basically, the code below imports the file into my workbook, but I since I have several files going into sheets in the workbook, I don't want to write the code 5 times. I figured the best way would be to create a multidimensional array and pass through my procedure below.
Code:
Sub import_Employee_Data()
strSourceFile = ThisWorkbook.Name
strPath = ThisWorkbook.Path & ""
strFirstImportFile = strPath & "byemployee.csv"
sDestSheet = "byEmployee"
If Len(Dir(strFirstImportFile)) > 0 Then
[code]......
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 .
View 2 Replies View RelatedI am trying to do is, to sum up the values in an array, given that the cell value is not an error.
If the cells were in order, the following array formula would solve it easily:
{SUM(IF(ISERROR(A1:A3),0,A1:A3))}
But my problem is that, my cells are not in order. To be more specific, I want to look at A1, B12 and C13, and sum them up with an array formula given the condition that cell value is not an error. Of course, in my case, I have too many cells.
I need to create multiple arrays arr_1(), array_2(), array_3() etc. Each array will be used to store different data.
My idea was to create a for loop like the one below, which doesn't work.
Code:
For i=1 to 180
String = i
then initialize the array
Dim arr_& String ()
Redim arr_& String(1 to 183)
Next i
Lets say I need an array of 100 numbers from zero to 100. I want a mean of 75, and SD of 12. Or the same data based on a skewness and kurtosis value. Can I do that in Excel?
I'm just generating fake datasets for my stats class to analyze.
Edit: I found that =norminv(rand(),,) will work.
i am trying to create an Array that will print the stock number as a title on row A. e.g. "Stock 1" in A2 "Stock 2" in A3...etc. here is what i have attempted:
View 2 Replies View RelatedI had some number combinations as this 5-23-34-233, 50-233-34-45, 34-5-23-45-67. The length can be variable in column A.
I want a formula which should return array of numbers of each cell in column A. So if A1 has 5-23-34-233 so the formula should return array like {5,23,34,233} and like wise.
[Sheet] .....
I have the above sheet, what i need to do is pull all records to a seperate sheet. The records need to be between the start and end dates and have no full reply. Please help. I am using excel verion 2003.
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?
how to draw the top 5 numbers for each type of element into a list, excluding duplicates, without using array formulae?
Example.xlsx
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.
View 2 Replies View RelatedCurrently I have a spreadsheet with the following columns.
A - company name
B - Company expense date
C - company expense amount
There are many different companies, and each company has multiple rows for different expenses.
How would I create a drop down box, where I can select the company name and have excel create a list of all the expense amounts for that company?
I want it just to give me a list of every instance a certain word is used in a column, just like on the autofilter. But each value should in the cell beneath.
Basically I have a list of clients, now when using the autofilter I can select to see the cases from just that client.
But what I want is it to list all the clients relevant to certain months in a column.
I have been having alot of trouble and have read alot. I can't seem to figure out the problem. I am currently working on an excel sheet that will select a name from the first drop down list. Based on that name five other list will be generated just on the first selection.
View 3 Replies View RelatedFormula/function to put a set of values into a bucket, depending upon the value.
I've attached a spreadsheet with sample data for your review.
I'm looking to put the Ranking # into one of several Ranking Buckets based on the criteria below:
1-399
400-449
0, 450-499
500-549
550-599
600-649
650-699
700+
I've tried using a LOOKUP function, but I think I'm missing something with it.
=LOOKUP(C2,{1-399,449,499,549,599,649,650,700},{"1-399","400-449","450-499","500-549","550-599","600-649","650-699","700+"})
Excel Bucket.xlsx
I have a spreadsheet set up in the following way;
Excel Sheet Set Up.JPG
What I'm trying to replicate in column A is similar to the live bullet point numbering that can be found in Word documents where if a line is deleted the section numbering (think bullet points in Word) automatically fall into line and update i.e what was 1.4 automatically becomes 1.3 and so on.
So far I've tried the simple formula of = cell above +0.1 which works fine until i have to remove lines.
Is it possible to change the Pop Up box formatting after creating a List.?
View 7 Replies View RelatedI am trying to set the below table up as a validation list. I am having issues with putting it in as list as it creates a new line every time a “,” (comma) occurs. I know I can use the custom and pull the list from a table, but I can’t use this option (the reason I can’t use the table, if interested, is because the single sheet with the validation contains about ~35,000 line items that are being split up into ~200 different sheets. Each sheet is then sent as a separate email…the problem with using the table is that it would on a different spreadsheet to begin with. The email only sends one sheet. As such, it would no longer be able to reference the table).
A - Less than $25,000
B - Between $25,000 and $49,999
C - Between $50,000 and $99,999
D - Between $100,000 and $249,999
E - Between $250,000 and $499,999
F - Between $500,000 and $999,999
G - Over $1,000,000
Any formula to recognize and copy text from any sheet and create a list of the text items on separate sheet. Attached is a sample file I use. The text items in sheet 20 column a,b,c are items I want copied to a different sheet (list). i would like those items in the 3 columns to be copied and create a single list so I can have a count(Don't want duplicates). From there I would add another formula to give me a count.
Baby Inventory Room 2 - 2012.xlsx
I have a large list of in rows with multiple column headings: ie. Name, date, amount. I need to generate a separate list whereby the rows that correspond to a given criteria are extracted and listed sequentially.
I have attached a simple example to demonsrate what i mean.
in the attached spreadsheet, i have a column of colours, and a column of objects- RED book, BLUE hat, etc.
I'd like the criteria that i type in the green cell (a choice of colour), to generate a list of all the items that match that colour, and be displayed in the orange cells. So, if the green cell says BLUE, the list should pick out the BLUE dog, BLUE hat etc.
I have a drop down list in excel. What do I need to do so that it will automatically take me to the closest/best label on the list when I type just a few first letters?
View 14 Replies View RelatedI have a long list of zip codes (this is just a small portion of the list) that I need to make into ranges. Is there a formula I can use?
From this list:
90080
90081
90082
90083
90084
[Code] ......
To this:
To From
9008090084
9008690089
9009190091
9009390099
9010190103
9017490174
9018590185
9018990189
9020190202
9020990213
9022090220
I want to create a list of names where the name is multiplied by the number on the second column. See below example.
NamesTickets EarnedMark5Hazel2Art4
On the other sheet's Column A
MarkMarkMarkMarkMarkHazelHazelArtArtArtArt
What macro can I use?
I am trying to create a master list in a new sheet based on the data contained in 4 separate tabs. The data is Username, Display Name, Company. Unfortunately some users have accounts in 2 of the companies (with the same username) and I need these to default to Company A.
The output need to be username, Display Name, Company.