Create A New Table That Displays The Information By Location Instead Of Code
I have a table of information with location codes as the column headers. Each location has from 1 to 6 codes associated with it.
I want to create a new table that displays the information by location instead of code, i.e. adding all of the codes for a location into one column for that location.
I'm hoping the example will make this clearer.
On the main page, I am trying to add in the wa column all of the columns in the raw page that have a code associated with wa as the header.
To make this more complex, I can't use vba on this one.
the only thing I've got so far is a very long, very complex formula that adds together numbers generated from index/matching each entry in the second table.
something like this.
(edit changing 1:1 to $1:$1)
=IF(VLOOKUP(B$1,lookup1,2,FALSE)=0,0,INDEX(sample.xls!main_table,ROW(),MATCH(VLOOKUP(B$1,lookup1,2,FALSE),raw!$1:$1,0)))+IF(VLOOKUP(B$1,lookup1,3,FALSE)=0,0,INDEX(sample.xls!main_table,ROW(),MATCH(VLOOKUP(B$1,lookup1,3,FALSE),raw!$1:$1,0)))+IF(VLOOKUP(B$1,lookup1,4,FALSE)=0,0,INDEX(sample.xls!main_table,ROW(),MATCH(VLOOKUP(B$1,lookup1,4,FALSE),raw!$1:$1,0)))+IF(VLOOKUP(B$1,lookup1,5,FALSE)=0,0,INDEX(sample.xls!main_table,ROW(),MATCH(VLOOKUP(B$1,lookup1,5,FALSE),raw!$1:$1,0)))+IF(VLOOKUP(B$1,lookup1,6,FALSE)=0,0,INDEX(sample.xls!main_table,ROW(),MATCH(VLOOKUP(B$1,lookup1,6,FALSE),raw!$1:$1,0)))
It works, but its horribly ugly, and if the number of locations goes higher (we could be looking at going to ten location codes for one of our locations) then I'll actually exeed the 1000 characters per formula limit!
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Interpolation Given A Variable Table Location & Location Of Data Within
I am trying to develop a spreadsheet that will calculate a cost based on a matrix. I am attaching a sample of the calculation created so far. The end result is in cell M13 and is highlighted in yellow. I kind of layed the formula out in a few different cells, so hopefully it would be easy to follow. simplify this process with maybe another formula that I might not be aware of, or maybe show me how to get this done in VB code. I think VB code would be the correct way to go just not sure.
View Replies!
View Related
Parse HTML Code, Create A Table
I have a function that locates a table on a webpage and pulls the html code into one cell in a worksheet. Basically we can call this one cell a text file. I need to parse through this text file (cell A1) to create a table. This text file only has info for 1 table, the table always has 12 columns, the rows are variable. I would like it to then write back this parsed text file back into excel as a table, say starting in cell A2 on the same worksheet.
View Replies!
View Related
Create New Folders In Variable Location
I would like to create new empty folders from the list in column B. The number of folders will vary depending on how many entry in column B. I need the folders to be created in an existing folder in the "current directory" called "Shop_Drawings". The following code may be able to be modified. Sub CreateFolders() Dim MyFile As String Dim sDir As String Dim rng As Range Set rng = Sheets("Matdata").Range("B2") While rng.Value <> "" MyFile = rng.Text sDir = "CurDirShop_Drawings" & MyFile ''above is where I am having trouble...don't know the correct syntax'' MkDir sDir Set rng = rng.Offset(1) Wend End Sub I have attached a sample workbook. I have been getting by with code which requires changing the destination in the module whenever making folders in different diectories or drives.
View Replies!
View Related
Create Macro To Chart Data With Location As Object In Active Sheet
I have been trying to create a macro in excel to chart a selection of data and to output the chart on the active sheet where the data was taken (as opposed to a named sheet). So basically, I have about 300 worksheets with data, and I would like to have a button on each page that automatically charts that data when clicked, and outputs the chart to the page where the macro was clicked. However, I have not been able to figure out a relative reference that will allow me to make the LocationasObject reference simply the ActiveSheet as opposed to a specifically named sheet. See my code below, which references an output to a worksheet called "Charts". Right now, all of my charts are outputting to the sheet called "Charts", as opposed to the active sheet. Sub ConsDiscChart() ActiveCell.Offset(29, 11).Range("A1").Select Selection.End(xlToLeft).Select Selection.End(xlToLeft).Select Selection.End(xlToLeft).Select Selection.End(xlToLeft).Select Selection.End(xlToLeft).Select Selection.End(xlToLeft).Select Selection.End(xlUp).Select Selection.End(xlUp).Select Selection.End(xlUp).Select Selection.End(xlUp).Select Selection.End(xlUp).Select Selection.End(xlUp).Select Selection.End(xlDown).Select ActiveCell.Offset(0, 1).Range("A1:B1").Select Range(Selection, Selection.End(xlDown)).Select ActiveCell.Offset(0, -1).Range("A1:C24").Select Charts.Add ActiveChart.ChartType = xlLineMarkers ActiveChart.Location Where:=xlLocationAsObject, Name:="Charts" With ActiveChart .HasTitle = False .Axes(xlCategory, xlPrimary).HasTitle = False .Axes(xlValue, xlPrimary).HasTitle = False End With End Sub
View Replies!
View Related
Functions On Imported Data That Can Change Location In Table
I am trying to achieve can not be based on a range of rows or cells it must as this data is imported from a forecasting application and the location of the targets may change. Perhaps an offset to the current selection can be used some how, but have a look-see if you know where Im coming from. 'I have used this to find a target in a data range. Dim r1 As range Public Sub FindDataIn Range(r As Range, target As Variant) Set r1 = r.Find(target) If r1 Is Nothing then Msg Box target & " was not found" Else r1.Select End If End Sub 'Then I inserted and Named this procedure to find the data on various assumptions or targets - SUCH AS THE VALUE "14306". FindDataInRange ActiveSheet.Range (A1:A226), "14306" Selection.EntireRow.Select Selection.Copy Selection.Insert Shift:=x1Down Selection.EntireRow.ClearContents Then I write this again for another Target such as 14307 and it repeats The result is that it finds the target cell I get an empty row above the two rows that contain that target. (LET ME EXPLAIN WHAT I MEAN BY TWO ROWS) The thing is there are two rows containing 14306 in that range and the range is sorted ascending so that they are positioned one under the other. Each row has different forecast totals beside this number because one is an export SKU and one is a Domestic. What I want to do is combine the two rows as one with one row of forecast totals for the number rather than two. Like this: 14306big Widget Domestic 26 89 (This is combined as a new row) Instead of this: 14306big Widget Export 12 14 (These 2 rows are then deleted) 14306big Widget Domestic 14 75 Also the Forecast totals run across 12 columns (one for each month) and then there is a column for year totals of each row that needs to remain the same. Most important this here is this cannot be based on a range it must as this data is imported from a forecasting application and the location of the targets may change.
View Replies!
View Related
Macro To Create And Compile Information
Currently where I work we print out registers to keep track of student attendances. This is done by merging an excel document that contains all the details of the names, days, dates , times, student groups and rooms of each lesson (each row contains the details for one lesson) with a word document containing the appropriate list of students in each specific group along with blank mail merge fields that relate to the excel document. There are often 100 lessons a week meaning it takes an age to merge all the documents to create the registers. Instead of this method I'd like the tutor to print off their own register by opening the excel document and clicking a button at the end of the appropriate row that contains the details for the lesson they are teaching. This would then take the details from that row and generate the appropriate register on another worksheet. Importantly it would have to list the correct group of students for each lesson - the names of these groups would be listed on other sheets. Im guessing macros would be the right way to do this, but I have no real knowledge of them so alternatives would be helpful. Can anyone point me in the right direction? I've included a sample of how it would look with just one lesson in the excel doc.
View Replies!
View Related
Macro To Create A Statistics Table From Another Data Table (containing Merged Cells)
I would like to have a macro to automatically generate a statistics table (on the "statistics" tab) with the 5 following fields: Fragment names / # samples / # of failed samples / % of success / # of variations in the fragment (SNP). At the bottom of this table, I would like to have a cell with the average % of success for all fragments. The data to generate these statistics are on the "gene name" tab (please note that this name will change every time I will work on a new gene). To make things easier, I think the macro should be run from this tab. 1. The Fragment names are displayed in row #5. I use one column per variation per fragment. If one fragment has 3 variations, there will be three columns and I will merge together the fragment name cells. The fact that some cells are merged can be a problem when copy-paste to the stats table (as I would like to get rid of the merging). 2. # of samples corresponds to the number of cells in blue in column A. The number of samples can change from one report to another but is always constant in the same report. 3. # of failed sequences. In the table, I type "Failed Sequence" (if the analysis has failed) and "Missing Sequence" (if the analysis has not been done). When a sample is failed or missing, it is for the who fragment, no matter how many variation there is in the fragment, so I usually merge the cells of all variations for this failed sample. 4. % of success: this is quite easy #sample/#of failed+missing sequence for this fragment 5. # of variation is equal to the number of variations for this fragment (can be 0, 1, 2, etc.). When there is no variation in a fragment, I put '-- in all cells of the corresponding fragment on the "gene name" tab. Fragment 3 on my file is an example of 0 variation.
View Replies!
View Related
Repeating Code In A Different Location
I have some code that copies and pastes data from one cell into a range. The one cell is B4, and the range is A6:A10. This same action must be done every 30 lines (so the next time the cell is B34 and the range is A36:A40). What do I need to do to make this action repeat/loop every 30 lines?
View Replies!
View Related
Create Database Table From Multi-Column Summary Table
I want to accomplish something like this but slightly different: [url] I have the same issue but a bit more complex. In my case I have an additional amount of columns (let's say dimensions). So for the example given (sales per month) I would add two columns for 4 different regions and 5 machine types (just examples). The output (that would serve as pivot tabel input) would then have to be a 4 x 5 x 12 x 4 = 960 row database table. I have tried to accomplish it by customizing the given code but that provedto be beyond my powers ;o) and some 3rd party software [url]and [url] but non of them is doing what I'm looking for. The code I'm looking for ideally would count the number of columns and unique records per column and construct the database table from that. So I am looking for flexibility in the number of dimensions also...
View Replies!
View Related
Extract Information From Table
I am not sure how would I go about finding the last data entry in a table and using formula to extract information. Attached excel 2007 file to better understand. Need formulas for cell B20 and E20. You can use column A as a reference if it is easier as this column is counting the number of entries and the next value will simply be a +1 to the last value entered in column A(or 75 in our case). For Cell B20: Need to calculate the days difference between todays date (Cell A20) and the last entry date(in this case B5, this will change as new data will be entered). A subtraction of two dates would suffice - my problem is that I do not know how to find the last entry on the table and use it. You have to remember that table gets updated and new entry will be added so then cell B20 should give the difference between A20 and the last date entered.
View Replies!
View Related
Editing Information In A Table
I have a table on sheet 2 consisting of 120 rows and 8 columns, on sheet 1 I have a combo box and have used INDEX to display the values for each row that I select. what I want to be able to do is edit the INDEX data on sheet 1 and then have a button that will replace the old value in the table on sheet 2 with the new value.
View Replies!
View Related
Formula To Lift Information From Table
I need to find a forumula that will enable me to transfer values from a table within the worksheet. Something along the lines of if I13 = A16 then need to enter the number that appears in A17 into cell 019, if I13=B16 enter number in B17 into 019. I also need the forumula to include if the figure in 113 is less than 3 then need to enter 0 into 019.
View Replies!
View Related
Seaching For Information Between Dates In A Table
Basically I have two groups of data Group 1 is machine info and has Serial Number Build Month Commission Month Group 2 is warranty info and has Serial Number Service Claim Value of Service Claim What I need to do is for all machines built in a given month count the number of claims within a given number of months after commissioning and the total value of the claims. Eg If I have a machine serial number abc123 built in jan 07 and commissioned mar 07 I need to search the table of data in group 2 for the number of claims between mar 07 and may 07 and also the total value of those claims
View Replies!
View Related
Inserting A Table Of Information Within A Cell
I need to have a cell (Total) but I need to be able to enter the information which all the subtotals are inputted into that area and will be the subtotals of the total. I have two areas that I will need to keep a running total of for each job #. I'm not sure how to find out how to do this as I'm not that advanced with excel.
View Replies!
View Related
Apostrophe: Fill Out A Table With Some Detail Information
I use the GetObjects function to gain access to the properties of a mailbox and want to fill out a table with some detail information. The code works fine until I have a mailbox which name has an apostrophe included. Set o = getobjects("winMgmts:!\VS1 ootMicrosoftExchangeV2:Exchange_Mailbox.LegacyDN='/O=TEST/OU=TEST/cn=Recipients/cn=co'brien',MailboxGUID='{59A40254-9F12-4502-9A03-3FB5CB0BBD38}',ServerName='VS1',StorageGroupName='sg1',StoreName='mb1'") I highlighted the section that causes the problem. The string within the string is encapsulated in apostrophes, hence the apostrophe in the mailbox name co'brien confuses the code and raises an error. Microsoft provides a solution by using escape characters, but that seems to work only in vbscript, not in vba. Here is a link to Microsoft's solution: http://www.microsoft.com/technet/scr...4/hey1217.mspx I tried some workarounds that are being used for a similiar problem with SQL statements as well, but again without success - these are the versions I tried: co/'brien co'brien co''brien
View Replies!
View Related
Create Macro Code With VBA Code
I'm trying to add buttons to an excel spreadsheet at runtime. Each row in the spreadsheet should have its own buttons. I was able to create and edit them with the ActiveSheet. OLEObjects.Add() function, but after that, when i was trying to create code dynamically to react on the buttons' click events excel crashes (actually it works for one button, but not if my routine for adding a new button and event code is called more than once in a row!) The code below works if the AddCmdbuttonWithCode() is called once, but crashes if it is called two or more times. Excel tries to restore the document after the crash and the first button and its corresponding click event code is visible and works... but NOT the second button and its event code... The only way I can create multiple buttons right now is by calling my method once per click, opening the vba editor, changing the parameters for my AddCmdbuttonWithCode() routine and execute it again. After that I have mutliple buttons in different lines which all work fine (so the concept seems to work). The problem seems to be the insertLine method, since everything seems to work if i leave it out (except for the code generation of course, since this is done by this part of the code :-) ). Is it possible that calling the insertLine Method can't be called multiple times? I don't know... any ideas? Feel free to test my code - it's small, easy to understand and has comments. 'this code calls the method which creates the buttons in specific cells with specific names Private Sub CommandButton3_Click() 'the first call always works! AddCmdbuttonWithCode "Edit_111_111_114", 23 'the second one crashes excel AddCmdbuttonWithCode "Edit_111_111_115", 27 End Sub
View Replies!
View Related
Code To Download Information From Notepad And Copying Info
Everyday system generates a notepad with the information.. from the notepad i ve to copy paste the info to the excel manually. Can a code be written where (after downloading information to the excel from the notepad) it automatically fill the information in the excel. For your reference i ve attached both notepad and excel with dummy datas. (how i do manually)
View Replies!
View Related
Create A Table Of Combinations
I have a list of variables, each of which has an associated list of possible values. Simple example: Fruit (apple, pear, peach) Snack (Snickers, peanuts, crackers, pretzels) Now, I need to build a table that for each line has every possible combination: Fruit Snack Apple Snickers Apple Peanuts Apple Crackers Apple Pretzels Pear Snack Pear Snickers Pear Peanuts Pear Crackers Pear Pretzels Peach Snickers Peach Peanuts Peach Crackers Peach Pretzels My table will have over 18000 lines, so building it from scratch will take time...is there an easier way (I'm real rusty on VBA, so that would take a while just to figure out the syntax again).
View Replies!
View Related
How Do I Create A HUGE Table?
I have to create a 12 row 14 column table which will be printed out and put onto a (around) 20 by 20 poster board...so it has to be HUGEEEEEE. How do I do this, all the boxes inside the table I want to have control of meaning size and stuff. I will have to write in all boxes. P.S: Yes. I know you guys prolly think im a idiot but this is my first time and my due date is appraoching fast, if anyone could help that would be great.
View Replies!
View Related
Create Top 20 Table
how I could create the following... I have a table that is 4 columns x 178 rows (varies by row) Columns are :- Name,sold,returned,% I want to reate another table that is 4 columns (same) x 20 rows. The twenty rows showing the worst % figures. This isn't a one off otherwise I could just use the data sort. I know one way would be to create and use a macro but was intrigued to find out if it could be done by perhaps some sort of array formula etc.
View Replies!
View Related
Create Subset Table
I've got a table on a worksheet (daylies) with daily data, dates in A6, A7, A8, ..., various data in columns B, C, D, ... . Now I would like to make a subset table on a separate worksheet (weeklies) with just weekly snapshots, i.e., snapshot from daylies off row 6 on row 2 of weeklies, snapshot from daylies off row 13 on row 3 of weeklies, snapshot from daylies off row 20 on row 4 of weeklies etc. Is this possible or do I have to use the sledge hammer technique of copy and paste?
View Replies!
View Related
Create A Reference Table
The spread sheet I work with will not post info on the second sheet automatically. So I'm in the process of trying to figure that out. The second page is basically giving a percentage of parts the person did compared to what another company would pay that person per hour to do at 7.42 an hour. I need the second page to take all the info on the first page and figure out the %. What it does to get it is, adds the numbers and divides by the total to get average and then compares it to 7.42 and hour. Also, if i could get the Name boxes to be auto filled with the file name? that possible? IE, John smith is the name of the file and it would auto fill that into all the name boxes on the sheets. And could i get the date to auto fill it self from the first date box? I included two files, one is a blank sheet and the other is a sample of the data entered and what i want it to look like.
View Replies!
View Related
Create Pivot Table In New Tab With New Name
1) I want to create a pivot table in a new tab that has both the prefix “ENG” and the date of creation as the new name of the tab every time a new table is required, using the example headers highlighted in the attached file. 2) Then if possible format the table as in the example, i.e. bordered cells for all the data and the dates only aligned to the right. Example in tab “Eng 01 05 09” No 1 is the most import question I would like help on. Formatting is not crucial. This is only to make the table easier on the “eye of the beholder”, I am writing this spreadsheet for people who have less knowledge than myself and would like to keep it as simple as possible, (run a macro and get the results).
View Replies!
View Related
Create List From Pivot Table
I'm trying to create what is essentially a report card for our staff. I have all the data in a pivot table and then I am using 'GETPIVOTDATA's in the report template to extract the information, linking the sheets via a cell that contains the staff members name. Easy enough. However for reasons of efficiency and practicality I would like to be able to select the names from a drop down list on the sheet rather than having to type it in manually or maintaining a separate sheet for everyone. The operators will change from month to month and there are approximately 90 staff at any given point in time that will need to be reported on so it really needs to be sourced from the pivot data.
View Replies!
View Related
Create A List From A Large Table
I am trying to do is create a list from a large table (see attached). I have 2 columns (A and B) listing 'Asset' and 'Area' and then 2 rows (1 and 2) listing 'type of task' and 'task'. This forms a table with entries in the cells (from C3 onwards) denoting frequency of each task at each location, i.e. daily, monthly, 6 monthly and annual. This gives me quite a large table which is quite confusing. Can I create a formula to sort all the daily activities which will give me a list of the 4 corresponding properties 'country', 'location' (in columns A and B) and 'type of task', 'task' (in Rows 1 and 2)? Then do the same for the different frequencies populated in the table, 'monthly', '6 monthly' and 'annual' ???
View Replies!
View Related
Create Sheet For Each Name In Table List
I am copying info from a program into the "Input" worksheet - It is from my phone switch of logins and logouts of my employees. I have 11 supervisors and my end goal is to have each one of their coordinators show up in their specific tab automatically with all of their login information, ids, logins, and logouts. I can do the other math for the Marks and break lengths but I am having a hard time with this. I have been trying vlookup, but cannot figure out a way to bring all of the different rows... It only brings over one row. I have tried to use some of the code from other posts on this sight, but have not had any luck. What I am looking for it to do is on the Alis worksheet - the light blue sections. I am also finding it difficult in the light purple section also. I am trying to figure out how much time they were on the clock.
View Replies!
View Related
Create Team Fixtures Table
I need to create a full list of fixtures for a football (Soccer) league i am involved in. i believe this is a multi stage project that i need to tackle in bite size chunks. I have a list of teams, division that they play in and there home stadium name. my spreadsheet has 2 sheets visible at the mo... Rules: I need to create a list of all games that require to be played. All teams in the same division must play each other twice, once at the home stadium of each club. Only division 1 clubs play against other division1 clubs Only division 2 clubs play against other division2 clubs I need a full list of matches to be generated in my worksheet called "required Matches"
View Replies!
View Related
Create Ranking Table With Names
I have an excel ranking 8 participants in a tournament. In the excel you can see the people participating and there cumulative score next to their name. What i want to do now is create a table that reads: Cell A Cell B 1st place "name of individual with minimum points" 2nd place "name of individual with min points, from the leftover people" "" 8th place I have used vlookup function, but I can either get it only to display first place individual but not the rest, sometimes it wont work because values of peoples scores have to be in ascending order.
View Replies!
View Related
Create Summary Report Of Table
I use a specific report on a daily basis which is attached here. Is it possible that that report is generated automatically in the same sequence of rows once the data is updated in another sheet. I am not good in VBA so looking some built in function. I have tried the pivot but all the formatting and design of the report is changed. use the Template wizard with data tracking is used is the above exercise is possible. Is there any other better methord is available in excel. Is there any good web site availble to learn these things.
View Replies!
View Related
Create Own Formula In Pivot Table
Another question about pivot tables. In Excel it is possible to show different kinds of information in a pivot table like sum, total, min, max, average and so on. I want to create my own formulas in my pivots. Is this possible and if so how?
View Replies!
View Related
Create Formula In Pivot Table
I have two questions about dealing with formulas in pivot tables and how to make them dynamic. First let me give you some further background. My database countains the following variables: Country, Company, Period, Product, Category and Sales. Out of this database i created a pivottable which sums the total sales amount for each company in every period. Further selection on country and category is possible in this pivot table. See my attached file! I want to express the sales of each company as a percentage of the total sales in that period. For example for company A in period 1 their total sales was (929/3172) 29,3% of the total market sales. Which formula do i have to use in my pivot table to express company's market shares? Further my pivot table can be specified more detailed by country and category. I want to be able to select on country and category in such a way that the company sales are still expressed as a percentage of the market sales. In other words how can i make my pivot table dynamic?
View Replies!
View Related
Create Sheet For Details Of Each In Table
I have a work sheet(sheet1) that contains 700 people's information. Each row contains information for one person. Let's call the first person A. I have to copy certain columns of person A's response to another work sheet(sheet3) in the same file. Then, copy person A's e-mail address and save the sheet as person A's e-mail. Repeat the same steps for the next respondent. I have created the macro for person A already (including the saving part). Now the question is how to write the loop in the macro. Sub Macro1() ' ' Macro1 Macro ' Macro recorded 7/5/2007 by Jean Kuo ' ' Range("Q4").Select ActiveCell.FormulaR1C1 = "=Sheet1!R[-2]C[-4]" Range("Q13").Select ActiveCell.FormulaR1C1 = "=Sheet2!R[-9]C[-14]" Range("Q14").Select Sheets("Sheet2").Select Range("D4").Select Sheets("Sheet3").Select Range("Q14").Select Sheets("Sheet2").Select Range("D4").Select ActiveCell.FormulaR1C1 = "" Range("C4").Select Selection.Copy Range("D4").Select ActiveSheet.Paste Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "=Sheet1!R[-2]C17" Range("C4").Select Sheets("Sheet1").Select Range("T1").Select Sheets("Sheet3").Select ActiveCell.FormulaR1C1 = "=Sheet2!R[-10]C[-13]" Range("Q15").Select ActiveCell.FormulaR1C1 = "=Sheet2!R[-11]C[-12]" Range("Q16").Select ActiveCell.FormulaR1C1 = "=Sheet2!R[-12]C[-11]" Range("Q17").Select Sheets("Sheet2").Select Range("F4").Select Sheets("Sheet2").Select Range("G5").Select Sheets("Sheet3").Select Range("Q17").Select ActiveCell.FormulaR1C1 = "=Sheet2!R[-13]C[-10]" Range("Q22").Select ActiveCell.FormulaR1C1 = "=Sheet1!R[-20]C[42]" Range("Q22").Select Selection.Copy ...
View Replies!
View Related
Create Summary Table From 2 Tables
I have 2 worksheets which have a table on each. I would like to create a list using data from both tables. The list I am looking to create is for an upload fom an excel planning sheet into an ERP system and requires certain data fields and a certain format. Both table have the same amount of fields and the dates across the top. On worksheet one there are material numbers on the left and dates across the top. In the list I require I would need the dates down the left in the first column, next the material number, next the quantity from table one and then the last column I would require the value from the second worksheet which is relevant to the material number and date from the first table. I have tried pivot tables but cannot get this to work and feel it may work with a lookup. I have attached an example of the data.
View Replies!
View Related
Create Unique Table Based On Criteria
Our reservation system exports a spreasheet daily which contains hundreds of records relating to bookings. The worksheet has an ID column which is not unique, but is sorted alphabetically. I need to write some code to delete all records below the first 'set' of records relating to the first ID value found. i.e. i want to end up with only the set of records relating to the first ID found. So the basic logic would be to look at the value of the field in the ID column of the first record and then iterate down the rows until it came to a different value and then delete all rows below that. Its simple in concept, but im stuck on how to write this.
View Replies!
View Related
Macro To Create Table From Spaced Data
I’m trying to do is create two tables from data evenly spaced throughout columns A to I. To make the screenshot easier to view I moved the second table under the first. The data is arranged so that each “Subject” has 42 columns and is arranged by “Type” and then by “Subject.” The tricky part is that the number of subjects will vary so everything has to be done in some sort of loop. From the little I know about VBA I was able to record and edit a macro to add spaces every six rows and average the “MaxResponse.” (that’s what stage the screenshot was taken at) To make the table I think I need to write some sort of Dim statement but that is way beyond my comprehension. Here is a link to a photo of the table http://yfrog.com/77excelshot3j Excel 2003 VBA 6.5 Win XP Pro
View Replies!
View Related
Macro Recorder To Create A Pivot Table
I used the macro recorder to create a pivot table, but to verfity that it works. I get a "Add fields method of Pivot Table class failed".. and this was highlighted: ActiveSheet.PivotTables("PivotTable2").AddFields RowFields:=Array("Warehouse" _ , "Product", "Description", "Data")
View Replies!
View Related
Combine Multiple Sheets To Create One Table
I need to combine the category sheets back into one main table, knowing that there may well be an increase in the number of categories, as well as the number of rows in each category sheet. I have attached a sample workbook - if anyone can help me with the code so that when the macro is run, the data in sheets A, B and C are combined into Main Table. the macro would also then update the Pivot table, that would be the icing on the cake (I have shown the pivot table configuration I need, in this case using only the data from sheet 'C', for reference)
View Replies!
View Related
Create Html Table From Range Of Cells
I am trying to output tables from excel to an HTML file using the following subroutine. It creates the table as I would like, but I am having a brain cramp trying to figure out how to get the cell data into the routine. The part in error is in the code below, in Red. I need some way of entering something like " Sheet(SheetNum)&.Range(Column&Row)&.Value" Private Sub CreateTable(Center As Boolean, Border As Integer, Width As Integer, SheetNum As Integer, StartRow As Integer, EndRow As Integer, StartColumn As Integer, EndColumn As Integer) Dim Row As Integer Dim Column As Integer Dim ColumnID(1 To 15) As String Dim CellLoc ColumnID(1) = "A" ColumnID(2) = "B" ColumnID(3) = "C" ColumnID(4) = "D" ColumnID(5) = "E" ColumnID(6) = "F" ColumnID(7) = "G"..................
View Replies!
View Related
|
TRACKER
