Turn The List Into A Table

Aug 28, 2009

I have a long list (almost 17,000 rows) with two columns (ID and Code). There are about 1400 IDs, each with multiple codes:

ID Code
1 ab
1 mn
1 hh
2 mn
2 hh
3 ab

etc.

I need to turn the list into a table that looks like this:

ID Code Code Code ...
1 ab mn hh
2 mn hh
3 ab

Each ID has a different number of codes (anywhere from 1 to 89).

ADVERTISEMENT

Pivot Table To Turn Numerical Data Into Letter?

Feb 26, 2013

What would cause a pivot table to turn numerical data into a letter?

I have a field, cost center, that has about 15 different possible values in it that look like this: 141350, 141360, 141370, 141380, etc. When I compile the data into a pivot table and try to sort on pivot table everything looks normal, except that 141350 is returned as 'R'. If I change the value to anything else, it is fine, but as 141350 it's changes from numerical formating to a single letter.

I'm sure something was accidentally set to do that automatically.

View 2 Replies View Related

Gathering Values Based Off Criteria And In Turn Forming List

May 5, 2014

ok lets say you have thousands of rows of raw data and you are running vlookups to pull specific data from the raw data to update your spreadsheet. Your raw data consist of some new ideas so when you vlook up, you get an error. In my case i get about 50 errors all spaced out amoung the thousands of rows or raw data. I would like to create a formula that would go and find the error and output a code/value that already exist in the raw data and output that in Cell D1 for example. The it will go and find the second error and output that into cell D2 and then the third error and output that into D3 and so on.....

The idea is that when its done a list of all the errors will have been created so then i can use other formulas to simple add the new info to the workbook. Instead of search through thousands of rows for the errow.

View 10 Replies View Related

Turn List Into Correlation Matrix Error Runtime 1004

May 25, 2014

I am trying to turn a list into a correlation matrix.

Goes like this :

From :
1 1 1
1 2 0.6
1 3 0.7
2 2 1
2 3 0.9
3 3 1

To
1 2 3
1 1 0.5 0.6
2 e 1 0.9
3 e e 1

The first list is from worksheet 1, and im trying to create the matrix in worksheet 2. Both worksheet is in the same open workbook.

Here's the code I've been using

Sub button1_click()
Dim i As Integer
Dim j As Integer
Dim b As Integer
Dim a As Integer
Dim c As Integer

[Code] ........

Now the weird thing is, this worked on a smaller matrix before (31 x 31) but now it just would't work at all (85 x 85)

it keeps giving me the error run time 1004 : application defined or object defined error.

Here's the file : matrix.xlsx

View 1 Replies View Related

Turn #DIV//0! To 0 Or Even Turn It To A Blank Cell

Nov 6, 2009

I have a workbook which is set up to take an average heart rate of a participant from a series of data points. I have set the spreadsheet up before I have collected some of the data. (so I can review the project at the 3 months period and its an ongoing project).

The problem is that if there is no data in a participants column then excel correctly gives you readout of #DIV//0!. On my results page this #DIV//0! makes it hard to read the spreadsheet. Is it possible to get excel to turn #DIV//0! to 0 or even turn it to a blank cell?

View 5 Replies View Related

Conditional Properties: If A Number In A Different Cell Is Above A Certain Number It Will Turn Green And Another Cell To Turn Red

Dec 10, 2008

can I set a cell in excel so that if a number in a different cell is above a certain number it will turn green and another cell to turn red. I have attached the file.

View 4 Replies View Related

Using A List In A Pivot Table To Generate A List On Another Sheet

Jul 29, 2008

I have a set of accounts (general ledger accounts) that the accounting group posts expenses to. every once in awhile a new account is added. This is captured through a pivot that i have built (sheet A).

on another sheet (sheetB) i want to display the accounts that are shown in the pivot so that i can forecast their future activity.

What i am trying to figure out is how can i make the list on Sheet B change when new accounts are added to the pivot on Sheet A (without simply referencing the pivot table making one cell equal the other)? I am not looking for a data validation pull down, i want a full list of the accounts.

View 9 Replies View Related

List Matches In A Table?

Aug 12, 2012

I have a large table that gives me the intersection points of many lines. I have used conditional formatting to highlight the intersection points that match by turning the text red, thus showing me when 3 or more lines intersect. Manually scanning the whole table is inefficient as I wish to expand it.

Due to size limitations, I've uploaded a shortened version of the workbook, just a copy with one example of a match.

How do I get excel to locate the matches (currently highlighted with red text) and list them in column I "Intersections" all neatly sorted?

I thought Advanced Autofilter but I don't think the criteria range can search for red text alone.

View 6 Replies View Related

List Data Into Table

Nov 7, 2008

I have data in a list of sales people and dates of their sales calls. What I want to do is transform the list into a table as shown in the attachment. Then I want to calculate the number of days between the first and last sales call. This part is easy using date formatting and min/max I guess. What I can't do is get the table arranged. I have used a pivot table to count the number of calls per sales person but pivot table wont just display the dates in a table format, it will only sum or count etc.

View 3 Replies View Related

Converting Table To List

Nov 12, 2007

I am wanting to manage my inventory and need to convert spread sheets with tables specifying style, size color to lists with skus specifying style, size color. please see attached spread sheet with one sheet with table and second sheet with list.

View 11 Replies View Related

Convert List To Table?

Jun 14, 2012

I have three columns of data starting in row 5, the headers of these columns show Origin, Destination and mileage. Is it possible to have a macro that converts the data into a table?

View 2 Replies View Related

Convert Table Into A List

Jun 3, 2014

I am trying to convert a table into a list, please see the example, I want to convert this table

10547
aaa
140x200

160x230

300x400

10549
bbb
140x200
150x260

[Code] ........

View 1 Replies View Related

Convert From Table To A List

Sep 18, 2008

I need to convert this kind of table to a list (like in below example)
Can someone give me an instruction or macro to do this ?

Table
X Y Z
A 3 5 7
B 2 4 6
C 8 9 10

List
Column1 Column2 Column3
A X 3
A Y 5
A Z 7
B X 2
B Y 4
B Z 6
C X 8
C Y 9
C Z 10

View 9 Replies View Related

Dropdown List From Table Categories?

Apr 14, 2014

I have a file and it has a table on it with 2 categories.

what i want to do is select a color in the first drop down list and than in the second only select the items that match the color i have previously selected.

Is there anyway to do this with named ranges because i would like to index everything in a table.

View 3 Replies View Related

Pivot Table Source List

Jun 19, 2008

I'm trying to create a Pivot table with various columns comparing information of executives from various Private Equity Firms. Some of the additional columns are Industry Specialty, Harvard Grad Year, My companies contact, etc . . .

The goal is to allow me to then query this information so if I wanted to see all Bain Capital employees who graduated with an MBA in 1990 and their specialty.

My question comes in trying to set up my source list for the data under Industry Specialty and my companies Contact. Is there a way I can set a value for these cells and mark them as an "AND" in the source if there are multiple contacts or industries that one of the Private equity executives is linked too, that would then allow them to be displayed as so when I create the pivot table?

Attached is .zip for further clarification.

View 9 Replies View Related

Create List From Pivot Table

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

Retrieving List Of Data From A Table?

Jan 4, 2014

I'm trying to create a automated calculator that i can customize and adjust. start and what functions should i be using. I've looked into Vlookup/Hlookup and it does not seem the function i need to make the workbook work. Basically what I need is when I enter a item on the A column excel would retrieve the list of Ingredients to the calculator worksheet and multiply it by the amount of variable entered. Then product will be printed and added together to the "sum row".

View 14 Replies View Related

Tranferring From A List To A Two Parameter Table

May 12, 2009

I need a two parameters table deriving data from a list in another sheet. The vertical column of the two parameters table will be the names of the personnel, and the horizontal one will be the dates, in weeks form. The list contains the names, flight departure, flight arrival and no. of working days spent in the foreign country. What I need to do is to tranferr the data from that list onto the table.

For eg, if john flew overseas on 20-Dec to 15-Jan, the list will show john on column A, 20-Dec in column B, 15-Jan in column C and 20 working days on column D.

This list of personnel and their data should go into the two parameters table, where it will be identified when the personnel will be overseas according to the calendar of the project. Since the vertical heading is the dates of every week, a date that corresponds to period where the personnel is overseas will reflect a number in the cell. The number will be the number of working days the personnel spent on the foreign country, therefore the max will be a 5 and minimum is a 0. I need a formula that will fill every cell within the area of the tables.

View 4 Replies View Related

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 View Related

Pivot Table Filtering On A List?

Nov 27, 2011

I have a pivot table with a group # on the left, and team members on the right. There are multiple team members in each group, and each are on a separate line in the pivot table, like this:

Group #Team1chrisdawnsally2cassiechrisdawnkathysally3jimjoejohntomGrand Total

I need to find where "sally" is on the team (easy to do"), BUT I need to display the rest of the team members in the group with her, like this:

Group #Team1Sally, Chris, Dawn2Cassie, Kathy, Sally, Dawn, ChrisGrand Total

Using the concatanate function to put them all into one cell and then filtering for "contains" was all I could come up with, but there are 14,000 groups

View 2 Replies View Related

Reversal Of A Crosstab Table To A List

Sep 22, 2008

I am trying to convert the table below into a 3 column list that I can then import into SQL Server from a .XLS file, using ODBC .

Assets01/09/0802/09/0803/09/0804/09/0805/09/0806/09/0807/09/0808/09/0809/09/0810/09/0811/09/08

MQBH073520.773540.413592.333578.543531.293535.043485.913543.463544.161789.03
MT1072688.693658.223410.453400.191915.563401.81
3586.713870.793846.383878.4P08

P123182.63323.393225.873299.541635.611641.7
1615.983304.913313.791637.02Totals52097.561575.5561750.5864889.2554803.6754775.2661905.5465112.9563407.6266701.1861598.34
The table supplied, starting at B8 demonstrates one of 8 worksheets in the spreadsheet... of which I would like all exported to 8 named worksheets in a new spreadsheet in the list format as:

Asset | Date | Value

I need to ignore the Totals column and row,
The table can grow or reduce the assets and
grow the days of the month, starting again at the begining of each month. I have to run a report each day of the month.

View 9 Replies View Related

Pivot Table Field List

Jan 12, 2009

on the pivot table field list-enable on the right hand side of the screen, only after that the needed fields can be dropped in.

Is there any other method to draw the pivot table?

View 9 Replies View Related

Security In Table/list Database

Jan 28, 2009

Is there any lock/security option in excel sheets, suppose we make some table/list and after entering one data when the cursor goes to second raw, the previous raw data automatically lock. the user cannot edit or delete that record.

View 10 Replies View Related

Pivot Table Page List

Jun 2, 2009

I've a Pivot Table which i would like to get a list of it.

It is from an OLAP cube from Oracle

Based on the image below, every month i refresh this PT, there will be an additional WK. Lets say WK22.

How do i export the whole list from WK08 to WK21 everytime after i refresh my PT?

How do i select the last item in the Page field? If there is WK22, den it will auto select WK22. If not WK21.

View 9 Replies View Related

Unique String List Into New Table

Aug 4, 2009

I have a list of location names that may be repeated within the list. I also have possible blank cells within the list. I would like to filter them automatically into another table without having to use the autofilter or any macros to keep things simple.

How could i do this by removing blank cells in the new table and having only uniquely listed names ie get rid of any duplicates.

View 9 Replies View Related

Create Sheet For Each Name In Table List

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

Convert Table To Concatenated List

Oct 9, 2007

I would like to convert the data by VBA macro from a table format into a list format. The convention of the TABLE data is as follows:

First row is Header Titles
[TABLE]
[SITE],[LOC],[PROD], [MTH1], [MTH2], to ->[MTH12]
North, Office, Cooling, 100, 125, 85
[/TABLE]

I would like the data to be provided into a list format for each month.
For example:

[TABLE]
Row 1: North, Office, Cooling, Month 1, Value
Row 2: North, Office, Heating, Month 1, Value
Row 3: North, Office, Electricity, Month 1, Value
Row 4: North, Retail, Cooling, Month 1, Value
Row 5: North, Retail, Heating, Month 1, Value
Row 6: North, Retail, Electricity, Month 1, Value
etc
[/TABLE]

View 7 Replies View Related

Break Down List Of Pivot Table

Apr 7, 2008

Originally I considered posting in the Excel / Email area but I know how to automate the email part of this already once I get the data collection I'm looking for. Let me explain. I have a pivot table that consists of reps, accounts, items, and sales data in that order.

For each rep I want to create an email of affected accounts and items with thier respective sales data. Because each rep has a variable number of lines I'm having trouble defining the exact range. I've used a few pieces of code taken from various posts from this forum to start me off but nothing seems to be working, else I'd post what I already have.

View 2 Replies View Related

Macro To Filter Table Based On Another List?

May 7, 2014

basically i have a macro to filter a lot of data based on peoples names but the people keep changing so I dont want to go into VBA each time to update the name list int he macro.I've created a list of names in a sheet in excel but not sure how to point the macro to filtering by that instead, heres what I have:

VB:
ActiveSheet.Range("$A$3:$H$61").AutoFilter Field:=7, Criteria1:=Array( _
"Name1", "Name2", "Name3", "Name4", Operator:=xlFilterValues
ActiveSheet.PageSetup.PrintArea = ""

[Code]....

I have named the database list for each name and its on a sheet called Team_List

View 4 Replies View Related

Creating A Drop Down List From Another Table Of Data

May 6, 2009

I want to put a drop down list in Cell D3 with the list of names from column B. There are 14 different names, but when i try and create the list it gives everyname from Column B.

Also once the drop down list works i would like the table to be populated with the managers supplier, so if Dan was chosen in D5, Suppliers 1 to 9 would appear below D3.

View 7 Replies View Related

Create Dropdown List For Table Of Contents?

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

Selecting Rows In Table Based On Another List?

Jan 30, 2013

I have attached the data in an excel file here. I'd like to select (or highlight) the rows (based on names) in the table on sheet1 based on the list provided in sheet 2. I've been struggling with using a formula for this one. The list on sheet 2 may be longer than the table on sheet 1(since there are entries on the list which might not be present in the table column 2), and the reverse may also be true. I would like to select "entire rows" of names in the table that are there on the list on sheet 2, not just the names column in the table. See the desired result on sheet 3.

View 9 Replies View Related

Automatically Create List / Table From Data That Changes Often

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

How To Change Horizontal Table To Vertical List

Jul 22, 2013

I need to change data from a horizontal table into a vertical list? example.xlsx

View 1 Replies View Related

Excel 2007 :: Converting A Table To A List

Sep 3, 2013

I have a large table of results that looks like this...

Forename___Surname___Art___Maths___Science___English___Drama
Billy________Bob_____________B_______A________C_____________
Michael_____Micky_____A_____D_______B_________________C____
Shelly______Sholly_____A*____A________________A________B____

I am looking for a way to write this data in a list like this........

Billy__Bob__Maths___B
Billy__Bob__Science_A
Billy__Bob__English__C
Michael__Micky__Art__A
Michael__Micky__Maths__D
Michael__Micky__Science__B
Michael__Micky__Drama__C
Shelly__Sholly__Art__A*
Shelly__Sholly__Maths__A
Shelly__Sholly__English__A
Shelly__Sholly__Drama__B

Excel 2007

View 5 Replies View Related

Get Cursor Location In A List Object Table

May 19, 2014

I know how to get the row number of the cursor in a spreadhseet (Activecell.Row), but how do I get the cursor location in a list object so that I can then insert a new row for the user at that point?

BTW, I am using tables (ListObjects) because when a row is inserted, all of my formulas are automatically inserted.

View 1 Replies View Related

Macro To Sort Dynamic List Into Table

Nov 16, 2008

I have a list of letting agents that I need to sort into a table to import into Access. The list is dynamic in that not all the fields are present for every record. I will have over 4000 records to sort out, so I would really like to automate this. If possible the macro should read rows from the data worksheet and write to columns and rows on the Table worksheet. I have attached a small example of the data and output required. To make it a little more challenging the column header name is part of the data and will need stripping out as well.

View 3 Replies View Related

List Names For Cells With Greater Value In A Table...

Feb 13, 2009

I'm trying to List names for cells w/greater value in a table w/ 5 columns.
name that has greatest value for each row should show in column B. See example booklet.

View 2 Replies View Related

Auto Populate Distinct List From One To Another Table

Sep 12, 2012

I have two tables on two different worksheets. I would like to automatically populate a distinct list into column A in table 2 from the data in column A in table 1.

Table 2 must auto populate from table 2 every time a new row is added into table 1 - without any extra user interaction e.g. pressing a button etc. No offense intended to my better half but if she has to do anything more than enter a new row in table 1 she'll likely manage to screw it up after a week

I kind of managed to get it working using the below formula but both sets of data must be in tables so I can't copy the formula down the column without creating redundant table rows.

=INDEX(Classes!$A$2:$A$999, MATCH(0, COUNTIF($A$1:A1, Classes!$A$2:$A$9), 0))

Example

Sheet 1 = "Classes"
Column A = "Student Names"

John
Mike
Paul
Andrew
Paul
Paul
Mike

Sheet 2 = "Total Due"
Column A = "Student Names"

Paul
Mike

View 3 Replies View Related

Down The List On The Largest Table Returns No Data

Sep 22, 2009

I have created a monitoring spreadsheet for a public School system. It pulls data from 5 seperate excell files. however when a lookup value that is further down the list on the largest table it returns no data. other cells can lookup that same number and return correct information but from a different table.

The table that has issues is A1 - Y12430.. if the lookup value is low in the first few thosand rows it works.. but if thye value is in say row 9,000 it can not locate it..

View 9 Replies View Related

Pivot Table Field List Doesn't Appear

Nov 27, 2009

The field list does not appear when I create a pivot table in Excel 2007. It works properly if I start Excel in safe mode. I have toggled the field list button in the PivotTable Tools show/hide ribbon and I tried repairing Office 2007 from the control panel.

View 9 Replies View Related

ADVERTISEMENT