# Creating An Array Of Data Based On Certain Criteria

Jan 22, 2009

[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.

## Extracting Data Based On Multiple Criteria - Index Match Array?

Jan 2, 2013

I have sheet full of data containing results of multiple tests on various equipment.The sheet contains many columns of data but below are the specifc criteria i want to use to extract the data. As maintenance is carried out regularly the list is always growing. I want to create a dashboard summary of the "Machines" which i will colour using condition formatting. I will list the machines in the columns and would like the rows below each machine to be populated with the results.

Column 1 Lists the various factories
Column 2 Lists the Machine
Column 3 Lists the Part
Column 4 Lists the result.

Results can either be "ok", "warning", "Alert" only

## Creating A Summary Based On Criteria

Apr 20, 2007

I am trying to create a summary of values based on some criteria. Here it goes, I have a list of number under a column ex 6299, 6399, 6118, 6124, 6411. I would like to create a summary table that would add all of the values associated with 6100's, 6200's etc... that are in an adjacent column. Ex. A1 is 6124 b1 is 100.00. I have tried different ways to use sumif, dsum and if but i am missing the part where the formula sees the column where the number is located then figures out if they are 6100, 6200 and so forth so it can then determine whether to add the adjacent column value. I woulld like for it to be one formula instead of two.

## Creating Report Based On Criteria

Jun 20, 2006

I have the following worksheet (see attached). What I would like to do with it to create a report is as follows:

1. Create separate reports for each estimator (KM, JW, WH)
2. Carry over the information in Columns I, A, B & H
3. Each report will filter column I to leave out COMPLETED and just report the number of days remaining is ascending order.
4. Flag data if the days remaining is <=5
5. Generate an email to send out notification of 5 days remaining
6. New report can be generated on any given day

## Creating Randomized Lists Based On Criteria

Sep 24, 2013

I have a long list of names in column A, and in Column B next to each name is one of 10 group identifiers (Advertising, Marketing, Finance, etc). I'd like to divide the 300 names into groups of four people, with none of the four people having the same group identifiers. So, groups of four people that look like:

Column A Column B
Amanda Jones Marketing
Shelly Stevens Finance
Tim Stewart Logistics

What is the best way to do this?

## Sort Array Based On Criteria From Drop-down

May 18, 2009

How can I sort an array of data based on the selection from a drop-down menu? And can it be 'secondary' sorted with the result from a second drop-down (ascending or descending is unimportant)?

## Array Formula Based On Multiple Criteria

Jul 20, 2006

I am creating a summary page to analyse my golf scores. I have come to a dead end when looking at the Par statistics. Does anyone fancy a challenge and try to fill in some of the blanks on the summary tab. I am trying to use array formulas based on multiple criteria. for instance under the Best Par 3 section, it would look at all the Par 3's for whichever golfer is selected and then return the lowest number.

## Array Formula To Match Closest Dates Based On Another Criteria

Jul 18, 2012

I am trying to run an array formula to match two dates on two seperate sheets as close together based on another cell. Below is what I currently have on some made up values. I am entering it as an array and ideally I would like it to run down the cells changing the reference A2 depending on which cell it is then to then enter this into a macro.

The formula I am currently using is:

=INDEX(Trees!B\$2:B\$75,MATCH(MIN(INDEX(ABS(IF(A2=Trees!A\$2:A\$75,(Trees!B\$2:B\$75-B2))),0)),INDEX(ABS(IF(A2=Trees!A\$2:A\$75,(Trees!B\$2:B\$75-B2))),0),0))

Sheet 1 (Named: Main)

Number
Date
Formula

1
15/06/2012

[Code] ....

Sheet 2 (Named: Trees)

Number
Date

1
05/06/2012

[Code] .....

## Array Formula To Return Multiple Values Based On Various Criteria?

Dec 19, 2013

I can manage an array formula that returns a value based on a criteria. Simple. But I want to add in an additional couple of criteria. Now I'm stuck....

My sheet looks at a manually entered postcode, finds out what region this is in, and returns a list of postcode I have defined as being in that region. (So the postcode WF1 3JY would return a region of Yorkshire, and list postcodes of WF, BD, L, etc)

I also have a list of engineers, with a column for their home postcode.I want to be able to list all the engineers from my list whose home postcode matches any of the values on the already created list from the postcode and region entered. So far I have this, which finds me all the engineers for just one postcode area.

## Creating Array From 2-dimensional Array

Nov 28, 2011

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.

## Creating List Based On Data In Two Cells?

Dec 16, 2009

Sheet11

EFGHIJK8480NVF-25747NVF25747

8581ATT-67546ATT67546

8682RTC-35615RTC35615RealRTC-35615
8783CSX-45715CSX45715RealCSX-45715
8884CSX-24915CSX24915RealCSX-24915
8985HTC-56947HTC56947

9086MCAS-85415MCAS85415RealMCAS-85415
9187BNSF-77716BNSF77716

9288MCAS-97316MCAS97316

9389CLRV-24760CLRV24760

9490CSX-24101CSX24101

## Filter An Array (the Longer One) Using The Shorter Array As The Criteria

Aug 26, 2009

I am trying to filter an array (the longer one) using the shorter array as the criteria. I am currently doing this using the following method

IF(LOOKUP(lookup cell, array)=lookupcell, lookupcell, "FALSE")

I then copy and paste 'values' and filter out the 'false' to get my final result.

This has worked in the past, but for some reason that I simply can't figure out, the formula isnt working! I've attached the example, and I've highlighted a number in blue (cell E522 and C103), (that should be being found in the 'LOOKUP' function) but is returning a "FALSE". I have looked over the code and simply can't figure out why Excel isn't returning the right value.

This is obviously happening for a quite a few of my numbers, as my filter result is returning an array that is about 1500 shorter than it should be. I have highlighted E522 as the 'example cell' to look at.

## Looking At Criteria In One Column And Creating List That Fit That Criteria

Aug 25, 2014

I can't seem to get this to work the way I need it to do. Let's say I have a 2 sheet workbook. Sheet1 is called "Order" and Sheet2 is called "005". On sheet2 (005) I have 9 columns that are populated. Row 1 is my header row and then row 2+ is all my figures. On sheet 1 (order) I want A1 to look at sheet2 (005) in F1:F10000 to find all cells that have "N" in the cell. I then want it to return value in column A that corresponds with the "N". I want it to list all the ones from Sheet2 (005).

Sheet 1 (order)
A
052611
052806
052843

Sheet 2 (005)
__A_____B____C_____D_____E____ F
052611_________________________N
154272_________________________Y
125485_________________________Y
052806_________________________N
125478_________________________N
052843_________________________N

how I can write is in a formula?

## Data Validation - Creating Subset Choices Based On Adjacent Column Choice

Oct 27, 2013

I am limiting choices in a certain cell to a list via data validation (using data validation, allow - list and pointing to the source). I want to limit the next set of choices in the next column (in this example in Column C) for the user based on the choice made in the adjoining cell. For example to keep it simple I will use the following: if the user selected "Fruit" in B1, then the options in C1 would only show "Oranges","Apples", "Pears". If however the user selected "Veg" it would only show "Cucumber", "Lettuce", "Spinach", "Radishes" in the drop down in column C. I can put a prefix in front of the column C data validation list if that is needed to narrow down e.g. FR-oranges, FR-apples etc.

A B C
1 Fruit Oranges
2 Veg Lettuce
3

Data Validation source for Column B (H2:H4)
Fruit
Veg
Nuts

Data Validation for Column C: (I2:I10)
Oranges
Apples
Pears
Cucumber
Lettuce
Spinach
Almonds
Peanuts

## Count Of Rows In Array That Meet Criteria In Column2 And Different Criteria In Column3

Dec 6, 2013

I have a table with 3 columns with an unkown number of rows (text, date, date) that is being imported daily.

I want to create a 4th column with dates starting from today and each subsequent row be one day earlier. I want to look at 30 previous days.

I then want to count the number of rows (looking at column 1-3) with the following criteria:

Countif column2<= "date in colum4" AND column3< "date in column4"

## Copying An Array Of Data Based On The Name In A Column

Aug 23, 2008

I have data as follows.

In the first column name of the month is specified.And in the third column of the same row day is specified.And followed by some data in rest of the columns and rows.

I need to copy the data from the presant sheet to a new sheet based on some conditions.

first need to search for the row where the word "MONTH" is there.if it is found then in the same row third column need to be checked for the day like "MONDAY" or "TUESDAY".If it is monday then need to copy 6*8 array of data i.e. (6 rows and 8 columns) into a sheet "MONDAY".if it is TUESDAY then 5*8 array data into a new sheet TUESDAY.And the search should be continued till the end of sheet.

## Creating List From Array?

Jul 7, 2014

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.

## Creating Multidimensional Array?

Feb 27, 2013

I 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]......

## Pull Data From Sheet Based On Criteria - Populate UserForm And Ask For Missing Data

Feb 8, 2014

I have a spreadsheet that is updated weekly -- but every week new info is added that needs a user to input corresponding info. I use a vlookup function to link to another spreadsheet that populates the info from previous weeks and the info that is missing shows up as #N/A...

First I was using a msgbox function to get the info:

HTML Code:

For Each b In myrange
If Application.IsNA(b.Value) Then
Employee = b.Offset(0, -2).Value
SSID = InputBox("Please enter ID# for " & Employee & " :", "New Employee Found")
b.Value = SSID
End If
Next b

But it can be up to 30 different new employees... and that is time consuming.

I would like to make it more user friendly by creating ONE userform that displays all of the employees as labels -- has a text box in which to put the ID # -- and then has a drop down box to choose the type of employee (2 options). I want all of that info to go back to the reference spreadsheet so it will be saved for following weeks, and then redo the vlookup to get the info into the new weekly spreadsheet (I can do that part)....

HTML Code:

Private Sub CloseButton_Click()
End Sub

Private Sub ComboBox1_Change()

[Code] ......

## Creating An Array From Several Seperate Cells

Mar 5, 2008

I 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.

## Creating Multiple Array Names

Mar 23, 2012

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

## Creating Array Of Numbers With Specific Definitions?

Feb 3, 2014

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.

## Creating An Array To Output Titles(text)

Jan 8, 2010

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:

## Creating Array Of Number From A Single Cell Value

Jan 17, 2014

I 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.

## Create Charts From Same Data But Each Based Ob Data Criteria

Aug 31, 2006

Hi, have a question regarding dynamic charts, specifically I am trying to create several dymanic charts based on data in a single column (So i can only say my first set of data will be starting a a set location in the chart, the rest has to shift down based chart data above it). This data should organized in charts depending on the data in several other columns. For instance, need to pull out values from column based some ID and some PartNumber. A sample excel or VBA would be much appreciated. I know VBA but do not know Excel VBA much.

## Extract Data Based On 3 Different Criteria?

Jun 28, 2010

I have a data sheet with 20 columns and about 300+ rows. In the results sheet there are 3 criterias (from column A, B, H in datasheet) which are in the form of dropdown in cell A1, A2, A3.

I would like to extract the data from the data sheet based on the criterias selected from dropdown list, to results sheet starting from row 6, with only 12 headers out of 20 (non-continuous, e.g. columns A-C, E, G-K etc.)

## Return Data Based On Criteria?

Dec 19, 2012

generating a formula that takes into account a range of values (an entire row) and from this row, I would like the formula to select, for example anything greater than 80%. After the formula selects anything greater than 8, I would like for it to select cells that are above or below the cells that have values greater than 8.

1
2 JLKNSTTP
3 85934942
4
5

For example, in the above datas, I would like a formula to select anything greater than 8 in row three and select cells above it. In this example it would be j, k, and t.

=if((3:3>8),offset(A3,-1,1,1))

## VBA To Populate Data Based On Criteria?

Jul 16, 2014

I need to populate data based on criteria.

The criteria has been attached as follows.

Sample Test.xlsx

## Data Validation Based On Two Criteria

Feb 16, 2009

I need a data validation to prevent entries when they are > 50, but only if the value of another cell in col A is "Payment". The value of 50 is an example, I have another formula to get this number using VLookup.

The idea is that if the value of the cell in col A is "Income", I don't want the entry to be rejected. But if the value in col A is "Payment" or "Transfer" and > 50, I want the entry to be rejected (if I only have \$50, then the payment cannot be > 50).

## How To Paste Data Based On Criteria

Feb 18, 2012

I have a problem and I hope that it can be solved using a macro. Basically, I have a set of data in column A and B in sheet(1):

A
B
1NameDate
2JOY12/09/11
3MAR12/12/11
4DAVID12/12/11
5

I have another set of data in Sheet(2):

A
B
C
D
E
1NameDatesalary
CHARACTERRATINGS

[code].....

basically, I would take into consideration the names and dates in sheet(1) and if it matches sheet(2) then it would then paste their salaries, characters and ratings into sheet(1).