Breaking A Table In To New Sheets
Nov 4, 2009
I was just wondering if there was an easy way to split several rows of a sheet in to their own worksheet? Right now I have a workbook with one worksheet which looks like: ..........
And so on. Every little block signifies a different product, so in this example R1 and R2 are for one product, R4 is for another product, R6:R8 are for another product etc.
So I was wondering, can I have a function which gives each product its own worksheet and names the worksheet the value in Column 2? The number of rows for each product (and the number of products) is variable but the number of columns stay the same and there'll always be one gap between each product. This isn't an essential can't-live-without thing, right now I'm just cutting and pasting, but it would be nice to have everything automated.
View 4 Replies
ADVERTISEMENT
Aug 13, 2008
data below is in sheet1. Upon pressing a button, it'll break into
can this be done? data means any number of columns with data.
sheeta:
row1: a---001-data
row2: a---002-data
sheetb:
row1: b---005-data
sheetc:
row1: 003-data
row2: 004-data
a----001-data
a----002-data
b----005-data
c----o03-data
c----004-data
View 9 Replies
View Related
Oct 6, 2008
I have a Sheet in a workbook that has about 250 Unique supervisors in it (column A) It has about 2300 rows of employees. Is there a macro that can take the employees supervisor column (A) and break the rows associated to that supervisor into a new sheet & and name the sheet with the supervisor’s name? Can excel have 250 sheets (2003 version)?
View 3 Replies
View Related
May 24, 2009
I have scores for golf teams that I have ranked. When there is a tie, I wish to break that tie based on another number.
For example, 2 teams tie for 1st place with a score of 312 from 4 golfers. I wish to rank those scores based on a 5th player score and determine the winner to be ranked 1st and the next 2nd without disrupting the ranking order of the other teams.
View 5 Replies
View Related
Jan 4, 2010
I have a number in one cell which is between 1 and 15 inclusive, and I want to break which ever number is chosen into the sum of 1,2,4 and 8, with each digit displayed in a separate cell. In the remainder cells if any are blank I would like 0 (zero) to be displayed
e.g.
15 ----> Cell 1 = 1, Cell 2 = 2, Cell 3 = 4, Cell 4 = 8
or
9 -----> Cell 1 = 1, Cell 2 = 0, Cell 3 = 0, Cell 4 = 8
Using the numbers 1,2,4 and 8 there is only one way to sum each of the numbers 1-15 inclusive. The results of the 4 cells I'm using an if statement to display an "x" next to an option.
View 10 Replies
View Related
Apr 3, 2007
I was wondering if there was a way to break down a single cell with it's componets.
For example, if cell A1 has a sum formula containing the sum of other cells, is there a was to just copy the single cell (A1) and pasting all of its different values to multiple cells?
Like, A1 has 100 in it but it is sum up by 4 other cells each @ 25 each, so is there a way to copy A1 and pasting it to different cell to show each entry?
View 9 Replies
View Related
Jul 5, 2006
I am in the middle of designing a macro that has 5 or 10 times ran away with itself causing me to sit & wait for it to finish. I am hoping there is a graceful way out of this without going to office application recovery.
View 2 Replies
View Related
Apr 2, 2014
I'm looking for a formula to break number in to decimal . Which means for 5 digits i.e 12555 to 12.5 and 4 digits i.e 1555 to 1.5
View 4 Replies
View Related
Dec 18, 2013
For three years I've created a workbook (A) that is dependent on another workbook (B) I upkeep. When I open (A), I Edit Links and update the location to my newest weekly file to map from (B). For my 2014 (A) book, I did the same process, but when I update the values, all the links break. Only when I open up (B) with (A) will the values appear.
As a short term solution, I saved (A) with the values in there and disabled the prompt to update values, so others can see the data. This isn't a feasible long term solution since updates will be made weekly, and not everyone will want to open up (B) just to update (A).
View 9 Replies
View Related
Apr 1, 2009
I have strings of data pumped out of a database like so "!OV !IPV ABL (850) !VL SM (150) !AD !PW !QT CC (-350)" If an exclamation point is listed, then no value follows however if no exclamation point is present, then each item will be followed by a value. I am trying to break this data out into a table. I am not sure if this is even possible. I am also attaching an example.
View 5 Replies
View Related
Jan 14, 2010
I have a work sheet called 'Cross Ref data' which I want to put a keyword in column A (row 1,2,3,4,5,6,7,8 etc)
I then want to be able to run a macro that will grab the contents of A1 in WS called 'Cross Ref data' and check if it appears in column D and E in WS 'Results'
If there is a match then for the entire line within WS 'Results' be copied to a WS that already exists called "Cross Ref Results" (please see the below heading '88888***********8888' for the layout) .........
View 14 Replies
View Related
Jan 15, 2010
Sheet1, Column3 is filled with property addresses, if available (some are blank). I need two columns on Sheet2, Column3 needs to be the street number and Column4 needs to be the street name.
Because it is not 'fixed length', I can't use 'RIGHT' or 'LEFT'
I have attached a sample file. There is other data/information on the sheet, but I can process the rest without help. I will be moving:
Sheet1, Column1 to Sheet2, Column2
Sheet1, Column2 to Sheet2, Column1
Sheet1, Column3 to...as described above...
View 14 Replies
View Related
May 29, 2014
I have an excel table with over 130 sheets in it. The first 20 sheets I have a list of names(the sames names) to collect different types of information of them. From the sheet #21 until sheet#135(more or less) I have the person sheet (each sheets has their respective name) to collect even more information for each names from the list mentioned before from the first sheets. I already created hyperlinks on the first 20 sheets (sames names) that way from there I can go straight to the individual sheet. The problem is that every day I have to correct a few hyperlink because for some reason breaks. I got this error message "Reference not valid", therefore I have to edit the hyperlinlk and connect it again. Even if I save it, next day is another or the same one.
Any alternative way to create hyperlink besides click on Insert - Hyperlink - click on the name of the sheet? Could I do it using macros, visual basic or something and if I can, how to do it.
View 8 Replies
View Related
Mar 11, 2008
I have a spreadsheet with part numbers containing letters and numbers. How can I break up the part numbers to insert dashes between any letters and numbers? (example: FXF3100LA01 needs to be FXF-3100-LA-01) There are approximately 17,000 part numbers on this sheet.
View 10 Replies
View Related
Mar 18, 2008
I have another question. When address come through our DB into Excel, they are in one big block. In other words the city and state and zip are in a lump with the street address. Text to columns does not work because it does not break up the text consistently. What can I do?
View 9 Replies
View Related
Jan 14, 2010
I have a consolidation workbook with many tabs. Each tab has many links to 8 data workbooks.
I would like to eliminate the links in all of the tabs of the consolidation workbook to half of the data workbooks.
I am using Excel 2007.
I tried Data/Edit Links, and selected one workbook, but when I hit Break Link, it broke the links to ALL the data workbooks.
View 9 Replies
View Related
Jun 23, 2006
I am working on a project that involves copying data from one sheet to another, then breaking all links to the original sheet. This part of the program is working fine. The problem comes when I try to put borders around the used range of cells in the new sheet. I want the border to only go down to the last row that contains text in column A.
Currently, the program is putting a border around all the cells that have text plus those that originally contained links (all the links are not always used). So even though I have removed the links from the new sheet, usedrange still includes these extra rows. This is no good for printing, since the links spill over to page two and I dont want that second page to print if there is no data.
So I basically need to know how to fix the problem with usedrange, or instead need a way of specifying the last row that contains text in column A. I have done a search on this and tried a number of different things, however none have worked so far.
View 9 Replies
View Related
Nov 15, 2006
I have a cell C2 that has the following data in it
C2= 4 Farmstead Way
I want cells
C3= 4
C4= Farmstead
C5= Way
View 2 Replies
View Related
Jun 21, 2014
I am trying to break ties in the Rank function.
Example worksheet attached.
I have an eight digit number (a digit can show up more than once) and determine the rank of the sum of each number. I had the ties broken by whatever digit showed up first.
In the following example, June 13th row showed 6 digits tied for second place and the tie was broken by the order in which the digit show up in the range.
On the June 14th row it showed digit 4 and 5 tied with 3 occurrence each (running total for the two days). But digit 4 came in twice that day, and reach a total 3 first before digit 5, but Rank2 shows that digit 5 was ranked first, which should of been digit 4.
View 14 Replies
View Related
Dec 2, 2008
I have a macro that cleans up data on one sheet, text-to-columns, flip rows of data, etc. Another sheet references the cells AFTER the data has been cleaned up. However, I can't put the cell references in from Sheet1 to Sheet2 before I run the macro because it breaks the references.
Just a simple example, Suppose cell A1 contains the formula: =Sheet2!B5.
If I go to Sheet2 and run the macro to clean up the data, when I got back to Sheet1, cell A1 contains: =Sheet2!#REF!
The idea is that this is a template, and the user can input aggregate data, run the macro to clean it up, and then go to the other sheet. I can easily create the macro to add copy/paste between the two sheets, but I'm looking for a cleaner way to do this.
View 4 Replies
View Related
Feb 21, 2014
Data layout in this excel sheet. I need to break it up properly in order to have a better way to import it into SQL. Is it a good idea to cram multiple data points into each cell. From the DB POC column, i need to separate that which is before the "/" from that which is after the "/" and put each item into it's own row. So there needs to be a new column for "DB" and a new column for "POC". The value before the "/" will go into the "DB" column and the data after the "/" goes in the POC column, with one new row for each item.
View 9 Replies
View Related
Jan 22, 2008
I am trying to record a macro so i can click a button and have excel break the link between my charts and the tables that drive them. during the record phase, i click on each chart and use F9. the problem i am facing is that now my macro sets the charts to the data that was there when i recorded the macro, erasing any changes i have made.
View 9 Replies
View Related
Jan 13, 2013
I have this text at J22 and i would like to break out 13 and 95 000 out of this:
<td><span>13</span></td><td class=""right_align""><span>0-0-2</span></td><td class=""right_align""><span>95 000
The numbers and lenght of the numbers where 13 and 95 000 are may vary in lenght. I need them to parse the values at those spots to separate cells.
Im using excel 2010
View 12 Replies
View Related
Jun 12, 2013
I have a master workbook (that I will call 'A') with a number of links to other workbooks ('B' to 'Z'). 'A' is designed to collate and display the data held in workbooks 'B' to 'Z'. The links all work fine until the 'B' to 'Z' workbooks are updated with new data, at which point, the links break with a "Error: source not found" error.
This is caused by the process by which we update the 'B' to 'Z' workbooks. We have to delete them, and replace them with new files, that have the same name.
Is there any VBA code or something that I could run that will update the links to pick up the new files?
View 2 Replies
View Related
Jan 28, 2010
my IF statement is breaking logic in picking the right strcode value.
View 2 Replies
View Related
Oct 25, 2011
I would like to refresh all external links to other xl files (on a network drive) and break all links
I have a code like this:
Code:
For Each l In ActiveWorkbook.LinkSources
Workbooks.Open Filename:=l, updatelinks:=1, ReadOnly:=True
ActiveWorkbook.Close False
Next l
For Each l In ActiveWorkbook.LinkSources
ActiveWorkbook.BreakLink l, xlLinkTypeExcelLinks
Next l
It seems to be working, but if i go step by step (f8) it updates values but sometimes after closing the source workbook and updating next one, the previously updated values disappear...
I cannot open all files because there are many of them and they are like 40mb each.
There are no defined names in the file, except print_area
View 2 Replies
View Related
Nov 20, 2006
Office 2003 with Windows XP
This is my first post so i may not have done everything correctly. I tried doing google searches to see if anyone else has done something similar but its a tricky thing to search for. I have found pages dealing with manipulating time but none seem to be what I want
I have an Excel sheet with data
A1=date
b1=source
C1-h1 all contain data (name, location, etc, etc) but I am not interested in those.
A1 (the date cell) is formatted like: m/d/yyyy" "h:mm:ss AM/PM
[Sample]
a2 = 8/1/2006 12:20:23 AM b2 = phone
a3 = 8/1/2006 12:21:13 AM b3 = email
a4 = 8/1/2006 12:33:03 AM b4 = phone
This is what I am trying to accomplish:
I want to break each hour, whether it contains data or not and get a count of the different types of source
my desired output would be:
time Phone email
12:00AM
to 2 3
12:30AM
My problem occurs because I am unsure how to break the times down into intervals. In my head, I would create a new column and try to break the time up into an interval. So for a2 (8/1/2006 12:20:23 AM), I think I would need to break the date and time up and then determine which interval would be needed based on the time.
I was going to perform the following:
=text(a2, "h" + if("mm:ss">30, 30,00)
Basically, pull the hour from a2 and evaluate the mins/secs.
If the min/sec is bigger than 30, put a 30 in the box. Less than 30, put double zero in the box. So for 8/1/2006 12:20:23 AM, b2 would have 12:00 since it falls in the 12 to 12:30 interval.
Once I have all the intervals, I would also need to preform a count on the source cell but I think I can use a vlookup to get the count for the interval.
View 9 Replies
View Related
Jan 27, 2010
finding a function/formula for determining the breaking point (Yvalue) in a graph, that doesn't necessarely equals the maximum value of that curve!
So the curve in the sample is a stress strain curve (tensile test) of a fibre and at the end it breaks and the Y value returns to 0. I would like to determine that Y value before it is 0, so the last Y value before it returns to zero.
I thought of a formula like:
display last y value before it drops with 90%...
but I don't have a clue how to write it in maths...
View 4 Replies
View Related
Aug 23, 2007
I have a workbook with a template worksheet. I can generate new worksheets using the Template, and create hyperlink to the new worksheet (Let's call it "New"). However, if an user changes the name of the worksheet from "New" to "SomethingElse", then the hyperlink breaks. How do I get the hyperlink to be automatically updated to "SomethingElse"?
View 4 Replies
View Related
Oct 18, 2012
I am running a macro-enabled Excel 2010 file, and there is a link to another workbook that pops up every time I open it asking if I want to update. I have searched formulas and pages, and can't find where it's linking from. I searched the VBA module I've been working in, and I can't find the link. When I click Data->Edit Links->Break Links and confirm, nothing happens. It still shows up as linked, and still prompts me to update next time I open it. I'd really like to get this data link severed.
View 3 Replies
View Related
