I need a formula that compares 2 databases and returns a count if both values or within the specific range.
Column A is titles Column B is values The first database is simple the range is
How does one count the number of x and y values (occurring in same row) in this array?
Time1 x a b c d
Time2 b c x y a
Time3 x a y d b
Time4 a b c d e
Time5 y d a x d
Time6 c y a d b
Note all of the variables above are positive numbers.
My specification is:
Calculate how many people within the UK live in a given area. Within that area what are the different age distributions (0-5 years old, 6-10 years, etc.) Then display the data twice - 1/ show each age range and how many people in the given area fall into each one. 2/ As before only report on people in the given area, and display age range, but also display them by ID number (col E in example file).
Can someone help me build the pivot tables that performs my analysis? (I got it working with countifs but it was an inelegant longwinded kludge.)
I've attached an example (.xls zipped up) which shows the data on one tab and my failed pivot table attempt on another.
I have a column of equipment average utilization percentages. This column is a dynamic name range called "Avg_Util".
Values in this range were created by a calculation that results in a value of "", for equipment which have no utilization as yet, to those with a utilization of anywhere between 16% to 160% for those with actual hours in service.
I need to count how many have a utilization of <=50% and those that have a utilization of >=150%.
COUNTIF didn't work because it apparently counts the "" values (as zeros?)? I didn't fare much better with SUMPRODUCT either, but that could just as easily be the fact that I'm still new to that function. I get the feeling I'm overlooking something regarding working with percentages here to be having so much difficulty with something that just doesn't seem like it ought to be that hard.
I want to count the number of groups of 3 adjacent cells in a horizontal range (C5:EV5) in which any cell in the group of 3 has a value in it (as opposed to all the cells being blank or containing "0"). For example, if any or all of the cells in C5:E5 have a value in it, it would count as 1, and if any of the cells in the group F5:H5 have a value, it would also count as 1, and so on. Is there a way to use COUNTIF for this?
View 5 Replies View RelatedAttached excel sheet below. Suggest a formula to get the count of items that fall in a specific data range ?
Count of items that specify a range criteria.xls
I am trying to create a Pivot Table that filters on a range of data.
I have a sheet with lots of data on it, and separate sheet where I have created my Pivot Table and my list I wish to use as the filter values.
I need to extract 630 specific Departments out of thousands and report the movements against those depts. The 630 will change as time goes on and I want to just have a range where I can update the values as and when changes occur. I initially tried using Filter on Department but realized this was going to be a very long winded process.
I want to accomplish something like this but slightly different:
[url]
I have the same issue but a bit more complex. In my case I have an additional amount of columns (let's say dimensions). So for the example given (sales per month) I would add two columns for 4 different regions and 5 machine types (just examples).
The output (that would serve as pivot tabel input) would then have to be a 4 x 5 x 12 x 4 = 960 row database table.
I have tried to accomplish it by customizing the given code but that provedto be beyond my powers ;o)
and some 3rd party software [url]and [url] but non of them is doing what I'm looking for.
The code I'm looking for ideally would count the number of columns and unique records per column and construct the database table from that. So I am looking for flexibility in the number of dimensions also...
I have a table which is refreshed from a view on our SQL server. I use a part of this data further on and would like to automate the following: I would like to have a range set which I can use to loop through data. The range should hold the filtered data of table "PremiumTable", the columns "Grouping2", "Price", "Grower"
I have found a couple of posts giving the ability to create a range of visible cells and specific columns, but I wasn't able to combine this yet.
I am using a pivot table in excel 2010. 15 columns (fixed) and plenty of dive downs for rows.
I am trying to 'hide' all of the rows containing values less than 10. If it is easier we can start with hiding values of zero and go from there.
It is key to hide the rows as within the dive downs the rows within the pivot are to many to sort through. If there is a way to do this using the grand total columns for each row that would work as well. Just to be clear I need to hide the entire row not just report the empty cells as zeros. If any further detail is needed feel free to ask.
For example: If value of column O "Grand Total" = 0 'hide rows with value 0' ELSE 'display rows with value greater than 0'
I am using MS Excel 2007 and I am having to prepare a database that takes data from other sheets and places them on sheet one with a specific sequence. I'll explain, my data is in the following
form:
Sheet2 A1 to A1617 (each a different value)
Sheet3 A1 to A1617 (each a different value)
Sheet4 A1 to A1617 (each a different value)
....
....
....
....
...
...
Sheet13 A1 to A1617 (each a different value)
The result I require is so that on sheet 1 I have a database showing me this
A1: Sheet2 A1
A2: Sheet3 A1
A3: Sheet4 A1
...
...
...
...
A12: Sheet13 A1
A13: Sheet2 A2
A14: Sheet3 A2
A15: Sheet4 A2
...
...
...
all the way to
A19404: Sheet13 A1617
Is there a way I could get this done automatically? I know the basics of Excel and this has been a dilemma lately. I have several databases to work in this fashion and I do believe that there must be a way to solve this in excel so that it fills automatically.
I'm opening a non-excel file in excel and it formats it into a series of rows/columns. The problem I have is that it doesn't consolidate all related data in the same row, for each new column of data it breaks into a separate row. I'd like to easily roll it all up into a single row.
I've attached a sample file to illustrate. The "Original" sheet is what I'm starting with and the "Final" sheet is what I hope to condense the data into.
I have a "form" type spread sheet that I have created where the user can enter different information (like name, company, addresses etc.). I have figured out how to create a drop down list. But what I would like to do is create on a seperate sheet a "data base" of information which will be "copied" into the appropriate cells on the first sheet depending on what the user selects from the drop down list.
The drop down list will list company names, which are stored on the second sheet. However, each company will have other data such as the company address (which will be entered in seperate cells with information like, box, street, town, province, country, code).
Now this is the clincher... The user must be able to add to the data list on sheet two and any new entry must automatically appear on the drop down list on sheet 1, and when selected it's "data" must be transferred to the appropriate cells on sheet one from sheet 2.
I have a pretty vast database which I populated through VBA from a certain number of worksheets. I now need to create a table which shows min, max and average for each location given x, y or z condition. So far I only approached it by creating a secondary table with each range address and then using INDIRECT, but this would require extra work on the database (insert a function to sort out locations alphabetically and within each Range.Location sort out by conditions (note. A, B and C are not in alphabetic order)) and is also very ugly.
View 3 Replies View RelatedI have a spreadsheet with 15,000 rows x 15 more columns, i would like to find out the number of delivery for each single order.
The database similar as below :-
Order No. Delivery Date Product
12345678 01.01.2009 TV
12345678 01.01.2009 DVD PLAYER
12344568 02.01.2009 TV
14567892 03.01.2009 RADIO
14567892 04.01.2009 RADIO
14567892 31.12.2008 BLUE RAY
15465879 06.01.2009 MP3
1)what excel function should I use in order to find out the number of delivery since there are so many duplicate records in the database.
2) Shall I use "Pivot table" ?
Is there any lock/security option in excel sheets, suppose we make some table/list and after entering one data when the cursor goes to second raw, the previous raw data automatically lock. the user cannot edit or delete that record.
View 10 Replies View RelatedI've posted an example workbook that has sheet1 as how the data comes and sheet2 as what I need it to look like (through some kind of automatic process). I really don't have a clue as to how to get this done, and I'm guessing some VBA is going to be required.
View 3 Replies View RelatedI am using Access as a backend and Excel as a frontend. I want to count total number of records for todays where Time<13:01
Modify the following code accordingly?
[Code] .....
I have 3 Databases for three different departments, namely Finance, Services, IT. These 3 databases are housed in the same workbook - Sheet 1(named Finance); Sheet2 (named Services) and Sheet3(named IT).
They each have the identical columns and column headings, but I have to maintain them for each of the departments.
I created an Excel UserForm to enable me to input data onto the first database (i.e. Finance). In the VBA editor, lefthand side, it has the name 'frmFinance', and all the codes are pasted on the righthand window of the 'frmFinance' sheet.
Is there anything I can do to be able to use the same UserForm to enter data onto the other two databases without having to create a new UserForm for each one of them. As indicated above, the three databases have identical columns and headings, and the data entered onto each of them are identical. The only difference is that they are for three different departments and I have to maintain each one of them - albeit in one Workbook.
I have a connection to an SQL database using microsoft query.
The query definition includes: ORDER BY AAStocktrans.Code But I need a two level sort along the lines of:
ORDER BY AAStocktrans.Code THEN ORDER BY AAStocktrans.Date but obviously that is not correct - cannot use THEN. what I do use to make this work?
I have two huge database and need to test if Employee name and ID in database A matches to name and IDin databse B. What formula could I use here?
Employee ID Database A
Employee Name Database A
Employee ID Database B
Employee Name Database B
1
JONES JR,STEPHEN W
1
NULL
[Code] ...........
I would like to change the greater than number to the value in cell 'I11' in sheet 'Linear Programming Data' and the less than number to the value in cell 'I12' in sheet 'Linear Programming Data'. The code I am using was done by recording a macro since I don't know how to code in vba.
Code:
Sub Results2()
' Results2 Macro
' copy table filter power by greater than and less than
Sheets("Finalizing Results 2").Select
Cells.Select
[Code] .......
I want to build a pivot table on database that is not centralize in one place.
every month the new data is insert in a new sheet (of course with the
same fields names)
But the wizard, refuse to build a pivot table from data that is not
concentrated in one place.
(My intention is not the option: "Multiple Consolidation Ranges".)
need macros :
Copy From Sheets ("PO")
Copy D7 & H7
Copy C11 : H25 (Only Active Cells)
Paste To Sheets ("Sheet1")
D7 & H7 = Column A & B
C11 : H25 = Column C, D, E, G, H
database.xlsm
Call center stats: I have a SQL database which is LIVE. I would like to create a live report on excel from this database. (update my excel table automatically)
View 6 Replies View RelatedI have an excel spreadsheet in which I must sort the data by program and then activity code. From that I need to know the number of consumers by activity code and their total units. I must then graph this by program. I have been debating on whether to use an excel pivot table, break the data in to multiple spreasheets to then graph it or shoud I upload the file into access.
View 9 Replies View RelatedI've created several access databases to which I've connected my excel files. However I've either deleted/moved these databases but my connections are still showing in Data -> Existing Connections tabs.
I've tried going to C:Program FilesMicrosoft OfficeOffice14QUERIES but no such queries exist.
Is there any way to clear my list ?
I have a dataset from Oracle that includes a study patient ID, study visit that a UC sample was collected at, date the sample was collected, and sample type. I also have a dataset from Quest, where the UC samples are sent to for testing, that contains similar information.
I need to reconcile this data, making sure that patient IDs, study visits and collection dates all match up with out using just vlookups and having to check the reverse of each (vlookup Quest into RDC, then vlookup RDC into Quest) and I've been wracking my brain trying to figure out VBA code to get my data from Oracle (orange header) to merge with Quest (blue header) and merge on Subject ID and Visit Name only.
In exhibit C, this is what ideally I would like to see as a result, data points from both are reported, but if Oracle subject id = Quest subject ID and Oracle Visit Name = Quest Visit Name, they are reported in the same row. I can then just run a simple formula to compare the collection dates and also identify discrepancies between visits (example: exhibit C, rows 10 and 11, these two UC samples look like they should reconcile because the collection date is actually the same for both, however either the study site or Quest is reporting the wrong study visit name).
I would like not to have to update the source data for the pivot table in my database. I've read that you can solve this by using a Dynamic Named Range and using that as the source for the pivot table. That way the pivot table will expand as new data is added to the database. The formula I used to create the DNR is:
View 7 Replies View Relatedi have a excel file which has a formatted pivot table which displays customers by country in row A 2 down to 36 with approx 36 customers in the values field i have volumes by alarms and tickets along the column labels it shows the previous 7 days with date and then the alarms and tickets in each row. My problem is i am trying to find the code or location where i can change the only bring back 7 days data to another value. i have searched all connection properties and definitions. I know there is a value somewhere that allows you to change this number to say from 7 days to 30 days. If you click on the column labels for date it shows the dates back 3+ years but it still if i tick more than 7 days only show 7 in the table.I have image if needed
View 1 Replies View Related