Extract Values From One Column Based On Two Criteria From Two Other Columns?

Feb 14, 2013

In the attached, sheet 2 has a formula that pulls the Item (column B) from Sheet 1 based on Instocks (column F) being less than the value in N2. Would it be possible to add a second criteria to the formula in sheet 2? In short, can I pull the item from column B of sheet 1 into sheet 2, where in sheet 1, column F values are less than N2 AND where column G values are greater than O2?

I'd love to just vlookup the fill rate value and then filter it down, I know at some point I will be asked to weed it down a bit via a formula instead.

Compare Data In 2 Columns Extract Unique Values And Paste Into 3rd Column?

Sep 11, 2013

Here's how my daily report is currently set up:

ExternalInternalDifferences
123000123000
234564234564
345456345456
456567378987
565456456567
654547524564
788879565456
865478654547
745654
788879
865478

The External column contains data provided by an external vendor. The Internal column contains data compiled internally. The data in the Internal column will always contain data duplicate to the External column, but will also contain unique data not present in the External column. I would normally cut out the data contained in Internal column that is not duplicated in the External column, and paste it into the Differences column, and move the remaining cells in the Internal column up. The result would be the External column data and Internal column data would align by row, and the data unique to the Internal column is segregated to the Differences column, like so:

ExternalInternalDifferences
123000123000378987
234564234564524564
345456345456745654
456567456567
565456565456
654547654547
788879788879
865478865478

While not a difficult task for 8 rows of data, the actual daily report contains over 1000 rows, on average. I would like to set up a template workbook, where I can simply open the template workbook, paste the data into both the External and Internal columns, then have the values that are unique to the Internal column extracted from the Internal column and inserted into the Differences column.

The end product would be used daily, with differing amounts of data (from 100 rows to 10,000 rows).

Sum Column Based On Two Criteria From Two Columns

Nov 7, 2012

I'm not sure if this is the correct way to display my sample data, but here it is (how to change it. I looked in the guidelines, but I can't download anything onto this computer, so I can't get the HTML maker)

A
B
C
D
E
F
G
H
I

1
Descriptor
Group
Identifier
Inventory
Total Vaue
Invetory Type

Group
Total Incomplete Inventory Value

[Code] .......

Here is what I would like in:

The sum of the total value of half finished and unfinished inventory for each group.

So, for group one, it would be 145. For group two if would be 38, and group three would be 316. (displayed in column I)

I think an array formula would do this, but I'm not really sure how this would work.

Return Multiple Values Across Columns And Rows Based On Criteria

Jun 20, 2008

I have a table with column headings of product ID Numbers (eg.1111) and row headings of Store number (Eg.1) with data showing the time each product was last sold at that store, I need something to consolidate for each store which Product ID's were sold prior to 5pm and what time they were sold.

EG

Store 1 1111 16:40
2222 13:00
Store 2 1111 15:05
3333 16:50

Adding Values From One Column Based On Criteria In Another Column?

Jan 30, 2013

If i have the following layout of data

Column 1 Column 2
January 1000
January 1234
February 1300
March 1600
January 15
March 123
April 234
January 3000

I would like a formula that adds all the January numbers together returning a result of 5249

To move this one step further i would eventually need to add these numbers based on quarters, for example if the value is January February or March in column 1 then add the numbers in column 2.

I'm sure i have done this before using a countifs maybe but my mind has drawn a blank

Match Two Columns Across Sheets And Based On Criteria Of Another Column

Aug 10, 2009

This is driving me nuts. What I have is:

Sheet1:
ColumnB - number stored as text (Work ID)
Remaining Columns: - mixed data, some columsn numbers, some text

Sheet 2

ColumnA - number stored as text (Work ID)
ColumnB:ColumnAW - mixed data, some columsn numbers, some text
CollumnAX - Dollar Values

What I want is in Column F (Sheet1) a formula to:

if workID in Sheet1!ColumnB is present in Sheet2!ColumnA AND Sheet2!ColumnAX>1 then return True

Count Unique Values In Column Based On Values In Other Columns

Mar 14, 2014

I'm running into an issue trying to calculate unique values in a Data column based on a few variables in other columns.

My current formula in Summary tab D4:D19 is
{=SUM(IF(FREQUENCY(IF(Data!\$I\$3:\$I\$66<\$E\$1,IF(Data!\$A\$3:\$A\$66=\$H\$1,
IF(Data!\$C\$3:\$C\$66=A4,ROW(Data!\$I\$3:\$I\$66)))),ROW(Data!\$I\$3:\$I\$66)),1))
+
SUM(IF(FREQUENCY(IF(Data!\$I\$3:\$I\$66<\$E\$1,IF(Data!\$A\$3:\$A\$66=\$I\$1,
IF(Data!\$C\$3:\$C\$66=A4,ROW(Data!\$I\$3:\$I\$66)))),ROW(Data!\$I\$3:\$I\$66)),1))}

This is currently counting the number of times a date value (data column I) appears for that name (A4:A19) in the data when meeting all of the conditions. I need it to instead count the number of times a unique date appears for that name with the additional conditions met (which all appear to work fine).

The results in the pink highlighted cells (Summary column D) should be:

Names starting with A - 3
All others - 2

I've left some other columns in the data with X's so that I can easily convert this back to my working spreadsheet.

Return Column Values Based On Criteria?

Jan 31, 2014

I have been creating a schedule on excel, the schedule includes a top row which has the following headings Date, Agent_ID, title, agent_name, 07:00, 07:15, 07:30, etc up until 21:45

The columns that are named with times are times that indicate a break time.
The column named title is the actual shift time, eg 08:00 - 17:00.

I need a formula that would look at my source data, and populate a sheet in the following layout

agent_id, agent_name, title, start_time, end_time

The title be one of the following:
Shift 08:00 - 17:00
Tea Break 10:00 - 10:15
Lunch Break 12:00 - 12:30
Tea Break 14:15 - 14:30

If I need to have the shift portion and the break portion appear on separate tabs that would also be ok, but ultimately I need to keep my original source as is, but the change it to be able to upload it into a MySQL database.

Turn To Values Every Nth Column Or Based On A Criteria?

Apr 14, 2014

I need a macro to turn to values all formulas in specific columns only. For example column C, F, I, L,O,...until column UYG, always with the interval of 2 columns.

Values In Column Based On Multiple Criteria

Dec 2, 2006

Option Explicit
Dim lastrow As Long, t As Long
Sub Method()
lastrow = ActiveSheet.UsedRange.Rows.Count
For t = lastrow To 1 Step -1
If Cells(t, 8).Value <> "" Then
If Cells(t, 9).Value = "Y" And Cells(t, 10).Value = "" And Cells(t, 12).Value > _
6 And Cells(t, 12).Value < 60 Then Range(t, 25).Value = 20
End If
Next t
End Sub

Alright, the above code is not working. I am not sure if it is the write part (t,25 value) that is wrong. I want the Y column to be written with a method numbered "20" if the conditions (H is not null, J="Y", K="", and 6<M<60). I have numerous other methods to put in. The reason I'm not doing Case Statements is this is jsut to write the basic code, and then I will have to move it over to ReportSmith using ReportBasic.

Sum Cells In A Column Based On Multiple Criteria For Neighboring Columns

Jul 1, 2013

Example data:

01/05/2013
150
Yes

01/06/2013
1
Yes

[code].....

Basically I'm trying to write a formula that will sum the total of the numbers from column B but only where the month equals June AND column 3 says "Yes".

So the answer would be 101, because rows 2 and 5 are the only rows of that table that meet all the criteria, and the sum of their column B cells would be 101.

I thought I was getting there with Sumproduct and Sumif but I'm now drawing a blank. There is a little extra complexity in that I'm actually pulling the month value from a set of cells elsewhere, but if someone can solve the puzzle above I hope I can do the rest on my own as it's just locations of data to pull from other worksheets really.

VBA To Sum Values In One Column Based On Criteria Of Another Column

May 10, 2014

The below sample is an accurate example of a much larger set of data. Column H will be my desired result.

I need a macro that will look at the matching values (numerical value of month) of Column F, then enter the sum of the corresponding values (rows) contained in Column D for that month. The sum of each month should be placed in Column H at the last value of the row for each particular month.

A
B
C
D
E
F
G
H

1
Item#
Description
Quantity
Month
Year

2
1001

[Code] ........

Finding Minimum Value In Column Based On Multiple Text Criteria In Other Columns?

Jan 2, 2013

This formula allows me to find the lowest value in column U where column N contains the text "NO".

{=MIN(IF(\$N\$2:\$N\$10000="NO",\$U\$2:\$U\$10000))}

I want to add another condition so that the formula only returns the lowest value in column U where (i) column N contains the text "NO" and also (ii) column F contains the text "YES".

Removing Duplicates Based On Column Values And Criteria?

Apr 3, 2013

removing duplicate names. Students were allowed to take a quiz as many times as they wanted. I need to remove the duplicate entry by keeping the highest grade.

Here is the setup of my excel file. Column 1 has surnames, Column 2, has first name, and column 3 has grade.

I can't figure out how to filter them based on first and last name because some students have the same name. with the grade as the criteria

I would need an excel formula not macro

Formula To Sum Values From Column Based On Multiple Criteria

Nov 29, 2013

I have attached an example workbook with a tiny subset of data and a number of criteria from the Dashboard Engine page removed.

What I need is to sum the total value for each division on the second sheet based on the date criteria (so for the first cell Jan 1/2010 - Jan 31/2010) and on a match between division name on the Dashboard Engine sheet table and the Masterdata sheet. I have tried a number of variations but keep getting a 0 for each return.

Sample Workbook.xlsx‎

Loop Criteria Based On Unique Values In A Column

Apr 3, 2009

i had a database from which i need to extract data from COlUMN B, select the

Excel VBA To Sum Column Based On Values In Two Other Columns

Jul 9, 2014

I have successfully populated a website forms using Excel VBA. As a continuation of this task, i need to calculate the total of a column based on two other columns in the same worksheet.

Column A contains Date, column B contains 3 fixed alphabets (I, L and K) and column C contains time. I have attached a sample sheet here. sample.xls

How can i write a VBA code to find the sum of effort values of column C for each values in column B for a given date (value in column A).

Sum Of 3rd Column Values Based On Nested If On 2 Other Columns

Apr 28, 2007

I have three columns as follows:

ItemsWeekEnding Count
a14/7/2007 10
a34/7/2007 3
a14/14/2007 11
a14/7/2007 5
a44/7/2007 6
a14/21/20078
a44/14/20072
a34/21/20071
a14/28/20077
a24/21/20074
a94/28/20079

I want to Sum Count values (column 3) for matching WeekEnding (Column 2) and Items (Column 1), example:

a1 4/7/2007 15
as a1 for this weekend is occuring twice.

I need the formula to do it in excel.

Divided Sums Of 2 Columns With Denominator Changing Based On Blank Values In Column

Dec 31, 2013

I am trying to create a sheet on docs.google.com that takes the sum of 1 row divided by the sum of another row removing numbers from the denominator if fields are left blank. Hard for me to explain so here is an example:

Counter
Column 2
Column 3

[Code]....

Leaving a blank value in column 2 makes it read 4/6 giving me 66%, Column 3 giving me 50%.

What I would like it to do is if there is a blank value in any column remove column one from the denominator.

Upon completion the total output percentage for column 2 should be 100%, and column 3 should be 75%.

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

Extract Certain Data Based On Criteria

Nov 30, 2006

I have a raw database that contains various data all in one column and I only want to extract certain data to a new column. All I want is to extract the time, like 14:00, 14:15, 14:30, etc...... from column "A" and populate column "D" if it meets this criteria.

I have tried everything I can think of, very difficult yet it seems to be so simple.

The "date" is really a date format but the "time" is really text.

Here is what the data looks like:

Column A has....

Name1:
Name2:
Name3:
10/16/2006
14:00
14:15
14:30
14:45
15:00
10/27/2006
16:30
16:45
17:00

Scripting Dictionary - Compare Columns And Extract Data That Meet Criteria

Mar 23, 2014

I have a spreadsheet with 2 tabs:

AllData:
Name
Data

[Code]....

Extract Largest Values From Column B And Corresponding Text Values In Column A?

Oct 1, 2013

There should be a formula to extract the largest values and its corresponding values. For example, if column

Products Costs
A1 Pen 200
A2 Pencil 125
A4 Apple 1500
A5 Xbox 222
A6 TV 100

The desired outcome

Products Cost
Apple 1500
Xbox 222
Pen 200
Pencil 125
TV 100

The second goal is extracting the top 3 largest values using a formula.

Sample file is attached for your convenience. Desired outcome is in GREEN.

Extract Rows From One Sheet Into Another Based On Certain Criteria?

Dec 21, 2013

How can I extract rows from one sheet into another, based on certain criteria?

Extract Sublist From List Based On Criteria(s)?

Jan 6, 2014

I have a table with multiple columns. I would like to extract a sublist from column A, based on 1 criteria (<94) on column J. I used the index-array-row array function, however, it does not give me the intended result. It repeats the same name.

My final intended result is to extract the whole row based on criteria on column J.

Macro Loop To Extract Specific Values And Min/Max Values From Column/Rows Range

Jun 3, 2009

Hi, I'm very new to writing Excel Macro's and wanted to know if I could do the following. Conceptually, I understand what I need done and think it should be fairly straightforward.

There's 2 main events in this loop (I hope that's the correct terminology):

Input 1) User defines the beginning cell to start the loop. In this case, A2.

Input 2) User defines the range of columns/rows to display. The formula for rows that I've thought of is 4r. So if a user wants 20 rows below cells A2, they simply input 5 for r. The number of columns is a constant 5. So if r=5, then I'd want the range to be A2:E22......

Extract Values That Exist In All Columns

Aug 8, 2008

Each column represents a user and each row represents an application the user has. I would like to extract all the applications that are common to all the users and place it in an arbitrary column.

For example

User1_____User2_____User3
Office_______Office_____Office
Flash________Flash_____Snake
Firefox_______Firefox
Solitaire

The above list should generate Office and Adobe since every user has Office and Adobe.

Extract Data From Excel Database Based On Criteria

Apr 16, 2012

What I am trying to do is extract all the data from a excel database based on criteria. When i specify the site and month I need to pull all the corresponding data and write it to a worksheet. Below is an example. I am not allowed to show all the data it is confidential.

Site Date Operator Month
Chicago 12/3/11 Daniel December
San Jose 4/8/12 Mike April
New York 4/8/12 John April

Extract Unique Values From Multi-Columns

Feb 20, 2013

I wan to extract Unique values, here is the data for reference, I have made to tables the second table is desire result I wanted.

Sheet30  ABCDEFG1JOB #WASHQTY JOB
#WASHQTY27800A1000 7800A100037801B1000 7801B100047802A1000 7802A100057800
A1000 7802C100067802C1000 7805B100077805B1000 7807D100087800A1000 7809D
100097807D1000    107802C1000    117809D1000

Excel 2010 :: Extract List Of Names From Table Based On Two Criteria?

Mar 3, 2013

I'm trying to extract a list of names from an Excel 2010 table based on two criteria, thus:

=IFERROR(INDEX(Database[FullName],AGGREGATE(15,6,ROWS(Database[Age]-ROW('Database'!\$A\$2))/((Database[Age]>1.8)*(Database[Age]