Excel 2010 :: Comparing Record And Associated Cost
Apr 7, 2014
I am trying to compare values of an item one year vs the other in excel 2010. So my spreadsheet would look like:
A B C D
028042$500,000.00028042$500,000.00
028349$300,000.00028349$300,000.00
028390$300,000.00028390$300,000.00
028498$100,000.00028498$100,000.00
029477$135,140.00029477$135,140.00
030374$452,865.45030374$452,865.45
030375$452,865.45030375$452,865.45
What I am trying to do is compare A and B vs C and D to see if the cost associated with the number in A is the same as it is in C(which is obvious it is in this example but I have a spreadsheet with thousands of entries). Additionally, the AB value might be anywhere in the CD columns not just in the row next door.
View 12 Replies
ADVERTISEMENT
Jul 29, 2014
I am trying to create a Dashboard in excel (2010) using tables/pivot tables to build it. The data I am bringing into excel has these key fields of data: cost center+cost center description, general ledger account+general ledger account description, and YTD amount.
My problem is the data is from an external source report and the report has subtotals built in at cost center, and the report's format of subtotaling puts the cost center first and then the general ledger accounts below. There is no
formula value in the cell that has the subtotaled amount and the number of general ledger accounts can vary depending on whether there has been general ledger activity.
I want to take this format:
July YTD
Cost Ctr 1050 XYZ$6.00
625110 Supplies$2.00
650150 Postage$2.00
650550 Fees$2.00
Cost Ctr 1052 ZZZ$4.00
670500 Pens$2.00
and have it look like this
Cost CenterCost Center DescrGL AcctGL Acct descYTD Amt
1050 XYZ 625110 Supplies $2.00
1050 XYZ 650150 Postage $2.00
1050 XYZ 650550 Fees $2.00
1052 ZZZ 679200 Pens $2.00
Besides manually doing data moves and assigning a unique sort sequence number to keep the records together, how else can I quickly move my cost centers to a new column and keep the cost center with the gl account and $amount?
View 3 Replies
View Related
Apr 10, 2013
MS Excel 2010, WinsXP
how to highlight an entire record based on the value of a single cell?
I would like to highlight all records grey where cells in a column = "closed".
View 4 Replies
View Related
Aug 8, 2012
I'm using Excel 2010 on Windows 7.
I have 2 worksheets. One has Employees and the devices they have. Last name, First Name, Device each in their own colulmn. Many have more than 1 device so they have multiple entries on seperate rows.
Another worksheet has Employees and thier location. Last name, First Name, Location. Again, all on seperate columns.
It would look something like this
Sheet1
Lastname
Firstname
Device
Johnson
[Code]...
So I'm tasked with combining them into 1 sheet with last name, first name, device and location. The issues I'm having are:
1) A team member could have multiple devices
2) A last and/or first name can appear many times, so a simple Vlookup against lastname won't work - it has to somehow also compare against both.
View 6 Replies
View Related
Mar 19, 2014
Win7/2010
I have an array PeopleList(6,320) that contains
PersonID, FirstName, LastName, Email, Phone, Notes
What I'm trying to do. I have two separate requirements:
(1) To add the whole array to a listbox on form initialization - see Sub UserForm_Initialize()
(2) To clear the listbox and re-add only certain items based on what's typed in a textbox - see Sub txtSearchTerm_Change()
I have two errors:
Error 1 in UserForm_Initialize()
The listbox contents need transposing! It is displaying as
Code:
1 2 3 4 5
Tom Ben Heidi Julie Mark
Smith Jones Evans Simpson Petersen
x@yo.com a@bo.com c@do.com e@fo.com g@ho.com
02071001022 02071001026 02071001027 02071001028 02071001029
Friend
When it should be displaying as
:
Code:
1 Tom Smith x@yo.com 02071001022 Friend
2 Ben Jones a@bo.com 02071001026
Is there a way to transpose the array?
Error 2 in txtSearchTerm_Change()
I cannot find anywhere - even on MSDN - all the information I need how to correctly add a single record to a multiple-column listbox! What I'm trying is:
Code:
For i = 0 To UBound(SearchList) If InStr(1, SearchList(i), SearchTerm) 0 Then
With lstPeople
.AddItem
For j = 0 To UBound(PeopleList, 1)
.List(c, j).Value = PeopleList(j, i)
[Code] ......
How do I add a record to the listbox????
Full code for reference:
Option Explicit
Private PeopleList As Variant
Private SearchList As Variant
Private Sub UserForm_Initialize()
[Code] .......
View 5 Replies
View Related
Aug 15, 2012
I have attached a work book example of what i am trying to do.
Column D is what i originally did in terms of the formula and now i have to have a column display text dependant on what is in column A to C.
I was trying this formula =IF(b2>a2,"Start Target Missed",IF(c2="","Failed","Tracking")) However if this isnt working.
Is there anything i can do to change this?
View 3 Replies
View Related
May 17, 2014
I have a parent spreadsheet with raw data(with errors) and a child spreadsheet without errors. I want to merge the child into parent. (:{). I am thinking of comparing multiple columns from each sheet to ensure maximum accuracy. And when those columns match up we paste the corrected column data from child sheet to parent one. I am using windows 7 and Excel 2010.
View 1 Replies
View Related
Dec 9, 2013
Attached is a spreadsheet wherein I'm trying to extrapolate project costs across various months based on working days in a month subject to start and end dates of the project. Need an accurate formula to spread the cost.
Days & Cost Allocation Example.xlsx
View 2 Replies
View Related
Sep 12, 2013
I need to develope a macro for Excel to calculate desired profit margin depending on our cost of each item. Here's the scenario.
A1: landing cost
If 0
View 4 Replies
View Related
Mar 15, 2007
In a financial environment we have a calculator which uses iteration to allow for a cost being added to loan amount where the cost is based on the total loan amount. Iteration is set to 100 iterations with max change .001
On one PC the first time the calculator is opened it gives a particular (incorrect) result. If the input cells are cleared and the data re-entered, it gives the correct result. This only happens on one particular PC. Is there some other setting , other than the iteration setting, that would cause this?
View 5 Replies
View Related
May 25, 2007
I seek advice on using the value of NOW() as a record ID in an address book program. Question #1: Do Excel developers often use a record ID? Question #2: What record ID schemes are fequently employed besides date/time? I have decided to create an Excel address book as an exercise to increase my knowledge of VBA, and also as a useful application for work.
I realize that a record ID is not essential in Excel in the way that it is essential in Access, but I feel the need to have some unique ID associated with each address, so that I may have different worksheets, with data related to a given Contact, sort and manipulate it, if necessary, but have the record ID as a way to restore the relationship of rows to a given Contact, and also, as a handy way to examine the data in the date/time sequence in which it was entered. I have experimented with the following code, to assure myself that I can access the number returned by the NOW() function, manipulate it as a string, and format in various ways if necessary.
Dim n As Double
n = Now()
sn = Str(n)
p = InStr(sn, ".")
first = Left(sn, (p - 1))
l = Len(sn)
d = l - p
S = Mid(sn, (p + 1), d)....................
View 2 Replies
View Related
May 24, 2012
I currently use a function very similar to this one with a SELECT query to return a field to excel. I have modified this into an UPDATE query to update a field.
I am getting errors at this point:
Code:
If adoRS.BOF And adoRS.EOF Then
DBNoteUpdate = "Value not Found"
Else
DBNoteUpdate = "Action Confirmation"
I believe this area of code was to trap the error that occurs when the lookup field name was not found in the table. I left it in because I still want to trap if the record I am trying to update is not found in the table.
Here is the full code:
Code:
Public Function DBNoteUpdate(RecordNumber As String, _
UpdateTableName As String, _
UpdateNote As String) As Variant
Dim adoRS As ADODB.Recordset
If adoCN Is Nothing Then SetUpConnection
[code].....
The code does work, in that the record is being sucessfully updated by the SQL statement, but its erroring at the point I described above with the error:
Code:
Runtime Error '3704':
Operation is not allowed when the object is closed.
how 'adoRS' is closed at that point because the call to close is right at the end of the function?
View 8 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
Mar 16, 2014
I am creating an asset management sheet. For the formula I am trying to work out there uses 3 fields : ID, start date, and end date.
What I want to do is be able to show if the ID is duplicated within another record with an overlapping date. So an item is flagged if it is in the list within the same dates as another record. I tried a few countif formulas but with no success.. I may just be approaching the problem incorrectly though.
View 1 Replies
View Related
Jan 11, 2007
Can I Restrict Excel Data Form only for appending new record. (No deletion allowed) and also Can I save Data Form in the File? Is there any way, whenever we open the file Data Form should popup?
View 9 Replies
View Related
Jun 6, 2013
I have currently browsed the forums and have came up with a code to compare two columns from two separate excel books and then highlight anything matching with the CompareRange. Here is a few more details about the problem:
- I have two excel sheets. And data like this in each sheet:
(First Sheet) (Second Sheet)
-A B N O
-7 .7 3 .56
-6 .6 8 .45
-5 .5 9 .55
-4 .4 11 .2
-3 .3 8 .22
-2 .2 9 .55
-1 .1 8 .54
As you can see, given this example nothing should be highlighted once the macro is run since nothing from Column A or B from the first sheet matches directly with Column N & O from the second sheet. The problem is that with the macro (module) I have come up with will highlight "3" from Column A and ".2" from Column B, just because they appear in Column N & Column O respectivally. What I want: I only want a number to be highlighted if both the numbers "7" & ".7" are matched in the same row of Column N & Column O on the other spreadsheet. To be a little more precise, I'll give an example. Say I edited the data to be like this.
(First Sheet) (Second Sheet)
A B N O
7 .7 3 .56
8 .45 8 .45
5 .5 9 .55
11 .4 11 .2
3 .3 8 .22
2 .2 9 .55
1 .1 8 .54
With this data, I would want the second row of A & B ("8" & ".45") highlighted, while my error "3" of Column A and ".2" of Column B is not highlighted. Also, I would like it if row 4 of Column A & B ("11" & ".4") is not highlighted at all either, just because in O it is .2 and in B it would be .4 even though the 11's match. Attached is the macro/module I have entered in which is working kind of correctly but producing the mistake. And also, (kind of a lesser problem), both the files with data will have the same header, example would be if Column A & Column N both had "Dogs" as it's title in Row 1 and Column B & O both had "Cats" as it's title in Row 1. Is there anyway the macro can be adjusted so it compares those two columns between the two workbooks without me even having to select or assigning a range?
HTML Code:
Sub Find_Matches()
Dim Column1 As Range
Dim Column2 As Range
[Code]....
View 1 Replies
View Related
Feb 15, 2009
I have two excel sheets (sheet1 and Sheet2) see attached file.
On sheet1 I have the data that I want to compare to sheet2.
I want to go through each row on Column A on sheet1 and compare it to sheet2.
For example on sheet 1 Column A, Row 6 has value of 5, when i compare this value with sheet2. it will not found. I want to highlight this value as Red.
View 9 Replies
View Related
Mar 8, 2012
How to compare two dates in excel. Like my requirement is I need to compare curent date vs from month begin date two weeks date.As of now I have tried like this.
Current Date : =TEXT(NOW(),"d")
Month Begindate : =TEXT(DATE(YEAR(NOW()),MONTH(NOW()),1)+14,"d")
And I have written condition like this : =IF(TEXT(NOW(),"d")
View 3 Replies
View Related
Apr 14, 2008
I have excel 2007 and I want to record a macro. I have enabled all macros via the Trust Center settings and have even saved the current workbook (created from scratch in 2007) to a macro-enabled version.
But when I click on the 'developer' toolbar, 'record macro' is grayed out.
View 9 Replies
View Related
Jun 2, 2014
i have an excel database regarding about 1000 different schemes having the details of their initiation date, budget allocated , up to date expenditure, this month expenditure and many others too. this is a shared workbook and relevant officers enter their data in their relevant columns and rows. the file is shared. now i want that any changes done by mr. A, for eg. at record No. 09 could be intimated to Mr. B who is working at record No. 560. may be via msg box or any other source.
View 1 Replies
View Related
Dec 6, 2013
Had 2003 now 2013. Trying to get the macro to select different printers for different doccuments. 2013 macro recorder does not record any printer info or path, all printers show the same wording just application print...
How can I get the recorder to acurrately record and diferentiate printers?
View 2 Replies
View Related
Jul 15, 2014
I'm trying to tweak this code from a previous form I created but I'm getting a compile error message. On my old form...the first box was a combo list box where the person entering data would select a value. On the new form, its a text box where the person will enter the value. I thought I could just change the me.cbo[name].listindex to me.tbo[name].listindex - but that seems to be causing the issue. I'm not sure what I would put after the me.tbo[name]. to get the code to run...
Code:
Private Sub cmdAdd_Click()Dim lRow As LongDim lPart As LongDim ws As WorksheetSet ws = Worksheets("DataNEW")'find first empty row in databaselRow = ws.Cells(Rows.Count, 1) _ .End(xlUp).Offset(1, 0).RowlPart = Me.tboProdCode.ListIndex
View 8 Replies
View Related
Aug 15, 2014
I am small trader and working on small excel sheet ... in that excel sheet I've 1500 names of stocks...I want to pair each and every stock with each other...on next column for E.g.:
I've
RELIANCE
RCOM
CAIRN
BPCL
I want other column comparing them to itself...
RELIANCE...RCOM
RELIANCE...CAIRN
RELIANCE...BPCL
RCOM...CAIRN
RCOM.....BPCL
CAIRN....BPCL
View 1 Replies
View Related
Jul 24, 2013
I have data in two cells.If both are different then it should display that column data with different color.
EX:
COL A COLB
1 1
2 1
If col b is not match with col A then it should display that col B value cell with some color.
View 1 Replies
View Related
Oct 21, 2013
I do backups on a daily schedule. Given the spreadsheet called "A" which is a list of all clients and current backup policy as of last week, and the file "B" which is a snapshot list of servers in our supported environment, does someone have a clever way to determine which systems are in "B" that are not in "A"? The clients are listed in column M in spreadsheet A and this column may have multiple client names listed due to a policy that includes multiple clients in the netbackup schedule.
View 4 Replies
View Related
May 30, 2012
In functions it is easy to simply say =if(a1>b1, do X, don't do X)
But how do I do this in VBA? Excel 2007
View 2 Replies
View Related
Jul 18, 2014
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] ...........
View 2 Replies
View Related
Oct 11, 2011
I am using office 2011 for MAC, and am trying to create a macro that changes the color of the selected cell(s).
if I start recording the macro
click the fill color button pull down and change the color on the edit section of the ribbon
Stop the macro
No code is recorded as below but the cell did change color to whatever I selected.
Sub Macro13()
'
' Macro13 Macro
'
'
End Sub
[code].....
The colorIndex is automatic no matter what color I choose.
When I run the macro there is no change to the selected cell, but the border is destroyed.
View 2 Replies
View Related
May 26, 2009
I am looking to create a macro that will create a new sheet when data is added on a summary sheet. Example.
1. Summary sheet called "Variations" contains columns that will contain the information needed for new sheet (Columns A to D)
2. When data is entered on "Variations" sheet: Column B, then macro automatically creates new sheet renamed to e.g. VO1 (Number used on "Variations" tab) and is a copy of "Master" tab.
3. Data entered in Column A to D on "Variations" tab is automatically entered onto new sheet created (e.g VO1). Shown is blue on attached file. Additional data is updated on "VO1" sheet and this then links back to "Variations" tab
View 6 Replies
View Related
Mar 20, 2009
I'm trying to find a macro that will run allowing the user to select a 'starting record number' and a 'finishing record number' when printing.
I have a spreadsheet that feeds from a master list in excel, from over 5000 records.
I need to print the s'sheet with any given indivdual record's information at any given time.
Individual prints are fine. However if I wanted to print from record number 1500 to record number 3000 it would take me all day.
Is there a way I can set up a macro so an option form pops up? allowing selection of "From record" and "to Record" ?
View 13 Replies
View Related
