(and about 5000 more sets with garbage page headers in between the sets of parent rows)
Basically the data spit out by machine is separated by a blank row.
Let's say I need all the data with the child row "tn" , so I would autofilter /PivotTable it for that entry, but that leaves me with a blank column A. It'd be no issue if I had the column A heading in the child rows (and the number of spaces in between is random like 2 sometimes 15+ so Vlookup/Index/Match aren't too useful). It is possible to have a function that repeats the first row until it hits a space?
I have the following table and i need to be able to present it an indented list of parent child relationship. The hierarchy can go 1000+ levels deep. How do i achieve this using VBA?
OName OPackID PPackID PName ParentID
Name1 556 556 Box1 389
[Code] .........
If there is a better way of presenting it, it isn't limited to an indented list.
I am trying to create a sheet to resemble tree structure of rows. That is by default all the parent rows must be visible and click of + sign, the row should expand to show its child rows which in turn can have child rows but appear collapsed. When I choose two rows and click on Group and select rows, I see a grouping but when I try to collapse using - sign, both 2 rows gets disabled. I want to make the first row as parent and second row as child. How do I do it?
I have data for , say - Cars, Company and Average.
I solve this upto 2 level i.e cars and companies.
But i want a third level also.
When i select Car from drop down list, using data - validation - list , in cells(1,1) , i should get all companies in Cells(1,2) and on selecting companies , i should get Average in cells(1,3).
If i do define name ,and then data validation list , and then use Indirect() function, i can solve it upto 2 level.
Eg. I can get company for car .. but how to get Average Also.
I have an excel file (see sample attached) with a hierarchy with parents and children spread across different columns and rows. However, I am trying to condense it into one column with parents and one column with children. The only way I've been able to do this is by copying and pasting parents onto children rows, which is both a manual and time-consuming process (when you have a lot of rows). I feel like there must be a way to use VBA or a formula or something to get it to work. I have attached a sample, so you can see what I am trying to do (current data and desired data).
I have searched numerous times for the answer I need, but no joy yet.
I have a spread sheet that has one column (call it 1) of data with a single entry each of 100, 200, 300, 400, 500, and so on.
I have a second column (call it 2) that has multiple entries of 100, 200, 300, 400, 500, and so on, and I want to sort so that the rows wind up with the numbers in column 1 all sorted in an accending order, but I want all the same numbers in column 2 sorted to "list" next to the same number before the next number sorts. See below for a small example of what I hope to see once it sorts. 100 100 100 100 200 200 200 200 300 300 300 300
Auto Merged Post Until 24 Hrs Passes;Unfortunately the example didn't post correctly..perhaps this will work but note that the x's are in column 1 and don't exist.
I have an organizational hierarchy dump in excel from an SAP ERP system. The format of that file is less than user friendly and I need to convert it to a flat file format so I can do pivots against it or possibly dump it into access database. The background is as follows (I am also attaching a file).
The organizational structure is multinoded/layered. Top most node is a company second from the top are divisions designated by 02DivName each 02DivName has multiple nodes assigned to it. The lowest level of the hierarchy is a department, always a numeric, always same length.
That's where it gets interesting. Each node/layer under 02DivName has a technical description and text description they get broken up into two different columns when SAP dumps them. Technical descriptions can be used to identify where in hierarchy the node falls(parent/child). For example 02A is a child of 02DivName 02B is a child of 02A 02C is a child of 02B and of course all of them roll up to 02DivName. There could be multiple 02A's under Div Name and their respective 02B's, C's D's would roll under them. So on export file it would be graphically represented 02A1 blank blank blank blank in that column and then change to 02A2 their respective 02B's would be in different columns works the same for C's and D's.
Here is another wrinkle the department is not always assigned to the lowest level grouping. I could have 02DivName dept, dept, dept, and then 02A group assigned to 02DivName. The department numbers end up on export file in the same column as fre form text descriptions for the nodes. It is staggered format.
The object of what I'm trying to do is basically convert the gibberish described above into flat file format via a vba script. I need to have node1 node1 desc node2, node2 desc, etc etc in columns the last column being a department number. So if for example I have a dept assigned directly to 02DivName directly all the other node descriptions will be blank if it's node 02A then the node1 and node2 will be populated. Any help will be appreciated. And here is the file with excel spreadsheet
I have a parent spreadsheet with raw data(with errors) and a child spreadsheet without errors. I want to merge the child into parent. (:{). I am thinking of comparing multiple columns from each sheet to ensure maximum accuracy. And when those columns match up we paste the corrected column data from child sheet to parent one. I am using windows 7 and Excel 2010.
a FORMULA (not a macro) that can be used to diplay the parent folder name in a cell? I don't need the file name or workbook name, just the folder name.
Example, My Workbook is stored in the following location: C:FilesExcelFiles
I want a fromula to return "ExcelFiles", since that is the parent folder
I have searched previous threads but found no resoultion.
I've been trying my best to create a directory structure using VBA and Excel, and have run out of patience on how best to do this. My goal is to create a directory tree containing 2 levels. The 1st level (Main Folders) will be named from data in Column A of an Excel Spreadsheet, the 2nd level (Subfolders) will be created from data in Columns B, C and D of the spreadsheet. I've been using the following code with success, but don't know how to create subdirectories:
Sub StartHere() Dim rCell As Range, rRng As Range Set rRng = Sheet1.Range("A1:A20") For Each rCell In rRng.Cells CreateFolders rCell.Value, "C: est" Next rCell End Sub
Sub CreateFolders(sSubFolder As String, ByVal sBaseFolder As String) Dim sTemp As String 'Make sure the base folder is ready to have a sub folder 'tacked on to the end If Right(sBaseFolder, 1) <> "" Then sBaseFolder = sBaseFolder & "" End If............
My Question: how would i be able to render a dynamic drop down tree in the following format (see attachment): look at the left Rich Graphics folder example: http://javascript.cooldev.com/script...mos/superdemo/
I want to create sections in excel exactely like below. I need to show user how many types systems are available and in each type how many config systems available.
I am trying to build a cup tree that automaticly move players over to the next round if they are first or second in their group of 4. What happens when first person have a result of lets say 33 and 2nd and 3d person have a result of 35 each and the 4th person 37? First person is easy but then there are 2 persons with the sam result and they play sudden death and we put in a 1 in the sd column for the winner and a 2 for the loser of the sudden death.
How do I make my formula figure that if they had the same result go look at the sd column?
Another thing is that of the 2 players advancing they have to be placed in the same order in next round, how can I achieve that?
If you look in column I row 19-22 there are players in the wrong order because the player with the better result goes in first. It should be this order: 9 11 15 16 instead. (if you are ahead in the previous round you will be ahead in the next round)
Another problem I noticed now is that when 2 players has the same lowest score the first player takes both spots as it is now and that doesn't work in real. You see what I mean if you look at player 1 in round 1 and 2 (R1 & R2), in this case it should have been players 1 2 6 and 7 moving on to round 2 and not 1 1 6 7 as it is at the moment.
(Rank is their rank from the qulification rounds where the first 4 is seeded and the rest is drawn into 1 group a time)
Klass B
* B C D E F G H I J K L M N O P Q R
2 Rank Name R1 sd Pl.
[Code] .......
Spreadsheet Formulas
Cell Formula
I7 =IF('Klass B Calculations'!F4=1;'Klass B Calculations'!C4;IF('Klass B Calculations'!F5=1;'Klass B Calculations'!C5; IF('Klass B Calculations'!F6=1;'Klass B Calculations'!C6;IF('Klass B Calculations'!F7=1;'Klass B Calculations'!C7;""))))
I have a data validation list in B31 "material type" that has 4 options (Adhesives, Metals, Backer Board, & Grout). Based on that selection F31 needs to display a list of "descriptions" that match that "material type". The list is in book "database.xls!sheet3". Material type list is Column A, Description is in Column E. I know that you can't link to an unopened workbook in data validation, & I've been trying to code it, but again the lack of expierence & knowledge has frustrated me again :P This code to get the info for the file to open
Private Function GetValue(path, file, sheet, ref) Dim arg As String If Right(path, 1) <> "" Then path = path & "" If Dir(path & file) = "" Then GetValue = "File Not Found" Exit Function End If arg = "'" & path & "[" & file & "]" & sheet & "'!" & _ Range(ref).Range("A1").Address(, , xlR1C1) GetValue = ExecuteExcel4Macro(arg) End Function....................
I have an application that uses data stored in an excel spreadsheet. Over time the amount of data has become greater, and the application more unwieldy and difficult to maintain. In order to simplify it, I am trying to create a userform that displays multiple levels of master-child table data (for example, clients-orders-items). The data is stored in separate worksheets. The lower levels must allow inserting and deleting lines, moving them up or down, and editing the data. Does anyone have an example of such a userform that I could adapt to my specific requirements?
Imagine this: * Main Category #1 Sub Title #1: "Value" Sub Title #2: "Value" Sub Title #3: "Value" Sub Title #4: "Value" * Main Category #2 Sub Title #1: "Value" Sub Title #2: "Value" Sub Title #3: "Value" Sub Title #4: "Value" Sub Title #5: "Value" Sub Title #6: "Value" Sub Title #7: "Value" Sub Title #8: "Value" Sub Title #9: "Value".................
Main categories and subtitles are in the same column. "Value"s are in the column to the right.
I want to make a list in the columns to the right. This list will only show something if the "value" is not zero. It will show in the first column right from the original list, the main category to which the subtitle belongs to and the subtitle. Something like this (imagine subtitle #2 from cat #1 and #2 and #3 from cat #2 are not zero):
Basically, the code copy contents from 4 "Child" files to a "Mater" file.
Everything works smoothly but no data is being copied from the "Child" files.
[Code] .....
The other thing I've noticed is that if a "Child" file is open, I cannot run the code. Is there a way that even a "Child" file is open, the code can copy it's contents on the background?
The code below works really great which Andy helped me with, but now I've put the textboxes that are changing in a frame. That is because I need to scroll the textboxes. That works in another form I've got, but for this form I've also got the textbox, TBSum601, which is updated from the sub UpdateTotal() (see below). BSum601 is placed on the form but not inside the frame. When I run the application it stops in Sub TxtGroup_Change() at the line: TxtGroup.Parent.UpdateTotal I think it's because of that the textboxes for the TxtGroup now are in a frame instead of as before, on the form. don't know exactly what the "Parent" does, but I think it's something I have to do with the code there.
Originally Posted by Andy Pope Class event VBA: Private Sub TxtGroup_Change() If Me.TxtGroup.Text = "" Then Me.TxtGroup.Text = 0 End If TxtGroup.Parent.UpdateTotal End Sub
Public Sub UpdateTotal() Dim lngTotal As Long Dim lngIndex As Long For lngIndex = 1 To UBound(X) / 4 ..................
I've been searching all morning through various posts on this subject, but I can't find anything that I can adapt to my needs. Here is the code I have:
ChDir "C:Documents and SettingsmynameDesktop" ActiveWorkbook. SaveAs Filename:= _ "C:Documents and SettingsmynameDesktopBook1.txt", FileFormat:=xlText, _ CreateBackup:=False
This file will be used accross multiple computers and therefore the directory will always change. What I need to hapen, is for this code to save the workbook in the same directory as the origional file. I also want the file name to refer to a cells value, as I will be having the user define the name through a userform.
When speaking with parents I want them to enter in their child's birthday i.e. 12/1/1998 in one cell and then automatically generate what grade they should be in the next cell. If the grade year cutoff date is Sept 1.