Count These Sorted Values
Jul 3, 2009
Still i could manage with Sorting of data. Now i have following data
Name Project Artha Artha Prj Artha Artha Prj PBB ACR Seema Brew Seema SEMC Seema handset dev & testing Seema SEMC Seema handset dev & testing Shashi PRJ1 Shashi PRJ1 Surajit FLV Surajit FLV Surajit FLV
And i want to have following result using a macro
Name Project Count Artha Artha Prj 2 PBB ACR 1 Seema Brew 1 Seema handset dev & testing 2 Seema SEMC 2 Shashi PRJ1 2 Surajit FLV 3
View 9 Replies
ADVERTISEMENT
Jun 10, 2014
I am currently using a pivot to return and populate the required data but this does not refresh correctly and returns zero values every time this report is refreshed. I am therefore hoping that there is a way to calculate this using formulas .
The following applies:
The attached document has 2 tabs: Date AND Report
The Data tab feeds the report tab
The REPORT tab has 2 reports in it but doing the first one then I should be able to do the second one. If you look at the first table you will see the data that needs to be populated. I have added a highlighted (in YELLOW) row at the bottom of this table which shows you which columns populate what column
Requirement:
I am after something that will populate this table in a descending order - IE: the figures in the DATA tab Column BE should be sorted from highest to lowest. It must be filtered to the top 30 results only and then all the other columns must be populated.
Column H in the REPORT tab should be formatted to [h]:mm
Column F & G in the REPORT tab should be formatted to dd/mm/yyyy hh:mm
View 2 Replies
View Related
Jun 17, 2014
My excel sheet contains a set of date-marked expenditures. It's all one running list - no starting a new column for each month or anything like that.
I'd like to include a separate column/sheet showing total expenditure for each month. I've tried multiple formulae, along the lines of "Sum the B cells but only those for which the corresponding A cell is in the right value range". Or in programming terms, "for i from 1 to infinity, if x < A(i) < y, add B(i) to sum." Since other formulae on the sheet are independent of item ordering (don't require the data to be sorted chronologically), a plus would be if the solution can be, as well.
A separate thing I'd like to do (which I'm guessing has a similar solution) is to include grand (running) totals by expenditure type. A separate column of (non-numerical) expenditure codes is next to the data.
View 5 Replies
View Related
May 24, 2013
I am using the formula below to find the # of hours that staff worked.
=SUMPRODUCT(('Data 1'!$B$2:$B$601=B7)*(LEFT('Data 1'!$A$2:$A$601,2)=LEFT(C7,2))*'Data 1'!$D$2:$D$601)
B7 = Last Name
Left(C7,2) = First 2 Letters of the First Name
$D$2:$D$601 = Hours Worked
My only problem is that some staff show up in the list on "Data 1" twice as they work in 2 separate stores and I am only getting the hours worked at the first store. Is there a way to have excel add the hours for both stores? My data is sorted alphabetically so they will be one above the other.
View 4 Replies
View Related
Jul 20, 2014
I use this formula, but it only works if I provide the first name on the list, otherwise I get 0 in all rows, instead of the unique values in alphabetical order.
Code:
={IFERROR(INDEX($A$2:$A$200;MATCH(TRUE;COUNTIF($A$2:$A$200;"
View 8 Replies
View Related
Jan 20, 2014
I need a way to find a value in a column which has one or more corresponding values in an adjacent column. Then take all of the corresponding values found and count all occurrences of the found values in another column. But I only want to count the entries if an adjacent column is not blank.
Not the easiest thing to describe. Starting to wonder if I need to think in reverse. I hope the attached example makes more sense.
Book1.xlsx
View 2 Replies
View Related
Sep 23, 2005
I require a Formula to calculate the INTERVALS (the number of Rows between
the LAST instance and the PREVIOUS instance in a column) between each
individual occurrence of any designated PAIR of Numeric values (single-digit
/ double-digit) in the same Row of the Named Range "Results" and return each
calculated INTERVAL result to a separate Column on the same Row of a New
Sheet - starting with the most recent ( the LAST) occurrence.
For instance, each time 80 and 87 appear together in the same Row, return the
INTERVAL by calculating the number of Rows between the LAST instance and the
PREVIOUS instance in a column - locate when both Numeric values LAST appeared
together and Count back to their PREVIOUS appearance together to get the
required Count; i.e. count from the Row ABOVE LAST appearance to the Row
BEFORE PREVIOUS appearance.
The results are returned to a chart / matrix layout: I have the criterion
vertically and horizontally and they are referenced using the horizontal and
vertical cell address that houses each criterion, and the results are
returned across the Row of the intercept of the vertical and horizontal
criterion. At some point both criterion values being referenced will be the
same, can the Formula return empty text "" when this occurs?
Example Chart / Matrix Layout:
Cell Ref. A2 and B1 criterion 80 and 80
Cell Ref. A3 and B1 criterion 81 and 80
Cell Ref. A4 and B1 criterion 82 and 80
Criteria B1 houses 80
A2 houses 80
A3 houses 81
A4 houses 82
A5 houses 83
View 12 Replies
View Related
Mar 2, 2014
I need a Macro (not formula) which compares the comma separated values present in Column "I" with individual values present in Column "D" and generate the count of unique values in Column "J".
The sample sheet has been attached for reference.
View 3 Replies
View Related
Mar 14, 2014
I'm running into an issue trying to calculate unique values in a Data column based on a few variables in other columns.
My current formula in Summary tab D4:D19 is
{=SUM(IF(FREQUENCY(IF(Data!$I$3:$I$66<$E$1,IF(Data!$A$3:$A$66=$H$1,
IF(Data!$C$3:$C$66=A4,ROW(Data!$I$3:$I$66)))),ROW(Data!$I$3:$I$66)),1))
+
SUM(IF(FREQUENCY(IF(Data!$I$3:$I$66<$E$1,IF(Data!$A$3:$A$66=$I$1,
IF(Data!$C$3:$C$66=A4,ROW(Data!$I$3:$I$66)))),ROW(Data!$I$3:$I$66)),1))}
This is currently counting the number of times a date value (data column I) appears for that name (A4:A19) in the data when meeting all of the conditions. I need it to instead count the number of times a unique date appears for that name with the additional conditions met (which all appear to work fine).
The results in the pink highlighted cells (Summary column D) should be:
Names starting with A - 3
All others - 2
I've left some other columns in the data with X's so that I can easily convert this back to my working spreadsheet.
View 2 Replies
View Related
Sep 13, 2006
I have an array that I'm using - and it works perfectly: =SUM(If((FREQUENCY(If(LEN(B10:B100)>0,MATCH(B10:B100,B10:B100,0),""),If(LEN(B10:B100)>0,MATCH(B10:B100,B10:B100,0),""))>0),1))
Now, what I would like to do, is see how many of these unique numbers relate to another number in column D
See attachment.
View 4 Replies
View Related
Mar 29, 2009
When it is sorted by ascending and next time when they click header I want it to be sorted by descending.
Right now I have implemented to sort Descending . Now how will I check in which order does it sorted?
If know the order in which it is sorted currently then I will sort it in the opposite way..
View 6 Replies
View Related
Mar 5, 2013
I have a data set of about 600 records. I want to be able to extract the Top 10% of the records and get rid of the other 90% - the values can change over time, so I always want to start with the full 600 records.
I am aware of the conditional formatting function that will identify the Top 10%, but I was looking for VBA language (does not have to be VBA - just automated), that calculated the 10% subset and either moved it to a different sheet, OR deleted the bottom 90% - in either instance the result being just the list of the top 10% of the data set.
View 9 Replies
View Related
Sep 19, 2006
I have a list of entries on one sheet. This sheet contains part numbers in random order. I would like to draw out the part numbers onto a new sheet, with no repeats and in numerical order. I want this to be done automatically without using a sort command. Also, I would like to avoid VBA if possible.
View 8 Replies
View Related
Apr 21, 2013
I have an issue sorting values. i can get the numbers together and sorted, but want to keep a name associated with the values being sorted. so when the values are sorted, and they change the name associated with each value moves with it. Its acutally a first-tenth place in a nba fantasy league.
View 7 Replies
View Related
Jan 8, 2010
Does anyone know of an Excel object class that you can put objects into with a key and the process it in key-sorted order? If not, is there a library that I can reference to get such a thing?
View 6 Replies
View Related
Oct 27, 2011
Is there a way to run a macro after a worksheet is sorted (after sort event)?
Scenario: I have a several hyperlinks that use defined names to jump to locations throughout a worksheet. When the sheet is sorted the defined names don't update to the new cell locations (Microsoft KB 58245). I've written a subroutine that fixes the defined names but I don't know how to have the fix run automatically.
I prefer not to use Worksheet_Change without other conditions to prevent a lot of unnecessary calls to repair subroutine.
View 1 Replies
View Related
Sep 20, 2013
Explaination of Data:
I have 2 Columns in the "RawData" sheet in the file attached. One says the Campaign No, and the other says the Site Code. One Campaign, will always never have duplicate Site Code.
I want to organize the data in the following fashion (as seen in the "Format" Sheet). I wish to acquire the trends of grouping of Site Codes, meaning which site (by Site code) is sold together, to understand the hottest combinations.
The idea is to see which Site Code sells more with a particular Campaign.
As you can see in the attached file, I'd like to know how many times a particular Sites (by Site Code) was sold with the other. As you can imagine, I have about 300 Campaigns, but have about 1500 different Sites codes to deal with, this activity will save me hours of time.
The sample file is uploaded on Google Docs. [URL] .........
View 1 Replies
View Related
Aug 19, 2008
I have data in a spreadsheet that need to be re sorted. Here is the data:
Name Address Address Address Phone
XYZ 123 Main NA WI, 12345 123-123-1234
ABC 123 South NA AZ, 1111 456-456-4567
is there a way (i.e marco) that can reorder this info to look like this?
XYZ
123 Main
NA
WA, 12345
123-123-1234
ABC
123 South
NA
AZ, 1111
456-45-4567
View 9 Replies
View Related
Apr 21, 2009
Is there any way to determine whether a column is sorted? Like a property of a column like ".ascending" true or false?
View 9 Replies
View Related
Jan 27, 2009
I have an Excel sheet with a list of data, which is likely to change frequently and has to remain in sorted order.
Requiring that users of the spreadsheet maintain the data in this way (i.e. sort it every time it changes) is a really bad solution and I'd rather not use a macro if I can avoid it (too much stuff can go wrong).
Is there a good way of doing this? At the moment, I've implemented merge-sort within a sheet, which works, but is a heavyweight solution, taking 6 columns per merge-sort iteration (so 60 columns to sort 1024 rows, 96 columns to sort 65536 rows). I could try to compact it, but my head was starting to implode programming it even with as few as 6 columns.
There must be a better way of getting Excel to maintain an automatically sorted list.
View 11 Replies
View Related
Aug 22, 2013
I have data that I need to be dynamically sorted in the highlighted manner. The values on the selection criteria are dynamic.
View 14 Replies
View Related
Jan 4, 2008
I want to copy the first 30 rows of a autofilter list can someone point me in the right direction?
View 13 Replies
View Related
May 1, 2009
Dependent Dropdowns from a Sorted List:
i have one file here..
how did they created validation here.
is there any macros?
i tried..but i didnt able creat like this.
i got this file in this link.
http://www.contextures.com/xlDataVal13.html
i saw this problem in this post..
http://www.excelforum.com/excel-prog...hierarchy.html
View 6 Replies
View Related
Jul 13, 2009
I have been struggling to figure this out for several days now and i don't really know where to go from here.
I have 2 columns and i need to break them up at certain points.
The attached excel file should hopefully be clear enough to show what i mean.
The first tab is how the data starts the second tab is how i want it to look.
To clarify i need to break it after each change in the first column. ie from 1 to 2.
Furthermore, i need to break the "sub groups" the second column up after 15, and 30. I will never have a number larger than 32 in the second column.
I think the excel file will clear up what i mean.
View 13 Replies
View Related
May 13, 2009
if i have a column of number that is not sorted(it cannot be sorted due to other requirements), is it possible to find the cell that reflects the nearest number that i want to look up?
View 4 Replies
View Related
Sep 28, 2011
I have a list of strings as such
A1: A
A2: B
A3: D
A4: E
How can I check to see if this range is sorted with vba?
I have searched but havent found anything for strings.
I can only think of adding each cell to an array, creating a copy array, sorting the copy array and then comparing the two arrays. I want it to return TRUE, FALSE or 1, 0
View 3 Replies
View Related
Apr 27, 2012
When clicking on the sorting button of filtered data near the top of a field, I can select different things to show or not show. In a date field, it starts to arrange things by year, which can be expanded to months, which can further expand to show individual days. All well and good.
But many of my dates do not show up in the year categories. They are instead shown below the years as individual dates. Furthermore, sorting the date field in question is imperfect for these dates. Here is an example where some dates from april are jammed inbetween others:
4/2/20124/2/20124/2/20124/2/20124/20/20124/20/20124/20/20124/20/20124/20/2012
...
4/27/20124/27/20124/27/20124/27/20124/3/20124/3/20124/3/2012
These jammed dates are some of the same ones that are orphaned outside the year groupings. All the dates have the same date cell type. No other filters are applied.
View 2 Replies
View Related
Dec 12, 2013
I am getting a debugging error when I try to highlight the first row of a column that is sorted. the error occurs on the "if .autofiltermode then" line. the code does highlighting like I want but is giving me a run time error.
HTML Code:
Private Sub Worksheet_Calculate()
ColorAutoFilter
End Sub
[Code]....
View 1 Replies
View Related
Mar 9, 2008
I have a matrix of mixed numbers (going across many columns and rows) in Sheet1. They are a mix of positive and negative numbers, In Sheet2 I need 2 lists to be automatically displayed, taken from the data in Sheet1.
The first list (column) is for positive numbers only, the second is for negative numbers.
There is one filter: only numbers over a specified value (for the positive list) must be used from the data, and only numbers under a specified value (for the negative list) must be used from the data. The 2 filters can be stored in any cells in Sheet2 (the worksheet where the lists will be), lets say, for ease of use, A1 and B1.
But here's the tricky part: I need the location reference of each number in the matrix displayed next to the number in the list!!! Eg: in the matrix, I have names running down the left (A2 to A25) and the same names in the same order running across the top (B1 to Y1) - just like any basic matrix! If A6 is called "John" and L1 is called "Pete" and their value (at cell L6) is 10, then I need them to be displayed in the "positive list" amongst the others (sorted automatically into numerical order) with the words "John" in the cell to the right of the score, and "Pete" in the one after that to the right: so it will look like:
A2 B2 C2
10 John Pete
(remember that these 2 lists are on Sheet2, not on the matrix worsheet! A1 and B1 contain the 2 filters for the positive and negative lists).
Of course, this must only happen if the filter in A1 (the positive filter) is 10 or under. If it was anything above 10, then this one would not make the list.
As for duplicates in the matrix coming up twice in the lists, that's not a problem this wont matter! It's ok for the lists to have constant duplicates displayed eg: 10 John Pete will also have next to it 10 Pete John.
View 9 Replies
View Related
Oct 24, 2008
How do I split a worksheet by sorted ranges and then save each range as a new workbook?
For example:
How would I split the data on the left into the ranges on the right of the image below?
View 9 Replies
View Related
