Adding Column With Description Based On Preset Codes
Jul 13, 2006
First it should Insert a Column to the left of A. Then it should go to column B and search for the number 99 and delete all rows after it. In column C I want it to do the following formula =vlookup(b2,descriptions!$b$2:$c$250,2,false) and have it copied all the way down to the end of data.
Next I need labels added to the tops of the rows
Next I need the description columns in C to be copied to column I
Finally, beginning at J2, I would like the formula (e2 * g2)/100 entered to the end of data. Attached is sample data.
View 2 Replies
ADVERTISEMENT
Apr 19, 2014
I'm trying to automatically add and remove one criterion from an autofilter without disturbing the previous existing criteria The autofilter code sounds like this:
Code:
ActiveSheet.Range("$A$1:$L$47").AutoFilter Field:=6, Criteria1:=Array( _
"Clay", "Grass"), Operator:=xlFilterValues
How would I go about referring to this array and adding another item called "Hard" to any pre-existing elements in this array?
I plan on attaching this code to a button and use it as a filter, so when the button is pressed, the item will be added to the list of criteria, and if it is "un-pressed" the item will be removed.
View 3 Replies
View Related
May 5, 2006
I'm trying to convert various codes from one column of an excel spreadsheet into different codes in another column. I was able to accomplish this with "If" statements, however I'm only able to string together seven of these statements in one command. Is there a better way to add formulas for more than seven conversions? Below is a copy of what I've done so far with the seven converts:
=IF(ISNUMBER(SEARCH("WARN",J2)),"Warning",IF(ISNUMBER(SEARCH("PSSNAP",N2)),"Sales",IF(ISNUMBER(SEARCH("WARN",L2)),"Warning",IF(ISNUMBER(SEARCH("2699",L2)),"Warning",IF(ISNUMBER(SEARCH("4004",L2)),"Warning",IF(ISNUMBER(SEARCH("2036",L2)),"Warning",""))))))
I want to add about 15 more codes to convert within this formula but it's maxed out in the format I'm using.
View 3 Replies
View Related
Jan 3, 2014
I am trying to find the revenue generated by different groups of accounts to figure out which group is worth the most, and rank the others.Here is a attached screenshot of what I am working with, cell BK22 is the revenue of all of the accounts coded AADA but i need to find an easier way other than plugging it in for all 3500 cells.
View 6 Replies
View Related
Nov 19, 2012
I have situation where my account (from trail balance) will present to different grouping based on reporting, example below. How I can formulaize or using VBA to sum to amount based on the format.
Example :
if i want the format b : if i sumif HCM - the total should be from account code a1001,a1002,a1003 and hrd will sum up based from account code a1004,a1005,a1006
Account Code
format a
Format b
format c
format d
format e
a1001
adm
hcm
adm
adm
hrd
[Code] ........
View 7 Replies
View Related
Mar 2, 2009
I have a table of postcodes and I need to import them into a bespoke application. The aaplication requires them to have extra spaces depending on the postcode i.e.
BS35 2JW - this is fine because it has the maximum amount of characters 8
BS1 2JW - I need to add 1 space
B1 2JW - I need to add 2 spaces
View 9 Replies
View Related
Nov 5, 2013
I have a worksheet (Sheet1) that is constantly growing with information. I have several categories under the "Category" column and then the various amounts under "Amount" column. I would like to have Sheet2 be able to keep a running total of the "Amount" column for each category as it increases in entries. I've attached an example sheet.
Example.xlsx
View 1 Replies
View Related
Jan 30, 2013
If i have the following layout of data
Column 1 Column 2
January 1000
January 1234
February 1300
March 1600
January 15
March 123
April 234
January 3000
I would like a formula that adds all the January numbers together returning a result of 5249
To move this one step further i would eventually need to add these numbers based on quarters, for example if the value is January February or March in column 1 then add the numbers in column 2.
I'm sure i have done this before using a countifs maybe but my mind has drawn a blank
View 1 Replies
View Related
Apr 14, 2009
I have a macro that inserts a blank column for every sheet in the workbook. The problem i have is that i only want a limited number of columns inserted. I only want a column to be inserted if the sheet tab has the name "detail" in it. I have tried for hours to get this to work, but i keep getting errors. Here is the code i am using.
View 4 Replies
View Related
Aug 10, 2009
I often use pivot tables to summarize accounting data. I wish to summarize the data by account number, but also wish to display the account description next to each account number. Both the account number and account description are separate columns in my original table of data.
I've always managed to do this by the use of lookup formulas after the formation of the pivot table in a column outside the pivot table, but it would be preferable to have those descriptions as part of the table.
If I designate both the account number and account description as row labels, they land on two different lines.
View 7 Replies
View Related
Mar 22, 2008
I have a list of products that I want a message to appear prior to printing based on the description choosen.
Product descriptions are choosen in D25:D34 via a drop down.
My wild cards are SWING GATE and DOUBLE SWING GATE, I want to use wild cards because of the many different sizes of each gate.
Private Sub Workbook_BeforePrint(Cancel As Boolean)
If Range("B" & Target.Row) = "swing gate" Or Range("B" & Target.Row) = "double swing gate" Then_
MsgBox (Range("F5").Value) & ", Please include chain and locks with order", vbOKOnly, "Chain and Locks"
End If
End Sub
View 9 Replies
View Related
Jun 12, 2009
a macro that will segreate data based on the the first string of each security description under the heading Security description and then sum the total on a separate row in the total column.
Starting row hearder in sheet "Raw_Data" start in row 6 and i would require the same in sheet "Result
Sheet "Raw_Data ...
View 9 Replies
View Related
Dec 3, 2012
I have attached the sheet :
Certain job descriptions require certain training. Drivers needs these two particular trainings and an office employee only needs that training...
I have a reference sheet with what trainings are required for what job. I then want the next chart with each employee name and subsequent job title to have the required trainings automatically checked. (I can take care of the conditional formatting after that).
Job Desc.xlsx
View 5 Replies
View Related
Aug 28, 2007
I need to write a couple of functions. Firstly I want to do a count on column B (project $) depending on the criteria on column A (project description). For example a total count of all values in col B if project description is "P" or "A". [I can do sumif functions but this is a countif and I cannot get this right].
View 4 Replies
View Related
Dec 30, 2013
I have a spread sheet which totals the amounts in 2 columns D and E, which are pounds and pence. The formulas for each one are:
Pounds
=SUM(D3:D8)+ROUNDDOWN((SUM(E3:E8)/100),0)
Pence
=MOD(SUM(E3:E8),100)
These work very well and give me the correct figures.
What I would like to do is to add up the values of each row based on the criteria of column C and have just one value in ONE cell (as shown on lines 13 & 14), so for instance:
When added together the values of rows 3,4 & 6 are 7.25 (based on a value of "W")When added together the values of rows 7 & 8 are 5.00 (based on a value of "F")
I have tried to show this as an example in the image below:
A
B
C
D
E
[Code]....
I'm using Excel 2010 on Win 7 Pro.
View 3 Replies
View Related
Jul 30, 2008
I got some functions from some of you to spot duplicate bar codes
the first function was this =IF(ISERROR(MATCH(D5;$D6:D$65536;0));"";D5)
the second next to it this =COUNTIF($D:$D;$D5)
and the third: =IF(COUNTIF(D:D;D5)>1;"DUPLICATE";"")
well fine but now I dlike to add the numbers of the 2 cells of those duplicate codes. In some cases I have more than 2 identical bar codes.
Example
4905524490831DPFV900B.CEU0649055244908312DUPLICATE
4905524490831DPFV900B.CEU502DUPLICATE
View 9 Replies
View Related
Jan 12, 2009
City, state 29785
city, state 29767
city, state 29719
I need the zip codes to be pushed to the next column, to column 'D' to be exact. I already tried using the "text to columns" function. I selected "delimited" file type and used "2". it pushed the data to the next column everytime there was a 2. The problem is, there are 2's in the middle of the zip code sometimes. Also, it gets rid of all the 2's. Also, I can't use the "fixed width" option because there are some city/states that have more letters in them obviously, so the column is not a perfect width.
View 8 Replies
View Related
Jul 8, 2009
I am trying to do in the title so here it goes. I want a user to be able (on form 1) to enter a list of codes that refer to specific requirements (e.g. code PB114 may mean the user needs to perform a pressure test) and then excel will automatically display the requirement somewhere below. It is going to be somewhat of a database. In otherwords lets say in cell A1 you type in a code, e.g. PB114. In a range of merged cells or somewhere below that cell I want the information "Perform a pressure test" to appear.
View 5 Replies
View Related
Mar 31, 2014
Is there any vba codes that can show tab/sheet that is based on the filter selection.
For example:
Cell A1 have a data validation of Apple, orange and Mango
I have 3 tabs named Apple, orange and Mango.
So every time I select Apple on the main in page filter. Apple tab will show. and if orange then orange tab will show.
View 3 Replies
View Related
May 30, 2012
I'm currently creating a formula to look up the date based on the alphanumeric codes, my problem is if it crosses the year, say 2011 & 2012, the lookup will be distorted because codes for jan. 1 2011 & jan. 1 2012 is the same. Our alpha numeric code is A01= Jan. 1.
to elaborate further:
wanted to get the ageing days between todays date and item manufactured date:
today: May 30 2012
when the administrator enters the manufacturing date code of "A01" I have a look up of the code that equates A01=Jan 1. which will compute the difference of May 30 2012-Jan. 1 2012 and give me the Age date of 150. Now, if I enter H1, I wanted to have it look up at the 2011 not 2012 of Aug. 1; is there a look up formula that looks up the date already past (current date below), not the future date of under the same database?
View 5 Replies
View Related
Aug 10, 2007
Having the Zip Code Tabulation Module from the US Census Bureau, I have the following and much more. But based on the 3 items :
5-digit Zip Code, Longitude, Latitude.
I'm wanting to Select a Zip Code and have (let's say) several Zip Codes plotted (maybe 4 - 10) out from the center point of the map.
either with, or without a connecting straight line .
And possibly a Scalable Miliage indicator |---- 10 Miles ----| which would be based on the distance.
View 9 Replies
View Related
Oct 18, 2007
I am using the following vba code to find numeric codes in a column and replace them with the same code along with descriptive text:
Range("Q:Q").Select
Selection.Replace What:="11", Replacement:="11 - GSA"
Selection.Replace What:="10", Replacement:="10 - NYS OGS"
Selection.Replace What:="1", Replacement:=" 1 - Dist Sale"
Selection.Replace What:="2", Replacement:=" 2 - Direct Sale"
Selection.Replace What:="3", Replacement:=" 3 - No Sale: Warranty Exchange"
The problem as you might already see is that when the replacement of "1" happens, Excel will replace the 1 in the 10 and 11. Of course I want to only replace the number 1. Is there a better way to code this operation?
View 9 Replies
View Related
Jan 24, 2014
I am using Excel 2003.I need two distinct sums:
I need to calculate out the loan amount of approved loans by week (weekending on Sunday). For example for the week ending December 28th, 2013
$1,234,567,890 in total loans
I also need to have a list of sums of the loan amounts per code types of approved loans. For the week ending December 28th, 2013
$123,456.00 in total loans for Code: LTV/TLTV > Guideline max
$987,654.00 in total loans for Code: FICO > 10 pts below Guideline min
I need to ignore any loan amounts and codes for any loans that have not yet had a approved date entered and also ignore all loan amounts for any loans that are denied. I have tried doing this in many different ways with Pivot Tables and I am not getting the results I need.
View 1 Replies
View Related
Jul 20, 2014
I have trying for 2 days now to write a macro/VBA that looks up IF the stock codes (i.e., in column A and G) match THEN insert the ID_ISIN from column H in the empty ISIN column C.
or should i use Vlookup?
View 3 Replies
View Related
Feb 19, 2010
My question is how many parts havinig end with C?Also arrange these parts in ascending order in a new column. Refer to the attached file and expected result also.
View 5 Replies
View Related
Sep 10, 2007
i have a User form that i'd like to add preset tabs command locations to.
For example,
on open i'd like the active cell to be on cell M3. and as the user presses the tab button jump to the following cells locations:
P3
Q5
P7
Q7
P9
View 9 Replies
View Related
Apr 23, 2007
I have a spreadsheet with time values incremented in column A.
In column K, there are numerical values that correspond to the time in column A.
Elsewhere, a specific time is chosen from which to begin the summing of the values in column K.
I need to be able to sum the values in column K until preset values are reached. Then I need to know at which corresponding time value the summing loop stops.
For example, a time of 11:00 AM is chosen as the "start time". I want to sum the values in column K until they are >= 2.000.
At that point, I need to know what the cell value was in colum A that is in the same row as the last cell value in column K.
I can't seem to do it with simple Excel functions without falling into a self referencing loop. So what I need is for a VBA loop to lookup the appropriate values in column K, sum the values, check the total, and continue the loop until the preset total is reached. Then look up the value in column A in the last row of summed values, and return that value.
View 9 Replies
View Related
Jul 8, 2014
I have a pivot table where i would like to filter the values according to top 10, bottom 10 etc. I know that it works with using the filters in the pivot tables (Value Filters - Top 10 ...), but for making it easier and faster to use these filters, I would like to have some preset filter options as buttons or a drop down menu that immediately display them.
So that you don't need to choose the filter values yourself. I need to be able to either filter the e.g. Top2 Business Areas or within one Business Area filter the top 3 Business Groups. I thought about maybe making a drop down menu where you can choose the business areas and then another drop down where you can choose the Filter such as Top10, Bottom 10, Top 3 etc.
bSample_Pivot.xlsx
View 3 Replies
View Related
Dec 6, 2013
I have 90+ rows of data that I want to transfer into a table format on another worksheet. At present I have laboriously been copying and pasting from one to the other but am losing the will to live
Id like to create a macro to do this for me. Is it a matter of recording the macro to replicate as I copy and paste or is there a better way
View 3 Replies
View Related
Oct 27, 2009
I have a big group of test data each has a list of record of the format (time, parameter). But they have different time step, ie. the records have different number of data set in the same duration. I would like to make them the same, so I need to extract data from the records. For example, I have a list of the original record (in column A & B) as follows: ..
View 9 Replies
View Related
