Excel 2007 :: Create Dynamic And Unique Sublist Based On Multiple Criteria
Aug 26, 2012
Excel 2007, Windows 7.
Most details are shown in the spreadsheet below. I would like it to be dynamic because the quarterly and annual data dumps I'm working with are are hundreds to thousands of lines.
Have the list be sorted, which is a part of the first attempt, would be nice but is not necessary. At this point, just being able to generate the dynamic list would be fantastic.
Excel 2007ABCDE1product lines:consist of these product subtypes2Widget series:Widget.type1Widget.type23Fidget series:Fidget.type4Gidget series:Gidget.type1Gidget.type2Gidget.type356data dump of parts sold or used in repairs7product subtype repairedpart number
[code]......
View 5 Replies
ADVERTISEMENT
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.
View 5 Replies
View Related
Jun 27, 2012
I have a table such that column A has a list of names, column B shows either "Yes" or "No" depending on if that person's info needs updating. I am trying to find a way to a populate a list (prefereably in column C) that has only the names of people from column a that show a yes in column b.
I know that i could just do =if(B2 = "Yes", A2, "") and then drag the formula down, but that leaves blanks in between the cells. If column A has 10 names and only 3 are Yes then I only want the first 3 cells in column C to be populated with the corresponding names.
I think I need to use a combination of INDEX and MATCH functions but i dont know how to do that with a criteria that falls in another column.
View 14 Replies
View Related
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.
View 2 Replies
View Related
Aug 31, 2007
Our reservation system exports a spreasheet daily which contains hundreds of records relating to bookings. The worksheet has an ID column which is not unique, but is sorted alphabetically. I need to write some code to delete all records below the first 'set' of records relating to the first ID value found. i.e. i want to end up with only the set of records relating to the first ID found. So the basic logic would be to look at the value of the field in the ID column of the first record and then iterate down the rows until it came to a different value and then delete all rows below that. Its simple in concept, but im stuck on how to write this.
View 9 Replies
View Related
Aug 26, 2013
I have an Excel Spreadsheet (2007) that contains over 500,000 records that shows Electric meter usage per month over a 24 month period per meter. What I want to be able to do is to select a meter row per number and copy is to it's own worksheet. At the end - I want to be able to have a seperate worksheet per electric meter number - that I can create a graph. If I go through all 9000 meters and copy and paste into a different worksheet - it will take me weeks to do manually. How can I do this automatically?
View 1 Replies
View Related
Apr 23, 2013
In the below data set i have the data ranked by branch and center by value. I am trying to create a summary page that looks at the data set by branch center and rank and brings back the debtor code. The letters above headers represent the columns.
a b c d e f g
BR
CNTR
DEBTOR NAME
2012
Rank
[Code].....
View 1 Replies
View Related
Apr 25, 2013
I have a spreadsheet with some calculations and the part i am interested in is data in R4 : V58
In this R4:R58, S4:S58 and T4:T58 have some rows that have False because of IF calculations that put False in some rows of R and hence in S and Hence in T.
For example, R15="FALSE" so will S15 and T15 be. Col U is based on whether the corresponding row in R is False or a number. If a Rown in R is false, the row in U= 0 else the row in U is the same as in the same row of a different column - Col I
Col R, T and U are numbers. Col S is text
For example:"
R S T U
False False False 0
False False False 0
False False False 0
55 DEF 3 15
46 XYZ 2 67
False False False 0
False False False 0
23 GEF 4 43
I want a table in another worksheet that will sort through this using a formula and print
Col1 Col2 Col3 Col4
55 DEF 3 15
46 XYZ 2 67
23 GEF 4 43
If I can define things like where the table will be placed in the new worksheet etc, that will be a bonus..But It appears I cannot understand how to do this at all..
View 3 Replies
View Related
Jan 21, 2014
I have a large sheet (several thousand rows and growing) - see a small cut of the data. The columns following on from this have a list of people's names, hence why a lot of the data repeats (as a number of people attended each program).
Program
Clinic Title
Start Date
[Code].....
formula that will automatically give me that answer?
View 2 Replies
View Related
Oct 6, 2013
I have use excel 2007. In our sheet i have in cell "B3" product name i.e. "abc" , "xyz" etc. That is called sales of goods.
In cell "C3" i have number of quantity
If "B3" is "abc" then only calculate below :
1st option : If product name is "abc" then only calculate (which is cell "B3") & cell "c3" is >=15 & cell "c3" is<19 then message shown as "one mobile free"
2nd option : If product name is "abc" then only calculate (which is cell "B3") & cell "c3" is >=20 & cell "c3" is<39 then message shown as "two mobile free"
3rd option : If product name is "abc" then only calculate (which is cell "B3") & cell "c3" is >=40 then message shown as "three mobile free"
In view of the above which formula applicable.
View 4 Replies
View Related
Sep 27, 2013
I have data and after doing some data manipulation it looks like this
Job #
Part #
Lot #
Total
Grand Total
[Code]..
What I'm trying to do is making a summary sheet that has only the first record with unique values using the Job number, part number, and lot number as the criteria. The grand total in the first record is the total for all of that job,part,lot so i need to bring that value over to the sheet as well. I have been able to do this easily with the duplicate finder, but need a formula to automatically do this.
View 2 Replies
View Related
Apr 13, 2014
I am trying to formulate a commission form which looks up the name of the sales person and then displays a list of the deal names withing Q1.
View 1 Replies
View Related
Aug 7, 2012
I am trying to create a bar chart that changes color based on a percentage range. Any advice on how to do this? I am using Excel 2007, Windows XP, Sharepoint 2010.
View 1 Replies
View Related
Mar 28, 2014
I'm trying to populate a table based on two different criteria in a another table. On is based on a date and the other the number of a unit. I'm trying to get the data in sheets Week 1 and Week 2 into the format in sheet Example. Is there a way to create a series of functions, filters, etc that I could use to create a macro to do multiple sheets or at least a whole sheet at once? I've been trying to think through it with my limited knowledge of filters and functions
Here is the file with an example and I'm using Excel 2007: Example.xls
View 2 Replies
View Related
May 17, 2014
I'm using Excel 2007
I have the below data in sheet 1
In a separate column on sheet 2 i want to return the value of the data in ACC1 if the data in DEB/CRED is LC or SC and the value of the data in ACC2 if the data in DEB/CRED is SD
DEB/CRED
ACC1
ACC2
[code]....
View 7 Replies
View Related
Sep 17, 2012
I have 2 excel files, let's call them 1.xlsx and 2.xlsx (excel 2007)
File 1 is the file where I want data copied into and file 2 is the file I want to copy data from.
File 1 has certain text strings in every say 5th column in always row 2. I want to find those strings in file 2 and if the string is found, go 6 rows down, copy the cell, and paste it into file 1, 8 rows down the text string. this is the code I came up with, but it doesn't work
Code:
Option Explicit
Sub get_data_from_2()
Dim i As Long
Dim j As Long
Dim FinalColumn As Long
Dim RngFrom As Range
[Code] .......
the bolded part gives me an error.
View 3 Replies
View Related
Jan 11, 2012
how would you do the following in excel 2007:
"SUM numbers within a cell based on unique strings in another cell".
For example, how would i use formula to SUM the following numbers (and only the numbers for david and sam only once), 700+454+50+40+2+129+16
700david
700 david
454john
50buch
40daniel
2sam
2sam
129mike
16steve
View 9 Replies
View Related
Jun 1, 2011
What I am looking to do is this... (using Excel 2007)
I need to have more dependent lists based on the selection in Column A. (My column B is already set up and works perfectly using INDIRECT)
For example:
Column F would be a dependent list of colors based on the sales rep chosen in Column A
Column I would be a dependent list of managers based on the sales rep chosen in Column A
Column M would be a dependent list of part numbers based on the rep chosen in column A
without using VBA? (If I HAD to - okay, the other person maintaining the sheet would be lost....) I haven't been able to find anything close to what I want to do online - maybe I am just not using the correct terms.
View 4 Replies
View Related
Jan 10, 2014
I have a perfectly working pivot table and I would like to make some graphs based on the report filter. My report filter has 4 categories, with each more than 10 sublevels.
When I make one pivot graph/chart, this goes fine, the data is ok, and I am happy. But one I make a second, and thus adjust the report filter, the first graphs changes according to the filter. I dont want that to happen
Ultimately I would like a powerpoint presentation with multiple charts, based on one table, with different report filter filters. Updated ONLY on the values, not the filter.
View 4 Replies
View Related
Jun 12, 2014
I have two lists of values in separate columns with condition in adjacent cells, all on the same sheet
List 1 consists of numbers from 1 to 12, column to the right is either blank or Free
List 2 consists of numbers from 13 to 24, column to the right is either blank of Free
List 1 starts in A2, B2 is either blank or Free
List 2 starts in D2, E2 is either blank or Free
In column G, from G2 i want it to list all values from List 1 and List 2 where their status is Free
View 14 Replies
View Related
May 31, 2013
I'm using Excel 2007. I would prefer to stay away from the scripting side of the house if possible. This is basically a 3 day forcast weather chart. The top is the actual weather data, the bottom portion is a color coded reflection of how the weather affects various things.
This product is created in excel, but will be embedded into a powerpoint. It will be updated daily. Here is what I would like. I want the color chart at the bottom to update automatically based on the data I enter above. I have a grasp that I can update the color through conditional formatting, although im not exactly sure what that will look like with all of those cells.
I also figured out that I can insert the letters in those lower cells with something similar to " =IF(C6>90, "T", "") " which would put in a 'T' for Temperature when the temperature got above a certain degree.
I run into a problem when I have multiple factors affecting a single cell. For instance on the example in day 2 of my image. Personnel are affected by Temperate AND UV Index. How would I set up that cell to pull that information from both of those cells and display it accordingly? I would prefer the letters to stay separated by the comma, but I could live without that. The default cell color will be green, with the potential to be yellow or red. I left a few examples of possible situations on day 2 and 3.
View 3 Replies
View Related
May 23, 2012
I would like to hide rows for data that does not meet specific criteria. For example: If a user selects room number 101 from a drop down in B1, I want to filter data in range A3:F1000 to show me only room 101 rows (A column) where C column contains a value larger than zero OR D column contains a value larger than zero OR E column contains a value larger than zero. I do not want it to return rows where 101 may be in other columns beside A.
Sample:
A B C D E F
__________________________________
1 ROOM: 101
2
3
4 101 XX 1.2 0 0 P
16 101 YA 0 0 1.1 L
23 101 JJ 3.2 2.1 0 L
55 101 JJ 0 0 1 P
So, if a row contains 101 in column A and all three values in columns C, D, and E equal 0, then those rows will be hidden.
View 8 Replies
View Related
Apr 25, 2014
I would like to shade my cell yellow if the priority equals A or A-NEW and ACV=o.
In the data below, items 4 and 13 would be shaded yellow.
I am using Excel 2010.
Priority A, B, C
Consumer UPC
Item Description
ACV Weighted Distribution
[Code] .......
View 8 Replies
View Related
Apr 25, 2014
I am trying to write a macro in VBA excel 2010 that compares 2 sheets.
The macro should be something along the lines of if column 7 on sheet 1 = column 1 on sheet 2
AND
on that same row if column 6 on sheet 1 = column 3 on sheet 2
highlight green
** also on sheet1 there can be the same batch ID so if it is the same batch ID it needs to calculate the sum and look at that amount...
Sheet1:
settleid
min Tran Date
Payment Vehicle
total Deposit
total CF
total MRI
RMBATCHID
475-T
03/03/2014
Connect
$562.95
$19.95
$543.00
6G000001450835
[Code] .......
Results >
Sheet1:
settleid
min Tran Date
Payment Vehicle
total Deposit
total CF
total MRI
RMBATCHID
475-T
03/03/2014
Connect
$562.95
$19.95
$543.00
6G000001450835
[Code] ..........
View 9 Replies
View Related
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
View 3 Replies
View Related
Sep 29, 2011
I am using Excel 2007. I have a table with department names and the number of employees in each department. Currently, I'm using the formula :
=SUM(C12,C16,C20,C24,C28,C32,C36,C40,C44)
However, if another section is added to the database I have to update each formula. I'm looking for a way to get my totals no matter how many sections I add to the table.
View 4 Replies
View Related
Aug 10, 2012
Excel 2007 is my version. I'm trying to build an array from a dynamic range. I will know the column letter and lastrow.I think I could do this via a FOR and NEXT routine but there has to be a more efficient way. Here is my inefficient code idea:
Code:
Dim orgctarray(65000) As Variant 'because I don't know how long the array will need to be
Dim cnt as integer
Dim startrow as integer[code]....
So as you see, technically, I need to take sheet1.range("B3:B" & lastrow) and turn it into a vb array (I don't want to copy the data to some hidden worksheet and do the work there)I'd also like to remove duplicates and sort orgctarray alphabetically after I have completed building it.
View 5 Replies
View Related
Sep 26, 2013
My excel version is 2007 and i am on WIN7 64bit
I have a workbook with VBA which was working fine earlier. Lately i have been getting
"Runtime error 1004" Select method of button class failed.
I am adding buttons dynamically in a loop in a sheet and what surprise me is , i go to debug mode when error pops up and it points to below line
MySheet.Buttons.Add(ActiveCell.Left - 5, 25, 20).Select
but my code is running in a loop and i can see that atleast 20 buttons were added and it fails to select when it try to add this iteration. It is completely confusing me.
My entire below code is in a loop
maWrk.Cells(xIt1, yIt2).Select
mySheet.Buttons.Add(ActiveCell.Left - 5, 25, 20).Select
Selection.OnAction = "callMe"
[Code].....
I also notice that when it fails, on select method .. the button was actually added to the sheet with caption/name as "Button 65536" but then fails to select it. What makes the select to fail after adding the button? Could there be anything special with Button 65536?
Is there any better way to code the adding button and setting action and name for excel 2007?
note before entering the loop i am deleting all the shapes with myShape.Delete which name matches "btRun"
View 3 Replies
View Related
Dec 9, 2013
I am using Excel 2007
I have 50 000+ rows that covers fiscal years from 2006 to 2014 and I want to calculate how many unique clients are there per asset type per fiscal year. In addition, the results table needs to adjust to filters i would use on the raw data..
As:
2007
2008
2008
Commodity
45
FX
Other
other
Here is a sample file: SampleData.xls
View 8 Replies
View Related
Nov 18, 2011
I would like to get the count of unique values in my 2nd column using my values in 1st column as the criteria. Below is my example of my data set.
EFGH5DeptHC
6A304794A17B86122B38B86179C39B90050D1310C82705
11C94955
12C308165
[Code] .........
Excel 2007
I would like to see the count on column H (highlighted in yellow).
View 1 Replies
View Related
