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.
View 1 RepliesI 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 know there are many ways to create an "All Data" worksheet. Copy & Paste is the most obvious or pasting named ranges into the new worksheet. I have a workbook with 48 tabs with up to 1000 rows of data per sheet. I need to merge each tab into one main "All Data" worksheet.
How to combine these 48 sheets in an easier way than the two options I already know (Copy/Past or Paste Named Range). Any Add-In's to Excel that can possibly do this? I am using Excel 2010.
How to make this "All Data" worksheet combining data from each sheet of the 48 tabs would be most useful.
[Code]......
(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?
Like so:
[Code] ......
Then I could autofilter/pivottable to get:
[Code] .....
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'm using Excel 2010 on Windows 7.
I have 2 worksheets. One has Employees and the devices they have. Last name, First Name, Device each in their own colulmn. Many have more than 1 device so they have multiple entries on seperate rows.
Another worksheet has Employees and thier location. Last name, First Name, Location. Again, all on seperate columns.
It would look something like this
Sheet1
Lastname
Firstname
Device
Johnson
[Code]...
So I'm tasked with combining them into 1 sheet with last name, first name, device and location. The issues I'm having are:
1) A team member could have multiple devices
2) A last and/or first name can appear many times, so a simple Vlookup against lastname won't work - it has to somehow also compare against both.
I am working on Excel 2010. I want to find a way to link data from one spreadsheet to another one and whenever I update the first spreadsheet, the second one will be automatically updated?
View 2 Replies View RelatedWe have a master worksheet, and I cant let staff see it, but I want them to update 'their bits' themselves, rather than email the admin staff and let them update the master sheet..
every job is on its own row and contains a unique ID.
is there a way i can merge their data in to the correct row and column(s) on the master sheet?
i've been playing with datasources etc
Excel 2010
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).
View 6 Replies View RelatedI encounter the following problem which has been bugging me for weeks.
I am doing up an engineering spreadsheet which calculate the electrical cable size from node A to node B.
Col A and B goes like this:
From|To
Transformer|MSB_A
MSB_A|SSB_A1
Transformer|MSB_B
MSB_B|SSB_B1
SSB_A1|DB_A1
SSB_B1|DB_B1
So Transformer is the parent of MSB_A and MSB_A is in turn the parent of SSB_A1 and so on.
What I would like to achieve is to put these data into the following configuration:
Transformer----->MSB_A
............................|-> SSB_A1
.....................................|----->DB_A1
Transformer----->MSB_B
...........................|-> SSB_B1
....................................|-----.>DB_B1
NB: Please ignore the "dots" above.
This will give the user an overview of the parents and node relationship.
I can't seem to find a solution how to achieve this.
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.
100
xxx100
xxx100
xxx100
200
xxx200
xxx200
xxx200
300
xxx300
xxx300
xxx300
My query is regarding internet explorer 6 automation via code in VBA (Excel 2003). I'm trying to open a page on clicking a button in main page.
I'm able to open both parent and child pages via VBA, but now want to automate events (like radio btn click etc) in child page.
below is my initial code -
'========
Sub ListLinks()
Dim IeApp As InternetExplorer
Dim sURL As String
Dim IeDoc As Object
Dim i As Long
'Create new instance of IE
Set IeApp = New InternetExplorer
In Excel 2010, spreadsheet with part # in column A and customer code in column B. Many part numbers have more than one customer buying the part.
I need to rearrange the information in the example below, where there are two part numbers:
Part Cust
334 CCC
334 CHD
334 CIL
401 CIL
Looks like the layout below, where there are still 2 columns, part # and customer, but with the customer(s) concantenated in column B:
Part Cust
334 CCC, CHD, CIL
401 CIL
Can this be done with any wizard type tool (pivot table, etc)? I am sure it could be done with VBA.
I work with Excel 2010 and have a very large spreadsheet with data that I need to manipulate in several different ways. I have been filtering and then cutting and pasting but this is very time consuming . Is there a way to extract specific data from the spreadsheet and transfer it to different worksheets? I don't really know how to use macros.
[URL] .....
I have been tasked with streamlining a process to collect data from a specific online website (Web of Science) and import it into an Excel 2010 spreadsheet.
Currently they are going to the website, entering a short number of search parameters and then manually recording the pertinent data from the webpage. They would like to be able to enter a keyword in Excel (which acts as the search item) which then automatically does the rest of the process and provides them with a spreadsheet of the required data.
Is this possible? Perhaps by using Visual Basic code within Excel? I also saw a method that employed SharePoint Server 2010.
I have a word template that gives a popup when started for the user to fill out. At present this is okay, but it is hard to maintain. So what I want is to be able to add all needed information in Excel - since our tools have the possibility to export my needed info to this.
I have a spreadsheet in Excel 2010 named 'Input TR'. This info I want in the popup macro in word. When choosing name from a dropdown menu - I want Excel to give me the choices instead of having it in the coded macro. After I have choosen the name - I want the product belonging for this name in the 'Product/Service:' dropdown menu, e.g Test 1 will give the value 1...5. (I will only be able to choose one of them)
Today - everything is coded in the word2010 macro, and thus difficult to maintain.
Is there anything in Excel 2007, the spreadsheet and written data are tangled lists of numbers and its hard work sorting it out? So something like highlight current row and darken others and tick each correct row with one click would help a lot.
View 10 Replies View RelatedHow to remove duplicate in child sheet if the records for columns A, H and S are exactly matching with the columns A, H and S of Master sheet.
When I used it in some of my other process, it is not working.
[Code] ....
Attached File : Macro.xlsm
I have two columns
Req | Expiry Date
yes 11/12/2010
yes 08/06/2012
yes 02/07/2017
how do i merge both columns into one.. not losing any data hence
Req Expiry Date
yes 11/12/2010
yes 08/06/2017
I am using excel 2007 ITS FOR WORK!
I have two Spreadsheets on Excel 2003, one provided by a service supplier (i), and one from my own system (ii).
I need a macro attached to spreadsheet ii that looks for data matches between the F column of my spreadsheet, and the C column of the suppliers. Where data matches (i.e. if cell C42 in spreadsheet ii is the same as cell F23 in spreadsheet i) it transfers the contents of the A column of spreadhseet i and places it in the corresponding cell in the C column of spreadsheet ii.
Example 1 : In spreadsheet i cell C20=BN213YD. In spreadhseet ii cell F55=BN213YD. Therefore, the macro takes the contents of cell A20 in spreadsheet i and places it in cell C55 of spreadheet ii
Example 2 : In spreadsheet i Cell C75 = TN142XS. In spreadsheet ii this data is not found. The macro therefore takes no further action.
Basically, the suppliers spreadsheet has less information per row, but more entries overall. My spreadsheet is lacking a lot of suppliers references, but has more comprehensive information elsewhere. Until now i have been doing a manual search of the postcodes between the spreadsheets and rectifying them manually, but this is taking longer and longer as the spreadsheets grow.
Currently I am using Excel 2007. I want to merge data of alternate row of a particular column in Excel.Also i want to remove merged row. E.g.
Name City Pincode
John Mumbai
John 400009
Carol Delhi
Carol 110001
Carol Pune
Carol 411079
I want output to be ----->
Name City
John Mumbai-400009
Carol Delhi-110001
Carol Pune-411079
macro/formula for the above requirement..?
I have two huge database and need to test if Employee name and ID in database A matches to name and IDin databse B. What formula could I use here?
Employee ID Database A
Employee Name Database A
Employee ID Database B
Employee Name Database B
1
JONES JR,STEPHEN W
1
NULL
[Code] ...........
I created a slick little excel sheet with the data coming in automatically via Access query. It has been working fine for months. Now all of the sudden there are a bunch of cells with missing data. The weird part is it's not as if whole columns are missing data, more like 90% missing. When I go to Access and run the query all cells are populated as the should be. There have been no changes to the query at all during this time.
btw...I am running Office 2010
Pivot Chart. I would like to set up something to where a user can click on an individual value on a pivot chart (currently a line chart set up with 4 data series) and somehow display some underlying data. I have a lot of information stored in a data worksheet that I can't display all at once, but if a user sees a questionable data point, he/she can click and learn more about it from source data, or even a new query of the data worksheet.
I am using Excel 2010
I have a question about using conditional formatting in excel (2010); I made a table with the following columns:
"Supplier Name" "Supplier Lead Time" "Internal Lead Time" "Total Lead Time". I made two different scenarios to show different supplier lead times and different internal lead times, and used the minimum function in the "Total Lead Time" column to find the smallest total lead time to select the best supplier.
The last thing I would want to do with this set of data, is plug in a formula that would somehow indicate which supplier corresponds to the shortest total lead time (which supplier has the smallest supplier lead time). I'd like to be able to use a formula that enters the name of the supplier in a designated cell, which I could indicate as the "Preferred Supplier" cell. If this is not possible perhaps there is a way to highlight the supplier's name with conditional formatting?
I have one excel 2010 workbook with 5 work sheets, each work sheet contains a list with first/last name(one column) and the company name, some have a 3rd column with their email address in each sheet represents each year starting at 2008 thru to 2013 i have to find out if the people that attended an event in 2008 also attended it in 2009/10/11/12/13 and if they didnt, put their name and company name onto a blank worksheet within the same workbook without using a macro, how can i do this?
View 2 Replies View RelatedI'm using Excel 2010 and I applied a Data Filter to a simple table. I then messed around with the drop downs in each column, sorting the data by different criteria. After doing this, is there a simple way to get the table to revert back to its original order/form?
View 3 Replies View RelatedI am using Excel 2010. I am a novice user.
I have a lot of data to filter / sort. I want to initially to create a filter for a column of data - which has the format similar to hierarchical paths to files. The data is a mix of text/numbers. e.g.
pathA/path_X/path_Y/path_Z/lso0_rxs_reg_254__5_0/d
pathA/path_X/path_Y/path_Z/lso0_rxs_reg_253__5_0/d
pathA/path_123/path_456/data_out_reg_17_0/d
pathA/path_123/path_456/data_out_reg_0_0/d
pathA/path_X/path_Y/path_Z/lso0_rxs_reg_255__5_0/d
[Code] .........
Doing an alphabetical sort of this date would return the following order. As you can see while each strings in unique - there are many instances where they are simialr - if you ignore the unique numeric values at the end of the string.
pathA/path_123/path_456/data_out_reg_0_0/d
pathA/path_123/path_456/data_out_reg_17_0/d
pathA/path_123/path_456/data_out_reg_4_0/d
pathA/path_X/path_Y/path_Z/lso0_rxs_reg_230__6_0/d
[Code] ......
So what I want to do is to create a filter for the strings - but ignoring the numeric bits at the end i.e.
reg_[0-9]+_+[0-9]+/d
The strings are obviiously of varying length and the number of hierarchical paths is different, so I can't split string on "/".
Similarly folder paths names can contain "_" so can't split string on this either.
As I don't know how many "/" or "-" instances there will be in the string I don't believe I can use the find function. Also as the amount of number will be different i don't think I can use =right(a1,X) either.
I may be able to search for the pattern above - as this is probabay unique - so maybe it's something like the following pseudo code:
Function GetString(txt As String) As String
With CreateObject("VBScript.RegExp")
.Pattern = "reg_d+(_)+d+//d"
GetString = .execute(txt)(0)
End With
End Function
If I do require VBA code - how do I then use this for creating a column filter? Or will I have to extract the filtered data first from the column (and its associated row data) into another worksheet to use?
Once I have the filter in place I want to create tables using the filtered data - so for example each column value above has a lot of associated data values in each row e.g
26 pathA/path_123/path_456/data_out_reg_0_0/d
32 pathA/path_123/path_456/data_out_reg_17_0/d
8 pathA/path_123/path_456/data_out_reg_4_0/d
So my table would show the name "data_out_reg" and the range of values 8-32
I have a workbook with data on sheet1 and a summary on sheet 2. I want to use two drop down boxes on sheet 2 (B3 and B6) to look up matching data from sheet 2. What formula is best to enable these two look_up values to be used? I saw an example where the two values were separated by a comma within {} i.e. {B2,B6}, but only got an error when I entered the formula into my Excel 2010 sheet.
I have previously used VLOOKUP, but this comes back saying I have to many arguments. I can attach the workbook if that makes it easier to explain.
I have a problem converting hourly data into daily data for electricity import capacity. My file has the following form:
| Connection | Date | Period | Available (capacity) | Where 'Connection' identifies the physical location of the capacity (4 possibilities, Germany -> Netherlands; Norway -> Nehterlands; Belgium -> NL; UK -> NL), 'Date' refers to the date, Period refers to hour of the day (e.g. 18:00-19:00) and 'Available' refers to the import capacity of electricity on that conncetion during that hour. I need daily aggregates and tried several things such as DSUM but I cannot manage I've shortened the attached file quite a bit because of the upload size restriction. I am using Excel 2010. I've tried some suggestions in similar topics but none worked for me.