Reformatting Data - Subtotaling Brings Numbers Down To One Line
Apr 2, 2013
I have several thousands lines of data....much of the data is the same, 2-5 rows per person, but at the last two columns is different numbers/totals....I'd like to get those all into their own column so that each person has one row with all the data...I USUALLY would sit and sort by each total, shift them over to the right into their own columns, then sit and shift them all up to one line...but thats a carpal tunnel project and i know there must be an easier way....subtotalling brings the numbers down to one line once I have shifted each one over, but not the rest of the data....
Pic Attached: Excel problem pic.JPG
View 8 Replies
ADVERTISEMENT
Apr 6, 2009
I have some data which is exported into excel by a package I can't amend. The information comes into excel in the format
00:00:04 (representing hh:mm:ss) but if I look at the formatting of the cell it says the formatting is general. I want to be able to cut and paste this into another spreadsheet and then apply conditional formatting to colour the text if the value is above 00:00:09, but try as I might, I can't! When I cut and paste (even using paste special) it still retains its previous format, and I still can't amend it
Any suggestions?
View 6 Replies
View Related
Dec 22, 2008
I need to translate my Source Data into a new format as follows: ...
View 9 Replies
View Related
Apr 30, 2007
I have a global dataset where column A represents longitude, B represents latitude, C is a spinup value which can be ignored (even deleted) and columns D to DH are runoff values for the years 1901 to 2002. The rows extend down to 6510 (including the column headers, 6509 without). I've been trying to create a reformatted table based on this data, where longitude values are listed along row 1 and latitude values are listed in column A, with the corresponding runoff values for each year correctly repositioned into the spreadsheet according to this new 'format'; there will be 102 grids created (one for each year). The longitude values are currently not ordered and will need to be sorted (low to high) into position along row 1, without causing the runoff data to become unassigned to its associated longitude and latitude coordinates.
The second part is to then perform a linear interpolation of the reformatted data for each year's table so that the data is again reformatted' from 1.5-degrees to a 5-minute spatial resolution. This involves creating tables for each runoff year as before, but this time creating new values via application of a linear interpolation between each adjacent pair of existing values (in both the x and y direction - as the data are to be imagined as being on a geographical grid with coordinates). I've currently been trying to achieve this using Fortran, but have been getting nowhere as I'm completely new to this programming language, so I thought I'd try using Excel as I'm more familiar with this, although not sufficiently so to solve this alone!
View 7 Replies
View Related
Jan 24, 2009
I have a table with three headers:
Types: close to 4,000 total cells in the column with multiple repeats
Amounts: Obvious
Names: Only 6 available names (i.e. Tom, Bill, Fred, Richard, Sam, Alex)
It looks like this:
Type Amount Name
Type 1 | $$$$ | Tom
Type 1 | $$$$ | Bill
Type 2 | $$$$ | Fred
Type 3 | $$$$ | Richard
Type 3 | $$$$ | Tom
Type 3 | $$$$ | Sam
Type 3 | $$$$ | Alex
Type 4 | $$$$ | Fred
What I want to do is create a table with the parameters using the information contained in the previous table:
Type Tom Bill Fred Richard Sam Alex
Type 1 | $$$$ $$$$ $$$$ $$$$ $$$$ $$$$
Type 2 | $$$$ $$$$ $$$$ $$$$ $$$$ $$$$
Type 3 | $$$$ $$$$ $$$$ $$$$ $$$$ $$$$
Type 4 | $$$$ $$$$ $$$$ $$$$ $$$$ $$$$
Is there any way to convert the first table to the second table? I'm using Mac OS/X
View 9 Replies
View Related
Sep 27, 2006
I have train departure times stored in rows for each date as follows:
Mon02-Oct8.399.069.4210.0910.3611.12
Tue03-Oct8.399.069.42
Each time/date is in its own cell.
I have hundreds of entries like this and what I want to do is restructure them as follows (on a new sheet). For each date and time couple I want the date in one cell and the time in the the cell to the right. Also please note that not all days have the same amount of dept. times and this also needs to be considered.
e.g...........................
View 8 Replies
View Related
Jan 9, 2007
I have a report presented in the format such as the one in the attached file.
Current Format.xls
Is there a macro that copies and pastes the data from the original report to a new worksheet with the format such as in the attached file below.
New Format.xls
The reason why i would rather not use Pivot Tables for this purpose is because the pivottable is limiting me from expanding the report to include other looked up data with the AutoFilter option.
View 4 Replies
View Related
Apr 16, 2009
I have a spreadsheet, in which I need to lookup invoice numbers from a raw data file with ~5,000 line items on a daily basis. The lookup is based on two criteria searches (1) search product type (2) search product make. In this example, I have 4 product types:
1 car
2 truck
3 boat
4 motorcycle
For this example I want to search invoices; (1) first search for cars only (2) search for product make. In my attached example, the first item (cell E2) would return invoice number 7147875-FRD from the raw data file. The second item (cell E3) would return invoice number 7147877-NSN.
View 2 Replies
View Related
Apr 17, 2009
when i position the cursor over a cell, i get muliple cells highlighted. i enter data and it is entered in the cell i want but the others being highlighted is confusing. i checked and i am not in extended mode.
View 9 Replies
View Related
May 20, 2009
In sheet1 there are three columns
A1 : PO Number (for example 111001)
B1 : PO Line (for example line # 91)
C1: Amount ( USD 50.00 )
In Sheet2 there are three columns also
A1 : PO Number (for example 111001)
B1 : PO Line (for example line # 91)
C1 : Account Code ( For example AA.00.000,725001)
Now, I would like to use a function that brings the value of C1 in sheet 2 ( The Account Code) to d1 in sheet1 taking into consideration that the PO number may repeat but the line numbers is not repeating for the same PO. Attached is a sample for better illustration
View 4 Replies
View Related
Aug 26, 2009
cell AT7 is a formula which brings back a numerical value i.e. 15.
in cell AS7 i need a formula that displays the value of cell AT7, as long as that value is less than 15, if the value is 15 or higher then 0 should be displayed.
I dont know why i cant get this, seems like a simple IF statement to me, but who knows.
Once this formula is done i need to drag it down about 2500 cells and autosum to get a column total...
View 3 Replies
View Related
Feb 23, 2013
I have the data to import / read in a pdf, in a doc, or in an Excel worksheet whichever is easier to use. I need to import the data, parse it into the correct cells for that row and then repeat the import until the end of the file. Not all the cells are in each group of data to import, so those cells will be null for that row. Some of the data for one cell may be in up to 14 lines in the data file. I have be concatenating these data rows into one cell. There are 48,000 lines in the file to import or I would do this manually. I am assuming that doing this in VBA would be the most efficient method.
View 11 Replies
View Related
Mar 30, 2013
I have about 180 workbooks which I need to compile into a Master Schedule.
All the tab 1's are different, these feed into tab 2, which the data has the same formatting throughout. The 2nd tab has the same data for A:F 1 but cells A:2 - F:2 down to row 9 are populated from tab 1, therefore different in each. I am trying to get a Master schedule that lifts the data in the fed cells into a line by line spreadsheet?
Is there a way I can get excel to look at a folder, then every workbook in it, the at the 2nd tab in every workbook, then list the cells as described above? I am not after a consolidation of this data, but a full list?
View 1 Replies
View Related
Aug 20, 2014
I need to read or clear all the line in specific range which will be increasing dynamically. Problem is "Selected File List" table range is not specified. New file chosen with browse will be added to "Selected File List" dynamically. Number of file can't be predicted.
vbaStk.JPG
What I've tried so far is, keep track of the browse button click and add the file path to arraylist. After that, (Row number 9 which is start line + arraylist size) to get the number of line of end of the table. But due to some requirement, I want to read the file from excel file. Something like -Read until found blank line or border bottom or something.
I'm new to VBA and I'm not so sure what I'm doing with vba codes
View 1 Replies
View Related
Sep 23, 2009
I have a list of invoices with their respective dollar amounts. These invoices belong to specific projects, and these projects belong to a particular customer. here's an example...
View 7 Replies
View Related
Aug 9, 2007
Looking for a way to generate the 'Top X' table listed below using formulas (not autofilters) to filter through and subtotal/rank the data. As you can see, the user will select the region, quarter, and year, and the result should be a ranking of Top X Branches for the specified time period....
View 8 Replies
View Related
Sep 19, 2007
I need to create subtotals summing an amount column and then also create a unique count of documents in a different column but on the same subtotal line. Currently, I am running the subtotal on the amount but then manually inserting the following formula on each subtotal line to obtain the unique count.
=sum(if(frequency(range,same range)>0,1))
Is there a way to automatically recieved a unique count when subtotaling without me manually entering this formula numerous times throughout my spreadsheet.
View 10 Replies
View Related
Sep 27, 2009
I autofilter a list which contains, among other fields, program numbers in column A, account numbers in column B and dollar amounts in column E. I want to display the filtered/subtotal of column E if I select a certain program. Each program has account numbers ranging from 1000 to 8999.
I used =SUM(IF()) formulas to separately display totals with account numbers below 8000, and >7999 with no problem. But when I got to create formulas to display the subtotal, I encountered difficulty.
I used the following CSE formulas to subtotal sum of column E based on the values in column B, the first formula return 0 (zero), which is incorrect. The second formula appears to ignore the IF statement, and returns the total sum instead of the filtered total.
1. {=IF($B$4:$B$112>7999,SUBTOTAL(9,E$4:E$112),0)}
2.{=IF($B$4:$B$112
View 8 Replies
View Related
Jun 3, 2006
I am trying to enter code into a Macro that will select a range and then put a subtotal at the bottom of the range.
I have values in col D & E that I am multiplying together in column F. I then want the macro to select a range from F2 to the last Value in col F and then put a total in the next cell down (in col F). The range needs to be variable.
View 3 Replies
View Related
Apr 13, 2008
I'm working with a large master list of part numbers, and matching data to these numbers from a varying data table via vlookup. I have to vlookup to the actual part number, (13,000 numbers), then subtotal the data based on the "Rollup" part number. (eg: Two different old PNs have now "rolled up" into a single part number. I am pulling sales data from all of the old numbers, and subtotaling the sales to the new number.) Too much information.
Here's my issue. After subtotaling on the rollup number, I want to copy and paste ONLY the rollup numbers and associated data to a different sheet. This should be easily accomplished by going to level 2 of the subtotals page, hiding all the old PNs and their data, selecting the remaining visible cells, then "Go To", "Special", "Visible cells". Normally, you could copy only the visible cells, and paste them wherever you want. But because of the total length of the subtotaled worksheet, (nearly 25,000 rows), I get the error message, "Micrsoft Excel cannot create or use the data range reference because it is too complex."
You can overcome this by selecting smaller ranges of data, and copying and pasting a little at a time, but since the "visible rows" will vary, I can't figure a way to incorporate this into a macro. I've tried setting specific ranges in the macro, and copying and pasting fewer cell at a time, but if the range named in the macro is a hidden cell, the macro won't select any of them.
Here's an example of what I've got so far. I realize it's not the most efficient code, but I'm working with limited knowledge and ability.
View 8 Replies
View Related
Jun 19, 2014
I have data in several columns going left to right. In column N I subtract column H from column I and then in column O I subtract column I from column J. I then use conditional formatting to indicate whether column N is greater than 45 and column O is less than 120, using different formats for each. If the cell value does not meet the condition then there is no conditional formatting used in that cell.
I want to subtotal each column to indicate the amount over or under 45 in column N and over and under 120 in column O and I would like to know if I can do it using the colors in each cell. Most rows do not meet either condition but a few rows do meet both conditions and I don't think sorting and subtotaling is not going to give me what I want.
I have about 7,000 rows in this worksheet and I am going to be removing lines and then maybe adding them back and I would like to track the balances as the data gets moved around. I can use Window Watch for that.
I am using Excel 2007, don't know how to write VBA and would really like a solution that does not use VBA.
View 1 Replies
View Related
Apr 21, 2007
I have a few hundred addresses in excel in the following format
NameAddress1Address2Address3AreaCityPin
Is it possible for me to change that into
Namee
Address1
Address2
Address3
Area
City-Pin
View 10 Replies
View Related
Apr 16, 2008
I have several non-consistent/non-sequential columns with supposed dates formatted as:
09.04.08 (day/month/year). I'd like a macro where a popup window will allow me to type in columns to execute on (ie. a,g,m,t) & then it will go through & change the dates to REAL date format 04/09/2008 (month/day/year).
If this easy enuf that any of you gurus could write a few lines?
View 9 Replies
View Related
Jan 9, 2013
I have a pivot table where I would like to add a column for the date next to each item listed in the pivot table. When I do this the pivot table sums the date, which is an incorrect number because it is adding a date to other dates. If I move the data to the row option it moves it under each item and the formatting is off. Is there a way to add the date in as a column without it sub totaling? I need the other columns to keep their totals.
I also was having issue creating a calculated field - below the total to show cost per item and then the profit. Is this possible.
I created a screen shot example to show what it is doing. (1st picture)
2nd picture shows what I would hope to achieve.
[IMG][/IMG]
[IMG][/IMG]
View 9 Replies
View Related
Jun 13, 2009
Is there an easy way of counting odd or even numbers in a row? I can't figure it out on countif, isodd, odd, count etc.
View 2 Replies
View Related
Jul 16, 2008
I have a line of numbers in one cell (B2). They look like this;
1, 21, 21, 94, 64, 2, 213
These numbers are currently in Decimal format (more on that later).
I need to "split" these numbers into individual cells and drop the comma. It would look like this;
B3 B4 B5 B6 B7 B8 B9
1 21 21 94 64 2 213
I know I can use the "text to column" function but I was hoping for a VB script to do this.
The second part is I then need to convert the numbers from decimal to Hex and add a "0" to the front of any single digit numbers.
I can do this part using standard excel formulas with no problems but if it could be added to the script then that would be great.
The final thing needs to look like this;
B3 B4 B5 B6 B7 B8 B9
01 15 15 5E 40 02 D5
View 10 Replies
View Related
Feb 14, 2008
1) I work with dozen's of tabs within workbooks, adding rows (that are numbered) etc. When I'm done and want to save the file, I usually go through this routine to make sure I'm at the top of each page before closing the file:
Ctrl-Home, Ctrl-Page Up, Ctrl-Home, Ctrl-Page Up ...through all tabs.
2) I also have to "add" all the extra line numbers (by dragging the last number I had in "row A", and filling down). I need to add the newly added rows to the "print area". Any easy way to do that?
View 9 Replies
View Related
Jun 6, 2008
Incrementing line numbers is a simple task.
If line Identification starts with
AA
AB
AC, why doesn't the same rules apply?
Highlight the cells then click and drag the corner down.
View 9 Replies
View Related
Apr 1, 2007
Col AB has numbers from xero to 110 in descending order. I need a macro that will open a blank line between the numbers 29 and 30.
View 9 Replies
View Related
Nov 20, 2008
If I number my lines of code and an error occurs, the use of Erl in the VBA will then return the exact VBA line number that the error occurred on. Is it possible to automatically capture or record that same effect when the macro process leaves its current sub and goes to another?
Reason: I'm trying to create a Call Stack that can be reported through an Error Handler that will include the exact location and process that the error occurred to better troubleshoot and understand where and why the error occurred.
The best I can come up with so far is manually putting in bookmarks along way so I know how far along the macro went before the error. From all of my searching I believe retrieving the Excel Call Stack is not possible and so one must be manually created.
Enclosed is an example of what I have so far. It goes through several macros and logs the Call Stack. Its a work in progress so it is a little sloppy looking but it is functional. If a Sub finishes it is then taken out of the Call stack.
Several "BookMarks" are placed to give an idea of how far along the Macro has gone within that Sub. The Code for the Erl example is:
Sub SampleErrorWithLineNumbers()
Dim dblNum As Double
10 On Error Goto PROC_ERR
' Errors if table doesn't exist
20 Select Case Rnd()
Case Is < 0.2
30 dblNum = 5 / 0
40 Case Is < 0.4
50 dblNum = 5 / 0
60 Case Is < 0.6
70 dblNum = 5 / 0
80 Case Is < 0.8
90 dblNum = 5 / 0
100 Case Else
End Select
110 Exit Sub
PROC_ERR:
120 MsgBox "Error Line: " & Erl & vbCrLf & vbCrLf & _
"Error: (" & Err.Number & ") " & Err.Description, vbCritical
End Sub
View 9 Replies
View Related
