# Pulling Rows Of Data Based On Criteria In One Column?

Dec 17, 2013

I've attached a workbook with a description of what I'm trying to do. I'm not sure if it can be done with formulas or if it will require VBA. I can move it to the VBA section if the latter terms out to be the case. it's not that I'm opposed to copying code, I just like to know how it works with enough depth that I can fix/modify it, and I can't do that with VBA).

## Excel 2010 :: Pulling Data Based On Multiple Criteria With Duplicate Values

Mar 14, 2012

I have a sheet (see Sheet 1) from a report we run which lists the following information: Personnel Number, Amount, Wage Type. This is generated for 1000's of employees, with each personnel number being repeated several times in column A.

I am trying to pull specific data to another sheet (see Sheet 2), which would ideally generate the sum of "Amount' for a specific wage type for each personnel number. The issue is is that there may be dplicates of the wage type for each ID number (which is also repeated).

For example, the total salary amount on sheet 2 for ID#12345678 would be 0, while for #9876543 it would be 1250. Is there a formula I could use on sheet 2 column B that would generate this?

Excel 2010 ABC1Personnel NumberAmountWage Type212345678550Payment312345678400Overtime412345678300Overtime512345678250
Payment612345678750Vacation798765432800Salary898765432250
Payment998765432100Overtime1098765432450Salary1198765432300Overtime
Sheet1

Excel 2010 ABC1Personnel NumberTotal SalaryTotal Overtime212345678398765432
Sheet2

## Pulling Data To Separate Sheets Based On One Column

Oct 4, 2013

How to pull data based on column B to individual tabs.

I have the following data:

A B C D
Jane Doe | L1 | 20% | High
John Doe | L2 | 15% | Medium
Mike Smith | L1 | 60% | Low
Marie Smith | L4 | 10% | Low

I want to have the first tab/spreadsheet only pull records that have L1 values for column B

In the next tab/spreadsheet I want to pull records that have L2 values in column B etc.

What formula can I write that will pull all L1 records (A.B.C.D) in to Tab 1, then all L2 records (A,B,C,D) into the next tab?

## Pulling Rows From Other Sheet - Skipping Over Rows If Column Is Blank

Jan 17, 2014

I am working in the attached spreadsheet. For anyone who does not wish to open the link, let's say these sheets are titled 'Sheet1' and 'Sheet2'.

Sheet2 looks like this:

Year Cat Name
2013 4 Sal
2013 4 Pat
2013 3 Pat

[Code]....

Now, how may I pull this data into a summary sheet (Sheet1) that skips over any instances where column A ("Year") is blank so it looks like this:

Year Cat Name
2013 4 Sal
2013 4 Pat
2013 3 Pat
2013 2 Pat
2013 5 Bob

[Code]...

Note: All columns are the EXACT same across each sheet(including Sheet1, Sheet2, Sheet3), if that extra piece of information works.

PS It is fine if, in order for a formula to work, I need to copy and paste the formula for the length of rows per each Sheet to consolidate.

EDIT:

So, in my industry, any summary-level data (shown on Sheet1) must have a linked reference to a particular sheet (Sheet2, Sheet3, etc.) so the user knows where the raw data came from. This is to ensure increased accuracy of data and minimize human error (like, if for example, I accidentally didn't copy and paste all the rows correctly).

I have uploaded the actual sheet to show what the data actually look like. I am trying to pull in 2013 data into my "Summary Page" and would like to have all the data linked to the '2013' sheet but skip any blank rows.

Here is my failed formula:

=INDEX('2013'!F\$3:F\$338,SMALL(IF('2013'!\$E\$3:\$E\$338<>"",ROW('2013'!\$E\$2:\$E\$338)-ROW('2013'!\$E\$2:\$E\$338)+1,ROWS(B\$1:B1)),""))

## Re-arrange 1 Column To Rows Based On Criteria

Oct 17, 2007

I'm trying to rearrange my data so that a column of info for each unique value in another column becomes a row next to that value.

Example:

A 1
A 2
A 3
A 4
B 66
B 77
B 88
C 10
C 15
C 20
C 25
C 30

should become... (6 columns)

A 1 2 3 4
B 66 77 88
C 10 15 20 25 30

I had a really hard time figuring out what to search for (and in writing a description) for this one.

## Pulling Over Data With Specific Criteria?

May 23, 2013

I have a huge listed of assets - the column i where my info is has vasrious serial numbers.... some are a simple string of letters/numbers and the others look like this for example AG-1234567

I need to somehow pull out the ones that look like that. (AG-1234567). I only need those. Now normally i would just do a filter and pull em out however, this sheet contains 73k worth of items.

what would be the formula to perform such a task?

## Copy Rows Based On Criteria In Single Column

Jan 30, 2008

I have on sheet1 of my workbook a number of rows of data, from column A through Column G. The Data in column G either contains the text "Yes", or "No".

I need a macro that will look through each row in this sheet, and upon finding a "Yes" in the cell in the column G, will copy that entire row (columns A through G) into a new row in sheet2. Sheet2 currently contains data, so I'd like to move everything down by one row and paste this data at the top (row 6 is the first line of data on this sheet).

I'd then like to go back to sheet 1, and delete the row I've just copied. Then continue though the loop. The loop needs to stop when a blank row is discovered.

## Automatically Copy Rows Based On Criteria In Column

Jul 31, 2008

I'm not sure that I'll be able to explain this clearly, so I've attached an example. There are 3 columns - Issue, Action Group and Status. Many Issues belong to the same Action Group and others do not belong to any Action Group. The is also a Status summary section that has the total Issues and Actions associated with each status. For example the Status called Resolved could have 10 issues and 5 Actions (including blanks). The problem is that I can't figure out a way to get the total for the Actions. As I said this explanantion isn't great, so the attached sheet

## Copy Paste Rows To Another Sheet Based On Column Criteria

Jan 17, 2007

I have a file that has two sheets, I have some formulas in the first sheet named "Data",What i would like to have is, If column G in the sheet Data is "Closed", then i want that particular row to be cut from the sheet " Data" and pated in to the Sheet "Done".

## Delete Rows Based On Criteria While Referencing Column Header

Jun 6, 2007

I'm trying to use VBA code to delete certain rows based on a couple of criteria as follows:

1) If column header is "TERMINAL NAME" and the cell value in that column is "BONDDESK", I need the entire row deleted.

2) Similiarly, if the column header is "PRODUCT TYPE" and the cell value in that column is "CORP", that entire row needs deleted as well.

The information is coming from another source, so the columns may change from day to day, i.e, "TERMINAL NAME" may be in Column L one day but in Column O another day.....

## Pulling Rows From Other Tabs In Workbook Based On Date?

Jan 25, 2014

I have a workbook with multiple tabs, each tab is representative of a person and their ongoing project list. What I am trying to do is to be able to pull rows from each tab identifying the persons active projects based on an assigned due date.

Details: Tabs for Tom/Susan/Phil/Jerry each person will be updating their own sheet with project completion details. Each tab has similar headers with a corresponding due date or completion date. On the master sheet I would like to pull those rows that correspond to the specific dates for all persons.

Example - give me all rows that reflect projects due on 1/21/2014.

## Pulling Data From Multiple Columns If Meets Criteria

Aug 13, 2012

Have a worksheet Pricelist, require to pull data from the columns to a new worksheet only if qty is more than 0, and delete empty rows afterwards. Required result is in worksheet order. Original file is about 10K rows.

Attached sample file : example.xls

## Formula For Pulling Data Into New Worksheet If Certain Criteria Met Without Using Macro

Jun 3, 2014

I would like the following columns to show on a new worksheet if column Z and AA (which show a date and time) are the same as todays date, the columns of info I would like it to pull through are Column C,D, Z, AA. The row numbers could be different daily? is this possible?

## Pulling Column Headers Based On Table Values (not Max Or Min)

Jun 12, 2014

I've done some searching and cannot find the right answer for my question. I need to create a formula that will pull column header text data based on cells within a table that contain a numeric value not equal to zero. The catch is that I cannot use any one specific numeric value as the basis for a Lookup.

I've attached an example spreadsheet for reference. Basically, I need two formulas:

1. Formula to pull column header (Receiver name) based on Sender. This is a necessity.
2. Formula to pull individual lines for those senders that have multiple receivers (Division D in my example).

My actual data file has 80+ senders and 100+ receivers.

## Delete Duplicate Rows Using Data In Column A As Criteria

Jan 13, 2009

I have a spreadsheet that has thousands of rows of data with maybe 6 columns.

I would like to remove the duplicate rows but the problem I am facing is that I would like to use column A as the criteria.

There are some duplicates in column A but the issue is that the data in column B, C, D, etc may not be the same for each duplicate.

Easier to see in this image:
http://img127.imageshack.us/img127/6364/datajv6.jpg

So A1 is the same as A6
A1 has data in B1 and C1, no data in D1
A6 has data in B1, C1 and D1

I need to delete either row A1 or row A6, it is irrelevant which row it is but one of them has to go.

## Pulling Individual Rows Out Of Repetitive Data.

May 13, 2009

I have a workbook populated with values in 3 columns. The values of the second column group rows into larger segments. I want to pull one row for each different value onto a different sheet.

example:
1. apple
2. apple
3. apple
4. apple

How do I only pull one row of apples onto another sheet without copying by hand? I have attached an live example. I want to pull one row for each classification (column 2) named on the sheet without copy/pasting each one.

## Finding Data Based On Row & Column Criteria

Jun 15, 2006

I have a main soure data which consist of row & column information. What i want to do is search the data from the source data into my result data as per the attachment file. Example: I want to information of Jan & banana from the main source file to appear in the XXXX

Result data(criteria base on Month & type)
JanApril
BananaXXXX
Apple
Orange

## Move Data Based On Criteria In Column

May 2, 2007

Here is the situation:

There is a spreadsheet that I use that has multiple plans in one column for various members. You can see the plan for Robert James and Amanda James is plan 5. If all this information was in column A how would you move the plan type say column C for each member?

For example
Column A
Customer Name
Robert James
Amanda James
Plan 5
Nikki Martinez
James Gross
Plan 6

## Link Row Of Data From One Worksheet To Another Based On Column Criteria

Oct 11, 2012

I have excel sheet with 4 worksheets tabs(Master, Won, Lost, No Bid). All data entered on Master. I want each row to automatically link/appear on 2nd, 3rd or 4th worksheet tab based on info in column Q (Result) which would be either 'Pending' - in which case it would stay on Master, 'Won' - would stay and copy to Won worksheet, 'Lost' - would stay andcopy to Lost worksheet, 'No Bid' - would stay and copy to No Bid worksheet. All rows would always show up on Master worksheet.

## Pulling Specific Data From One Column

Apr 16, 2009

Start Date: 1/1/08
End Date: 1/1/08
Users: 100
Description: ...whatever

What I want to do is pull just the end dates into the next column. Is there an easy way to do this or do I need to build a macro?

## Copy Data Table Seperate Sheets Based On Column Criteria

May 12, 2008

I need to copy all the work sheets into one single work sheet (mastersheet). The source work sheets are having same column structure. The condition which i need to take care of is that after column 3 if at all there is any data till column 10 then in the destination mastersheet these should be copied in different rows with first two columns repeated. I need to do this using VBA macro.

## Excel 2007 :: Summing Column On Data Sheet Based On Multiple Criteria

Feb 23, 2012

Excel 2007. I have an Excel file that contains a data dump from an external database file with numerous analytical sheets that perform calculations. Some of these calculations utilize the SUMIFs function that was introduced in Excel 2007. This function does an outstanding job of summing a column on the data sheet based on multiple criteria.

However, someone high up in management in my organization would like to "drill down" into the data behind the

SUMIFs formulas to get a quick snapshot of the lines in the database that roll into the
SUMIFs formula. =SUMIFS(DataBase!E:E,Data!A:A,C7,DataBase!B:B,D7,DataBase!C:C,E7,DataBase!D:D,F7)

If I double click on a cell with the formula above, Excel takes me to the Database tab and selects Column E which is close, but not exactly what I need. What I really need is for Excel to only show the rows on the database sheet that make up the total in the SUMIFs formula and not the entire data dump from the database.

At present, we have to manually apply the autofilter on multiple columns to show the rows in column E that make up the total in the SUMIFs formula which is a tedious and time consuming task. Is there a way to force Excel to do this? Suggested custom database application or pivot tables, but we do not want to reinvent the wheel.

## Copy Column Data From Multiple Worksheets To Single Worksheet Based On Header Criteria

Oct 18, 2013

I have a workbook with many sheets of similar but not identical data. I need to extract columns from each sheet based on 5 header criteria and paste them to a single sheet. Each worksheet contains these 5 criteria.

I've been working with the VBA script I've pasted below. It's grabbing the 5 column criteria that I have in sheet 12, and comparing them to sheet 1 in the workbook, then copying them to sheet 12. This much is good, but I need the script to also return the data from the other worksheets as well. I've tried modifying the script based on other loop functions in other scripts I've found, but I'm not having any luck.

## Paste Rows Based On Data In A Column

Jul 24, 2007

I've been using a macro that, among other things, will split a set of data into different sheets based on one column. For example, if I have Column A populated with numbers 1-10, each entry with a "1" in column A gets pasted into a sheet entitled "1", and so on. Some of the numbers I group together (for example, let's say I would group the 2s and 3s together).

I've been using the code below, but it seems to be very fickle. Half the time I run the macro, I get a "Run-time error 1004" and it points to the line "Sheets.Add. Name = WhtSht". Is there a different (easier) way to accomplish what I'm trying to do?

Dim rng As Range, StrtSht As String, WhtSht As String
StrtSht = ActiveSheet.Name
For Each rng In Range("A1:A" & Range("A65536").End(xlUp).Row)
WhtSht = rng.Value
If WhtSht = "21" Then WhtSht = "7"
If WhtSht = "34" Then WhtSht = "33"
If WhtSht = "36" Then WhtSht = "33"
If WhtSht = "37" Then WhtSht = "33"
If WhtSht = "56" Then WhtSht = "55"
If WhtSht = "57" Then WhtSht = "55"
If WhtSht = "76" Then WhtSht = "75"
If WhtSht = "97" Then WhtSht = "96"
If SheetExists(WhtSht) Then
Rows(rng.Row).Copy
Sheets(WhtSht).Select
Range("A" & Range("A65536").End(xlUp).Row + 1).PasteSpecial xlPasteAll
Sheets(StrtSht).Select
Else
Sheets(StrtSht).Select
Rows(rng.Row).Copy
Sheets(WhtSht).Select
Range("A1").PasteSpecial xlPasteAll
Sheets(StrtSht).Select
End If
Next

## Consolidate Rows Of Data Based On Single Column?

Aug 16, 2014

I have thousands and thousands of rows of data which look like the spreadsheet screen shot shown in this image:

ExcelConsolidateRaw.JPG

I want to consolidate them so that they look like the spreadsheet screen shot shown in this image:

ExcelConsolidateDesired.JPG

How to insert it into a module in the VBA editor.

## Delete Rows Based On Seperate Column Data

Nov 29, 2006

How can I delete an entire row based on seperate column data? Example: I want Excel to look at cell A2 and cell E2. If the values are the same I want Excel to delete row 2.

## Pulling Information From A Column Based On Information In A Second Column?

Jan 27, 2014

Imagine I have 2 columns of information that look like this:

Column A
Column B

AS
Dog

AS
Cat

AS
Hamster

FT
Fish

These are my key columns. The letters are initials of people and the animals are the pets they're responsible for.

Now, I have 3 more columns that look like this:

Column D
Column E
Column F

These columns can go on for hundreds of rows.

What I want to do is pull out the information from columns D, E and F where the initials and pet match those in the key list, then paste that elsewhere (say to columns J, K and L).

So, for instance, the first entry would be copied across because, according to the key list, AS is responsible for a Dog, but the bottom entry for AS wouldn't because he was looking after a fish, and that pet isn't listed as one of his animals in the key list (Fish is listed alongside FT). Likewise, the entry for AH wouldn't come across because AH doesn't appear on the key list at all.

## Sum Range Based On 1 Criteria Of Column & 2 Criteria Of Another

Mar 4, 2008

i m trying to use the sumproduct formula, and OR but i cannot seem to get this right! =Sumproduct(--(A1:A10="Yes"),--(OR(B1:B10="Yes",B1:B10="Mayby")),C1:C10)

I have also tried Array Formula as follows; {=SUM(IF(A1:A10="Yes",IF(OR(B1:B10="Yes",B1:B10="Mayby"),C1:C10)))}

I have also used UDF to for the sumproduct, but cannot make that work! keep giving me value message

Function
Function Customer(Service as Range, Outcome as String, Service2 as Range, Outcome2 as String)

Customer = Sumproduct(--(Service = Outcome),--(Service2 = Outcome2), Result)

-Didnt get thru this bit to start building on the Function! keep giving me #Value!

## Pulling Name If Multiple Criteria Follows?

May 10, 2013

I'm looking to create an automated table with formulas to populate based on multiple criteria in a data sheet. I want to pull the person with the highest SALES but meets every criteria listed.

Currently I have it set up like:

Name | Sales | Penetration | Score | Target, etc.

But each criteria has a minimum/maximum criteria that they have.

I'm wondering, how would I be able to pull the NAME of the individual with the highest sales but meets criteria such as Penetration, Score and Target.

## Delete Rows Based On Criteria & X Rows Below

Mar 5, 2008

This sheet has A:K columns and 1:3212 rows. There are 'page headers' that are in the text file that I want to delete (the text file was exported from an AS400 program). The first row that starts the page header has SA341 in column 1. Each page header has 5 rows. I used this code from one of the other threads on deleting rows, but I obviously do not understand the code as it deleted all rows that contained SA341.
Sub DeleteRows()
Column_To_Check = 1
Start_Row = 1
End_Row = ActiveSheet. Cells(Rows.Count, Column_To_Check).End(xlUp).Row
MsgBox End_Row
Search_String = "SA341"
For Row_Counter = End_Row To Start_Row Step -1
If ActiveSheet.Cells(Row_Counter, Column_To_Check).Value < SA341 > Search_String Then
ActiveSheet.Rows(Row_Counter).Delete

End If
Next Row_Counter
End Sub