Return All Row / Column Numbers Of All Occurrences Of A Value In Database
Jul 8, 2014
Say I have a certain range in excel from A1:C10. All of these cells contain one of three strings: Math, English, or Reading.
Given an input of ONE of these three (for example, a user can select "Reading"), the program should output ALL the row&column numbers of each occurrence of "Reading" in the range. Ideally, this would appear in a column.
Consider the following image that contains a sample range:
Screen Shot 2014-07-08 at 12.26.15 PM.png
If "English" is the input, the output should be (in another column)
A1
A7
B3
C2
C3
View 5 Replies
ADVERTISEMENT
Nov 7, 2008
I'm trying to lookup a database named database and return a time in column A, based on criteria in cells a1 and b1 on another sheet, A1 would contain a number and B1 would contain a day from mon-fri
eg of Database
A B C D E F G
10:00 5000 Mon Tues
11:00 5000 Wed Thur Fri
and so on
if a1= 5000 & b1=Wed
how can I return 11:00
I have tried index and match =index(a:a,match(a1&b1,b:b&e:e,0))
View 9 Replies
View Related
Dec 13, 2013
I'm working on a productivity report, on which some employees may produce units for more than one team. The report is organized by team.
I'm already totaling the number of units for each employee which I'm listing in alphabetical order.
But, I would also like to do the following:
a) see the number of occurrence/teams each employee is producing units for.
b) see the row numbers for each occurrence, which would normally be three or less.
c) see the value for each occurrence.
View 9 Replies
View Related
Dec 17, 2008
My question is presented, in details, inside the WB.
View 14 Replies
View Related
Mar 13, 2009
In the attached sheet there is a list of horse runs in time order with the latest at the bottom..col C contains the rating for each run. I need a formula to fill down col d and e. Col d is to contain the rating of its previous run and column e the rating of its second last run. for example row 21 ..I have filled in manually Autumn charms last run had a rating of 116 and its second last run was 122..filled in in cell e21. if a horse has not ran(is now shown previously..above in column e..just leave the cols d and e blank...
View 2 Replies
View Related
Jul 18, 2007
In Sheet1 are unique VM entries (Column A) which have a Group (Column B), Time (Column C) and a Reference (Column D) which can be same for other VM entries. In Sheet2 are the unique Reference Entries (Column A) which need Group (Column B) and Time (Column C) entries populated from Sheet 1. Multiple occureneces need to go in one cell, I realise this is not the best idea, but for this purpose they need to be and seperated by a comma or semi-colon.
In B2 I've used this formula:
=INDEX(Sheet1!A1:D11,MATCH(A2,Sheet1!D1:D11,0),2)
Which partly works, but only returns the first occurrence of H1 on Sheet1. I need all occurrences (i.e G1;G4;G8) returning for B2.
I can only think that some VBA script may be needed but I'm not really an expert in this area. In the full spreadsheet there are hundreds of rows and multiple entries, where Group and Time need to be populated on the second sheet
View 3 Replies
View Related
Jul 16, 2013
I have the following data: (The Dept can occur multiple times in Col B.)
Col A
Col B
Row 1
[Code]....
What I want to do I want to insert a formula in each row of Col A that returns a number that indicates if the Dept in Col B is the first occurrence of the Dept, the second occurrence of the Dept, etc.
View 3 Replies
View Related
Jul 13, 2007
My task is that I have several documents exported in excel. The requirements in them were mapped to another document. The IDs were populated in a certain column but are now no longer needed. I have to convert those old Ids to the current ones. In my example I have populated Sheet one with the lookup table. In column A are the old IDs. In Column B are the new ones they map to. The old ones can map to several which is why there are more than one ID in some of the cells. In column D of Sheet 2 I have populated it with the old Ids. In Column A I'd like to have the lookup return the appropriate maps to the new Ids. For example for the first map in cell A2 it should return:
abc_18
abc_43
abc_6
abc_4
all in one cell.
Vlookup can return the first hit (abc_18 and abc_43 for cell D2), but ignores the other two IDs in cell D2. I was able to work around this combining the find, mid, and vlookup to return all values. The problem with that is that the formula gets monstrously big after just having it return 4. In the actual files it can be up to 30 IDs which would make that formula literally pages long since. I have pretty much no experience with macros, but it seems like the only solution since I have to send this off to various people so they can use it.
View 3 Replies
View Related
May 19, 2014
I have a table with 4 columns where in column 3 I have part numbers and in column 4 I have the number sold. In column 3, the part sold can be listed several times with a different number sold in the column 3. What I need is code to go through the part numbers and return a total number sold for that part in a new column. I have sorted the table by part number, then by Column 1 which is for the date. The new total should go in the last column with the oldest date for that part.
View 2 Replies
View Related
Jul 24, 2013
By "Return all row numbers that have a given value written somewhere inside text in their column B" I mean....
I want to be able to search an alphanumeric value "ABC12345" within a column that has a paragraph of text written in each row, I want to know which rows have this value written somewhere in them and have it return 2,4,33, as row 2 and 4 and 33 have this value in them inside column B on that row.
on column A i have the respective row numbers all the way down ie: ( 1,2,3,4, etc) (to pull up the row numbers)(unless you have a better way)
On Column B i have the paragraphs of text to search in (there is lots of text and spaces in the paragraphs.)
On column C i have the value to look for... ie: ABC12345 (different values in each row of column C) to be compared to all of column B
On column D would be the formula to Pull down and get the results.
and something tells me you might have a better way of getting the row numbers rather than having column A dedicated to numerating each row 1,2,3,etc
1
paragraphs of text
look for
it's found in row #
2
anything can be written here and might have ABC11112
DDD77777
4
3
anything can be written here and might have ABC12345
ABC55555
5,6
4
anything can be written here and might have DDD77777
BBB11111
Not Found
5
anything can be written here and might haveABC55555
ABC11112
2
6
anything can be written here and might haveABC55555
ABC12345
3
View 6 Replies
View Related
Jan 14, 2010
I am using the following array equation to return the value in column 1 for the smallest 5 numbers. It works for small 1 and 3 but i get a #NUM! for 2 , 4 , and 5. The smallest 5 numbers are:
0
3.196408441
4.491972091
4.491972091
4.491972091
Equation:
=INDIRECT(ADDRESS(SMALL(IF((NumRange)=SMALL(NumRange,1),ROW(NumRange),""),1),1))
View 12 Replies
View Related
Nov 3, 2011
my worksheet has a range (AN2:AN10000), and I want to find the total number of occurrences that specific numbers occur.
Example:
I want to find out how many times in this range above the numbers from 11 to 15 occur (11,12,13,14 & 15).
View 6 Replies
View Related
Oct 30, 2013
I am trying to identify the row(s) where a match occurs when there can be multiple occurrences of the match.
A
B
C
1
john brown
Brown
True
2
Cathy Smith
3
Brown excavating company
4
XYZ Corp
5
Brown Advisors Inc.
The first test I ran in cell C1 with the information above was to determine if "Brown" was located in the range of A1:A5 using formula:
=IF(ISNUMBER(MATCH("*"&B1&"*",A1:A5,0))=TRUE,"True","False")
In the case of the word "Brown" it occurs in the range of A1:A5 three times, so the result of the above formula would be "True"
Now what I would like to do is return the locations, in this case the row number(s), where the word brown is contained range of A1:A5 because in the case of the word Brown, it occurs three separate times in the range of A1:A5. I would like the resulting value of the formula in this case to be "1, 3, 5" indicating the word Brown occurs in rows 1, 3, and 5. The formula also needs to work in case there is only one match as well.
I tried using the below array formula:
={MATCH(FALSE,ISERROR(SEARCH(B1,'Working List of Vendors'!$A$1:$A$5)),0)}
But it would only return the first occurrence of the match which in this case would be row 1, or "1"
View 8 Replies
View Related
Aug 27, 2012
I have a column (B) of randomly generated numbers 1-14, and am using this formula range to sort in descending order and return the relevant value from column (c).
=VLOOKUP(LARGE(B3:B20,1),B3:C20,2,) to =VLOOKUP(LARGE(B3:B20,14),B3:C20,2,)
Works great, except when a number is duplicated, (E.G. 14,13,13,12,11,10,10,9,8 etc). It then returns the first value from(C) repeated, and not the value from the second and subsequent duplicated reference numbers.
View 3 Replies
View Related
Jul 25, 2014
how to count the unique occurrences in column A based on the value in column B. In the example below, I am trying to count the number of unique names in each state (Illinois=1, Colorado=2, New York=2). I would like the formula to count unique occurrences for the entire column A because I will be adding to the list.
..A.............B............C
John......Illinois
John.....Illinois
John.....Colorado
Alex.....Colorado
Alex.....New York
Steve...New York
Steve...New York
View 10 Replies
View Related
Mar 15, 2007
I am using the attached code however when my database query does not return any data I dont want the Dim function to perform.
Sub macro3()
Worksheets("Data 1"). Range("I3:BU65536").ClearContents
'declare a variable to hold date
Dim strDate As String
strDate = Format(CDate(Range("startDate").Value), "yyyy-mm-dd") & " " & FormatDateTime(CDate(Range("startDate").Value), vbLongTime)
Sheets("Data 1").Select
Range("A1").Select
With Selection.QueryTable
.Connection = _...........................
View 4 Replies
View Related
Mar 5, 2012
I am building a data base. I have a tab where you fill out of a form and then click an activeX command button to write to the data base. I am wondering if there is a way to record how many times to button is pushed. Each time the button is pushed, I want to record the data from the form into a single row in the data base without writing over old data but I do not know how to read how many rows of data are already there.
View 9 Replies
View Related
Jul 23, 2014
Row Number (A)
ID Number(B)
No.of Repetitions(C)
1
1234
4 or TRUE
2
2538
1 or FALSE
[Code] ............
In need checking if a particular ID number is repeated more than once in column B. I need to write a formula in each cells of "No.of Repetition" column or Column C to check if respective ID number in column B is repeated more than once and display the count or display a condition true or false.
View 6 Replies
View Related
Jan 9, 2012
I have made a database to record drawing numbers.
In column A is the drawing number and in Column B is the revision number.
I am trying to write a macro that will alert the user when they are trying to enter a drawing that contains BOTH the same drawing number AND the same revision number as one already in the database.
Since drawing numbers will be reused and only revision numbers changed when a revision is entered, I need the function to check both column A and Column B then display a msgbox if there is a match.
View 1 Replies
View Related
Sep 20, 2013
So, I have two columns on a sheet that all contain drop down boxes for various information. We'll refer to them as column 1 and column 2.
In column 3, I want to have the highest instances of those words sorted with the highest on top, and downward from there, along with the number of times it appears in either column 1 or 2. (And I want the sorting to be dynamic, i.e. when I change what's in column 1 or 2, it will re-rank them according to highest number)
i.e.
Column1 Column2 Column3 (Column4-#)
Bob Tom Bob 5
Joe Bob Tom 2
Bob Joe 1
Tom Bob
Bob
I have the drop downs and what not for the 1st 2 columns, I just need to know how to make 3 & 4 happen.
View 1 Replies
View Related
Mar 2, 2006
I have one column with names and another with date ranges (JAN06) etc. I
want to count the number of times a name occurs within a given date range.
View 12 Replies
View Related
Aug 9, 2007
I want to count the number of times a certain value is found in columnB, only for unique values in columnA.
I have made a simple example file, attached. Column B shows Status which can be Active, Current, Dormant. Column A holds Item names. I want to know how many Active Items I have, but an Item can appear more than once in ColA, so I want it only to include in the count the unique ColA values.
View 9 Replies
View Related
Apr 13, 2012
I have a list in column 1 that I would like to do an auto find and replace with the list in column two.
column a
big
fast
tough
mean
goofy
column a
"keyword" store
"keyword" stores
"keyword" stores.com
"Keyword" department store
store "keyword"
stores "Keyword"
View 2 Replies
View Related
Apr 13, 2012
I'd like to count the number of occurrences a value appears in a column. Hard to explain what I'm after so I will draw it out:
Column A
2512
2512
2513
2513
2513
2513
2518
2519
2519
2519
I want to add a formula in column b that will add the number of times it appears as it appears:
ColA ColB
2512 1
2512 2
2513 1
2513 2
2513 3
2513 4
2518 1
2519 1
2519 2
2519 3
View 3 Replies
View Related
Aug 16, 2007
I have created (pieced) together a macro to search through a list and paste the results in another worksheet it works fine but i can't get it to loop through the list.
Sub Findall()
Dim y As Long
Dim starta As String
Dim tr As Long, tc As Long
Dim sr As Long, sc As Long
Dim s As Worksheet, t As Worksheet
Dim SourceCell As Range
'Setup
Application. ScreenUpdating = False
Set t = Sheets("Target Sheet")
Set s = Sheets("Search sheet")
Set SourceCell = ActiveCell
Do While IsEmpty(ActiveCell) = False
Counter = 1
'Get last used row in Target Sheet
t.Select..........................
View 6 Replies
View Related
Sep 30, 2009
I am trying to use a PRODUCT formula to return the product of a list of numbers in a column, between 2 specified dates. The spreadsheet is arranged as follows:
Column BA list of dates
Column C & DNumerical data not used in any calculations
Column EThe numbers to multiply together
Cell A1Start date to be used in the PRODUCTformula
Cell A2End date to be used in the PRODUCT formula
The formula I have to multiply the data in column E, subject to the start and end dates in Column A is:
{=PRODUCT(IF(B15:B1000>A1,IF(B15:B1000<=A2,INDEX(B15:E1000,0,4))))}
This returns zero though works appropriately if I replace “PRODUCT” with a “SUM”.
View 2 Replies
View Related
Jun 14, 2013
E11 through E24 contains numbers and a few errors (#N/A) that need to persist (the errors need to show).
E10 needs to show the average of the numbers that are in E11 through E24, and just ignore the errors.
I have many columns like that - where the errors need to show and I need to show an average of the number/values that do appear, ignoring the errors.
View 14 Replies
View Related
Aug 15, 2014
I am working on a big project involving baseball statistics, evaluating individual seasons. After a great deal of entertaining data gathering, I have a spreadsheet of 1896 seasons that meet certain criteria--that is, seasons belong to an individual player. The player's names are in column A of my spreadsheet.
Individual names appear between 1 and 15 times. I would very much like to generate a quick table (or just a couple of columns) that lists every name in column A, and how many times it appears. My understanding is that the histogram function won't work with text. I'm running Excel 2013 on windows.
View 7 Replies
View Related
Feb 5, 2013
I get this work a lot and am looking for a much more effective way of working the dataset,
I have numbers like 1.1, 1.2, 1.3 and so on.......
These come under criteria like (1) = 1.1, 1.2, 1.3 and so on.....
I am look for an automated way of doing a count of any number that falls under this criteria, so I want to count based on criteria (1) it would count all 1.1,1.2,1.3 and so on as one count.
I am attaching a sample document to see how it is laid out. [URL] .....
View 6 Replies
View Related
Mar 18, 2013
I've got two columns, one with policy numbers, the other with error codes.
A policy could be in the list more than once, so I'm counting the number of unique policy numbers with the function
=SUM(IF(FREQUENCY(A10:F10000,A10:A10000)>0,1))
I want to count the number of errors that are NOT code 00 or code 21. Because the error codes are saved as text, I'm counting the number of errors with
=SUMPRODUCT((B10:B10000"21")*(B10:B10000"00")*(B10:B10000""))
The problem is that since a policy number could be listed twice, when I count the errors, I might be counting the same policy as an error twice. This means that I could feasibly end up with more errors than I have unique policies, which doesn't work.
What I'd like to do is only count the number of errors that have unique policy numbers. That is, I only want to count an error the first time it is in the list, based off it's policy number. Is that possible?
Here's my sample data: [URL]
View 9 Replies
View Related
