I have two matrices:
table 1: A B
and
table 2: D E
I need to summarize A where B could be found in D, when E is "alpha".
In SQL this will be like:
SELECT * from table_1 WHERE A=1 AND
(SELECT * FROM table_2 WHERE D=B and E="alpha") IN NOT NULL
I think this requires SUMPRODUCT, but can't work it out.
The attached spreadsheet should explain clearly.
Looking to nest or combine the following 2 functions because I'm looking up the same cell content, except within 2 different tables.
=IF(B12="","",SUMPRODUCT(--($B$12:$B$17=X18),--($S$12:$S$17))) and =IF(B22="","",SUMPRODUCT(--($B$22:$B$32=X18),--($S$22:$S$32)))
Right now, these functions works well from a single table but need to combine them. Other function examples are welecomed too.
All I am doing is counting text values in a table.
The table has 3 main columns(which are relevant to this thread anyway).
Shift - Area - Status
The example I have attached shows examples of DCOUNTA, SUMPRODUCT and a Pivot Table.
I have read many threads stating that the best one to use is Pivot Table followed by DCOUNTA followed by SUMPRODUCT.
The most effective for me seems to be SUMPRODUCT (although this does slow excel down dramatically when you use a lot of these formulas). As do Array Formulas
The Pivot Table does not update on its own, therefore constantly needs to be refreshed. (I could use code to do this)
The DCOUNTA seems to be the least effective at doing what I want (unless I am doing something wrong)
In the attached example can the DCOUNTA be used more efficiently as I don't like the fact that I am duplicating rows to apply the criteria for a different shift. e.g
Area 1 - Late Shift - Banned
Area 1 - Early Shift - Banned
I want my table to be as follows (as the SUMPRODUCT shows)
AREA - Early Shift - Late Shift - Night Shift - Area Total
Area 1
Area 2
Area 3
Area 4
Area 5
Shift Total
In Sheet1 I have the following horizontal fields:
Cust#
Order#
Order Qty#
Item#
In sheet2 I have the following horizontal fields:
Cust#
CustAddress
CustCity
CustState
CustZip
How can I automatically add the address fields from sheet2 to the appropriate line in sheet one with the matching cust#?
I have to combine the data from different sheets in a file. I'm attaching a mini version of the file for demonstration. As you can see, there are 6 sheets in the file-a sheet for a different variable. In each sheet, there are around 150 countries in columns and monthly data for that particular variable in rows. I have only put data for few months, but in actual file it has many rows. What I need is data for around 30 countries where in each country I have monthly data for these 6 variables under one roof. I have put one example in last sheet -Sheet1- for one country to demonstrate how i need the data arranged.
View 8 Replies View RelatedSome people throughout our company were having problems using the excel form I created which contains a popup calender. Even with the mscal.ocx installed some people were having issues.
I found a popup calender that can be integrated into the form, and doesn't rely on the mscal.ocx. I put this into the form (that MANY people here helped create), and now all of the people that use the form no longer have any issues.
I have two spreadsheets, on that gets generated everyday which is a "fuel transaction report" and another with "captured fuel"
I was wondering if its possible to somehow intergrate it so that it will automatically show me if the "fuel transactions" have entries that does not reflect on the "captured fuel"
This is the fuel transactions report
Unitrans Fuel Transactions Repo BCDEFGHIJKLMNOPRS21REGISTRATION : BH83MGGP VEHICLE DESCRIPTION : HINO 500 1626 LWB F/C C/C 2226/11/2011NESERHOF MOTORSMISMATCH MISMATCH11980DIESEL100.021 009.152324/11/2011NESERHOF
[Code].....
See, I need to cross check the two reports to see if there is any missing fuel from the statement report from the supplier to what is getting captured by my people.
it seems to be possible to integrate to some extent with the Outlook Tasks function....
Is it possible however to 'update' a given task, and have it linked somehow to my workbook project? (Maybe using some sort of reference?)
Using Excel 2010. I'm writing a macro that sets up a workbook to be used for estimating at the beginning of a project. In the code I need to create multiple tables (formerly known as "lists") in the workbook. Then later in the code I need to refer back to those newly created tables. Currently, the code that creates the table is part of a loop that creates the table on many different worksheets. The problem of course, is that I have to name the Table, and then it won't create a table of the same name on the next sheet. Then, later in the code, I need to make adjustments to the table that was just created before looping to the next sheet.
Is there a way to create a table without giving it a constant name? Or by giving it a name that builds off of other info in the sheet? For example, I would be good with the naming the table after the sheet name: "Sheet1_Table" or such.
Code:
Sub Auto_Open()
'
Dim sht As Worksheet
If Range("A1") = 1 Then
[Code].....
I am trying to merge two different databases in the next week. The problem I am running into is the name from 1 database isn't always the same on the other database.
I want to merge Database 1 with Database 2 and create a relationship with them both by using the database 2 internal #. As you can see in my table database 1 has similar names to database 2 but they are not exact. I want to match these names and have the formula on match tell me what the database 2 internal # is. I have also uploaded a sample excel doc with a note in B2.
database1
Herb Chambers Ford of Braintree
Herb Chambers Honda of Seekonk
Dimmitt St. Petersburg
Database 2 matched internal #
Database 2
Herb Chambers Ford of Braintree (Dave Dinger Ford)
Herb Chambers Honda Of Seekonk (7-FI)
Dew Cadillac (Dimmitt of St. Petersburg)
Database 2 internal #
15360
15301
25413
I have one .xls file that contains an inventory list of all the products that I want.
Unfortunately this .xls file does not contain the prices for these items. I have another .xls file that contains SKU numbers and the prices related to those SKU numbers.
Is there a way to run every single SKU in the first file against the second file and, when a match is found, take the entry in the price column of the second file and place it in the first file so that my first .xls file contains all my products with prices?
I included sample data.
I have:
- Table 1 has ~1,600 records (name of game, meta score, user score)
- Table 2 has ~3,000 records (name of game, genre, publisher)
I want:
- Table X has ~1,600 records (name of game, meta score, user score, genre, publisher)
Basically, I want to take the genre and publish columns from Table 2, and add it to Table 1. However, I want to ignore the extra ~1,400 games that Table 2 has.
I'm nearly a complete noob when it comes to excel. Noob to the point where I don't even know what this thing is called, so I don't even know how to search for it properly! I know something can be done since there is a common column between the two (name of game).
Edit: Also, I think it'd be easier if I were able to attach my excel spreadsheets but I don't even know how to do that
I have a table with dates that have bonuses associated with them, some times the bonues change, so i have another table with the date the bonus is effective and then the range of bonuses based on the sales of the month....
View 12 Replies View Relatedi am trying to correspond 3 columns like:
Code:
________________________________________________________________________
points from / points to / note defined / points pupil got / his note
------------------------------------------------------------------------------------------------------------------------------
0 10 4 33 #
11 20 3 16 #
21 30 2 25 #
31 40 1 07 #
-------------------------------------------------------------------------------------------------------------------------------
Column 5 (his note) must be calculated looking at value on column 4 (points pupil got) and finding it on the intervals given by columns 1 and 2 (points from/to), and finally ascribing the value from column 3 (note defined).
I have a worksheet with data in named tables on several sheets. Each of these tables has a column called "filter" (this is not always the same column number between sheets). The values in this column are either 0 or 1, depending on if that particular row is relevant under the book's current selections.
I'm trying to figure out the code that will take a table and filter it to show only the rows which have a 1 under the "filter" column.
Need to sort data from one table to other tables in diferent worksheets so I can easy print report.
for example: (i got about 40 markets to add in table, this 5 is just for e.g.)
ABCDEFGHIJ1DATEMARKETEMPTY B.FULL B.E.B. - F.B.NETOPRICEM1M2M3215.6.2014market 11500145050145004260900062000011000315.6.2014market 21600160001600042672000670000-2000415.6.2014market
[Code].....
I have validation tables in a few cells. I've unlocked them and password protected the sheet so that they couldn't be changed other than what is in the list.
What's happening is that the list is there, but the cell is also allowing an entry that is not in the drop down.
How can I protect the cell to only allow the validation list?
I am after some code that will hide tables based on whether they contain certain values.
If we take the following example: ....
i entered an item (ie, Aircard5740, cost = $99.99). This specific item must be compared from the 1st table (type of phone) with 7 different columns where does it fall the same. Once found, another item will be entered, (ie, America's Choice 450, cost = $59.99) and again will be compared to the 2nd table (commission) with 7 different columns, (while still based from the 1st item), and from this 2nd table, there's the commission amount be taken. what formula can i type to get the commission from the 2nd table?
View 2 Replies View RelatedI have 2 tables in excel that i need to merge into one, Table 1 has 10 colmns and Table 2 has 7. Both tables have as column 1 a heading of Material, this is the column i need to compare as table 1 does not contain a material description field, but table 2 does, the bold headings are the key ones, but other column information is required. I have attached a sample ss to show what i am trying to do.
Table 1 Col Headings
Material
MTyp
MS
R
Clt
L/O
Created on
Created by
how to create tables from XML with CSS.Below are the codes....
<?xml version="1.0"?>
<?xml-stylesheet type="text/css" href="feedrecipe.css"?>
<recipe_summary>
< percentages>Percentages</percentages>
<percentages>
<eggshells_heading>Eggshells</eggshells_heading>
<eggshells>5%</eggshells>
<wheat_heading>Wheat</wheat_heading>
<wheat>60%</wheat>
<sunflower_heading>Sunflower Seeds</sunflower_heading>
<sunflower>5%</sunflower>
<meat_heading>Meat and Bone Meal</meat_heading>
<meat>10%</meat>..................................
I am currently constructing a spreadsheet with an engineering application. There are standard size shafts that bearings must fit on. Once the design is run, i obtain a number which is usually inbetween the two standard sizes. I need the spreadsheet to select the larger one. for example, 184 must be looked up from
160
180
200
220
I need it to select 200, however and the moment using lookup function i am getting 180.
i'm building a spreadsheet where there is a list where users will input work shifts (those shift can be, for example, "M", "N", "T", "Me", "Ne", "Te"). i use two different columns to retrieve (using vlookup) values for M, N, T and Me, Ne, Te shifts. those values are in two different tables (one table for M, N, T shifts and another one for Me, Ne, Te)
for the M, N, T shifts column i use:
VLOOKUP(A1;$values.$A$1:$D$3;IF(WEEKDAY(G3)=1;4;IF(WEEKDAY(G3)=7;3;2)))
for the Me, Ne, Te shifts column i have:
IF(ISNA(VLOOKUP(A1;$values.$A$67:$D$69;IF(WEEKDAY(G3)=1;4;IF(WEEKDAY(G3)=7;3;2));0));0;VLOOKUP(A1;$values.$A$67:$D$69;IF(WEEKDAY(G3)=1;4;IF(WEEKDAY(G3)=7;3;2));0))
this seems to works fine, but only for one shift per day. the problem is that workers may work more than one shift per day. is there a simple way of, in one column, vlooking up the values for every M, N, T shift, every day ignoring any Me, Ne, Te and the opposite in the other column?
Say I have 10 tables and want to copy them into one single table. Easy right? Just copy / paste.
The restraint with copy paste is, if I make an adjustment to one of the 10 tables, it will not be reflected in the master table. I would have to recopy the table every time I make a change.
I was wondering if this there was such a function that says: "Once done copying with X table, start copying Y table, and so forth."
I have a spreadsheet with two tables in it. Table 1 contains some data consisting of two columns both populated with data (where 1 = a, 2 = b, 3 = c and so on) and table 2 consists of two columns one column contains data and initially (numbers) column two is blank. Now I need a VLOOKUP that will look at the second column (red letters in the example) in table one, and where the same value (number) appears in table 2 populate the corresponding letter.
I do not wish to use filters as the table is extremely large. I have attached an example
I copied a worksheet with a pivot table as a new worksheet. I wanted to group each pivot table differently on each worksheet, but the changes I make on one worksheet is reflected on the other. Is there a way to break the "link" between these tables?
View 2 Replies View RelatedHow do you reference a table on another sheet so that when you sort that original table it doesn't change the reference you made on that other sheet.
i know how to do this when i am referencing a cell. for instance if i am referencing a cell on sheet 5 and that comes out to something like =Sheet5!B2, i know that when i sort the data differently in sheet 5, that reference will know to change to =Sheet5!B18 or where ever that original reference ends up in this new sorting. but if I do =Table5[2] or something, and i sort that table differently, the reference will change to the new data in that cell and will not follow old reference through the new sort.
Sync these two tables? the firs one is connected to a master sheet so when that master sheet is updated by entereing data, inserting rows or deleting them so is the table, the second table was created to hardcode data manually so it doesn't feed from the master. My problem is that if a row is inserted in the first table, the second table doesn't update that change. Is there any way to connect both?
View 2 Replies View RelatedI got a excel file which i use to add invoices on. But these invoices are numbered and in a book.
1 book has 50 pages, i have to write the amount of $$ on it. But i made this file to enter the amount of money on the invoice. And the sheet automatically gives me the total amount of money which was earned when the book is full.
Now i was wondering if its possible to let excel somehow add 50 new lines with the same formulas used in the first 50 lines. So once line 50 has info enter, excel will add 50 new ones with the correct info(book and page numbers).
Can excel do this with some sort of macro?