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).
View 6 RepliesI 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
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?
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)),""))
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.
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?
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.
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
View 3 Replies View RelatedI 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".
View 5 Replies View RelatedI'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.....
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.
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
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?
View 7 Replies View RelatedI'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.
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.
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.
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
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
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.
View 1 Replies View RelatedStart 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?
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.
View 9 Replies View RelatedExcel 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.
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.
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.Add.Name = WhtSht
Sheets(StrtSht).Select
Rows(rng.Row).Copy
Sheets(WhtSht).Select
Range("A1").PasteSpecial xlPasteAll
Sheets(StrtSht).Select
End If
Next
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.
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.
View 4 Replies View RelatedImagine 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.
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!
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.
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