Excel 2010 :: Comparison Of Contents Of Two Tables
Oct 31, 2012
I have Table1 in sheet1 and Table2 in sheet2 of same Excel Workbook. The contents ( Data in columns B, C, D, E and F) of Sheet1 Assets are to be verified with respect to the contents ( Data in columns B, C, D, E and F) of the same Asset in Sheet2. If the all contents (of all columns) are matching, it should return Boolean value (True) in column G of the sheet1 or else Boolean value (False).
I am using Excel 2010 version and I do not have Power Pivot menu.
Table1:
Excel 2010
A
B
C
D
E
F
1
Asset
Prevention
Control
Detection
Mitigation
Escape, Evacuation & Rescue
[Code] .........
Table2:
Excel 2010
A
B
C
D
E
F
1
Asset
Prevention
Control
Detection
Mitigation
Escape, Evacuation & Rescue
2
U1101JBM
Y
Y
Y
[Code] .....
View 4 Replies
ADVERTISEMENT
Jul 1, 2013
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].....
View 2 Replies
View Related
Jun 20, 2013
I have two columns (A and B) with the same data. Column A is missing a value that is in column B.
Column A has 11330 rows whereas Column B has 11331 rows.
I am trying to do a comparison on the two columns to determine which value is missing from Column A.
I am using Excel 2010.
View 3 Replies
View Related
Sep 28, 2011
I have to generate a report using a pivot table, that tells me what work was done on time and what was late. Entering a formula outside the pivot table sees the scheduled start date drop downs as "blank" and says they are late when they are not. I cannot seem to use the value fields when I try to create a calculation field within the table. I use 2010 at work, 2007 at home.
sep 11-17
FGHIJK10Count of WO No.
=IF(G12>=H12, I12," ")=IF(G12
View 3 Replies
View Related
Jun 21, 2014
Have an Excel 2010 spreadsheet with 902 email addresses in one column, and 927 email addresses in other column, sorted in alphabetical order.
Overview: Organization database has 902 Members (some have 2 email addresses subscribed to listserv) and Organizational Listserv which has all 927 (Difference is numbers are some members have 2 email addresses subscribed) BUT 902 of emails should be the same
Objective to compare Listserv Members 927 with Database Members 902. I am trying to isolate the email addresses which do NOT exist in Listserv Column, or who do not exist in Database Column for Audit purposes. All Members from the Database column should be subscribed or entered into the Listserv column.
If it's a perfect world the 902 email addresses will all be exactly the same as 902 out of the 927 Listserv email addresses.
Trying to highlight or sort and put identical email addresses on same line or using Conditionally formatting? Highlight Cell Rules/Duplicate cell rules Even when same email addresses are on the same row conditional formatting does not recognize them.
View 4 Replies
View Related
Apr 11, 2013
Is there any way to use slicers on a table in 2010? I've seen posts that it is available in 2013, but can't find find a definitive answer on if there is a way to get it to work in 2010.
View 5 Replies
View Related
Jan 27, 2014
Code:
tblCluster
Jan-14 Feb-14 Mar-14
Salary 10,000 20,000 30,000
Jan-14
Feb-14
Salary 30,000
I have a table above (in red), as an example, my actual table goes out 60 months.
Where the Green Value is, I need a formula that will reference the two months above it (They could be any of the 60 months), that will then sum the salary from the table between the selected two months (including these months)
I have tried,
Code:
=SUMIFS(C6:BJ6,
tblCluster[[#Headers],[Jan-14]:[Dec-18]],">="&'Cluster Analysis'!$I$51,
tblCluster[[#Headers],[Jan-14]:[Dec-18]],"
View 2 Replies
View Related
Aug 7, 2013
I am using Excel 2010. I am digging through a workbook with 80-some worksheets. There is one worksheet with 11 data tables. When the workbook refreshes, there is a note at the bottom saying something about 21 data tables. I am not sure where the other 10 are. In an online post, someone said that the Name Manager should show them. But in Name Manager, when I filter on Tables, the list goes blank, and all the icons are the same for the other named ranges. Is there another way to find the data tables?
View 9 Replies
View Related
Oct 24, 2013
I have a pivot table below: Excel 2010. All of the information within it is text, not numbers, as illustrated below as well.
Below is what I have now....
Column A, Row 1 = Red
Column B, Row 1 = Blue
Column B, Row 2 = Yellow
Column C, Row 1 = Green
Column C, Row 2 = Green
What I want it to look like is below, removing the second word "green" from column C.
Column A, Row 1 = Red
Column B, Row 1 = Blue
Column B, Row 2 = Yellow
Column C, Row 1 = Green
Is there a way to do this and do it automatically? If I was doing this in a regular excel sheet, I would have a merged cell in column C, rows 1 & 2.
View 2 Replies
View Related
Jan 4, 2014
I have a table that contains all my data and would like to filter it by company into their own tables that will stay up to date with the main table and then hopefully somehow calculate their investment returns (future stage). I thought Microsoft Query would work but I came across a problem see my other post MS Query returning data to excel some columns I cant sum looks like text
Excel 2010
View 1 Replies
View Related
Sep 1, 2013
I have 2 Data tables in 2 sheets of the same workbook. I want to compare my column A of table 2 with column A of table 1 and delete any rows of table 2 where (column A of table 2 has a value which is not in the column A of table 1)
In Excel I used the Vlookup function and deleted any rows which had Error in result of the formula. May I know how to execute this in VB
View 3 Replies
View Related
Sep 13, 2013
I'm trying to create some vba code that will go into other .xlsx documents, pull all cells with a value in the first 30 columns and then return them as a table. Basically each work crew has a .xlsx spreadsheet containing their schedule and I am trying to bring them all together into one nice little package. If I go to import from external sources then click XML data, navigate to the folder they are in and them click show all files and pick my spreadsheet it imports nicely. Then I go to the next blank cell in column "A" and repeat for the 4 crews. Now I can filter by each crew but all 4 are visible on a single page as a table. I recorded a macro that shows everything I did, but it is not dynamic (the .xlsx source files name changes based on the month) I'm running XP sp3 with Excel 2010
Here is a copy of what I have
Code:
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
"OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=C:Documents and SettingsUSERNAMEDesktopSCHEDULE STUF" _
, _
"FAcrewCurrent.xlsx;Mode=Share Deny Write;Extended Properties=""HDR=YES;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path=""""" _
, _
";Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=37;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;J" _
, _
"et OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt " _
[code].....
Is there a better or simpler way to do this? The tables don't need to be forever linked to their previous spreadsheets so if copy/pasting is a better way than I am all for that as well.
View 1 Replies
View Related
Jun 5, 2014
I am trying to take multiple tables from a Word document and import them into an Excel worksheet. Currently I have found two versions that when combined, could yield what I am looking for. The first one imports the table's data from Word, but does not maintain formatting of the table (font, colors, rows/columns etc.):
The next code maintains formatting, but only imports/pastes one table:
[Code] .........
For the second one, I do not like the fact that it is calling a specific Workbook to paste into. If I could somehow maintain the ability to import/past multiple tables while keeping formatting that would be perfect. An extra bonus would be to import each table within the Word document into individual Worksheets in Excel. I am also using Office 2010.
References: [URL] .........
VBA - How to preserve source formatting while copying data from word table to excel sheet using VB macro? - Stack Overflow
View 14 Replies
View Related
Jun 27, 2012
I've been trying to create pivot table in excel 2010. Unfortunately I've been getting this message "Data source reference is not valid"?
View 2 Replies
View Related
Nov 13, 2013
SUMIFS with less than & more than
I found this thread which works for normal ranges
I want to Sum for Values Greater than a specific date in my formula.
This first one works for a specific date (Relevant reference in red), so say 01/01/2014
Code:
=SUMIFS(Table2[[#All],[Basic]],
Table2[[#All],[Cluster]],Table3[[#Headers],[Company]],
Table2[[#All],[Start Date]],Table3[#Headers],
Table2[[#All],[Perm / Temp / Prof / ETA]],[@Company])
Code:
To Sum for ranges >= 01/01/2014, I have tried this below based on the thread above but Excel does not accept this.
=SUMIFS(Table2[[#All],[Basic]],
Table2[[#All],[Cluster]],Table3[[#Headers],[Company]],
">="Table2[[#All],[Start Date]],Table3[#Headers],
Table2[[#All],[Perm / Temp / Prof / ETA]],[@Company])
View 4 Replies
View Related
Jan 10, 2014
I am trying to make the contents of a cell into a chart title.I know I can do it like this...
ActiveChart.ChartTitle.Select
Selection.Caption = "=tab_name!R2C1"
However, I am trying to use the same macro on several different tabs in the same EXCEL worksheet. Obviously, I can't have the tab name in the macro. But, I don't get a title at all when I try to remove it like this...
ActiveChart.ChartTitle.Select
Selection.Caption = "=R2C1"
or
ActiveChart.ChartTitle.Select
Selection.Caption = "=A2"
Is this possible? I am (kinda new at) using EXCEL2010.
View 3 Replies
View Related
Jun 4, 2013
I am trying to clear the contents of a range of contiguous cells (containing formula) in Excel 2010 64bit. But it is taking a LONG time (read >30min to clear a range 288 x 100). Originally was doing it in VBA but it was taking too long so I started stepping through the macro and identified that the slow down was occurring on a particular sheet only. So the problem is in Excel....not VBA because it is still very slow when deleting the same range in Excel without using VBA.
I've tried all the usual suspects including: Application.EnableEvents = False (run from the VBA Immediate window when using Excel without VBA), Setting to Example of slow clear contents5.xlsxExample of slow clear contents5.xlsxManual Calculation, Turning off screen updating, deleting all the conditional formatting on the sheet, removing any data validation from the sheet (yes all these at the same time). There are no links to external workbooks. I've even tried in VBA changing from .ClearContents to .Value = vbNullString. All to NO AVAIL! By way of background the file is about 72Mb #.xlsm.
If I try to clear contents of larger ranges on other sheets in the same workbook it is instantaneous. If I copy the problem worksheet off to a new workbook, I can delete the problem range almost instantly. Why deleting a range of cells should take so long! Again to clarify, I am using the terms "delete" and "clear contents" interchangeably, but they both mean "clear contents" (not delete and move up cells). P.S I've also looked into the reported conflict between Excel and Google Desktop Office Add-in (Office Button->Excel Options->Addins->Com Addins and deselect Google Desktop Office Addin) but I didn't have Google Desktop Office Add-in so it isn't that.
View 6 Replies
View Related
Jul 30, 2013
I'm using excel 2010 and windows XP with a moderate amount of experience tinkering with macro programming. I know what I need is very doable but I can't get my head around what the code would look like. I must not be wording my searches correctly because most of what I'm getting for results are iterative programs based on a cells value which isn't what I need.
I'm trying to build a macro that will check a cell (C3) and based on the contents of that cell copy a column (I) to one of 12 different columns (K:V). So if the value in C3 is 1 it should copy I to K, if the value is 2 it should copy I to L, and so on.
View 2 Replies
View Related
Sep 26, 2013
I have a range of cells each containing different values. Each of these different cells has a related date contained in the row underneath.
I want to be able to search the range of cells and find it in the contents of a cell and return the corresponding date from the row below.
Excel 2010
A
B
C
D
E
F
G
[Code]...
Row A4:O4 is the range of cells to be tested. Row A5:O5 are the corresponding dates. Cell B1 is the value I want to search for and B2 is where the date I would like to appear.
In the example shown I'd like 01-Sep to appear in B2.
I'm using Excel2010 and Windows 7.
View 3 Replies
View Related
Apr 17, 2013
S/W Environment: Excel 2010
Some of my worksheets are permission protected. They will allow me only to view the sheets - No editing, copy, cut, sort, add filter, etc. Is there a way to hack this and copy data (including formating) into other / new worksheets?
View 1 Replies
View Related
Feb 12, 2014
we have a Excel 2010 worksheet containing multiple tables.
Table1
ColumnA ColumnB
1 Blue 12
2 Orange 14
15
11
3 Red 10
Table2
ColumnA ColumnB
1 Blue 11
2 Black 13
15
11
3 White 10
19
17
On a separate worksheet we want to calculate with the first worksheet's values.
For example: Calc1: Sum ColumnB IN Table1 WHERE ColumnA = Orange
How do we do such a calculation/formula?
View 4 Replies
View Related
Mar 13, 2012
How, via VBA, would I add a comment from a userform (text box name: txtReason) into a cell that may already have a comment in it? I would like to keep the comment that is in the cell and then have the program add a "/" and the next comment from txtReason..
(using excel 2010)
View 2 Replies
View Related
Oct 8, 2013
Recently I have been practising writing my own macros in VBA. I am using Excel 2010.
I have been trying to write a macro to look at two columns in a worksheet (say, columns D and E) and to delete the contents (not the formatting, and I don't want to delete the cell itself) of that cell only if it contains a numerical value (e.g. 1, 2, 256 or any other number). If it finds a cell with letters, then it wouldn't delete the contents of the cell. The cells are currently formatted as "currency" cells.
I have been trying all sorts of things and I just can't get it to work. I can write a macro to clear a range easy enough, but I am stuck on getting it to delete the right stuff. I won't embarrass myself by uploading the code that I have been using..
View 7 Replies
View Related
Apr 11, 2003
When we create a database in an Excel Spreadsheet and then some pivot tables, we use to copy the spreadsheet with another name, in order to clean the data and update it with another date (for instance, one excel spreadsheet for one BU or country, another one for other bU or country), to avoid creating the pivot tables again and again.
However, when we clean up all the data and enter the new one, on the Pivot Tables fields, you still see the "old data that was deleted"... Do you know if there is a way to delete that data without recretaing the pivot table?
View 9 Replies
View Related
May 7, 2014
The following formula works perfectly in Excel 2003:
=SUMPRODUCT((A2:A10="A")*(b2:b10="B")*(c2:c10="C"))
However, I've got a fourth column that contains either a date or a blank cells; I want to limit the formula to only include certain dates in the sum. i.e:
=SUMPRODUCT((A2:A1000="A")*(b2:b10="B")*(c2:c10="C")*(d2:d10>="01/04/2014"))
Rather than an error it's just retuning an incorrect number.
View 6 Replies
View Related
Mar 25, 2014
I am trying to send bulk emails from my excel 2010 - however I am getting a POP UP. find the screen shot in the enclosed word document So every time a new mail is sent from excel we need to press the button allow Is there a way where I can turn off this warning.
View 6 Replies
View Related
May 15, 2014
All I want to do is write some vba script that inserts a pivot table based on all of the data in my current workbook.
Therefore I want something incredibly simple if it exists like:
ActiveWorkbook.PivotCaches.Create(SourceData:= ActiveWorksheet.Select.AllData)
ie the vba code that just takes the data you've got in your current worksheet and creates a pivot table.
View 1 Replies
View Related
Jan 23, 2014
How do i get my excel to compare between tables and come up with a result. For example i have 2 tables,
Table 1
[Code]....
Table 2
[Code] ....
How do i make them compare and then come up with a result like
[Code] ....
View 1 Replies
View Related
Apr 18, 2011
I have roughly 100 HTML files, each containing a single table of formatted data (created with the SAS statistical software package).
I realize that I can open an HTML file with Excel, do what I need (e.g., copy, format, save as an Excel file), but I would like to try to automate the process, if at all possible, even if it's simply open the HTML file and save it as Excel. That would save me quite a few clicks it seems.
Ideally I could pass a directory name to Excel (via VBA) and have a series of "operations" (i.e., save as an Excel file) performed on each HTML file in the directory, ultimately resulting in a corresponding collection of "new" files (in my case, Excel files).
If this was very fancy, I would love to take each HTML file and copy the one table in each, and paste into a separate worksheet in a new Excel file (so 100 HTML files would result in one Excel file with 100 worksheets).
I did try to generate a macro while using the Import External Data "tool", but didn't have luck with that (although I am able to get the tool to work, just not within a macro environment).
View 9 Replies
View Related
Jan 8, 2013
I am creating many tables in excel using a macro, and I want to copy them into a word document for later use. All the tables come from a long list, so for simplicity, they all get created on the same range starting at F1. Before deleting the existing table and adding the new one, I'm trying to copy/cut the table and paste it into a word document.
In case it makes a difference, I have office 2007
VB:
Sub ExcelToWord(LastRow)
Dim objWord As Word.Application
Range("F1:F" & LastRow).Copy
With objWord
.Documents.Add
.Selection.Paste
.Visible = True
End With
End Sub
View 6 Replies
View Related
