From Matching Numbers In One Column Return Value Of Second Cells In Another Cell?
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.
I have a collum (A )with numbers and in other sheet have a collum (B)with numbers too.. and some numbers match. what i want is in the cells that the numbers match have other colum and i want to copy that number to another cell.
I am trying to use a nested INDEX and MATCH array formula to return the value in column C when matching column A and column B, but with a few more criteria.
The range containing all the data
A B C
1 Cat 1 January 1, 2014 John
I am looking for the array formula to return the name of the person in column C who is in Cat 1 after the date in column B.
For example; C7 should return "John" because B7 requests "January 15, 2014", which is after the value in B1 C8 should return "John" because B8 requests "February 15, 2014", which is after the value in B1 C9 should return "Andrew" because B9 requests "August 15, 2014", which is after the value in B4
This brings back "John" as desired in C7, but when copying down the table into C8 and C9 both C8 and C9 return Andrew.
I guess this is due to my ">=" condition in the Match formula and it is returning "Andrew" because "Andrew" is also after the date requested, but I cannot for the life of me work out how to get it to work.
I have a mass of data which look something like this:
and I require the ranges of reference numbers to be listed in a column one above the other, which requires inserting new rows. I also need the date & description columns copied down into the newly inserted rows.
So basically for example I would want the top row to now read:
and then apply the same procedure to the other ranges below this.
I have an array of data with multiple codes stored in text...
I want to set a formula in a cell to look for all instances of specific text in an array. Then compare the value of all of the column headings, which are numbered across the top of the array, which contain a match in that column and return the highest value.
Example: 10x10 array, columns 1,3, and 5 contain "text" somewhere in those columns. Formula would return 5.
On Pipe worksheet, pipe numbers 1-203 are listed in column A. Each pipe number has a flow rate that is listed in column E. I need it to look up the pipe number in column A on the Job worksheet and report the flow rate (from the Pipe worksheet) in column D for each pipe.
I am looking for a formula that will satisfy the following:
1) find all the values in column "A" that match 2) In column "G", sum up all the values in "F" that go with the matching values in column "A" 3) For example, in rows 14-16, the values in column "A" match. Cell G16 sums up F14:F16
I have the same report, one a monday snapshot, the other a daily comparison. I would like to index columns B, in both reports, to find the same part number and then reference the horizontal row and highlight increases in columns G thru AD. The comparison report looks identical but the column B numbers may not be in the same location. this is a report to show prosduction requirements and the increases throughout the week.
I have been stuck for about a week on trying to create a Case Select macro. I am a novice in using VBA, so I am not sure what I am doing right OR wrong. What I need the macro to do is go through all of the text in a column and if the text is one of the 50 states, I need it to return one specific value, and if it is anything other than one of the 50 states, it needs to return a different value in a column 5 over from the column with the states in it. I have changed the code MANY times and so far I haven't been able to get anything to work. Here's the gist of what I have been trying:
Sub usstates() Dim R As Range, s As String Set R = Sheets(1).Range("J1:J50000") For i = 5 To R.Rows.Count X = UCase(R.Cells(10, 1)) If X = "AL" Or X = "AZ" Or X = "AK" Or X = "AR" Or X = "CA" Then R.Cells(i, 2) = "SC" End If Next i End Sub.................
I have two sheets - one work sheet and the second - database with existing entries. Going by visual example below, I need the column C of the work sheet to automatically populate with the data pulled from the database sheet. For example, C4 on the Work sheet should find both values "Bike" and "Green" in one row on Database sheet and return the corresponding value 44444 from F97. The work sheet cells in the row C should remain empty if no match found on the database sheet.
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)
Right there data in cell A1 lets say LTP BY001 and then the next data in column A is LTP BY002 in cell A9. What I need to do is copy LTP BY001 from cell a2-A8 and then LTP BY002 From A10 until LTP BY003 appears lets say A15. Therefore need LTP BY002 copying from A11-A14. I need that to go right the way down the workbook at everytime their is a number change.
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
Having a bit of trouble trying to get excel to pick up text in one sheet (sheet 2) and populate cells in another (sheet 1) if the row (row 1) labels and columns (column a) in both sheets match. hope that makes sense? I've tried googling this to no avail, i've also tried index-match however i keep getting errors.
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.
i have stores numbers in column a and the sets and cartons in column b but there is gaps between the data i want in column c a list of stores with the set and car displayed next to it, some information in column b is on 2 rows for 1 store please see attached, how i would like to see it on a row is b345 car011119 etc