Sort Table (Names And Values) From Cell

Jun 7, 2014

I am keeping a spreadsheet for an auction

We have 20 people and each person gets a total bid for 48 items.

if person1 = 55,person2 = 100,person3 = 76,person4 = 67,person5 = 49 etc.

The person's names will be in column C while values in D in cell G3 will hold the total bid for the items each week updating.

What I am trying to do is sort the table (names & values) from cell G3.

So if each week G3 total gets bigger then the table sorts to match.

If the overall total bid after 48 weeks (G3) was 52 then the table sorts with 55 on top (because it's closer) 49 next.

The closer the person's bid to the total bid the further up the table the person will be.

I have uploaded an example with before and after sample.BidExample.xlsx

View 8 Replies


Sort And Group Names Then Sum Associated Numerical Values For Each Group - Sort Total

Apr 18, 2014

I have this massive spreadsheet, with the maximum rows excel will fit on one sheet. In the interest of simplicity, this is what the raw data looks like:

Last First Amount

Jones Jim $1000
Jung Joe $700
White Jon $100
Jones Jim $200
Jones Jan $300
Jung Joe $800
White Jon $200

What I want to do is automatically get excel to group all the same names together. Then I want it to sum all the values for each name, then order by largest total value for each person, then order that by name alphabetically. In other words, the above would look like this after the sort:

Last First Amount
Jung Joe $800
Jung Joe $700
Jones Jim $1000
Jones Jim $200
Jones Jan $300
White Jon $200
White Jon $100

Or, if necessary, there could be sum total rows under each name...although I don't have spare rows, so IDK. I could delete some rows if I had to, but would rather not.

View 1 Replies View Related

Table Names Revert To Cell Names When File Is Reopened

Jan 4, 2013

I have a worksheet with many tables that I use in formulas.

I like tables for a couple reasons, one being the ability to insert/delete rows without affecting the rest of that worksheet row.

Also, automatic copy of formulas/formatting is great.

But, what I really like about tables is the ability to use the naming conventions in formulas.

Problem is when I save this worksheet, after I close it and open it back up, all table references in my formulas have been converted to cell references.



=IF((SUMIF('Quote 1'!$M$28:$M$43,">"&'Quote 1'!$J$57:$J$60)*'Quote 1'!$G$57:$G$60)+(COUNTIF('Quote 1'!$M$28:$M$43,""&'Quote 1'!$J$57:$J$60)*'Quote 1'!$G$57:$G$60)+(COUNTIF('Quote 1'!$M$28:$M$43,""&tblOSSRV[Min Order Cost])*tblOSSRV[Cost / Part])+(COUNTIF(tblFam[[#Data],[Qty by Factor2]],""&tblOSSRV[Min Parts Per Line])*tblOSSRV[Cost / Part])+(COUNTIF(tblFam[[#Data],[Qty by Factor2]],"

View 4 Replies View Related

Sort Table On Cell Event

Oct 10, 2009

I have a table that is contained within cells B4:D9. I am trying to sort the table from the highest to lowest (column D) everytime the value in G18 or G19 changes. Basically the table changes values everytime there is a change in these two cells. I have attached an example for clarification.

View 14 Replies View Related

How To Populate Table With Names Based On Cell Numbers

Mar 13, 2014

I have a column (A) with names, Column (B) with platoon numbers. On another sheet I have a table for each platoon and I want it to be populated with the names of each platoon indevidually. The names are alphabetically and the platoon numbers are mixed.

Column A Column B
Name Platoon
J Goodman 1
L Barns 7
H Law 1
B Aims 10

and so on....

I want all the names from platoon 1 to appear in the table for platoon 1. I have about 3000 members and 40 platoons.

View 3 Replies View Related

Remove Names Without Values From A List Of Names And Values

Mar 1, 2013

I have the list below and would like to create a new list which contains only names with corresponding values and lists them.

+ A B
1 James 3
2 Derek
3 Brett 6
4 Allan
5 Jess 7
6 Sam 10
7 frank 10

The solution should look like:

+ A B
1 James 3
2 Brett 6
3 Jess 7
4 Sam 10
5 frank 10

View 2 Replies View Related

Cannot Set Shape With Names From Cell Values

Mar 15, 2014

I have a couple of hundred string values in column A. I am trying to create the same number of rectangle shapes in column B, each one taking its name from the corresponding cell in column A. I keep hitting the dreaded 400 error code .

Attached File : test add shapes.xlsm

View 3 Replies View Related

Cell Values Used In Sheet Names

Mar 29, 2007

I am trying to allow for a cell selection to initiate a macro that would take the top cell in the current column and most left cell in the current row and placing those values into the names of the workbook and sheet that I want to move to.

I am tyring to allow for my users to drill down into data via selecting the cell in question that is referenced from another workbook and sheet. I want to place the value of the cell

= most left cell in current row - 1-1 to be placed in Active.sheet"1-1".select The work bood would pull the value from the most top cell in the current column and place that value in

View 4 Replies View Related

Sort Multiple Values In A Cell

Jan 12, 2010

Need code of function that can sort the values in a cell?

For example:

Cell A1 contains 67,87,1,45,109 (note the commas), is there a quick way to change this to 1 45 67 87 10?

This is only a snapshot of the data I'm working with, I have 400 cells each containing between 10 - 50 numbers.

What I'm doing so far is, for each cell:

Text to Columns
Side Ways Sort A-Z
Copy all values to notepad
Copy all values from notepad directly back into the cell

View 9 Replies View Related

How To Make Tab Names Dependent On Cell Values

Oct 17, 2013

Is it possible to make tab names dependent on Cell Values?

I have 12 Tabs in Workbook 1

Tab 1 = "COVER PAGE"

I would like Tab 2 ='COVER PAGE'!A1

Tab 3 ='COVER PAGE'!B1
Tab 4 ='COVER PAGE'!C1

Is this possible so that I can add cells (between B1 and C1) at a later date and have all the tab names change depending on the values I put in the cells?

View 9 Replies View Related

Define Names As Adjacent Cell Values

Dec 18, 2007

I would Like to define each row in a spreadsheet to the value of the first cell in each row and I have no idea how to go about this.

Code Customer
_0888 Williams
_0435 Smith

So basiclally I would like to name the first row "_0888" being 2:2. But without doing each row manually via Insert --> Names --> Define..

View 4 Replies View Related

Only Sort Cells If Cell Values Are Greater Than 0?

May 14, 2014

I am working on a Golf League score sheet, and each week there may be a different amount of players that play. I have a weekly worksheet (attached) that contains the full list of golfers that had played that week and I only want to sort the columns for those golfers who have a score greater than 0 (this would be the ones who played that week). You can see that in week 1 I had more players than in week 2. So in my weekly worksheet I sort these sub grids by Score first. However I ONLY want to sort the rows that have a score greater than 0. Can this be done in VBA or am I stuck doing this manually every week?


View 9 Replies View Related

Assign Group Names Based On Cell Values

Feb 19, 2010

I have two tables below. First table contains the list of domain names (given just for example, actual list may contain several more such Domain names) with the group names assigned to them. Second table shows the criteria for assigning them the group names:


My concern is to automate this process using a VBA code. How can I search for the keyword given in Table 2 in the first column of Table 1 and assign the group names as it is assigned currently, considering the below conditions and criteria.

1) The correct group name should be assigned to the Domain name containing the keyword given as per criteria in Table 2

2) It is possible that the domain name will include more than one keywords given in Table 2. For example Cell number A7 contains both keywords NCG and EXC, in such cases the preferance should be given to EXC and we will assign the group name as EEC. And in Cell number A12 also contains both NCG and DMZ, again the preferance should be given to DMZ and we have to assign group name as ETIS.

That means we will give second preference to keyword NCG. If domain name contains none of the keywords except NCG as per given criteria then only we will assign grup name DOSS.

View 9 Replies View Related

Rank & Sort Table: Unique Numbers Sort Ascending, But The Non-unique Numbers Sort Descending

Oct 5, 2007

I have a list in rows where I have a ranking formula =COUNT($G$5:$G$81)-(RANK(G5,$G$5:$G$81)+ COUNTIF($G$5:G5,G5)-1)+1 When I sort the rank, ascending. All of the unique numbers sort ascending, but the non-unique numbers sort descending

ex) 1.751

View 5 Replies View Related

Sort The Names In Column

Jun 29, 2008

Is there a way to have this sheet which is named "sheet1" sort the names in column A which is named "Name" by the data in column B which is named "Rank" automatically as the data in column B "Rank" changes?

A B1NameRank2Jones13Smith64White25Green76Alpha17Baker38Top59Low4

View 9 Replies View Related

Assigning Label Names Based On A Range Of Cell Values

Feb 20, 2007

I searched and found that to assign a name to a label based on a cell value requires the following

Label1.Caption = Worksheets("Sheet1").Range("A1").Value

which would assign the value in cell A1 as the caption for Label1. I've got a range of values in cells:


and I'd like to assign them as names to labels 1 through 12. How would I do that using VBA?

View 6 Replies View Related

Sort Names Based On Rank.

Feb 12, 2009

See the attached spreadsheet. I have people's names in cells A2:A5. These names have a rank value in cells B2:B5. What I would like to do is create a formula that sorts/orders the names in cells A2:A5 according to the rank in cells B2:B5. The results I would like to achieve are shown in A8:A11. I am aware of the "Sort" function in the "Data" drop down menu, but need to leave cells A1:B5 as they are.

View 2 Replies View Related

Automatically Sort Names In Spreadsheet

Dec 31, 2012

I have a Spreadsheet with five columns. The column headings begins in rows 3 and 4(merged), these are Reg.#, Rank, First Name, Last Name and Station respectively.

The 5 row is used as a filter row. Therefore my Data Range is from A6:E505. So far I have input some data up to row 25.

Problem: Each time I input data I have to sort manually, I need a VBA Code to paste to automatically sort by Last Name even as I continue to input data in the other rows.

View 4 Replies View Related

Basic Cell Referencing - Return Correct Names And Values On Another Sheet?

May 13, 2013

Here's the data table being referenced

Week Ending


[Code] ........

Now on another sheet, I want to return the top two 'Name's and their values like below:


There is a fluctuating amount of rows in the first table, too. So what formula can I use to return the correct names and values on another sheet?

I'm thinking it will use some form of concatenate for the first and use a sumifs function for the value column..

View 1 Replies View Related

List Files & Cell Values With Specific Sheet Names From ListBox

Dec 4, 2008

I have this script (below, Krishnakumar orignally provided this script).

I'm trying to edit it to add cell values from cells C16 and E16 along with the worksheet name it displays in listbox2. So lets say worksheet "rollover" is the sheet being displayed in the listbox2.

I would like for it to look something like this:

rollover TT:'data from cell C16' TA:'data from cell E16'

Public FilePath As String
Public dic As Object
Public oWB As String
Public oWS As String
Public aWS As Worksheet

Private Sub CommandButton1_Click()
Dim i As Long, wb As Workbook, n As Long
With Me.ListBox2
For i = 0 To .ListCount - 1
If .Selected(i) = True Then
oWS = .list(i)
Set wb = Workbooks.Open(FilePath & oWB, UpdateLinks:=0)
Exit For
End If
End With
End Sub

View 7 Replies View Related

Fill ListBox With Found Values Sheet Names & Cell Addresses

Feb 22, 2008

I have a spreadsheet containing a list of key fob numbers, key numbers, etc. I have 3 userforms with different for different options, i.e. Key fob no. search, key number search, room number search. I have set up the code for each of the userforms to search for the information entered in the textbox and then lists the info in the listbox which I can then click on the one of the listed items to take me directly to the place in the spreadsheet.

However since there are a range of different key numbers per key fob, I would like to know what code I can use to allow a range of information such as the key number and room number, etc to be displayed in the same listbox as the Key fob number i searched for.

Here is my code for one of the Userforms:

Option Explicit

Sub Locate(Name As String, Data As Range)

Dim rngFind As Range
Dim strFirstFind As String

With Sheet1.UsedRange
Set rngFind = .Find(Name, LookIn:=xlValues, lookat:=xlPart)
If Not rngFind Is Nothing Then
strFirstFind = rngFind.Address

I cannot attach a example of my form since the site seems to have a limit on the size of the upload and my part of the file compressed in zip format is still at 168kb.

View 5 Replies View Related

Sort Names Separated By Semi-colon?

Jan 24, 2013

I have a long list of names

Doe, John;Doe Jane; etc..

Is there any way to have Excel separate them into individual cells?

View 2 Replies View Related

Sort Alphabetically List Of Chemical Names

Sep 17, 2008

I have a long list of chemical names that I need to sort alphabetically. A problem arises wherein many of the chemical names are preceded by numbers, as in 1,1,1-Trichloroethane. When Excel sorts the list, it of course groups all entries beginning with 1,1,1- together, then 1,1,2-, and so on, then alphabetically. How can I get Excel to ignore the preceding numbers, and sort only alphabetically?

View 9 Replies View Related

Look At Column In Table Remove Duplicates And Sort To Another Table

Mar 19, 2014

I have a list of parts in a table. I am manually copy/pasting this list to another sheet in my workbook and then using DATA→Remove Duplicates to get a shortened list. Is there a way to automate this process?

In the attached workbook I want the yellow cells to auto-fill for me.

I get a lot of lists in the format on the left hand side so I want excel to automatically format them for me so that I can use the output on the right.

List Sorting Help.xlsx‎

View 1 Replies View Related

Automate Recorded Macro - Sort Range Of Cell Based On Two Values

Jan 23, 2013

I have recorded a macro to sort a range of cell based on two values that is dependent on time in another cell. I now want the macro to run automatically when refreshing the workbook with F9, so as the time changes so will the sorting. Everything works fine except the sorting doesn't refresh when F9 is refreshed.

My recorded macro is:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim X As Long, FillCT As Long
For X = 1 To 4
If Cells(Rows.count, 1).End(xlUp).Offset(0, X - 1) <> "" Then FillCT = FillCT + 1

[Code] .....

View 4 Replies View Related

Filter And Sort Data Based On Names And Hours

May 2, 2014

I just want to filter and sort the data based on names and hours.

Here it goes:

1. Filter Column F.
2. Filter it by: UK & M UK
3. After that, filter Column E in "Ascending order"
4. Count the data that are:

-starts 1:01AM - 7:59AM and display the total count in Sheet2, if there are no data in other specific time, then display a value "0"

For example for UK and M UK:

In Sheet 2, ("F5") = 3 as there are 3 data, G5=1, H5=0 and so on and so forth..

I've attached my file : Filter and Sort.xlsx‎

View 3 Replies View Related

Sort Matching Orders Into Groups And Add Product Names To Breaklines

May 21, 2014

How do I create a macro to sort rows into groups, add break rows in between & insert a product sourced from another excel workbook onto the break line?

I receive a spreadsheet each day with a series of requests, most of the information is the addresses & names etc with the important data in columns K,M & O. Data in each of these columns is a code that refers to a product and information sheet to be sent out to the person requesting it. They may want just one in which case data will only be in column K, if they want two there will also be data in M and if they require three there will be data in column O as well. The bit I do know is how to sort the important data into groups of the same by the country (column I) and the product sku's.

Whilst I can do the sort as a recorded macro I really need it to be part of a larger macro that does the following bit I can't get my head around. I need to insert a break row above each group then by cross referencing the data in the product sku columns to a separate workbook insert the appropriate item / letter combination onto the break line row, telling the volunteer which items to send out. This is a v look up?

I have attached an example of the files we work with, the first tab is with the data simply sorted and the second is what I need it to look like. I normally receive it as a CSV file, there are around 8000 possible combinations of product and instructions and the files are normally around 4000 records long. At present we insert the break lines and copy/paste the information manually, seriously slowing down the process.

Orders Example EUR RM.xlsx

View 12 Replies View Related

Look Up Names From A Table

Sep 20, 2009

I have attched my workbook. As you will see I would like to use this as a template.
Worksheet 1 (fixtures) has the layout for the first half of the season all the places are numbered. Worksheet 2 (teams) has the numbers but as yet I do not know the names of the teams. Is it possible that when I receive the names of the teams I can put them in the teams worksheet and Excel change the numbers on the fixtures list to the names of the teams. I have been doing this for a couple of years now and have done all the working out with pen and paper which can take a while.

View 2 Replies View Related

Table That Has Values Spilling Over To Next Cell When It Reaches Certain Value?

Apr 12, 2013

I'm trying to come up with a way that will populate a table after each cell reaches a certain value.

My first column has rows with the total time required to perform a certain operation. The columns after that, represent operators that should be picking up a certain amount of tasks that do not exceed a set time.

Let's assume that the first row (total operation) has a total of 550 sec. The second column (1st operator) should assign a a value of no more than 200 sec. The third column (2nd operator) another 200 and the fourth column (3rd operator) another 150. So as to create a spill over effect.
The second row has a different operator with a different time. Let's say 300. These tasks should be picked up by a fourth operator with a value of 200 and then a fifth with 100. All in all I would like the table to populate as below.

xxxxxxxxxxxxxxxxxxxxxx operator1operator2operator3operator4operator5
operation 1550 xxxxxxxxxxxxx200 xxxxxx200 xxxxxx150
operation 2300 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx200 xxxxxxx100

ignore the "x". I used those to make sure that the rows aligned with the columns

View 1 Replies View Related

Table References Based On Cell Values?

Aug 13, 2012

I'm trying to put together a dashboard that is referencing a table. Now the dash board I would like to have it so I can change a value and the details will update based off of my selection. This is all drived off of named ranges and have that working. The part I don't have working is dynamically changing what part of the table is being referenced to.

i.e. Say i have modeling quality I have a formula that looks like this

=SMALL(IF(Table1[[#All],[Modeling Quality]]=$D$12,ROW(Table1[[#All],[Modeling Quality]])),ROW($M1))

Which works perfectly. However if I change modeling quality to say modeling experience which has different criteria i need the formula to look like this

=SMALL(IF(Table1[[#All],[Modeling Experience]]=$D$12,ROW(Table1[[#All],[Modeling Experience]])),ROW($M1))

Is there a way I can drive the table reference from a cell selection? I've tried referencing them by manually typing in the cell address to no avail.

View 3 Replies View Related

Copyrights 2005-15, All rights reserved