Picking Up Numbers That Add Up To Another Number
Apr 26, 2008
I have a question that has been bugging me for a long time and an answer to it can be a big timesaver for alot of excel users specially accountants that do account reconciliations..
lets assume that I have 3000 numbers(amounts) listed on an Excel spreadsheet vertically, and i know for a fact that the sum of most of these 3000 transactions equal a certian amount..lets say $1,200,320.23,
lets also assume that all these amounts on the list are unique..
now..I want to isolate these numbers(amounts) that add up to that $1,200,320.23 by way of highlighting them or any other method. once that is done, I would be left with the amounts that are causing the descrepency in that account. I wonder if there is any function, utility or even VBA code in excel or even another software that can help solve that problem..I hope my explaination was suffecient, let me know if you need further clarification.
View 9 Replies
ADVERTISEMENT
Jul 17, 2009
I am trying to generate a list of the 5 highest and 5 lowest numbers from a list of scores that range from 1 to 10. I have found the highest and lowest absolute values (numbers over 7.5, and less than 5, out of 10), but I would also like to generate the 5 highest and lowest relative numbers, ie. if there are no scores over 7.5, then the 5 next highest numbers. I have used if/then formulas for the absolute highest and lowest numbers, and a rank/countif formula to rank them. I have no idea how to generate a list of the "relative strengths and weaknesses".
View 14 Replies
View Related
Jan 2, 2007
I have numbers in A1:F1.
I have numbers in A2:F50.
How can I write a formaule that will check the numbers that is in A1:F1.
And if the number matches then change the colore of the number in A1:F50. too RED
This way you can just enter the new numbers in A1:F1 and if there is a match in A2:F50, you will be able to see it cause the number would turn RED.
View 9 Replies
View Related
Jan 5, 2012
I have a CSV file which contains a list of records about offices. This CSV file is generated from another program.
I read the CSV file with VBA, a SELECT * SQL statement which puts the elements into an Office Object and adds the offices to a collection.
For some reason, when one of the Telephone numbers is read it is being identified as a Null value. If I manually open the CSV file and change it to a different number e.g. 12345 and try re-read the CSV file with SQL VBA the number gets read fine. Every other element in the CSV file reads fine except for this. The number itself is 7736786460.
Attached is the small snippets of code to read the CSV file which is fairly straightforward.
Code:
Sub LoadUnits()
Dim oRs As Recordset
Set oFSObj = CreateObject("SCRIPTING.FILESYSTEMOBJECT")
'Open an ADO connection to the folder specified
Set oConn = CreateObject("ADODB.CONNECTION")
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strFilePath & ";" & _
[code]...
View 7 Replies
View Related
Mar 14, 2003
I want to pick the last number entered in column and put it in a formula. Is there an easy way to do this, or do I have to stack a lot of IFs in a string.
View 8 Replies
View Related
Oct 24, 2013
I need to generate 8 (random) numbers which should range between 0 to 80 and the sum of those generated 8 numbers should be 80.? How can I achieve this.
View 14 Replies
View Related
Apr 2, 2014
The purpose of the attached workbook is to calculate the average number of days a customer takes to pay their invoices. There are two tabs - Invoices and Payments. I am trying to calculate the number of days between Invoices "Sales Date" and Payments "Settlement Date".
The Payments tab is raw data exported from Attache financial system as CSV. It is the best I can get out of the system but the columns are all over the place.
I thought maybe from the Invoices tab I could search a row on the Payments tab to match the invoice number, find the cell containing the phrase "Invoice Number" then pick up the date from the following cell which contains the Settlement Date . This would be straight forward if the data from the CSV file consistently landed in the same column in the Payments worksheet, but it doesn't. As you can see, the phrase "Invoice Number" might appear in any one of several different columns.
View 5 Replies
View Related
Oct 6, 2008
I work in school which for example offers 20 courses for every student. Therefore I have a spreadsheet which has students name in A and B then the 20 courses spread from C to V.
Each student will study 3, 4 or 5 of the courses and will have value in the corresponding column (their teaching group) the rest of their row will be blank.
For each student create in columns X, Y , Z, AA and AB I would like to be able to pull from the 20 courses those which the student studies. Therefore finishing up with C to V hidden and just the student names and the 3, 4 or 5 courses they study.
Any ideas? if one method is to get them into one cell how do I get them into the 5 columns; i know DATA : Text to Columns but won't have anything to Delimit it with and can't use Fixed width as students will study subjects with different length charactors.
View 6 Replies
View Related
May 20, 2014
I have a table, which has pairs, e.g like this:
A=1
B=2
etc
how to give the value to variable based on this pairs? I mean something like this:
If left(mystring;1)=A, then
myvar=1
I used the case-structure, but maybe there is a better solution, which can use an array in order not to write all of the items whenever I need it.
View 9 Replies
View Related
Oct 21, 2009
I've created a useform for recording stock trades. So it asks the user to enter values for
"Date of trade"
"Time of trade"
"Name of stock"
"Price"
Right now I'm using a calendar control I downloaded from
http://www.fontstuff.com/vba/vbatut07.htm and it looks good, but I'd also like to have an easy way for the user to select the time of the trade (in minutes).
Is there an easy way to have a time picker? Like a dropdown box maybe? I'd like to have date and time picked separately since the date will remain the same for a lot of trades but the time will always change.
I've googled it a lot and some people mentioned using the datetimepicker Access add-in but I'm not sure if that's right for me. Also, I'm using Excel 2007 but the form will also be used on excel 2003 machines, will that affect the choice of solution?
View 3 Replies
View Related
Jan 5, 2012
Copied the below formula 1 column to the left - to pick up numbers 1 column to the left:
=IF(SUMPRODUCT(--(MOD(COLUMN(E7:AX7),3)=2),E7:AX7)=0,"",SUMPRODUCT(--(MOD(COLUMN(E7:AX7),3)=2),E7:AX7))
But the below resulting formula still picks up the same columns as the formula above:
=IF(SUMPRODUCT(--(MOD(COLUMN(D7:AW7),3)=2),D7:AW7)=0,"",SUMPRODUCT(--(MOD(COLUMN(D7:AW7),3)=2),D7:AW7))
View 1 Replies
View Related
Apr 10, 2014
I'm trying to use SUMPRODUCT as a sort of SUMIF/VLOOKUP hybrid. The following formula:
Code:
=ROUND(SUMPRODUCT('[12 GB BUDGET 15 V1 LOCAL VERSION.xlsx]
Revenue'!$G$3:$FM$3=$K2)*('[12 GB BUDGET 15 V1
LOCAL VERSION.xlsx]Revenue'!$G$27:$FM$27);0)
... should check for the value in cell K2 of the current sheet, compare it to all the values in row 3 of the Revenue sheet, and add up any and all values for those columns in row 27 where there's a match in row 3.
I don't get an error, but it's not adding anything up, either.
View 9 Replies
View Related
Sep 20, 2007
I have a excel file with names of employees and projects handled by them. In the 1st column is the name of employee and in the second column, its the name of the project. Every employee has approx 10-15 projects made by them.
I have another file with name of all employees in the 1st column. What is want is to insert the name of any two projects (taken randomly from 1st file) ahead of each employee.
View 9 Replies
View Related
Feb 4, 2008
I have Workbook with numerous worksheets,on one particular worksheet i require a formula/macro code that will pick up (say) those cells highlighted in red and then transfer that data set and the other selected information on that row to a another worksheet within the workbook.
E.g
Sheet 1 contains columns A to J;
Sheet 2 is where i require the information which is highlighted in red to be uplifted and pasted too.
so if Cell B2 contains the highlighted cell how would i uplift and transfer this along with data in cell C2,H2,I2 and J2 automatically to sheet 2.
View 9 Replies
View Related
Aug 1, 2008
I'm using the following formula to pick up data from another worksheet
=SUMPRODUCT(--(RawActuals!$A$1:$A$10000="jun"),--(RawActuals!$E$1:$E$10000=A236),RawActuals!$M$1:$M$10000)
It's picking up most of the data, but it's missing some - and it's to do with formatting of SOME of the data within the RawActuals sheet
212605Agency Staff - Professional & Other212701Staff Cost Tfr to Change Programme221112Property Rents - Passing Rent221310Hire, office/courtr'm/related eqpt
The 1st, 3rd and 4th lines are correctly picking up the data - but the 2nd is not. They are all set to the same number format.
View 9 Replies
View Related
May 18, 2009
I have a column where some cells are coloured yellow. In another column, I'd like to mark out these cells, e.g. if the cell next to it was yellow, I would like to to display 'Y'.
View 9 Replies
View Related
May 3, 2007
I have a simple program that picks up an ID on sheet1, finds that ID on sheet2, picks up the information in one cell from sheet2, and brings that information back to sheet1.
The program works fine until for all IDs above row 570. After row 569 something strang happens. The programs is still able to find the ID but when it tries to pick up the one cell value it is saying it is blank yet you can physically see the data is present.
In all my years of VBA programming I have never encountered anything like this. Especially, where things work fine on half the worksheet and not on the other.
I am posting the code below, but I don't actually believe it is in the code. I cannot post the workbook because of the information it contains...
View 9 Replies
View Related
Oct 22, 2013
I have a long table like this:
Car,date last washed
Civic, 11/1/2012
F150, 5/4/2013
Corvette, 7/3/2013
If I washed the Corvette, I'd add another row to it:
Corvette, 10/22/2013
And I would like to remove the "Corvette, 7/3/2013" entry. What's the easiest (read: least user intervention) way of doing this?
View 2 Replies
View Related
Nov 7, 2013
I have a table like this:
Book, Loaned to, Date
A Tale of 2 Cities, John, 1/1/2010
Oliver Twist, Mary, 2/3/2011
Great Expectations, James, 4/6/2011
Oliver Twist, Greg, 6/2/2010
I want to make it such that, when I input the name of the book, it will tell me the person whom I lent it to last and the date of the transaction. Preferably, this should be done without any assumptions on how the table is ordered. For example:
Book= Oliver Twist
Loaned to= Greg
Date= 6/2/2010
View 2 Replies
View Related
May 5, 2009
This should be a simple one, But I can get it to work. Could someone advise me as to how I can change this line of code to pick sheet(2) as it is now it picks sheet(1) . and throws the formula off. In A nut shell the formula is looking at sheets(1) C3 When it needs to look at sheets(2) C3. I always get messed up with the & and "" and!
View 3 Replies
View Related
Dec 21, 2011
I am trying to write a macro that will automate the process of selecting rows from a data sheet. I want the rows to be selected based on the values in two columns. I want to find and select the row at the beginning and end of positions in a column where the values are equal. For a quick example, if my data looks like this below, I'd want to select the entire row for rows 2,3, and 6... I also need the row just before the value in both columns are equal.
row column11 column12
1 0 1
2 0 1
3 1 1
4 1 1
5 1 1
6 1 1
7 0 1
8 0 1
I have written this macro and it is not working... are there any obvious problems with this? This is new to me and I'm not exactly sure that I've used the correct commands. It also seems like LastRow is referencing 0 and not 12965 or whatever the last row of filled data is for the sheet.
Sub Deltadata()
Dim RowNum As Long, LastRow As Long
LastRow = Range("A" & Rows.Count).End(xlUp).Row
'Finds last filled row (should be 12965)
For RowNum = 3 To LastRow Step 1
[Code] ........
View 4 Replies
View Related
Mar 26, 2013
I am trying to get excel to pick a cell from a hiden matrix and showing it on my worksheet. I want to pick the cell using 2 drop down boxes as bellow - 1 drop down picks option on X axis and the second picks using the Y axis and then the coresponding cell in the matrix is displayedMatrix example [URL] Drop downs example [URL]
View 4 Replies
View Related
Mar 18, 2014
I have cell A1 and it has the following data "-110 or -99%".
I want a formula that will pick up the percentage numbers only, IE -99%. Please keep in mind that the percentages change.
It can be 10000%, 0%, -500000% etc.
Can there be a formula that will pick up whatever is before or from the right?
View 1 Replies
View Related
Jul 22, 2006
I need to look at each filename in a particular folder, open the file if the name contains "THO", then modify the file and close it. I know how to open, modify, and close files using VBA. I'm stuck on how to pick one particular file based on the filename. I copied the code below from another thread. It's great for opening all files in the specified directory. Can anyone show me how to modify it so I only open files with "THO" in the name?
Sub OpenWorkbooks()
Dim wb As Workbook
Dim dirr As String
Dim I As Long
Application.DisplayAlerts = False
dirr = "C:Documents and SettingsHalldaviDesktopKoen's weekly sample reports"
With Application.FileSearch
.NewSearch
.LookIn = dirr
.FileType = msoFileTypeExcelWorkbooks
.SearchSubFolders = False
.Execute
For I = 1 To .FoundFiles.Count
Set wb = Workbooks.Open(.FoundFiles(I))
Next I
End With
End Sub
View 3 Replies
View Related
Jul 26, 2006
how to pick the numeric part out from a cell?
e.g. range("a1").value= 384HK
in range("b1"), i would like to have only 384, ignoring the HK
any excel tools or VBA code can accomplish this? there is a bunch of data that comes like this and i just dont have time to retype only the number one by one
View 4 Replies
View Related
Jul 29, 2006
I got some data like this
A1=CTAC US
A2=FCOB US
A3=TW US
The gap between each word and the word "US" is uneven. sometimes it's 3 spacebar apart, and sometimes 4.
I am trying the pick the part not including US. I tried =IF(ISERROR(SEARCH("US",A1)),A1,LEFT(A1,SEARCH("US",A1)-1)). It works but when I use match function to look up the word. It returns N.A. As the output is not just CTAC(4 characters), but 5 characters(including blank). The problem is the gap between between the word and "US" is different for each cell. so I cannot just minus the same character in the above formula (e.g. -2 if there is one blank cell). Anyone can tell me how to pick out the word and with only the number of characters in the word?(no blank)
View 5 Replies
View Related
Sep 11, 2012
I'm trying to take 5 inputs that are all in text format and inputs have fixed options what i need is a macro that will take them and turn them in to the relevant outputs. there is a select amount of combinations and i've been asked to do each combination as a separate macro or sub macro.
What I have so far is;
If C6 = "Panel plate" And D6 = "24 by 21" And E6 = "<1.59" And F6 = "N/A " Then I6 = "120" And j6 = "4" And L6 = "120"
End Sub
A B C D E F G H I J K L
1
2
4
5
6 Panel plate 24 by 21 <1.59 N/A
7
8
9
10
(Doesn't seem to work)...
If those four cell match up then i need I6-L6 to line up with whats required.
If they don't equal that i need it to stay clear.
I also need to add the same macro with different options.
View 7 Replies
View Related
Oct 13, 2013
last item in we 13-9-2013 is 13-9-2013
last item in we 20-9-2013 is 13-18-2013
last item in we 4-11-2013 is 30-9-2013 as shown in the attached
1.jpg
View 2 Replies
View Related
Mar 11, 2014
I am trying (and failing miserably) to create a fomula to 'score' my pickers in a warehouse environment.
The basic variables are;
1) Units (Total quantity of units on picklist)
2) Lines (Number of different products on picklist)
3) Time (Total time to complete pick)
To give an example, if Units = 8, Lines = 1 and Time = 00:01, I want 'Score' to = 100.
(Based on benchmark, rounded down to lowest common denominators)
The added complication I face is the relationship between Lines and Units; it would take someone longer to pick 1 unit from 10 lines than 10 units from 1 line.
View 2 Replies
View Related
Jul 22, 2014
I have a series of spreadsheets that represent the rosters of teams in a league. At the end of the league season, some teams qualify to advance to a final event, and some do not. I start with a file of all the teams for that level of competition. This list comes from a program that creates it's output in an excel '97-2003 format (and I can't change the way the data comes out of this program). I modify the data a bit by removing data from merged cells and rename that worksheet a 'Raw Data'. In the past I have manually copied and pasted the data for the teams that advance to a sheet called 'Roster Data'. I am hoping there is a way to automate this part of the chore.
There must be 6 blank lines between the team names on the 'Roster Data' tab, but some teams have more members than others, so they are not spaced the same on the 'raw data' tab. Is there a way to select teams from the 'Raw Data' tab and have only the selected teams show on the 'Roster data' tab, AND have the spacing become uniform?
View 13 Replies
View Related
