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.

ADVERTISEMENT

Referring To Array And Adding / Removing One Preset Item

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

If Statement Limit Workarounds: Convert Various Codes From One Column Of Spreadsheet Into Different Codes In Another Column

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

Sum Of A Column Based On Different Account Codes

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

Sumif Based On Preset Grouping

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

Adding In Extra Spaces For Post Codes

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

Adding Values (currency) From One Column Based On Value Of Another Column (Categories)

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

Adding Values From One Column Based On Criteria In Another Column?

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

Insert Columns Based On Tab Description

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

Description Column In Pivot Table

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

Message To Appear Prior To Printing Based On The Description Choosen

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

Segregate Data Based On The The First String Of Each Security Description

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

Auto-Populate Items From Master Reference Chart Based On Job Description

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

Tally Report Of X: Count On Column B (project $) Depending On The Criteria On Column A (project Description)

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

Excel 2010 :: Adding Together Separate Currency Rows Based On Adjacent Column Value?

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

Add Numbers Based On Duplicate Codes Only

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

Zip Codes To Be Pushed To The Next Column

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

Display Data Based On Key Codes Entered?

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

VBA Codes To Show Sheet / Tab Based On Data Validation

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

Lookup Moving Dates Based On Alphanumeric Codes

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

Plotting Surrounding Zip Codes Based On Longitude & Latitudes

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

Find Numeric Codes In A Column And Replace In Vba

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

Excel 2003 :: Get Sums Based On Codes In Dropdowns By Week?

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

Macro To Find And Add ISIN For A Company Based On Stock Codes

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

Find The Part Codes Having Some Indication And Arrange Them In A New Column

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

Preset Tab Locations In Workbook

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

To Sum Cells Until Preset Value Is Reached

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

Dropdown For Preset Filters In Pivot

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

Transferring Data Into Preset Table?

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

How To Extract Data With A Preset Time

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

Dividing To Get A Result Of Whole Numbers Preset

Dec 11, 2009

Does anybody know the code or the formula to divide a number generated by a formula. Id like to divide this number by 2 different set numbers. Meaning if my number comes out ot 19, id like to divide this by 5.5 and if it's not a whole number which this isn't (it's 3.45), i'd like to round this number to a whole number and add 2.5. My end answer would be 3 5.5 and 1 2.5.

Windows XP
Excel 2003

View 9 Replies View Related

When A Drop Down Option Is Selected, Can It Show A Preset Value In Another Cell

Jan 14, 2009

If I have a dropdown with

A (value 1)
B (value 2)
C (value 3)
D (value 4)

and a person selects one of those letters in the dropdown in column 1, I would like column 2 to show the number value associated with it

View 14 Replies View Related

Add ‘country Name’ Column To Worksheet From A List Of Country Codes.

Apr 2, 2009

I've got a worksheet with five columns of data. One of the columns contains country codes. I have a list of countries that the codes relate to. I would like a new column in the worksheet that contains the country name. I'm not sure if this is possible, but it seem that most things are with VBA! The countries and their codes are on 'Sheet2' I relalise I might have to do some boring re-ordering of the data on 'Sheet2'

View 2 Replies View Related

Adding Column Widths Of Merged Area To Match Single Column Width

Sep 19, 2012

I needed to match the width a merged area of seven columns to a single column width (for row autofitting). Adding the column unit values and setting the single column to that value produced a significantly narrower width.

The documentation mentions that the column width unit is scaled to the font type and size and the absolute width is given in points.

This is set by the normal style setting in Excel Options or by VBA application.standardfontsize = 8 (in this case).

For instance, ten columns of Arial font 8 at 8.5 units you would think to be equivalent to a single column of 85 units.
In points, the difference is 420 vs. 386.25, or 33.75 points.

Well, the standard character zero has a width at this setting of 4.5 points and 1 unit is 8.25 points, leaving 3.75 points for margins.

Then (10-1) margins allowances time 3.75 points resolves the difference.

Determining the margin allowances is straightforward, and reveals that the gradation with size is stepped by MS design.
For instance, sizes up to 11 use 3.75 points for margins and increasing points for characters (except between 9 & 10).
Sizes 12 through 18 use 5.25 points, 20 & 22 use 6.75, 24 & 26 use 8.25, etc.

I have created a table for this purpose, however I rarely use a "normal" other than 8, so I can probably use that set in programming.

View 1 Replies View Related

Adding Column Minus Rows That Have Data In Another Column

Feb 15, 2007

I want a cell on one spreadsheet (SP-A) to add up a column (M) in another spreadsheet (SP-B) rows 11 through 10000.

Here's the thing, I want that cell to actually display not the sum that it gets but that sum minus all numbers (in M column again) which column E is filled out with any data for their given row...

i'm not even sure if this makes sense lol... let me use example

on SP-B there's a column M.
in row 11, value = 3
in row 12, value = 5, in this same row column E is filled with whatever
in row 13, value = 2, in this same row column E is filled with whatever
in row 14, value = 6

in my SP-A I need the cell to display 9, since rows 12 and 13 have values in E and I don't want to add those to the sum.

View 9 Replies View Related

Adding ROW Using 1 Column As Reference And Another Column For Naming

Nov 19, 2013

I Basically need to use DATA in Column D of my file to add a ROW and then use Column C to name that new ROW added...

Example: [URL] ........

View 2 Replies View Related

Macro To Preset Field With Text If Condition In Another Field Is True

Jun 9, 2006

I have a macro that imports a report. If the Charge Type in column A is BTOREPLX, I need to prefill the field next to it in column B with the text "REPLX", e.g. "REPLXCDROM". I've attached an example of the report.

View 5 Replies View Related

How To Combine The Description In One Line

Jun 17, 2014

Attached is a file containing stock codes and description. The stock code is repeated but i would like to have one line description per stock code.

View 4 Replies View Related

Lookup In A Description (color)

Mar 13, 2009

I have a column with a description that includes color in the description, I want to sort by color with breaking up the description, is there a formula that will look at the description such as "Am Cannoe black tecan" and in the cel next to it come back with "black". Then I would be able to sort by color column, otherwise I would have to text to column and break up the description, etc,,, ect....

So column a1 has the description, I want a formula that will put in column b1 "black" I have 3 colors, so I can do one at a time or add to same formula.....

View 6 Replies View Related

Sum Turnover Where Name Is PART Of The Description

May 14, 2009

I am looking for a way to use the sum.if formula, combined with the find.spec (I am not sure if this is the correct formule, at the moment I only have the Dutch Excel version, it is meant to find a word as part of a cell)

The part I am struggling with is how to find the correct cells to "sum" when the criterium is part of the cell contents. e.g. Paul is the description in am looking for in "Paul is riding his bike" If so, then add the turnover to Paul. I have made a demo to clarify what I mean.

View 4 Replies View Related

ADVERTISEMENT