Convert Row Data To 2 Rows Based On Formula
Jul 2, 2014
Every day I collect 2 values. The data is placed in a row, with this scenario:
Day1Val1, Day1Val2, Day2Val1, Day2Val2. etc.
On any specific day, either Val1 or Val2 can be the larger value.
I want 2 rows, with the following scenario:
Row1:
MAX(Day1Val1:Day1Val2), MAX(Day2Val1:Day2Val2), etc
Row2:
MIN(Day1Val1:Day1Val2), MAX(Day2Val1:Day2Val2), etc
This is for a Min/Max (stock) type chart.
Entering the formulas for the first 2 columns and then extending doesn't work because it single steps column numbers instead of double-stepping.
I already have 5 weeks of data, (and 4 rows: 280 cells) so I don't want to painstakingly do it manually.
View 10 Replies
ADVERTISEMENT
Aug 5, 2009
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.
View 10 Replies
View Related
Jun 28, 2014
I have raw data stored in one row which I would like to break up into four individual rows.
I cannot get the formula to repeat and I don't want to manually do it.
View 3 Replies
View Related
Apr 18, 2008
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.
View 6 Replies
View Related
Mar 17, 2013
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] ....
View 8 Replies
View Related
Feb 12, 2009
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)?
View 3 Replies
View Related
Apr 20, 2014
I student data in the following format
STUD ID, Surname, Forename, Subject
E.g
0001,Smith, Stan,Biology
0001,Smith,Stan,Chemistry
0001,Smith,Stan,Physics
Etc
I would like to display it so instead of there being multiple rows per student there is just 1 row with each subject in a new column
e.g
STUD ID,Surname,Forename, Subject 1, Subject 2, Subject 3
e.g
0001,Smith,Stan, Biology, Chemistry, Physics
Is there any way I can do this quickly for hundreds of students?
View 12 Replies
View Related
Aug 5, 2008
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 .....
View 6 Replies
View Related
Mar 5, 2013
I am trying to convert some data...
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?
View 1 Replies
View Related
Dec 2, 2009
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:
Row 1: invoice ABCD-1234 Sales Rep 1: 55%
Row 2: invoice ABCD-1234 Sales Rep 2: 25%
Row 3: invoice ABCD-1234 Sales Rep 3: 20%
I want the output as follows:
Row1: invoice ABCD-1234 Sales Rep 1: 55% - Sales Rep 2: 25% - Sales Rep 3: 20%
I have attached a sample workbook with two sheets; one containing the raw data and the second, which shows the desired output.
View 2 Replies
View Related
Jan 5, 2008
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.
View 9 Replies
View Related
Feb 20, 2014
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.
View 2 Replies
View Related
Dec 12, 2013
Solution to convert data columns to rows in excel.
View 4 Replies
View Related
Jan 9, 2011
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.
Book1.xlsx
View 8 Replies
View Related
May 8, 2014
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.
View 11 Replies
View Related
Jan 8, 2014
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:
Copy Row A1:X1
select sheet 2
Paste Special > Transpose (fills A1:A24)
repeat for rows 2-365
Tried using this from a previous post, but it takes A1:A24 then B1:B24 and so on and makes them a single column - so it doesn't work unfortunately.
Sub MakeColumn()
Dim rng As Range
Dim LastRowSrc As Long
Dim LastRowDst As Long
Set rng = Worksheets("Sheet1").Range("A1")
[Code] .....
View 3 Replies
View Related
Jan 15, 2014
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.
View 5 Replies
View Related
Apr 3, 2014
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.
View 1 Replies
View Related
Jul 28, 2009
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.
View 5 Replies
View Related
Sep 11, 2009
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.
Table 1
Team 1Team 2Team 3Team 4Team 4Team 5Team 5ABABCity 12531642City 231173705City 367891125City 436251348
Table 2
City 4City 2City 1City 3Team 4BTeam 2Team 5ATeam 4ATeam 1Team 3Team 5B
View 2 Replies
View Related
Mar 27, 2013
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.
View 3 Replies
View Related
Jun 17, 2014
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.
View 3 Replies
View Related
Feb 7, 2014
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.
View 8 Replies
View Related
Jan 15, 2014
I have two columns
Column L and Column W
When I input something into Column W, I want it to turn green if it is equal to the corresponding value in Column L
So say we're on Row 50
And I put $280 in Cell W50
I want it to turn green if W50=L50
And red if W50 does not equal L 50
Infinitely in the same column
View 5 Replies
View Related
Jan 25, 2013
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?
View 6 Replies
View Related
Jul 17, 2014
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.
View 7 Replies
View Related
Jul 10, 2008
I have two columns of data as follows:
10:57:42 273
10:57:42 263
10:57:42 253
10:57:42 241
10:57:37 273
10:57:37 243
10:57:37 249
10:57:37 261
10:57:37 253
11:04:47 241
11:04:47 253
11:04:47 263
10:54:31 254
10:54:31 240
10:54:31 265.......
View 9 Replies
View Related
Sep 30, 2008
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.
Havent had much luck with advanced filter either.
View 14 Replies
View Related
Mar 13, 2014
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.
View 4 Replies
View Related
Sep 21, 2012
two things on the attached sheet.
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!
View 6 Replies
View Related
