My workbook contains information about investigations. Each investigation gets a number and a line in the workbook. Many infos are stored: dates, subject, ...
A dynamic table contains all those informations, which allows me to manipulate data pretty easily.
Issue: For one of the characteristics of the investigation (let's say names), several results might have to be entered. For example, one investigation can have 3 different names, the next one only 2 and another one can have 6.
This causes a problem regarding the manipulation of the data.
Right now I have 10 columns "name 1" "name 2" .... and only those who need to be filled are filled. I can't however efficiently manipulate the data. For example, George could have been entered in "name 1" for the investigation 1 and in "name 2" for another investigation. Using a pivottable, to track George I'd have to select him in all 10 columns.
It seems to me the best choice would be to somehow index the names into a single column with each name linked to its investigation number..
With the index function I have 2 issues:
- It starts by going down the 1st column, then goes to 2nd, ... where I'd want all names of 1 row then names of 2nd row.
Solved this with this formula:
I turned my "names" columns in a ranged name "Names"
but - When fields are empty I get 0s. When I try to remove them with an if statement, it'll just give me empty cells. I'd prefer to skip the empty cells.
Also, I don't know how to 'link' them to my investigation number...
I have this (dynamic) table:
investigation number name 1 name 2 name 3
001 George Paul
002 Chris George Suzy
And I want something like this:
Investigation number Names
001 George
001 Paul
002 Chris
002 George
002 Suzy
I have attached a spreadsheet. I have a data set with ID's in column A and data in columns to the right. If an ID has only one entry then there will only be a value in column B if an ID has 2 entries then there will be a value in Columns B and C, if they have 3.......... And so on....
In my attached file I have created a macro that filters each column and copies the values to another sheet. If an ID had 5 entries (B-F) I would want to have 5 rows for that ID. The attached example macro does what I want but the real data has over 50 columns. Is there a more efficient way of doing this?
Sample file attached. How do I combine the data in columns B-E into column A? Obviously in my sample file you'd use a simple cut/paste but my actual file has hundreds of columns and rows so cut/paste isn't an option. I need to move all the data into column A.
Context: My workbook contains information about investigations. Each investigation gets a number and a line in the workbook. Many infos are stored: dates, subject, ...
A dynamic table contains all those informations, which allows me to manipulate data pretty easily.
Issue: For one of the characteristics of the investigation (let's say names), several results might have to be entered. For example, one investigation can have 3 different names, the next one only 2 and another one can have 6.
This causes a problem regarding the manipulation of the data.
Right now I have 10 columns "name 1" "name 2" .... and only those who need to be filled are filled. I can't however efficiently manipulate the data. For example, George could have been entered in "name 1" for the investigation 1 and in "name 2" for another investigation. Using a pivottable, to track George I'd have to select him in all 10 columns.
I could maybe - Write all names in 1 cell, separated by something and use somemorething to virtually split that - Write each name in a row below, with the other characteristics copy-pasted (although then if I have 2 names and thus 2 rows, Excel will consider I have 2 different investigations). - ???
I am trying to combine data from multiple columns into one column. The way my data is set up there shouldn't be any columns that have data in the same row. See below for my example.
I have a spreadsheet that has identical data in column A throughout the worksheet and want to take data in columns F and G and combine them all onto one row. An example of the data is attached.
I'm trying to combine multiple columns into 1 column in Excel. I've searched a few places but most of them are tailored for a specific sheet and my VBA skills are elementary
My table looks like this (there could be more than 5 columns... it's somewhat dynamic based on the data given). Note that some columns could have no data, and the size may be different, some have 100 rows of data, while others have 5.
Firm A Firm B Firm C Firm D Firm E
[Code]...
I want to combine these columns into
Consolidated
A B B C D E
[Code]...
Is it possible to do this with an Excel formula? If not, a VBA code
I am trying to combine the data of 31 columns into one column on a separate worksheet. The number of pieces of data in each column is different and as a result I am hung up on how to code the macro so that it automatically moves to copying the next column when a blank cell is reached, as it currently copies until it reaches the 100th row and then moves to the next column even though they're blank.
Sub CopyColumns()
Dim improw As Long, impcolumn As Long, MyCell Dim ws1 As Worksheet, ws2 As Worksheet Dim improw2 As Long, impcolumn2 As Long
Application. ScreenUpdating = False Set ws1 = ThisWorkbook.Sheets("Data") Set ws2 = ThisWorkbook.Sheets("Details")
'paste location on ws2 improw2 = 2 impcolumn2 = 2
There are column headers for the data on ws1 in row 2. The other thing I would like to do if possible is copy these column headers from ws1 onto ws2 by pasting them to the left of each piece of data (impcolumn2 - 1).
I wanted to see if it were possible to combine multiple worksheets into one new worksheet, but without duplicating columns with the same heading, enabling the data to fall into the correct column if it's shared in multiple worksheets, and adding columns if their unique. Furthermore, if there are a few "leading" columns, i.e. ones that are shared in every sheet and never move from where they are, to include those as the leading columns in the new sheet. I'm unable to post an attachment, but I'll try to paint a picture. This is on a much smaller scale than intended, but I want to combine Sheet 1 and Sheet 2 to form Sheet 3 (which I've manually copied to look how I would want it to look if the formula/code works properly). Columns A, B, and C are the "leading" columns I was referencing, whereas any of the following columns may or may not be shared in the various worksheets, but should still be included and combined if they're the same. There also might be a different number of columns depending on the sheet. I consider myself somewhat of an intermediate user, and I am familiar with using VBA codes for formulas should that be the proper remedy.
combining multiple rows of related data into one row but have not seen one that matches my situation. My experience level with excel only includes some basics like vlookup, match and recorded macros, although I have used vba code for a macro that met my needs. I have a parts list for a PCB that has many different parts. Some of the parts are used multiple times across different reference designations("Reference" column in the file). I have shown a desired format and the original format below. Since the item number has blanks across the reference designators I haven't found a solution that would work for me.
I am trying to do something that would look at column B and for each value that is the same in any row in column B, it would look to column A and add that value into a text string of all those values in column C separated by commas.
example, If A1=37294 A2=37295 A3 = 37296 A4=37297 A5 = 37298 and B1 = abc B2=ABC B3=ABC B4=XYZ and B5=XYZ I want C1 to result in 37294,37295,37296. And I want C2 to result in 37294,37295,37296 and C3 to result in 37294,37295,37296 and C4 to result in 37297,37298 and C5 to result in 37297,37298.
I have two sets of data, and one colum in each set is titled ID numbers. I would like to search both those colums and combine the matching ID numbers in one row?
I have data in columns A:D with the headings Proposal, Sort_Code, Seq, Comm, and data in row 2:???, it is in the format below, / is to show a new column, I want to look at the proposal column (A) and the sort_code column (B) and then take all the data that is associated with it in the comm column(D) and combine it in column E on the first row of the proposal number. A Formula or macro would work for me.
Is it possible to combine datas from over 200 cells (all cells are from the same row) in one cell? I have tried CONCATENATE function and e.g. =A1&B2&... but is there shorter formula for this?
I have data in multiple worksheets and I want it all combined in 1 excel sheet. The 1st worksheet is named as A and all the data in column labled "date" as well as column labled "name" should be copied to master sheet. The range is not specific as vary every month. 2nd worksheet is named as B and all the data in column labled "date" as well as column labled "name" should be copied to master sheet. The range is not specific as vary every month. and there are some more worksheets like that. The start point of data will always be same but can end till any row. Can I get a code for collating all together in one sheet,
I have 5 worksheets (all formatted the same) with data inputs from row 19 to 119 and columns A to BA. After row 119 I have several sums based on the data that is being entered on rows 19 to 119, I don't want this data being copied over. In most cases all rows from 19 to 119 will not be used, so I would only like to copy the rows that have data. To complicate matters, there are times when there will be blank rows within rows that have data. For example rows 19 to 25 will have data and 26 will be blank but rows 27 to 38 will have data. The blank rows are identified by column A (Job Number) being blank. This occurs because a job is dropped from the schedule and is no longer necessary.
In the end, I would like to have a button using VBA that will first clear the data in the new sheet (the sheet that is being populated with the information) and then re-populate it with updated data from the 5 worksheets.
so basically, here's an example of what I have. The spaces in between represent different cells:
Name Corporation Type Group Vicki SchweitzerAon ConsultingBusiness GroupCouncil 1 Vicki SchweitzerAon ConsultingBusiness GroupCouncil 2 Vicki SchweitzerAon ConsultingBusiness GroupCouncil 3
What I want to have is instead of having 3 rows in this instance, I want to have a macro that looks at rows and if it finds multiple rows with the exact same name, corporation, and type, it merges the rows to look like this:
Vicki SchweitzerAon ConsultingBusiness GroupCouncil 1, Council 2, Council 3
In some instances, I have 3 rows that might match, some its 2, some its 10, it varies.
I have a set of data with 5 columns. The first column is ID. Now I have the same ID recorded several times with different data (in the other 4 columns) against the ID. I want to concatenate the data with the same ID into the same row. The data has already been sorted by ID. If it is the first time this ID appears, record the whole 5 columns. If the ID appears again, then record only the 3rd, 4th and 5th column. When I run my code, I got error 'subscript out of range (Error 9)' . It seems that the array I use has only 5 columns. But I don't know how to modify this.
VB: Sub Patient_Detail() Dim n As Integer 'index of rows to record to Dim i As Integer 'index of column to record from
I have four worksheets that all contain the same header row in row 1, but different data in the data rows. I would like to combine all the data from each of the 4 worksheets into a new (created by code) worksheet named "WS Combine". The worksheet named "Result I want 01" simulates exactly what I want the "WS Combine" worksheet to look like. Can this be done?
The header row, however, only needs to be brought over once (with all formatting intact; ie header pane frozen, yellow, centered & bold).
The Worksheet named "Result I want 02" simulates the second thing I would like to do. This worksheet basically looks at "Result I want 01" and copies ONLY the rows that are RED and BOLD and pastes these rows (along with the header row). This worksheet could be named "Red Totals"
A couple of nuances...
1. The rows that are RED and BOLD in the four original worksheets are not always in the same position. That's because they don't currently populate that way so I wanted to make this as real as possible. Therefore, ideally, code that says "just copy all data from four worksheets" would not be sufficient.
If it's not possible or too involved to have the worksheet named "Result I want 01" reorder the rows this way when copying them over, then having them in any order is fine.
2. I need to keep the font formatting of ALL the rows intact as future code will not work without this formatting retained on the two new worksheets.
3. It is possible that duplicate rows can be created (two worksheets have the same exact data) when combining these four worksheets into one. If this is the case, then either allow that to happen or simply delete the duplicate row, whichever is easier.
I have a table with multiple rows for some employees and single rows for others. Each employee has a unique identifier.
Employee IDT1T2T3T4123Net salesmarket assetsmarginsspeed to market123Net salesturnover per quarterProfitOn time delivery112Net salesmarket assetsmarginsspeed to market180No. of ordersturnover per quarterProfitOn time delivery180unit salesturnover per quartermarginsspeed to market98No. of ordersmarket assetsmarginsturnover per quarter
Is there a way in which I can restructure the data so that the multiple rows for any single employee are added into subsequent columns in one single row? e.g.
Employee IDT1T2T3T4T1bT2bT3bT4b123Net salesmarket assetsmarginsspeed to marketNet salesturnover per quarterProfitOn time delivery112Net salesmarket assetsmarginsspeed to market180No. of ordersturnover per quarterProfitOn time deliveryunit salesturnover per quartermarginsspeed to market98No. of ordersmarket assetsmarginsturnover per quarter
The table has approximately 15000 rows & c. 30 columns in the original format. I can use basic excel, but I've never dipped into macros or anything very complicated...
What I would like to do is combine all data (from sheet: Page1,Page2, and Page3) into one sheet named 'Consolidated'. Unfortunately, as you will see from the attached file, my programming and understanding of VBA programming is pretty naive.
What I am after is, when I combine the data, from all sheets into the one, the data should be
1. Automatically Sorted by Patient Name
2. Group, results of the same patient from all sheets one below the other in seperate rows
3. Delete any other rows that may have a patient name and ID, but rest of the rows (New Mole,Clinically Suspicious......Benign Naevus) are blank. (This happens because of the way I have designed my userform. For first visit the details are entered in Page 1, then for second Visit Details are entered in Page2, and for third visit in Page 3. So when I enter the first visit details, the Patient Name and Patient Number are copied into Page 2 and Page 3 even though the other details remain blank. I have done it this way because, if say the Patient comes for visit the second time, the clinician may or may not be aware which visit this would be for the patient. So as soon as they enter patient number, it will give them a message that Patient already exists. So when they click Open Existing Patient Record, the form autopopulates with any notes from their previous visit into the respective page (i.e Page 1,Page 2 or Page 3).
Page 1 (Page 2 and Page 3 are exactly the same) Patient Name Patient Number New Mole Clinically suspicious Changed from mapping Photo Level of Suspicion: Score Monitor
I am trying to combine sorted data from 2 worksheets to a new work sheet to process further. I have one worksheet with order number, part number, order qty but with different delivery dates. On another worksheet, I have the order number too, part number, the qty delivered. The qty delivered is not always the same each delivery.
My aim is to find out how many are already delivered under a certain purchase order and the balance of undelivered parts.
I used pivot tables to sort out the data but I am stuck here not knowing how to extra the sorted data from the pivot tables to the 3rd worksheet. I will need to match the order number and the part number.
I have a master workbook that has been set up to mirror the structure of a single worksheet in various other workbooks saved in different directory locations. I need some VBA code to retreive specific data from a specified worksheet in multiple workbooks which are saved in different directories and then copy the data to the master workbook, listing each data set one after another. I do not want to open any of the source workbooks to acheive this.
I attach two example workbooks to better explain:
The code has to look in various sub directories to find the relevant workbooks, (Source1) then find the specified worksheet, (Stock) and copy only rows that have data from column B to O. The data needs to be copied to the master workbook, (master) from all the source workbooks as a list with no space.
I am trying to combine several workbooks containing data in multiple sheets into a master workbook. All the workbooks have the same number of worksheets. I would like to combine all data in Sheet1 into a new Sheet1, all data from Sheet2 into a new Sheet2, etc.
I am trying to write a macro that will take data from column A and use it as a column header in column C, and then take the corresponding data in column B and list it under the column header in column C. When the value in column A changes it will move on to column D and then list the corresponding data from column B underneath it in column D until all of the data in the 2 columns is spread across the worksheet. Example:..............
I am trying to combine data from multiple worksheets and make a chart. I have about 200 keywords in every worksheet (about 50), and some of them repeat themselves through worksheets and some don't. For every keyword, I have an associated value in the next column that I want to portray over time (each worksheet is for a different period).
So what I need to figure out is how to be able to pick any 10 keywords from the worksheets and put them in a line chart where I can see the associated value for each period for every worksheet so I can compare my keywords' efficiency. The tricky part is that some worksheets do not contain the keyword and other worksheets contain the keyword in a different cell than the previous wsheet.
I have a large database of research data where the first column contains different categories (labelled as I, II, III, IV, V, and VI). For each row there is a data value in the second column that is numeric. What I need to do is move the data so that the data are displayed in 6 columns with the data values listed each column heading according to the category label. In the example I've shown there are 6 different categories, but the number of categories (and hence the number of columns in the final resulting sheet) will change for different data sets that I'm using. Also note the the number of cases for each different category is different (so that each column will not be even in terms of the number of rows of data under each column heading). I hope I've been able to explain what I'm looking for clearly.
I've attached a sample file showing the sample data input that I have on one sheet, and then the desired re-tablulated outcome on the second. I have done this using the auto-filter function and copy/paste, but this will take far too long for the larger datasets I'm working with.
I have about 20 workbooks with different file names for different projects all saved in the same folder. Each workbook has about 10 worksheets and each worksheet is named in a similar fashion in each of the 20 workbooks (eg. revenue, cost, variance etc.). I want to pull out a worksheet named ' forecast' from each workbook into a master workbook so that the master workbook would contain the 20 forecast worksheets.
Essentially need a macro to combine the columns into 1 column.
Need a Macro to put everything in each "box" into one column, for example CU is the end of the first box, CV to GP is the second box ect. Essentially a box is the row where the yellow line is to the column where the yellow column is.
The problem is the setup of these colums change, sometimes they are bigger sometimes smaller and the spaces beneth it change. Sometimes I might have a column which goes to row 18 followed by 3 blank rows then the yellow box
I first design the first colum then select that and drag across to complete the rest ( my real file has links ect )
So I need a macro in which it can scan the first column up to the yellow line then put everthing within a "box " into 1 column, each )
then repeat the same process for other boxes with each box being put in its own column ( preferablly leaving an empty column in between )
THe file itself will help clarify any problem, if this request is not possible if it could combine every column into 1 column ( along with the spaces to the yellow line ) that would help also, i could manually cut each box into another column then.
