Create A List From A Large Table
Nov 21, 2009
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 7 Replies
ADVERTISEMENT
Jul 17, 2014
I have a lot of data to try and summarize but they are in multiple smaller tables. I wish to only extract 2 variables, 'number' and 'total' from all of my smaller tables (lots of them) so the problem looks like this, i have these tables for example where the 'total' title is in a different column depending on which of the smaller tables you are looking at:
cat 1
cat 2
cat 3
total
[Code]....
I am trying to get:
number
Total
123456
30
234567
60
[Code]...
Because the number column is fixed and all of the smaller tables will all have a number in the same column maybe i can insert a column next to the number column and say "find the value where the column title is 'total' - however the location of the column title would change so not sure if this would work? I was also thinking of sorting the columns but that will not work either because of the smaller multiple tables?
View 4 Replies
View Related
Feb 24, 2010
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 7 Replies
View Related
Aug 14, 2006
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 3 Replies
View Related
Dec 6, 2010
I am trying to create a drop down list for a table of contents that can link off to other pages within the same work book since it is very large and difficult to navigate through. I can create dropdowns were the lists it draws from is hyperlinks and that doesn't do anything special. It is just like drawing a regular list. I want the drop down when I click on something, to link me over to the other sheet.
View 7 Replies
View Related
May 30, 2013
I have a client list/table that gets new data each day. (ALL) - see file
I need to create separate lists/tables on new sheets from the main data sheet (ALL) base on certain conditions/criteria...
On the (ID) sheet I need all the clients that have ID numbers to display, on the (Reg no) sheet I need all the clients with Reg numbers to display... etc.
Like I said that the main data sheet grows each day and the lists/tables on the other sheets needs to automatically include all the new clients that's entered.
Is there a way to do this using formulas or do I need code to do this?
View 2 Replies
View Related
May 26, 2014
I am trying to create a straight column list that can take the rows and columns of a table, and list only the nonblank items. The formula I am using only seems to work with one column, not multiple.
Formula:
[Code] .....
View 12 Replies
View Related
Nov 18, 2013
I download a daily inventory file from my supplier.
.
I sell only selected products from that file.
.
I would like to use a list of the product part numbers of the products I sell on sheet 2 (Inventory) and have all the results from sheet 1 (ProductFeed) built into an expandable table on sheet two including all the parts numbers (expandable because I will add or delete part numbers from the list) I have listed and not just one part number but several.
.
Second issue: the download file contains part numbers in column "A" with variables like 418, 418_color, 418_size etc.... I would like all part numbers that begin with the number 418 and the ones that contain the variable added to be listed in the new table so I will get a list of all part number 418 and the variables which include 418 in them.
.
I want to return the results of the whole row(s) of the matched data as well in the new table.
.
I have attached a file of the working data that I have with the data I will use although the ProductFeed will change daily which I intend to copy and paste into the table each day.
View 2 Replies
View Related
May 18, 2009
I want to create a user input for a "LARGE" function so that I can then return the specified # of "large" items. i.e. user wants to get the top X number of users, user then enters number either into a dialog box or cell, code then looks at the number and populates a range of cells based on the input value. The current code that I am using simply refers to an existing table (r9:r30) that I then used the auto fill to copy to cells below it.
=INDEX(Sheet2!B$20:B$961,MATCH((LARGE(Sheet2!$F$20:$F$961,$R9)),Sheet2!$F$20:$F$961,0))
I really want to be able to just get a user dialog box going that will automatically populate both the range r9:rx (where X is the user input) and then also copy the above formula into the x number of cells below it.
View 2 Replies
View Related
Apr 25, 2014
create cells which could be expanded/shrunk, to either show the first sentence or so of the essay, or the full essay. I'd need to be able to expand/collapse each cell individually, not by row or column as the tutorials I've found have shown. I'm pretty sure I've seen this done with a little +/- sign in the corner of the cell but I can't find the option. It would be a bit like on a website that have show more, show less buttons.
View 2 Replies
View Related
Oct 31, 2013
I have a dynamic table which is linked to a couple of charts. The table must remain dynamic.
I needed to add two new columns to the table, "Focus Area" and "Category". I need a drop down list in the "Category" column to be dependent on the item selected from a drop down menu in "Focus Area".
I can get the first row of the dynamic table to do this.... however; subsequent rows all lock the drop down list in the "Category" column to the same choices regardless of what is chosen in the "Focus Area" list.
Is there a way to make dynamic drop downs within a dynamic table?
View 1 Replies
View Related
May 8, 2014
I have 3 columns thousands of rows each, P, Q and R. Column R is the names of accounts.
I have another column E with also account names but not in same order as R. Another small glitch is, some of the names in R have extra characters at the end (such as one or more spaces bars, etc). But the entirety of the text in E is present in a cell in R, sometimes with extra characters at the end.
I want to match the account names and pull the corresponding values in the same row from columns P and Q, and populate my currently empty columns C and D.
View 4 Replies
View Related
Dec 6, 2012
I'm having an issue where I want to merge lots of tables held in different spreadsheets into one large table so that I can use it in a pivot table. The attached spreadsheet demonstrates what I mean:
So there are 3 different accounts held in the first 3 tabs. These all have the same columns and format. I want to merge these automatically into the table that is in tab 4 so that I can use the pivot table also demonstrated in this tab.
What I want to happen is when I add rows to the tables in the first 3 tabs, these are automatically populated onto the 'overall' table in the 4th tab. (I'm aware I could do this manually, but for what I'm using this for these will be dozens of tables with hundreds of rows updated monthly).
Is this possible? Basically it doesn't matter how it's done, as long as i can show the information held in the pivot table each month end.
View 3 Replies
View Related
Jun 7, 2013
My pivot table file is currently 12.7 MB and I need to get it much smaller so I can email it and it still be functional with the data.
I have tried right clicking on the pivot table, going to PivotTable Options, clicking on the data tab, unchecking the 'save source data with file' box, and checking the 'refresh data when opening file' box. This shrunk the file perfectly but when i email the file the Pivot Table was unusable because there was no data to back up the function.
The Error messages I got were 'Cannot open PivotTable source file'..... and 'Pivot Table report was saved without the underlying data. Use the Refresh Data command to update the report'. When I click on Refresh Data I still get the same error message.
Any way I can make this File smaller and still functional.
View 4 Replies
View Related
Apr 24, 2007
way to copy certain cell ranges from a main table into a different sheet (for nicer printing output, as in the main table there are also unused ranges) and in such a way that they would be copied there one after the other with no spaces between them.
( I have say A1:M1 with some cells for labels,
then A2:M4 with a smaller table with some user choices etc. etc.
then again A5:M5 with cells for labels
and A6:M8 with another smaller table with user choices... )
multiply by 2x
Then I want to copy just those ranges that the User has selected something in - e.g. only A1:M4, if he selected something in A2
or A5:M8, if he has selected something in A6
View 13 Replies
View Related
Mar 23, 2009
I have a data dump, on this single excel sheet their are a large number of tables, up to 100 in all, and what i need to do is extract key figures to form a summary table which i then produce stats for.
What i need is a macro or code that will automatically scan the tables, retrieve the key figures and return them in my summary table.
Attached is an example, you will see three small tables labelled Servicing query complex, servicing query simple and total.
I need to extract the blue cell, being the name of the table, the purple cell being the last figure in column c in each table (total number or processes) and the green figure being the the average turnaround. The final figure is i think the hardest to get as its the total number of figures in column c that have either a 0 or a 1 in Colum A. Note that their will be table where neither 0 or 1 will appear.
All figures are dummy figures but the table structure is correct.
View 14 Replies
View Related
Apr 7, 2007
if there was a way I can put it in to some kind of pivot table, because the hardest part for people when they read his list is it's so large it's hard to find data easily.
So this is how he formatted his data... I was wondering what would be the best way to get this list in to a possible pivot table. This is a consilidated example, there are plenty more columns, but this will give you an idea of my problem.
A2007 A2006 A2006 B2006 B2006 B2007
Feb Jan Feb Jan Feb Jan
630 Labor Cost 1000 7500 3000 4500 800 5000
624 Equipment Cost 900 50 40 300 20 1400
Now, the only thing I can think of is to make columns, but then I'd have to recopy all the task names (which there is about 700) for each of the different years (A= Actual, B= Budget, F= Forecast). Is there any other way that you can think of to do this with out making it so complicated? Any help or suggestions would be great. I really want some format that allows you to click the total and it goes to what makes up that total.
View 7 Replies
View Related
Jan 14, 2014
I have a list of IDs that recurs over a time period. It consists of a Start Data an ID Number. These IDs recur over and over again through one month with different start dates.
See Below:
Date ID
12/1/2013 10:00:00 AM 67890
12/6/2013 12:00:30 PM 67890
12/18/2013 06:30:05 AM 67890
From Another List I'd like to pull a max enddate that is within 24 hrs of the start date. There will be multiple end dates. Here is what the other list would look like.
End Date ID
12/1/2013 1:00:30PM 67890
12/6/2013 4:00:45PM 67890
12/18/2013 9:30:00 AM 67890
Seems like using vlookup with the ID as the lookup wouldnt work because it would just pull the first date it found over and over again.
View 9 Replies
View Related
Sep 1, 2009
I have a very large numbered list in MS Word, in the following style:
1. Animals
1.1 Mammals
1.1.1 Dogs
1.1.1.1 Beagles
1.1.1.2 Pugs
1.1.2 Cats
1.1.3 Elephants
1.2 Fish
1.2.1 Cod
...and so on.
I am trying to bring this data into Excel (2007) but in a way so that:
"Animals" goes into column A.
"Mammals" and "Fish" go into column B (in their own cells, in different rows).
"Dogs" and "Cats" go into column C.
"Beagles" and "Pugs" go into column D...
and so on.
The idea is to have each line in a different row, but have the columns serve more or less the same function that the tab indents do when looking at it in Word. (I'm trying to create a "breadcrumbs" style list for each line item). I've tried using tabs as a delimiter for importing text into word, but it doesn't seem to recognize Word's tabs when applied through a numbered list.
View 4 Replies
View Related
Dec 3, 2013
I have a large list of in rows with multiple column headings: ie. Name, date, amount. I need to generate a separate list whereby the rows that correspond to a given criteria are extracted and listed sequentially.
I have attached a simple example to demonsrate what i mean.
in the attached spreadsheet, i have a column of colours, and a column of objects- RED book, BLUE hat, etc.
I'd like the criteria that i type in the green cell (a choice of colour), to generate a list of all the items that match that colour, and be displayed in the orange cells. So, if the green cell says BLUE, the list should pick out the BLUE dog, BLUE hat etc.
View 1 Replies
View Related
Apr 20, 2007
I have a large list of data in sheet1. The relevant columns contain a unique identifier (column 'N') and a value of either "True" or "False" in column O. Because I don't want users to have to trawl through hundreds of lines looking for the 'False' entries, I have a macro that copies only those records to Sheet3. In this sheet the user can set the 'False' value to 'true' but I don't know how to return the new value to Sheet2 (possibly for multiple records).
View 9 Replies
View Related
Jan 20, 2009
I need to setup a macro that will search for a keyword in a list with about 21000 rows. When the macro finds the keyword it cuts the row out and moves it to another sheet. I'm having a few problems
1. I don't know how to make the function search until it can't find the keyword anymore, then move on to the next word
2. I can't get it to select what it finds it finds the cell but I don't know how to select that cell once its found.
Sub Search()
'
' Search Macro
'
Dim keyword As String
Dim x, y, z As Long
For x = 1 To 53
Sheets("Test1").Select
Range("A" & x).Select
keyword = Selection.........
View 9 Replies
View Related
Apr 26, 2013
I have 2 large XLS sheets that need to be split into seperate sheets.
The first is only 5 columns wide but the amount of rows changes day to day.
The second is a maximum of 7 columns wide and again the amount of rows will change.
I'm hoping for a VBA code to be able to do both on seperate books.
The A Column has the name of the company, and this can include (/,&) that will need to be removed, the names can also exceed 31 characters.
I would like to seperate the sheets by the company name and have the name appear as the sheet name.
Also the formatting from the master sheet to be copied to the resulting sheets, with a header row.
I have included the data for the larger of the two workbooks. data.jpg I have to pull the smaller report each day and the larger report each week.
View 3 Replies
View Related
Oct 15, 2012
I'm trying to count how many production orders i have per week. However, there are duplicated production orders per week. I only want to count how many unique orders there are for each week. I only see the ability to "Count", which counts my duplicates as well so it over inflates my true quantity.
View 3 Replies
View Related
Jan 2, 2014
We collect loan payments for 36 months from customers.
Column A lists 1000+ customers.
Column J lists the date we received payment 1 ... Column Q lists the amount we received on payment 1.
Column R lists the date we received payment 2 ... Column Y lists the amount we received on payment 2.
Column Z lists the date we received payment 3 ... Column AG lists the amount we received on payment 3.
This repeats for all 36 payments.
New customers are loaded in each month, so be aware that Column J, Column R, Column Z (and so on) have dates from 2011 and 2012 and 2013.
We'd like to create a list of all customers that have not made a payment for the current month as of a certain day (say the 12th). So this month, on January 12th, we'd like to search our data for all customers that don't have a payment listed between January 1st - January 12th.
View 14 Replies
View Related
Oct 17, 2011
I have sales data in columns, the right most column has values in it.
If I filter the data using the standard Filter, I can add only filtered records by using SUBTOTAL(9, filtered_list) and this gives me the answer.
How can I find the 2nd largest or second smallest ONLY from the filtered data without using a Pivot table?
View 5 Replies
View Related
Dec 30, 2011
I am facing problem in finding the second large value while my list is having duplicate data.
For Example
my numbers are:-
12,65,45,12,87,90,65,87
if I want to find out the top four value by using the formula:-
=large(A1:A8,{1;2;3;4})
the result is coming like: - 90,87,87,65
instead of this I want the result as: - 90,87,65,45.
View 6 Replies
View Related
Nov 4, 2012
(Excel for Mac 2011). The size of my data means am crashing by usual methods of sorting - I need a formula for this rather than pivot tables.
I have a list of c. 40,000 rows. Col A is client name (largely different but some duplicates); Col B is Policy type (eg Home, Motor, Travel) and Col C is earnings on that policy (eg -60). An example would be:
Name
Policy Type
Earnings
Johnson
Home
[Code]...
So I need to figure out (via a formula) two things:
1) How to count duplicate names (eg appearing 3x = 1 customer; appearing 2x = 2 customers; appearing 1x = 1 customer)
2) How to build a simple table to show the relationship between policies. This would have Motor, Home, Travel, Motorcycle as columns and also as rows - the data would then be how many policies and in each cross point?
I have tried just working the data using Countifs etc, but as soon as I apply it to the whole data set the machine freezes, presumably due to its size
View 6 Replies
View Related
Apr 14, 2009
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 3 Replies
View Related
Apr 26, 2012
Recently, the boss showed me a Pivot table & chart, which consists of a list of about 30 user names in the first column. The row headings were the different items they purchased from a vending machine. & when he clicked on any name in the first column, this created a new sheet, renamed with users name, with a small table of results showing what that person purchased.
Problem is, none of us can figure out how to do this. I have created a new Pivot table & chart exactly like the original, but I cannot get the smaller sheet to generate. (Excel 2010)
View 3 Replies
View Related
