Create A List Will All Different Occurences
Jun 15, 2007
I have a list with several stocks tickers and the respective quantities and prices on three columns: example below. I wanted a formula to list all different occurrences of each stock only once. Later on I would consolidate all trades.
stock / price / quant / expected list
A / 10 / 1000 / A
B / 20 / -2000 / B
C / 25 / 500 / C
D / 30 / 2000 / D
E / 15 / -2000 / E
B / 20.5 / 1500
A / 10.2 / 1200
D / 29 / -500
C / 25 / 800
E / 16 / 2000
View 10 Replies
ADVERTISEMENT
May 11, 2006
I have been struggling for almost 2 days with this problem.
I have a list of data, one column of which is the date displayed in dd/mmm/yy format.
This date will always be entered by users and is variable.
I can't find a way of counting the number of occurences of each month and each day that the date represents.
So, I want to know how many occurences of March, June, May, Septemeber etc are on this list and Mondays, Tuesdays, etc.
I have tried several posssible routes, DCOUNT DCOUNTA COUNT COUNTIF SUM. I've also tried separating the date out into days & months using the MONTH & DAY functions but this didn't work either.
It also apepars (shock horror) that Excel has incorrect date & day values because entering todays date in one cell (11/05/2006) and then using the DAY function to find the day of the week for this date produces WED when it should be THU. (My system date is set as 1 Jan 1900).
I feel that Excel can't separate the months & days away from the date, since the date is stored as a numerical value and not as we humans use dates.
The dates will always be manually entered by users, probably as dd-mm and Excel will automatically add in the year.
How can I count the occurences of each month and days of the week?
View 9 Replies
View Related
Apr 10, 2009
I need to make a list of items that occur above the string "Room" and the data offset to the bottom and bottom right.
Here is the sample data:
1, 100A
2, Room, Rh
3, 123, 11
3, 200B
4, Room, Rh
5, 456, 24
6, 300C
7, Room, Rh
8, 789, 56
...
On another sheet this is what the output should look like:
1 100A 123 11
2 200B 456 24
3 300C 789 56
...
Here is a copied function that I've been trying to work with. "ROOM_AREAS" is the range in column A. I just can't seem to figure out how the ROW and SMALL functions are supposed to work here.
View 12 Replies
View Related
May 17, 2013
i have many excel workbooks in a folder i want a macro that will get the names of all the files and make the file names so extracted as a hyperlink to open the files.
View 1 Replies
View Related
Nov 21, 2013
I'm trying to create a staff rota which will populate a rota randomly when prompted - I have been trying to find some way of connecting the random lists and the staff names, though this has proved difficult (to say the least!). How best to proceed? I'm also fully aware of the possibility that my present design will also double book people (place then on reception and telephone duty simultaneously).
View 3 Replies
View Related
Feb 25, 2014
Is it posible and how to create a list of barcodes from a list of numbers on the worksheet?
Strokescribe seems to have some ind of solution but the data can't come from the worksheet.
View 8 Replies
View Related
May 23, 2009
I've searched the forum and believe this thread to be the closest to what im looking for, but its doesnt completely apply: matrix to list conversion. I have an attachment to support my questions (see attached). I have a matrix of words (strings) that contain repeating and non-repeating contents. The matrix also has spaces which are of no value. Essentially, the VBA needs to ' analyze' this matrix and create a consolidated frequency list (as shown in the file).
The matrices are HUGE and therefore some of the clumsy VBA i am using is turning out to be a bit inefficient. The file also contains formulas and such that I cannot use AutoFilter nor can I use Insert or Delete rows...so the VBA shouldnt use those either.
View 6 Replies
View Related
May 27, 2014
Eng list.xlsx
The attached file (a copy of my main one) has a list of our engineers, and what stock they carry. The stock parts are the 64, 65, 66... numbers.
I need to create a list from this (as underneath the main table), for all instances where there is a 'Y' in the columns next to each engineer. So if an engineer has 3x pieces of stock, they need to appear in the list 3x times. If they have 1x piece of stock, they appear in the list once.
View 8 Replies
View Related
Mar 17, 2007
I Attached a sheet for what i'm asking about ,, i sent it before but the sheet showing it more clearly
View 10 Replies
View Related
Jan 4, 2013
I've attached a spreadsheet showing the effect I'm trying to achieve. A list (which changes weekly) contains employee details and length of service. Based on the length of service I need to build a new list grouping the employees based on the service length.
list-test.xlsx
View 6 Replies
View Related
Feb 6, 2014
I am looking for a formula to search two columns.
1) The first column needs to be search to match a text string.
2) If the text string matchs, I need excel to evaluate the number in the second column and find the MIN value of all numbers associated with the text string.
So:
Joe 50
Jim 12
Joe 10
Rob 25
Jim 8
Rob 99
When searching for "Joe", the function would return 10. When searching for "Jim" the function would return 8. For "Rob", it would return "25".
View 4 Replies
View Related
Apr 5, 2014
I need to create a mailing list for people located in a specific area from a master list.
The address data is not in great shape so I need to check address field 2 and address field 3 in the list to see if the suburb in the location tab appears in either of the two fields.
Desired result appears in column F of the List tab of the attached spreadsheet.
seminar addresses.xlsx
View 2 Replies
View Related
Nov 28, 2007
I have a list of data which looks like this
Column B
Name
---------
Jones, Bob
Jones, Bob
Jones, Bob
Smith, Mike
Smith, Mike
Smith, Mike
Calai, Dave
Calai, Dave
Calai, Dave
Etc...
What I want to do is take the entire list and create a distinct list that I can use in a named range then use a validation list to have a drop down within a different tab.
View 9 Replies
View Related
Jan 26, 2009
I need to create a 'sub' list from a drop down list.
I have 8 categories in a drop down list in column A, but i need column B to also have a drop down list of sub categories depending on what category is selected in column A.
And once column B is populated, i need column C to auto populate with a value. All data is stored on the spreadsheet.
View 9 Replies
View Related
Jul 22, 2009
In sheet 10 there is a list of hunderd of names with information. The information is download several times a week and is not sorted in any way
Column A - Name of sales agent
Column B - Property Name
Tabs 1-9 represent all the different sales reps
Here is what I want to do (I realize I can sort the data and copy paste but it is pain):
Tab 1
Cell A1 - "John Smith"
Cells A2-A50 I want to list the names of the properties in column B of sheet 10.
Cell A2 would find the first occurence of "John Smith" in column A of Sheet 10 and put in the data from column B.
Cell A3 would find the second occurence of John Smith in column A of Sheet 10 and put in the data from column B
Cell A4-A50 would find occurences 3-50
View 4 Replies
View Related
Jun 25, 2009
I'm putting together a database in Excel and there is a particular item that I am having trouble with. I need to count the frequency of occurences in between events over a series of cells.
This:
ABBBAABBAB
Needs to be tracked like this:
ABABAB132211
Basically I need to write it so that when A occurs it counts how many times B occurs immediately after, until the next A. Once that is figured out I should be ok counting double A occurences.
View 9 Replies
View Related
May 13, 2008
how to count unique Occurneces in Excel?
For example: -
What formula can I use to count the Unique Occurences non - zero values in Column 2 for the letters in Column 1?
Column 1 Column 2
A 1
A 1
A 1
B 0
C 2
C 2
D -1
View 10 Replies
View Related
Feb 12, 2010
I need to count data from column A to column C
whereas:
Column A - has data but there are data which occur more than once then it will be counted to column C
Reason is I need to delete the data with several occurence at column A so when I see the count at column C then I can be aware how many times the data at Column A occurs so I can delete it by filtering the data.
View 9 Replies
View Related
Jan 19, 2010
I have a list and the list contains the same string multiple times. I am trying to take that list and print it in column D and then list in column E the number of times that string appears in column A. S for example I would like the list to output
Col A | Col B| ....| Col D | Col E |
String1__________String1 3
String1__________String2 1
String1__________String3 2
String2
String3
String3
View 3 Replies
View Related
Jun 22, 2007
how many dates appear in a column. I have a spreadsheet wherein when a name is encoded in column A, the date is automatically logged in column B. Now, i need to count how many of those dates occurred in column B.
For the month of May - how many 1 May 2007, 2 May 2007...an so on.
After that - i need to match those dates to the name of the encoder and a another encoded status. It's like - 1 May 2007+john+approved = 1 occurence. I need to count them separately and combined.
View 9 Replies
View Related
Jul 31, 2008
A few days ago I used the function from a good member here:
=TEXT(B43/B$42;"0,00%")&" ("&RANK(B43;B$43:B$68)&")"
Can I use a function that counts how many times the rank value (1) for example appears in a row.Different row underneath will be OK.
View 9 Replies
View Related
Nov 23, 2009
I am using a code to validate that a person enters only an email address in a certain cell. I would like to confirm that the address has only two occurences of ".", and of course one occurence of "@". What would be the easiet method to do this.
View 9 Replies
View Related
Mar 3, 2010
I'm trying to formulate something that will count how many times say 5 comes up in a =randbetween(1,20) function. Of course if I do =(IF(A1=5,1,"") it will count it, but once the randbetween recalculates it will reset. Is there any way to do this so it keeps a cumulative count?
View 9 Replies
View Related
Jul 10, 2007
I have been given a spreadsheet to calculate. I think the data is very badly laid out but I cant change it. I have attached a sample from it. The original has 1000 people (accross row 1 in multiple sheets) and has approx 200 rows. I only have to calculate sheets individually.
I need to count all occurences in the row for;
Current Level = 1 and Training Requirement = 1 ( Cell B4)
Current Level = 1 and Training Requirement = 2 (Cell C4)
etc for all Currant Level 1-4 and Training Requirement 1-3.
I have been looking over how to use Sumproduct but I don@t see how to use it here.
View 4 Replies
View Related
Jan 8, 2008
I have and interesting delimma and I have been spending way too much time trying to figure this out.
I have a value in column B "Agreement". I have data in column C which could be just about anything. I need to be able to find "Agreement" copy the value immediately above it and paste it next to the value in column C.
You see this gets difficult for me, because there are many spaces in the spreadsheet and there may or may not be a value in C.
Here is an Example: I need for the value above "Agreement" to bel placed in the same column where there is a value in C? Clear as mud. I need this for every occurence of "Agreement" in this column. There are many occurences of "Agreement"
View 5 Replies
View Related
Feb 7, 2008
how to write a search and replace macro, but instead of writing:
Cells.Replace "OldText", "NewText"
how to independently read this "search" text into a string (hopefully a string that can handle 'more' than 255 characters per cell, even in Excel 2000) then replacing that search text("OldText") with ("NewText") step-by-step?
("OldText" can appear multiple times in one cell)
View 6 Replies
View Related
Apr 15, 2008
I'd like to evaluate a series of columns where a value greater than 1 would return all matches in another worksheet. I can't seem to use LOOKUP or Pivot Tables effectively to do this.
For example
Task Week1 Week2 Week3
task1 5 8
task2 6 10
task3 5
I'd like to create a view that would show Week1 having one task (task1) that will take 5 (hours). Week2 should show task1 and task2, Week3 should show task2 and task3
View 4 Replies
View Related
Aug 10, 2008
I am trying to get this code to loop through a dynamic range on one sheet and copy all occurences of a name (John Doe) to be copied into another sheet in a single column, but a new row for each occurence of the name.
I believe the problem is in looping the findnext statement,
Sub find_actor()
' Keyboard Shortcut: Ctrl+Shift+A
Application. ScreenUpdating = False
Dim random As Range
Dim connector As Range
Dim paste_connector As Range
Set random = Sheets("database").Range("C2")
View 7 Replies
View Related
Oct 21, 2009
I have a database output file where one of the columns contains managers names, often more than once. I want to apply an autofilter on manager name and then copy the result to another sheet or sheets. My criteria for the autofilter is a variable pointing to a list of names that at present I maintain by hand; a for-each-next loop then cycles through the names.
What I would like to do, before running the autofilter code, is to create the list of names via code. This would then automatically pickup names that are missing.
The code I have so far is below:
Public Sub find_managers()
Dim managers1 As Range
Dim names1 As Range
Dim n1 As Variant
Dim n2 As Variant
In my mind it should check the names in the unique list against the imported list and add any missing names.
View 9 Replies
View Related
Dec 11, 2008
I do not know how to create a drop down list to ensure the user inputs only the values from that list. Once I figure this out I would love to add a drop down calender for a cell as well.
View 14 Replies
View Related
