I'm trying (with little success) to match some negatives and positives, but the formulas that I found on line keep on picking up all matches (just like a VLOOKUP formula)
23
23
-23
The formula would match the -23 with the 2 positive 23s. This formula goes in Column O
i'm looking for a formula that gives the greatest number of a range
problem is that there are positive and negative numbers in the range
but I only want to look at the value (+/- signs are not important)
I am using one file, in that out put is coming but some times it is showing out put as -1.
Please find the attachment.
In the Sheet 1 Column X some out puts are coming as -1, what is the error in my formula.
Platform Open tickets on 29.01.2014.xlsx‎
I have a column with both positive and negative numbers, is there a macro or a bit of code that will convert all negative numbers in a selected column to zeroes?
View 3 Replies View RelatedI have a question regarding calculating percentage differences, here is how our formula works, but I've done a little research regarding using negative number for percentage calculations and am not sure...
For sample:
this year (TY) we had net profit of $200 million
last year (LY) we had net loss of -$32 million
our formula works kinda like this:
=IF(B49="","-",IF(B49=0,0,D49/B49-1))
but if, the comparing numbers is negative should I convert that to an absolute number using ABS ? what if both numbers are negative ?
The way I understand the current formula is like this:
if B49 is blank, then insert a dash, otherwise IF B49 is 0, then 0 (since we can't divide by 0), otherwise do the formula.
Long Version: This should be a fairly simple question, with a solution involving unchecking a box somewhere, but I can't find it. Whenever I enter a number in parentheses (for example: "(187.13)") into a cell Excel automatically converts that number to a negative--- "(187.13)" becomes "-187.13". I've been using the apostrophe option as a workaround (i.e., '(187.13)) to store it as text instead of a number, but this is becoming a hassle because it recognizes that (187.13) is a number and alerts me with an error message of numbers being stored as text.
Short Version: Basically, I want it to stop automatically making "(187.13)" become "-187.13" but I don't know where the setting is.
I'm trying to SUM a column, but it has a negative number in it. I want to sum the column but ignore the negative number.
I.E.
5
46
12
-15
3
so the total would be 66. How is this possible or would i have to use another formula instead of SUM.
Let us say that this is a certain cash flow
Year - Cash flow
2007 - (200)
2008 - (100)
2009 - 50
2010 - 100
2011 - 200
2012 - (100)
2013 - (25)
2014 - 50
2015 - 300
For year 2007,2008,2012 and 2013, we have negative cash.
I want to compute for the investment cost, which I define to be the total accumulated cost before first positive cash. First positive cash occurs at 2009, so I want to sum 2007 and 2008, However, 2012 and 2013 also reports negative cash, so in the SUM formula, their values will be included.
How should I write the formula in order to sum all the negative cash before positive cash, disregarding all the other cash flows thereafter (be it positive or negative)?
I have the following code in a user form, attached to a data field that the user should fill. This code should alert the user if he enters a negative number i.s.o. a positive (and vise versa). Due language related issues, it is quite possible that the user will enter (by mistake) a negative sign but it will be at the end (500-) and the user will not notice this mistake. For some reason, my code does not pick up on this, and does not pop up the error message. Therefore, I decided that I need to check if the value entered by the user also contains a “-“ at the end of the string. This, I believe, will take care of the problem.
In excel, I would simply enter a formula with Mid and Len (to check if the last character is “-“) , but I don’t know how to implement it in my code.
Sub txt_sum_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
'Verify that a negative sign was added for expenses
With Me.txt_sum
Select Case cbo_act
Case "Expense",
If Me.txt_sum.Value > 0 Then
MsgBox "Number must be negative"
End If
Case "Income"
If Me.txt_sum.Value < 0 Then
MsgBox "Number must be positive"
End If
End Select
End With
End Sub
I would like to be able to enter numbers into a cell as a positive number, but have them actually display as negative in that cell.
View 2 Replies View RelatedI use keyboard commands whenever possible. They are much quicker than the mouse.
For years I have been using [Ctrl]+[Shift]+[1] to format cells as numeric with two decimal places, 1000 separator(,) and showing negative numbers in black with a minus sign in front.
I like to see negatives in red with parenthesis.
look at my attachment and see what I am doing wrong in my formula? I have a hard time understanding the Sumproduct formula and when to use comma's, double negatives, addition, etc.
View 2 Replies View RelatedThis is my formula and it returns: -55 bps.
=[ASK.xls]QTR!$AT$81&" "&"bps"
However, I want it to return (55 bps). I don't want to just add the parenthesis because in the future this formula could return a positive number. I tried to format the cell differently, but that did not work.
My task is to combine two large databases into one spreadsheet by extending the number of columns. The data from each database is 90% matching based on an identification number, however occasionally there are additional rows or unmatched identification numbers that need to be kept for analysis.
When this happens, there needs to be a blank row inserted to represent the missing data in the rest of the corresponding row.
I am having trouble finding a quick way to do this because I have approximately 12,000 rows (and columns up to DV when combined).
for example:
p1
data
data
data
data
p2
data
data
data
data
[Code] .....
needs to become:
p1
data
data
data
data
[Code] .....
I am guessing I will need a macro of sorts, So far I have made one column that tell me if the ID's are matching or not (1 or 0) and if they are not matching (0) I manually insert the rest of the row that is missing or make space for the duplicate data (which needs to remain).
programming 2 macros in a bank reconciliation sheet I am trying to create.
Basically the data consists of two sets: the ledger side and bank side. Both sides consists of multiple columns that include date, description and amount.
The following two situations can occur and which need to be covered with a macro.
Many to one matching
When we make payments to say 100 different suppliers at once, the ledger side will show 100 lines with the different amounts. However on the bank side it will only show one line with a total amount for the transaction. As the description on the ledger side is the same for all transactions done at the same time, it should be possible to have a macro add these lines and compare the total with entries on the bank side. Once a matching amount has been found, the macro should place an ‘x’ next to all entries on the ledger and bank side, in order to show that these transactions have been reconciled.
Partial cell matching
In this case we are only dealing with one line on both the ledger and bank side. The issue is that the description does not always perfectly match with one another. The ledger side might say ‘Brown Corp.’ and the bank side might say ‘Brown Corporation’. I want to come up with a macro that can recognize this partial match and still reconcile the lines by placing a ‘x’ to both the ledger and bank entry. The match however should be done with a combination of description, date and amount as several different bookings might be done for the same supplier in the same month.
I have two tables, Table1 one has only customer codes in it, and I have Table2 with plenty of customer codes and those customer name, surname, age, location...
And I want to match and copy each of those customer codes in Table 1 all the information which is on Table2
Table 1:
50025
50026
50086
Table 2: Considering that name, age and location is each in separate cell
50025JohnSecond25Location 1
60085EmilyThird 20Location 2
45454Wilhelm Fourth35Location 3
10000RoseFifth 60Location 4
50086JohnySixth 45Location 5
65501JacobSeventh18Location 6
50026Jackie Eighth22Location 7
And the outcame should be that it finds the value from Table1 in Table2 and copy's information in Table 1 like this:
50025 JohnSecond25Location 1
50026 Jackie Eighth22Location 7
50086 Johny Sixth 45Location 5
I have two worksheet "ABX" and "ACX" from which I want to find the matching data for specific columns B, D and E only.
Matching data will be in sheet "Match" and vice versa.
Data are present dynamically.
see attachment below for easier comprehension of what I need to be done. Note that I have around 20 subjects (2 in example) and about 15000 values per subject (5 per subject shown) Basically, I want matching rows to be aligned (see grey) and non-matching rows to be deleted, throughout the 20 or so subjects.
Exemple.xlsx
I have a cell (A1) that contains text.
That text will match a single value in a reference list (i.e. range of cells e.g. Z1:Z26).
When the match occurs (e.g. A1 = Z16), I want the format of the reference cell (fill color, text color) to apply to the cell of interest (A1).
The reference cells are manually formatted.
I know that I could create a long list of conditional format formulas instead of using the reference, but it seems that there must be some way to tell excel: if the cell value contents are equivalent, the formatting should be as well.
Attaching the excel (QOS- ARP- Applications.xlsx) - Formula I am trying in D27, "X" value is there in the row and the corresponding application (Header ) should be the return in D27.
View 1 Replies View RelatedI'm trying to get column Z4:Z14 to add automatically from information from Cells A15:Y17 under the correct name of each person. I have created an example sheet.
View 2 Replies View RelatedI have a long table with duplicating id numbers but different values which i need to match. Basically the initial state is like this:
ID1 | example 1 |
ID2 | example 2 |
ID1 | example 3 |
and what i need is this:
ID1 | example 1 | example 3|
ID2 | example 2 |
I need to produce three different formats of accounts data monthly. I attached a file with each accounts description in Columns A, E and G. Is there a way to match the data with those accounts name in a faster way than manual linking?
I do have a workbook has many worksheets. I would like to be able to match (or search) for first name and / or surname between one worksheet (Sheet,1, which has all the names I would like to match with the other worksheets) and the other sheets in the same workbook (the data is very sensitive). Is the possible way of doing this using VBA.
View 14 Replies View RelatedI have some text in $a$1.
In Column B I have all kinds of stuff, including the text in $a$1.
How do I find and select the matching cell in Column B?
I want to add these numbered steps onto the macro I already have, which I pasted below.
1. Starting in Row 2 in the worksheet named Report 1 look at contents in Column A then look at the contents in Column C, if the contents in Column C do not match exactly the contents in Column A then copy the contents in the range column C:E from that row till the last row in Column C:E.
2. Now we will have a range that is made up of cells from C:E. I would like to move that range down a row at a time, until the first cell in Column C from the range Column C:E matches exactly the contents in Column A. Then I would like to continue this pattern for every row in the worksheet.
i have cell N1 witch is a number example "8832"
cell O1 is text example "state street"
now i have A1 thur A10 as an address "number"
now E1 thur E10 is street address "text"
i need to take n1 match it to A1-A10
then take that row number and see if E1 of that row numbers matches
O1 if it does that that row numer and get g of that number if not keep checking to i find a match
I have two spreadsheets, both contain matchable data. First one with amounts. Next one hs customer name, period and amount columns.
View 3 Replies View RelatedI have a list of company names and I need to find the duplicates. Of course pivot table finds the exact matches, but how do I find duplicates when only 1 character is off. Like punctuation or "s" or a mistype of a single character?
Example:
Jo Blo inc
Jo Blo inc.
Jo Blo inc
Jo Blo icn
Joe Blo inc
Jo Blo inc
If the following macro matchs two ID numbers from column B in column C it shoud highlight them green.
Column C may has multiple entries of column B, however the macro i have written appears to only match the first ID number found in column C from B.
I think this may have something to do with my loop, can anyone suggest a solution?