Find Comparable Credit Values
Dec 19, 2007
I have been trying to write a macro but its getting very complicated and im just getting confused. I need to look through a long list of data and see if there is a comparable figure. e.g if there is £350 at branch A for supplier X, i need to know if there is also a credit of £350 at branch A for the same supplier..
The problem is that the comparable figure could be above or below the original figure. Then i will delete both these lines from the list. I have tried to do this but have got cofused by so many if and else formulas.
View 5 Replies
ADVERTISEMENT
Aug 15, 2014
I need a macro that searches through column F(amount). If it finds a negative amount(a credit note), it should search through column A(client ID), look for the same client and deduct the amount from the oldest invoice of this client. Then it should delete the whole line of the credit note.
If it doesn't find the same client, it should not adjust anything.
If the negative amount is a cancellation(all records with the invoice number 9999999), it should not adjust anything.
If the amount of the oldest invoice is smaller than the credit note, it should deduct down to 0 and deduct the rest of the credit note from the second oldest invoice.
I have attached a sample file : makro credit note.xlsx‎
View 4 Replies
View Related
Jun 19, 2007
I have a database which holds a field for people's credit card numbers. When I try to import them into excel it removes the last number and replaces it with a 0. So if I have credit card number 123456789123456 it will show as 1.23456E+15. Then when i format it as a number is shows as 123456789123450.
View 11 Replies
View Related
Apr 6, 2012
Why doesn't a three column formula work?
Column C is Credit, D is Debit, E is Balance
I put the following formula in the Balance column
=E21+C22-D22
where the first term refers to cell immediately above. In other words, this formula takes the prior balance, adds a credit if any, subtracts a debit, if any, and gives a new balance.
The formula worked on the cell I put it in. It will not work when I copy and pasted it into the cell below the first one. Error I get is circular reference.
View 2 Replies
View Related
Mar 19, 2008
I have a userform capturing Credit Card Numbers for Visa, Master, Amex and Diners as part of a payment tracking system and would like to check that the entered Card Number is valid before processing the payment through a third-party website to minimize to number of error / invalid card messages.
I suspect there is some kind of checksum built into the cardnr. Any tips on how to check for the validity would be appreciated, including links to relevant sites.
this question is related to an earlier question of mine: [url]
View 9 Replies
View Related
Nov 20, 2013
I need to extract the credit card expiration date from a cell. For example
1111 1111 1111 1111 1/17 code 1111
I was able to get the /17 using =MID(C77,FIND("/",C77),4)
However, I can't get the "1" before the /.
View 5 Replies
View Related
Mar 5, 2010
I have 2 reports starting col A to W. I want a macro code for the below.
1st Report
1. 1st sort the entire report with col O leaving the headings.
2. 2nd delete the rows if where value of O is "Credit"
3. After the above step sort the entire report again with col H
2nd Report i hvae is also from Col A to AB. I want a macro code for below.
1. 1st sort the entire report with col O leaving the headings.
2. 2nd delete the rows if where value of O is "Credit"
3. 3rd delete the rows if where value of AB os "DEL"
4. After the above step sort the entire report again with col H
View 9 Replies
View Related
Apr 30, 2014
I am trying to find some missing values compared to 6 base values. For instance, I have a sheet with some names translated to another language, I am trying to find the languages some names have not been translated too.
For example, if I have six languages, Arabic, Japanese, Russian, Chinese-Simplified, Chinese- Traditional, and Korean to compare too, I want to find any names that are not translated in certain languages.
Sample:
John Japanese
John Chinese - Simplified
John Korean
Martin Arabic
Martin Chinese - Simplified
Martin Russian
Ramon Arabic
Ramon Russian
Sam Arabic
Sam Chinese- Traditional
View 1 Replies
View Related
Jul 2, 2009
I have data similar as shown in the following:
A1A11
B2AA1122
C3B22
D4C33
D44
The idea is to add compare the cells of the first column with the third column. Where same letters/words exist, the corresponding value of the first column should be added to the second column (where no letter exists equally, the space remains empty), so it will look like this
A111
AA1122
B222
C333
D444
the third column always will have at least the same letters as the first column, but new letters/entries can occur.
View 3 Replies
View Related
Feb 11, 2013
compare some list of values in order to fiind the common values.
View 1 Replies
View Related
Aug 21, 2008
Trying to find the sum of all cells in the array described in the formula that are equal to the values inside the quotations. I used this exact (as far as I can tell) formula to find the sum of values that were NOT equal to my quoted values and it worked just fine. Any ideas why formula 'A' will not work but formula 'B' does work? I have a feeling I'm missing something simple here!
Formula A - Does not work:
=SUMPRODUCT(--('Master Lead Sheet'!$J$2:$J$10000=$B2),--('Master Lead Sheet'!$N$2:$N$10000="REJECTED"),--('Master Lead Sheet'!$N$2:$N$10000="CONDITIONED"),--('Master Lead Sheet'!$N$2:$N$10000="APPROVED"))
Formla B - Works:
=SUMPRODUCT(--('Master Lead Sheet'!$J$2:$J$10000=$B2),--('Master Lead Sheet'!$N$2:$N$10000"No Answer"),--('Master Lead Sheet'!$N$2:$N$10000"Disconnected"),--('Master Lead Sheet'!$N$2:$N$10000"Wrong Number"),--('Master Lead Sheet'!$N$2:$N$10000"EMAILED"),--('Master Lead Sheet'!$N$2:$N$10000"needs to be emailed"),--('Master Lead Sheet'!$N$2:$N$10000"Refund"),--('Master Lead Sheet'!$N$2:$N$10000"REFUNDED"))
View 9 Replies
View Related
Oct 13, 2006
I have data that looks like this:
day# id amount
1 56575 0
1 56675 0
1 56680 0
1 56683 0
1 56681 0
1 51810 0..............
How do you write an excel macro that looks at the number in the first column (day #) and finds all the duplicate id#s in the second column that are in day 1and adds the amounts together in the 3rd column then writes the first column number (day#), second column number(id#) and the third column (sum of the amounts of duplicate Id#) to an new worksheet. Then the macro would loop through day #2 and do the same thing. Notice that the values in the id column are unique in this data set below this is how I would like the data to look. I have accomplished this in a pivot table but my problem is I need a cvs file to export the final data into an external database which is why I need a macro.....
View 2 Replies
View Related
May 5, 2008
I have x coordinates in column 1 with coresponding y coordinates in column 2. From my data of x,y coordinates I want to find points with similar y values. In my data I have defined groups of numbers, i.e. I have a set of numbers with y values around 30 (+-10), then a new group with y values around 60 (+-10), and so on... Sometimes the groups are not totaly separeted, there could be a few points with y values between 30 and 60. These points can be grouped with the closest group of coordinates (30 or 60). Each group of x,y coordinates could be copypasted in the columns to the right (columns 3 and up).
So what I want to do is find a group of coordinates. This group will have at least 40 individual points +-10 from a group mean. The coordinates need to be sorted out from the data and put in seperate columns.
View 9 Replies
View Related
Oct 6, 2009
I have table values like below in sheet
Key values
ABCD
ACDE
BFGH
CIJK
CJKL
CKLM
BGHI
If i enter Value B in one of cell, it should pick values of FGH, GHI. Can this be done using formula or other methods??
View 2 Replies
View Related
Feb 18, 2009
In Excel 2007 can you do a find that contains multiple valued cells? I know you can find one at a time but it takes to long.
Example.
column A
5963190,7281143,7283188
4559000
7286000
7289696
5963499,5962468
5881017,7281176,4556060
That is my list to search in.
Here is the list to find that may contain these values.
7289696
2419888
9741452
7283188
7281176
4997755
If either match I need them to Highlight a different color.
This will not work under the duplicate format because it will not pick out what may contain it will on pick exact.
View 9 Replies
View Related
Jan 28, 2013
I am looking for a smart way to extract the values corresponding to the first and last day of a month I specify from my time series. Column B has the dates (dd/mm/yy), column A is text(B,("mmm-yy") and column E has the values I need to extract. I have a column with all the months of the year: Dec 12, Jan 13, Feb 13 etc... and I need to pick the value in E that corresponds to the last observable day of each month and divide that by the value corresponding to the first observable day of the month.
[URL]
View 5 Replies
View Related
Sep 10, 2006
I have a column (i.e. A) with hundreds of numbers. I'd like to be able to write a number in a cell (i.e. B4), and have Excel find and tell the coordinates of those cells in column A that sum up to the result I wrote in B4. I also would like to be able to "hit a key" and see the next possible set of result-cells.
View 7 Replies
View Related
Mar 11, 2014
I've got two question on how to 'find' some values in a specific data file, namely:
- the '52-Week High' of the share price (highest value of the share in that specific year)
- the value of the share price of the last trading day of that specific year (this isn't always 12/31/20..)
Simplified, my data looks as follows:
-In column 'A' I've a created a line of unique edentifiers (company CUSIP + year -> combined column D and E), in my set this column consists of 405 companies over 6 years of daily data -> aprox 460000 rows
-In column 'B' the date is shown -> its daily trading data
-In column 'C' the daily share price is shown
-In column 'D' the company's CUSIP (edentifier for specific company)
-In column 'E' the year
Sheet 1:
1.JPG
My final data set has to look something like this:
-In column 'A' the unique edentifiers (company CUSIP + year)
-In column 'B' the specific year
-In column 'C' the '52-Week High' of the share price (highest value of the share in that specific year) -> red numbers from sheet 1
-In column 'D' the value of the share price of the last trading day of that specific year (this isn't always 12/31/20..) -> green numbers from sheet 1
-> off course all the empty cells in column C and D normally contain the missing values
Sheet 2:
2.JPG
The big question is how do I manage to identify the red and green colored values (and link them to 'sheet 2')?
Regarding to the '52-Week High' (red colored in the sheets) I've already tried with the following functions:
- VLOOKUP: When I use this one I only get the most upper value, for example in the case of '8668102001' this would become '12'
- Combined INDEX/MATCH: The same problem as with VLOOKUP
- IF-function:In this case I get the max value of the entire 'C'-column, in the example of sheet 1: this would be '115'
Regarding the value of the share price of the last trading day of a specific year, I couldn't manage to come up with one.
View 5 Replies
View Related
Apr 18, 2014
I have 6 columns containing Agency IDs of different years. I would like to know which agencies appear in all the years. That is which agencies were funded for all the years. How can I do this? perhaps Vlookup?
View 11 Replies
View Related
Apr 29, 2014
Bonus calculation model...
100% of sales target = 100% of bonus
110% of sales target = 125% of bonus
120% of sales target = 165% of bonus
etc...
How can I solve for someone who books 107% of sales target?
View 4 Replies
View Related
Oct 4, 2008
Column B: The day of the week that the date is, Sunday, Monday, etc.
Column C: Every date in 2008
Column D: my data (the number to be averaged)
What I need the formula to do:
Look in Column B for every instance of "Sunday", and count them up (they count only if the data in column D is not 0) and use that number to average the number in column D
So if I have 52 Sundays and we are only 4 weeks into the year and the 4 data points are 50, 100, 75, 25. The average will be 62.5 instead of 4.8 (divided by 52).
View 6 Replies
View Related
Nov 23, 2013
I have a huge data base (daily temperatures dating back to 1872), but for simplicity I have limited the values to just a month for this question. What I want to do it list the ten warmest temperatures and then return the date in which it occurred. I am using the following LARGE function to get the ten warmest temperatures (Column B - high temperatures).
=LARGE($B$2:$B$31,$D2)
Column B = High Temp in Data Table
Column D = Rank in Results Table
This works great. However when I use the following Index function to get the date in which it occurred (Column A - Date), it keeps returning the first date in which it occurred when there are multiple occurrences of the same temperature.
=INDEX($A$2:$A$31,MATCH(LARGE($B$2:$B$31,$D2),$B$2:$B$31,0))
Column A = Date in Data Table
Column B = High Temp in Data Table
Column D = Rank in Results Table
In some cases, I have more than 2 occurrences of the same temperatures. For example, the high temperature of 23 degrees occurs 3 times during the month (1/4/2013, 1/11/2013, and 1/12/2013), but only the first one 1/4/2013 shows up in my results table. I would like the other dates to show up. These tables are listed below.
Data TableResults Table
DateHigh TempRankHigh TempDate
1/2/201311271/5/2013
[Code].....
View 3 Replies
View Related
Apr 16, 2009
I don't know if this is even possible via VBA, but thought I'd throw it out there and see what you guys think.
In the attached workbook column D contains fields for the UsagePN. If a part is "rolled-up" in our order system to a new part number, then the OLD part number, (in colmn B), is associated with a UsagePN. What I need to do is, for every part that has a UsagePN in Column D, I need to find that part number in column B and add the inventory values from the old part number to the new one.
For Example:
Row 74 in the attachment has a UsagePN of AC1230105V. I need to take the values from cells G74 thru K74 andd ADD them to the existing values in G77 thru K77, which are the inventory values for AC1230105V.
In other words, since part number AC1230105 has rolled-up to the new part number AC1230105V, I need to add all of the inventory to the new part number, AC1230105V.
After this is done for ALL parts with a usage part number, I will delete all the rows containing UsagePN's.
Here's the catch, as you can see in the example, there is no consistency, so a simple formula like adding the values to the same part number with a "V" at the end won't work. I guess you would have to do something like for each row with a value in column D, dim that row so you can refer back to it, then search for a match in column B, insert a "helper" row below where the part number is found to add the quantities, then replace the original with the helper row, and then delete the helper. One of you guys probably knows a better way.
Keep in mind that you won't always find a match, so the macro needs to continue to the next part if no match is found.
I hope I was clear. This one looks like it will be a nightmare, but I hope somebody on here has some ideas.
View 14 Replies
View Related
Jun 8, 2009
I've got a piece of code I'm stuck on. Basically, I have a range of data and most cells are empty. However, I want to replace all the nonblank cells with an X. I can't quite figure out how to have the code replace something non-specific...
View 3 Replies
View Related
Aug 2, 2009
1) Find strings "CA", "Ca", "ca", "California", "california", "cali", "Cali" in Column F
2) If the string is found, then record the Real value at Column D of that row in Column I
3) Continue this until there are no more strings in Column F
4) Add the values recorded in Column I and display the final value at the bottom of the list.
View 2 Replies
View Related
Dec 5, 2012
I have a spreadsheet that has a number of values and a separate sheet that has a paid total. What would be the easiest way to find the values in sheet 1 that total the sum in sheet 2? There may be more than one possible solution as to the items that total the sum.
View 1 Replies
View Related
Dec 13, 2012
I am looking for a VBA code which will search for a last empty row in given range, and sumup all the values above it in the same cell...this has to be repeated for other columns as well. The column and row numbers may vary depending on another code. In any case the summation has to be in the last empty row...The excel table should look like as below, the last row is summation of all the values above..
A B C D
1 - - -
2 - - -
- 3 - -
- 4 - -
- - 5 -
- - 6 -
- - - 7
- - - 8
3 7 11 15
View 2 Replies
View Related
Nov 20, 2013
I have store numbers (#3, #5, #6) (not consecutive, but ascending right to left) across the top, and dates (11/19/2013) ascending down the left.
I know date, I know store number. I need VBA code (excel 2000) to move cursor to the intersection of the two.
View 9 Replies
View Related
Jan 19, 2007
i have in the range (Ag1:an1)the names of the months from january- august)in the range (Ag2:An55) ihave numbers in every cell now in every row for example Ag2:An2 i want to find the values less than 50 then i want to write thier month's names in the cells from Ap2:Aw2 i want to do this with every row from row 2 to 55
View 9 Replies
View Related
Mar 6, 2008
Can you use a V-lookup to find several different values and then sum them?
View 9 Replies
View Related
