See the attached xls. There are two worksheets contained therein, one with the original data, and the other showing how I'd like it organized.
I have rows of product data consisting of a product id followed by repeating attributes of quantity and associated price. I need to convert each product row to multiple rows of each quantity/price pair while preserving the id for each pair.
I'm trying to hard-set the value of the current cell (i.e. remove formula and keep value) if there is data entered into another cell. =IF(C14="",IF($M$5="",G13*($I$6+1),G13+($L$6/ COUNTIF(A$12:A$200,"<"&$M$5))),Set the value of this cell and remove the formula.
I have workbook with multiple sheets with full of formulas. I need a excel macro which can convert formulas to values based on multiple conditions.
The formulas are in B2 and below.This formula provides results as soon as a value is entered in C2 and one among D2 and E2 cells (see attached excel)I want a excel macro to convert the values provided by formula to actual values as soon as results are obtained.Further, when user delete data in C2, D2 and E2, i want the formula to be working again since if a user enters different data in C2, D2, or E2 the value should get updated.
I found a similar post which addresses some part of my problem but works only with one condition. [URL] ....
When I was using Excel 2000, there was an Excel add-in where I could highlight rows of information and then transpose these into columns of information. Since we have upgraded to Excel 2003, the same Excel add-in does not work and I have not been able to find a simple solution to transpose my information from a horizontal view to a vertical view or the reverse.
Or do I just have to move 53 columns (weeks) of 4 rows one cell at a time to 4 columns of 53 rows (weeks)?
i have a problem in copying many columns to to rows that originally contains data, this is gonna be really complicated so here is wt i want, let's assume i have this table .....
I have a spreadsheet of 1000 rows on my spreadsheet and I am trying to convert them to 2 columns. Below is an example
Antidiarrheal AgentsBrand or SeriesApplicationGeneric Drug NameStrengthTypeDosage FormContainer TypeVolume Antidyskinetics Brand or SeriesApplicationGeneric Drug NameStrengthTypeDosage FormContainer TypeVolume
I want it to look like this:
Antidiarrheal Agents Brand or Series Antidiarrheal Agents Application Antidiarrheal Agents Generic Drug Name Antidiarrheal Agents Strength Antidiarrheal Agents Type Antidiarrheal Agents Dosage Form Antidiarrheal Agents Container Type Antidiarrheal Agents Volume Antidyskinetics Brand or Series Antidyskinetics Application Antidyskinetics Generic Drug Name Antidyskinetics Strength Antidyskinetics Type Antidyskinetics Dosage Form Antidyskinetics Container Type Antidyskinetics Volume
Is there a formula or a suggested way of doing a macro to acheive this?
I have a spreadsheet that consists of columns of data and I need to covert it into rows depending on how the data is set up. In column A, there is a list of invoice numbers. Columns B, C, & D are as follows:
Column B: Sales Rep Name Column C: Sales Rep Number Column D: Sales Rep % Split
I want to have a single row for each unique invoice number. As an example, I have the following:
I have data which is in rows. I want them to be in 5 columns.
I am attaching the sample.
for some of the entries, there will be 6 rows: Company Name - 2nd row Address - 3 row. In this case, I want to combile them as one entry Separated by a comma & placed in one column Eg: For 3rd one in sample,
Haifa Marble & Tile 69 Garfield St Wanted Result: Haifa Marble & Tile, 69 Garfield St
If this is not possible, then suggest how to combine two columns to make it into one column separated by comma.
We receive payments in either USD or HNL. I want to be able to have a final price (last column) that puts all prices in USD. I have a reference cell on a separate worksheet (drop down inputs) that we will use to store the data for validations and the conversion rate. We use one conversion rate for all transactions for a fiscal year, so only need to update once a year. Not sure how to make this work.
I looking for a macro that will go through multiple sheets & change specific cells to values if the column header is = to value set in specific cell.
for example
I would like the macro to look at row 3 in each tab (page 1, page 2, page 3) and if the value you is equal to X (parameter input on different sheet) then change the formula to a value in row 6 & row 12 of that column.
I'm attaching an simple example that i looking for this on. The green cells are the one i would like to change to a value.
I have 12 observations per variable over 2013. At the end of each month I gathered the average value of X of said month. What I want to do is convert these observations to a list of weekly observations, where each week in January gets the value of the January observation
Example:
Month123456789101112 Value 2345678910111213
And get it like:
Week Value 1 2 2 2 3 2 4 2 5 2 6 3 7 3
and so on.
Since I've got a lot of variables I'd like to use some kind of formula.
I have A:1 - X365 filled with hourly energy prices for 2013. I need to convert these to a single column - A1:A8760. I have tried writing some code and some macros with no success.
If I were to do it manually 365 times, the macro would look like this:
I have a workbook with two sheets: Sheet 2 (CustomerVehicleInfo) contains a client database. Sheet 1 is kind of a virtual work order where the mechanic enters the name of a client (in A1) and all his info pops up in cells B4-F4, according to Sheet 2.
I used a simple VLOOKUP for that: =IF($A$1<>"",VLOOKUP($A$1,CustomerVehicleInfo!$A:$E,1),"")
Same formula for cells C4-F4.
Problem is that the client data needs to convert from a formula to static text and next time the mechanic enters a new name in A1 it needs to jump to the next row, starting at B5.
I have VBA code the extracts data from a source and puts it into excel. I need to change the format of some of the extracted data so i can use it for a VLookup formula. Reference the attached example. Columns A AND B have the extraced data which are dates. In Columns C and D or E and F I have typed what I would like the data to look like so I can use in a Vloolup formula. Is there VBA or a formula that word change the format of the dates in Columns A and B to look like C and D or E and F. The changed format could be moved to any new cells or rows.
I received assistance from NBVC for combining data from two cells. Post http://www.excelforum.com/showthread.php?p=2135144. I need to convert the formula into macro code. I thought I would be able to convert it on my own, but running into some troubles with run time errors. Here is the Excel formula, which is working fine.
Is there a function to convert the data shown in table 1 to table 2 without rearranging the columns and rows? because i don't want to use TRANSPOSE. I want a function, somthing like SUMIF with OFFSET or INDEX and MATCH or any other function.
I have cells (all in one column) containing text separated by commas e.g. (SD-299, SD-200, SD-300)
I am trying to transpose the text in these cells into rows.
VB: Sub SplitAndTranspose() Dim N() As String N = Split(ActiveCell, ", ") ActiveCell.Resize(UBound(N) + 1) = WorksheetFunction.Transpose(N) End Sub
The problem with that is that when transposing it does not shift the cells down (/ insert new rows) so I lose the data already in the cells underneath.
Also, that macro would only apply to one cell I would like to be able to apply it all the data in the specific column on my Sheet.
I have the following code which works perfectly. It needs to hide rows 3:60 based on the value in K2.
Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("K2")) Is Nothing Then If Not IsNumeric(Range("K2").Value) Or Range("K2").Value = "" Then Range("A3:A60").EntireRow.Hidden = True Else Range("A3:A60").EntireRow.Hidden = False End If End If End Sub
The problem I am experiencing is that I want to use a formula in K2. K2 must get its value from a cell on a different sheet.
If I manually delete the value in K2 the rows hide and if I manually enter a 1 in K2 the rows unhide, however I want to enter a formula in K2 that will result in either a 1 or blank cell.
I have a report that is run weekly that shows items that have been returned over the last 3 months. The report shows the original date of purchase and the return date, but not the number of days since the purchase and the return. I need to have any items that were returned over 15 days go to a new sheet and display just those rows of information.
I am wondering if I could have a formula be used against a certain row/cell containing a certain "phrase" or "number" for instance
I want E1 to read something like this = (row containing item "FF32105") (the given row from the previous statement) ( the input column ex. B) (the input value ex. *6)
Essential I want to be able to copy and past a sales forecast and have the formulas automatically (listed below the pasted forecast) calculate purchase needs. The issue is that if our forecast one month has a certain item and then the next does not then they will end up on different lines after the copy/paste. We have over 500 items so a manual adjustment would be time consuming..
Is there a possible way for the formulas to "find" the correct line to start calculating data?
formula to make data on same line.On converting data2 is above data 1. I insert a column on left . Need to use mouse right click shortcut delete manually shift cells left and then delete shift cells upto have both data on same line but should be in different cells and same sequence per attached ALSO TO REMOVE "/ -DASH HYPENS.
What I need is formula to move the data2 on same alignment and to delete the two empty rows between both data.
See annexed file for expected results.I have preference to have a formula much more.
Attached is a spreadsheet describing my problem in greater detail, but essentially i want to extract rows of data depending on quoter name. I'm fairly competent with excel formulae, but cannot get the desired results. I know nothing of VBA other than copying and pasting other's VBA projects in and fiddling with them until something good happens!! I wish to extract the data and deposit into another worksheet or better still another workbook entirely but without any breaks in the rows of data.
I want to show/hide multiple rows based on the value of a cell on another sheet.
For example,
If Sheet1!A1="A" I want to show hide rows on sheet 2 as follows:
2 Show 3 Show 4 Hide 5 Hide 6 Hide 7 Hide
If Sheet1!A1="B" I want to show hide rows on sheet 2 as follows:
2 Hide 3 Hide 4 Show 5 Show 6 Hide 7 Hide
If Sheet1!A1="C" I want to show hide rows on sheet 2 as follows:
2 Hide 3 Hide 4 Hide 5 Hide 6 Show 7 Show
In my actual example the number of rows to show hide is greater (27 rows per selection) so a range, rather than specifying individual rows would be best.
First problem: in column B, I have a formula based on contents of column A. However, the limitations of 7 nested IFs leaves me with errors - I need 3 more IFs. Is there a way around that?
Second problem: I need to show the row of the first (earliest time in column A) and the last (latest hour in column A) record for each name. I've been sorting by name and time, then manually deleteing the rows I don't need. I know there's a better way, but I can't find it!
