Column A has data e.g. cat is fat xy>zzy, dog is random xyzzy, rat is a hoax.set xyz, frog is alon.23e xyz, bullseye.8 x.z
I have a look up table as below:
Col D
xy>zzy
xyz
x.z
(Note: the values in Col D do not have a set number of characters, so I cant use a Right/left Formula)
I need a lookup to look for the values in Col D within Col A and return the values in Col D. Is this possible?
All I really need is col D characters separated from the first word/words.
I have a few CSV files that sits in a folder ie C:/Data and I want to split the data in those files into different sheets. Each of those files contains rows of data with an account number. For example, I have 3 files that contains transactions and they have accounts "Orange", Apple, pear, I want to put all transations with account apple together and all Orange transaction together etc etc.
I have a column in one of my spreadsheets that I would like to split. There's currently not anything that can be used as a delimiter, nor can I use fixed width. the cells in question start with numbers and end with letters.
1.0 Develop Vision and Strategy 1.1 Define the business concept and long-term vision 1.1.1 Assess the external environment 1.1.1.1 Analyze and evaluate competition 1.1.1.2 Identify economic trends 1.1.1.3 Identify political and regulatory issues 1.1.1.4 Assess new technology innovations 1.1.1.5 Analyze demographics .... etc
I need to split this into two columns column A should contain 1.0 and column B should contain Develop Vision and Strategy . Ex: 1.1.1.2 Identify economic trends in column A then column B - 1.1.1.2 column C - Identify economic trends.
In the workbook, 3 columns of importance Column N,S and AC. Concatenate order ("S"&"N"&"AC") - combine it and show value in column AG. Column AC has either 0 or any other number (50,60,100 etc). If it is 0 I need to insert an 'F' at the end of the code in column AG, any other numbr and i need an 'O'.
Column N has contract code, this will be in the middle, usually its alphabets like RF,EF, but sometimes numbers like 21,33, etc but when its a single digit number like 6, i need it changed to 06 (need the 0 infront). Column S has exchange codes, and this has to appear in the front of the code in Column AG. Same as Column N, if there is a single digit number then put a 0 infront of it.
I need to be able to fill a cell in the same row based on values in two other cells in different columns.
Current Layout. Call these columns A, B, and C
I have 3 different states I work with, OH, IL and PA. There are different tax exemptions based on whether or not the class is RES or COM.
Conditions: State = PA, Class = RES, Tax Exempt = 1 State = PA, Class = COM, Tax Exempt = Null (not "0") For all other states, Tax Exempt will always = 1
I need the VBA code that will autofill column B with the proper Tax Exempt status based upon columns A and C.
The code I have now just looks at the first value in columns A and C then fills all of column B with that number.
I've created (with the help of jmthompson from here) a macro to pull an entire row of data and copy to another sheet (Sheet1) if column U has "YES" in the cell.
Now I need to clear the contents of cells in range B-U after the copy has occurred.
I am trying to Excel VBA for doing an AutoSum function in Excel.I am struggling as the recorded macro is giving me fixed ranges whereas the number of rows would be different each time. What I need that the Delivery Qty in Column C be a Total whenever there is a text "Results" in column B?This should happen for every row with Results as a text.In Excel the Autosum feature should take care of this but I am unable to write a VBA code to do this.
Basicalley I have a series of columns, each 5 letters together represent an order(a,b,c,d,e), the columns are repeated approximately 15 times, similar to the following;
The B/G stands for Whole Bean, or Ground, the A's are drop down lists giving those 2 options, the other cells (non-A's) are all numeric. At the end of each row, the orders need to be summed up into categories; 5lb whole, 5lb ground, 2lbs whole, 2lbs ground, 16 whole, 16 ground etc..
I am looking for a way to sum each category for all orders as necessary, in one cell adding all B's (in that row for that catagory) if its corresponding A is whole bean, in the next summing all b's if its corresponding A is Ground. The same will go for the C's D's and E's.
I will have 2 columns. In column B, will be a list of invoice numbers. Now each one starts with the "job number" (Ex. 51APGC01) then a dash and unique number per invoice. (Ex. 51APGC01-01)
In column A, will be a list of Purchase Order numbers sent by the customer so we can bill. Now, sometimes we get one PO for multiple invoices, (Ex. 03, 05, 06, 07.....)
What I am looking to do, is when I get these PO's, I enter them in and then I print a report for our billing Dept. The way the output line (cell) will read, is Job Number, then each unique number: (51APGC01-03, 05, 06, 07)
There are multiple jobs and each one has it's own report, so the job number will be dynamic and defined else where in the workbook. I can define that. It's just getting the numbers after the "-" and putting them in only.
"copy cell range based on conditions" and it didnt really get an answer. There was one that copied the info the next blank cell on that line, but im not smart enough yet to figure out how to copy it over. ( getting there though with lots of staring at code).
2 sheets. I have already created auto modules to fill in data and code,and sorted the columns so they are in line.
Sheet 1. Info : has 9 columns. So if column = 9 and the value = new. Then i want to copy the range on the columns (A:G) and then paste it on the other sheet ( Card info) as long as Column A is empty ( as in next available blank cell) ( something like a DO while worksheet("Card info").column(A) <> ""
I am self-taught (arguably by a pretty shoddy teacher), and am determined to figure *some* of this stuff out. I need a code to look at the rows on one worksheet and based on the value of column A for each row, copy cells A through E in that row to cells A through E of a blank row on a corresponding worksheet. I found a code from another post that does just what I need it to with one small exception. The code was posted by DaveGuggs and is as follows:
Basically, I need to copy and paste upon certain condition (references) and paste related-datas in another sheet corresponding with these references. Every references are organized by lines with indicators on columns.
In attached file, see: - worksheet ANALYSIS: * datas in E2, G2, D18, D19, D20, D21, D22 that needs to be copy based on reference in C2, * then need to find this reference in worksheet called GROUP SAVE, * and based on this reference, paste above cells value in corresponding "Indicators" column number
THEN, I will have to do pretty much the same with subgroup data. But I can figure out for this second step that request same manipulation.
So I have one sheet that needs to pull data through to another sheet (which is a stats summary)
I have a drop down list containing 4 options all of which have to be counted separately on the stats summary sheet. However I only need them counted when a value is input in another cell in that row.
For example: I select option 1 from down down menu, but I only want this to be counted on the stats page when I enter a date in the "date" cell.
1) 4 values contains in each row based on the values from those cells the max value will display.
2) if more than 2 cells have empty,NR or NA text means the entire row has to delete.
3) if 2 or more that means 3 cells having values the empty cell,NR or NA cell will place value with the condition of macro that is 75% of other values which is maximum among them.
I'm trying to figure out if there is a formula I could use that will calculate the average of a group cells in one column based on the condition of another column. It's hard to explain, so I will show an example. All the data is on a one worksheet and I'm trying to show totals and averages on another worksheet. Location, Days
17, 4 17, 3 17, 5 26, 4 26, 8 26, 10 26, 7
On a different worksheet I would want to know what the average days are for each location. So is there a formula that I could use that will look at column A for a specified location number and then average all the days in column B for that location? I'm using Excel 2003 and have tried using the Average(if) but with no success.
I'm looking to return the values in column A adjacent to the cells in Column B equal to "Y". The kicker has been returning only the cells where the condition is true. Column C displays the desired behavior.
The closest I've been able to get is with a simple IF statement but I'm pretty sure the answer is a far cry away from there and likely requires an array formula. I'd prefer not to use VLOOKUP or OFFSET but will if the alternative is very complex.
I'm sure this is mighty easy, but I'm hoping to take Column A which currently has names written in it in the form "Joe Bloggs" and split it into two columns, "Joe" in Column B and "Bloggs" in Column C. All names are enterred seperated by a space.
I have seen this function Quickly split data into multiple worksheets based on selected column in Excel when searching for a solution. I would very much like to use VBA to filter data and open up new files in a similar way.
spliting of main data By column criterias which start from column E1:L1 something like Pivot Table in new worksheet, but based on formula functions.Each worksheets have to based in one of this column D1:K1 headings. That also have to rapidly change with main data table.
We are an office furniture company and we want to hold a master list of all stock. The columns we require are:
Product ID Product Description Product Grade Product Price Sold
The Sold column would be a YES or NO answer, and based on that I want the data to split into two additional worksheets, one with all the unsold items and one with all the sold items. So really I need to know how to split the data based on whether the line has a YES or a NO in the sold column.
The data needs to update instantly so if I change a product from unsold to sold it then comes off the current available stock tab.
I have one column of data which I need to split into two. Those values that are bold need to migrate to column B. Have some difficulty in phrasing the question properly.
Original: A 10 0 0 2 4
Desired output: A B 0 10 2 0 0 0 2 0 0 4
* note a few formatting issues here: 1. instead of bold and not bold, they are in green or in purple 2. 0 stands for an empty cell in my data set (I tried to fill the empty cells with zeros, but they somehow inherit the colors, i.e. some zeros are green and some zeros are purple; guess that wouldn't be a problem since the desired output they'll all be zeros anyway?)
I need to split the data into worksheets (see attached) by the Advertiser column and then by the deal year and deal code columns. I need each worksheet to be named per advertiser and deal year_deal code. I took off and replaced the data since it is sensitive information.
creating a macro or implicating one into the workbook to run for future reporting.
I have tried to split using KuTool and ASAP Tool but they dont work. They can only split base on #of row. Between each of the group of my data, there is a empty row that split them. I want to split every single row from A:H to a new workbook. Why A:H becuz from A1:A8, the A5 is empty. It has to be in range, specify by me, so if the condition is met as empty from A:H, then split from there and keep going on the whole spreadsheet.
I am looking to split data in a worksheet into separate sheets based on Column C which contains Either Babycare or Homecare or Industrial or Professional. Eventually there maybe more names. If possible I would also like to name each sheet.