I've got this table that I want to sort. I've got it formatted as a table in Excel 07.
ABDescription
11Sunbake
24Make Sandcastle
35Apply Sunscreen
46Sip Coconut Mocktail
52Play with kids
66Seashells
72Starfish
83Swim
91Snorkel
2Surf
3Boogieboarding
4Eat Icecream
1Play beach volleyball
I'd like to sort the table where all the 1's are together and in line with column A ie......
i am looking for formula in my macbook pro excel where i have purchase apple 50kg for $50 and grapes 20 kg for $30 and orange 10kg for $15 and am doing this daily and in end of month i want to see separate kg's and amount for whole month and later year of apple, grapes and orange without sorting it
View 1 Replies View RelatedI work for a printing company that prints labels. We have 6 printing presses, lets call them A,B,C,D,E & F. Certain labels can only be printed on printing press F.
From our business system I can run a report in excel format giving details of all the labels that we have sold in the present month. I need split that report into 2 separate excel spreadsheets; 1 showing everything that was printed on presses A to E and the other showing everything printed on press F.
I also have another excel spreadsheet which gives a complete list of all the labels that have to be printed on press F.
Every label that we print has a part code and both spreadsheets show this part code so that would be the linking factor between the 2 sheets.
I have two lists. A1:A7000 has 7000 items and B1:B10000 has 10,000 items all in random order. List B includes all the items on list A, but with an additional 3000 items. How can I extract the 3000 items that are exclusively on list B? I would like to put these on a new list C1:C3000.
View 6 Replies View RelatedI have a spreadsheet (rows 1-15) where column A is a list of items in ascending order, and column B shows the catalog# that the item appears in. An items can appears in multiple catalogs.
I would like to develop a spreadsheet(rows 18-21) that shows the items that appears in each catalog.
The items should still be in ascending order.
The order of the catalog# row18 does not matter.
I have several hundred items and 30 catalogs....
I have a price table with about 70,000 line items (some lines are duplicates).
There are about 12 columns each with different qualities such as item name, size, thickness, price and etc.
I've been building this table in excel and just importing it into Access for the time being.
When a customer sends me an order, I'd like to be able to look up the items in my price table (matching across multiple columns) and automatically return the price for each item.
So far, I've been using the following formula....
=LOOKUP(2,1/($A$1:$A$60000=D1),$B$1:$B$60000)
With A5:A60000 = price list concatenate so all columns in 1
D1 = item I'm searching for
B1:B60000 = prices for items
The only thing is that this is very labor intensive.
The only thing is my customers will typically submit an order and it is NOT in the same format as I need it to be.
So it's very labor intensive and I spend hours just editing their order in a spreadsheet so that it follows the format I need it to.
Im developing one excel vba file to an entity .
My main objective is speed and automatization in the competition organization.
Im almost finishing it but now im stuck in this situation:
I have a table with 13 cells
A: athlete name
B: athlete weight
C: random number (raffle)
and then from 1 to 10 is the final Groups.
E.g
Name
Weight
No
1
2
3
4
5
6
7
[code].....
Now i need to create a macro to display the itens in the groups when the weighing is finished...
E.g
Name
Weight
No
1
2
3
4
5
6
7
8
9
[code].....
The conditions are:
The numbers should be displayed in the athletes groups
The difference between all the athletes weights shouldn't be > 3 kilos If theres is more than 5 athletes per group the heaviest should go to next athletes group and pop up one message informing that maximum athletes per group was reached...
I have a database table with selling prices. I want to get a count of number of customers by sales price range.
For example:
Price Count
$0-$5 #
$5-$10 #
$10-$15 #
Total
My nose says this is what a pivot table should do but there are too many sales prices and the pivot table craps out. How do I get them into ranges as above and then count them?
I am trying to create a macro where users can rearange data in a table. I used the macro recorder to sort the data in colmn C in descending order and this is the code that I got:
VB:
ActiveWorkbook.Worksheets("By Client").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("By Client").Sort.SortFields.Add Key:=Range( _
"C3:C176"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("By Client").Sort
[Code]...
This works for the table as it is but the range may change as more rows are added to the table. I want it to start at row three as the first two rows are headers but in future tables the bottom row may not be 176. I dont want to include all rows as row 177 contains totals that I dont want to be included in the sort. what I should put as the range so that this macro will work regardless of how many rows are in the table?
I am trying to sort by date in a table (It is named Linked_file_table), C173:I188. I would like the table to be sorted by I173 AZ.
View 1 Replies View RelatedI have managed to produce a table which uses formulas to calculate the amount of 'points' someone/a team has accumulated over a period of time however it seems that for some reason or another I cannot sort the table into order of 'who has the most points'
PosPlayerPlayedWonDrawLostPoints
1stDean120939
2ndJaymo1239018
3rdMacca1229115
4thRoss1219212
above is the final standings but as you can see the 'sort' function isnt working, and if i do any other sort the name of the person and the points no longer correspond... i.e. it ends up putting the names in alphabetical order and leaving all other columns as they currently are, or it changes the points column, leaving the names in the same place...
I've just come across Excel doing seemingly doing something really rather daft - though it equally could be me. I've grouped dates in a pivot table to 7 days at a time to group weeks, but when I then sort by date order it sorts by all those ranges starting with a 1st of the month, followed by all the 2nd's, etc.
View 3 Replies View RelatedTrying to format a pre-formated table. Would like to have all records for each item on one line so to be able to create a regular table and then a pivot table from it.
Pre-formatted table is attached as Samplesort1.xls.
I do not have SQL.
Is it possible to do this without macros?
Is it possible to have dynamic sorting in a table?
What I am after is a scoreboard type effect where as results are entered, the teams are sorted automatically, rather than entering all of the details and then doing a sort afterwards.
I have attached a table where I would like the sorting of columns B - E done in the following order (All decreasing order):
Comp Points first
then by Sets %
then by Points %
I am drawing this info from another table. Will this have an effect on if it can be sorted dynamically? Hoping that as i enter a result in the other table, it will bring across the current comp points, set % and points %, and then sort the table into the rankings all at once.
I have created a pivot table that contains the following data:
Country Name
Date as in 5/1/08, 6/1/08, etc
With counts in the cells
I can sort on Country name and get an alphabethical list by country name showing teh numbers for each date
However I want to sort by the largest to smallest number that shows in the date column 01/01/08
Is there a way to sort on that colum-while moveing all the numbers in the other date columns along with it?
I've created a PivotTable using VBA that contains hundreds of PivotItems, which would look bad when a PivotChart is made.
I'd like to set the PivotTable to make visible only the first X items (let's say 10). How would I do this in VBA?The macro recorder gives me the name of the PivotItem, but this varies so I'd like to use an index:
VB:
ActiveSheet.PivotTables("PivotTable4").PivotFields("UWI").PivotItems("Item1").Visible = False
ActiveSheet.PivotTables("PivotTable4").PivotFields("UWI").PivotItems("Item2").Visible = False
See attached.
Basically, I just want to do a pivot which shows the Name and Number for those names listed in the LIST tab.
The pivot source is the data tab, and the result is in the result tab.
I have the code to create the pivot, but filtering it for those specific names on the LIST tab is where I am getting stuck.
Attached File : Excel VBA Pivot Problem.xlsx
Items, of 3 different groups, and their values are entered in a table progressively, over a year period . I am trying to work out a way of getting the month total value of each of the 3 groups seperately, for each month of the year. A sample worksheet is attached
Is there a way to have a Pivot Table show only the Top 10 items based on dollar amount. Data covers a month of daily activity (+/- 250 rows), but i only want the Top 10 items based on Dollar amount. Is this possible?
I know filters can do top 10 but it doesn't consolidate similar items.
I've created a pivot table that is not grouping "like" items. I have verified all fields are numbers using the =isnumber() formula. All items present with the "true" value. Just in case, I tried doing text to columns and refreshing the data but that did not work either. All items are formatted the same. All data fields have values.
View 1 Replies View RelatedI'm working on a travel form, which allows people to enter a travel itinerary. From that, I want to be able to extract the cities where they are spending one or more nights - so that I can then do the calculations for accommodation allowances.
I am able to calculate the number of nights stay in each city where there is an overnight stop. But I'm stuck on how to extract every combination of City and Nights where Nights is greater than zero - there is no need to calculate for cities with no overnight stay.
I have attached an example spreadsheet.
Some notes:
- the itinerary and accommodation tables have to remain separate, as they hold more data than in the example,
- the itinerary table can't be sorted for number of nights. It has to be in chronological order for each segment!
Every lookup function I check out seems to demand a sorted table.
I'm trying to sort a table as displayed in the image below. The table consisting of items in column A, here fruits, and a column E where totals over three month is displayed.
I have entered an array formula in column H where the total of each fruit is summed and then sorted in descending order. My problem is that whenever two fruits have equal value (here, apple and pear), Excel will not manage the display of both fruits, it will just display the first twice. The formula in column I is a simple SUMIFS.
It doesn't matter if Apple or Pear is displayed first, but I need both items to be displayed in column H.
excel1.png
I would like to know If we can sort the pivot table data in a bunch of Groups. e.g 50 years based on decades. currently I am able to group only once in a given range. but I would like to define several more ranges e.g 1970-1990 and 1990-2010 and etc
View 2 Replies View RelatedSo i have a column full of first and surnames (both names in one cell). Is there a fast way to arrange them in alphabetical order? (surnames)
View 2 Replies View RelatedI can't seem to be able to find out how to sort data within a pivot table.
I have about 1000 different names that have data values for two years. I have a calculated field to show the difference....
I have a birthday list with name, client, birthday. I want to create a pivot table that groups by month. Report layout is tabular. Then within that month, name, client & birthday (in date order). So my end result would look like;
January Jim Jones Company 1/4
Then rest of January names 1/15
Etc.
Right now, I can't get the birthday to sort by date within the month.
Created the following macro, but doesn't seem to work.
Sub SortTable()
With Range("TC_Table")
.Sort key1:=Range("TC_Table").Cells(1, 1), Order1:=xlAscending
.Sort key2:=Range("TC_Table").Cells(1, 2), Order2:=xlAscending
.Header = xlYes
[Code] .....
I am having the hardest time trying to get my pivot table to sort right. I downloaded data over a 7 year period and its sorted by months (January-2005 etc). The issue I am having is excel is putting it into this format
January-2005 January- 2006 ......... February - 2005 February-2005.
I need it to sort chronologically
jan 2005, Feb 2005, March 2005...... Jan 2006, Feb 2006 etc.
What do I have to do to get this fixed. I would rather do not have to do it manually each time I need this type of data.
So, I have a table of scores from players for a category I have. It looks like this:
Place-------------Name-------------Country-------------Score
1st---------------Jim----------------USA--------------------376
2nd--------------Michael-----------Germany-------------364
2nd--------------Tim---------------USA-----------------364
4th---------------John--------------Norway-------------355
if one of the guys, for example, John were to improve his score to a 369, I'd change the score in Excel, then use the sort feature to put him above Michael and Tim. Only problem is, it would say his place is still 4th place, and it doesn't change to 2nd place.
I want it so the "Place" column automatically changes to the correct rank based on the score the person has. So if I changed John's 355 score to 369 and hit sort by A-Z, Excel would automatically change the "Place" he's in appropriately so it would look like this:
Place-------------Name-------------Country-------------Score
1st---------------Jim----------------USA--------------------376
2nd--------------John--------------Norway--------------369
3rd---------------Michael-----------Germany-------------364
3rd--------------Tim----------------USA-----------------364
I have a pivot table and i am trying to display the rows in order of relevance. I have a bit of code that will sort the rows into the order I want them which I have included. My problem is that sometimes I will have rows that will not be there, If position 3 doesn't exist I would expect it to display 1,2,4,5 but instead I get an error message.
ActiveSheet.PivotTables("PivotTable11").PivotFields("Baan Order").PivotItems("Nomination") _
.Position = 1
ActiveSheet.PivotTables("PivotTable11").PivotFields("Baan Order").PivotItems("Full") _
.Position = 2
ActiveSheet.PivotTables("PivotTable11").PivotFields("Baan Order").PivotItems("Equal") _
.Position = 3
ActiveSheet.PivotTables("PivotTable11").PivotFields("Baan Order").PivotItems("Target") _
.Position = 4
ActiveSheet.PivotTables("PivotTable11").PivotFields("Baan Order").PivotItems("None") _
.Position = 5
