Search First Non-blank Cell To Return A Value In Another Column?
Jun 9, 2014
Want to look in one column and find the first non-blank numerical value, then have it return a value from another column.
Used to nest multiple IF functions together from different cells, but it seems overly complicated and time consuming. Sometimes I have over 30 cells to check.
For example, if Column A contains weekly sales data by week, entered weekly, and Column E has corresponding comparison data from the previous year. I want to enter a formula to check the first row that has sales data entered and have it match up the comparison value in the other column.
View 7 Replies
ADVERTISEMENT
May 7, 2014
I am trying to figure out how to return the contents of last non blank cell in column B, based on the name in column A. So, if I choose "Sam", the result I am looking for is "blue". If I choose "Pete", the result I am looking for is "orange".
Sam
red
Sam
blue
Sam
Pete
orange
Sam
View 4 Replies
View Related
Jan 17, 2014
I have this table, which can be seen as a basic custom gantt chart: KLRWo.png
And I would like to fill the A column with start dates, based on the first filled cell of the range on the same row, and the header value of its respective column (row 1). It's easier to show my expected result than write it actually:
WiMZH.png
View 6 Replies
View Related
Apr 4, 2008
how to make the data look like a table with three columns. Other than the date, it is space delimited. I have a tracking spreadsheet where Column A is populated with dates for the year. Column C contains daily values.
I don't always start entering daily values on the first day of the year, e.g., this year the first value in Column C corresponds to March 9. All values in Column C are contiguous - there are no blank cells until the value in Column A is greater than today's date code. I would like to use a formula (rather than VBA) to look down Column C and find the first non-blank entry where the value in Column A is less than or equal to today(). In this case, the formula should return the value for March 9, 2008.
CREATE TABLES LIKE BELOW?Column A Column B Column C
March 1, 2008Saturday
March 2, 2008Sunday
March 3, 2008Monday
March 4, 2008Tuesday
March 5, 2008Wednesday ...................
View 4 Replies
View Related
Dec 16, 2013
I have an array 20 Rows x 42 Columns, which contains a competition draw.
I need to search this array for a unique value and return whatever the time is in the first column on the same row as the value appears, and enter it into column C in the Womens Times sheet.
I also want to return into column D the court number from row 3.
The reason i want this automated is as teams enter / withdraw we may need to drag the games from court to court to fill gaps, so i want the Womens Times sheet to update accordingly.
I have been messing around with index and match, but cannot quite get it to return what i need.
I have attached an example ... on the sheet "Womens Times" in column A there is a list of game numbers ( #1W etc etc) indicating womens game #1 and so on. The main sheet i am using also has a seperate tab for the mens games, hence the designator of W or M on the end of the game number.
View 2 Replies
View Related
Jun 8, 2009
I am trying to get a formula that will search one column range “B” and pull data from another, “D”. Dates are down column “B” and some of those dates are repeated several times. In column “D” there is only one piece of data (a number) entered for each day. Eg, if 08/06/2009 has been entered 3 times in column “A”, there will only be data entered in 1 of the cells of column “D” and blank cells in the other 2.
Column B -- Column D
07/06/09 -- 54000
08/06/09
08/06/09 -- 62000
08/06/09
09/06/09 -- 61000
I couldn’t get the LOOKUP function to work properly, as there are duplicate dates in column “B” and I often got a result of 0 returned.
As I’ve only got 1 piece of data added in column “D” per day and any duplicate days would just have blank cells in “D”, I can actually get a SUMIF function to work, SUMIF(B3:B60,DATE(2009,6,8),D3:D60). Although it does work, it doesn’t feel right using it and I would prefer a formula that would return just the one cell, instead of the sum of a range of cells.
View 6 Replies
View Related
Jul 20, 2009
ok I have multiple columns where Col-A can have any number between 1 and 5. Col-B has a 3 digit number in it. i want to search all of column A and determine if there is a 1 in it, if so i want it to print the value of col b into a specified area.
here is an example of my form.
COLA | COLB |
--------------
1 | 324 |
--------------
2 | 325 |
--------------
1 | 327 |
---------------
this is basics, i will be using this to search col-a, determine what number is in the column, then print the number found in col b, on another page in a specific place, on the other page i have squares labeled 1-50. in a grid pattern, 10 squares per row. im trying to get all the entries in col-a, that are a 1, to put whatever value is in col-b in the first box of the grid, whatever is found in col-a with a 2, the value found n col-b of that row, into the grid box labeled 2 etc......
View 2 Replies
View Related
May 8, 2008
I have a value stored in variable A. I need to search one column for the location of that value and return the row value. there is no chance for a duplicate entry. Is a loop my only option, or is there a find command in VB?
View 9 Replies
View Related
Jul 8, 2008
I have a workbook with two worksheets.
On Sheet1 I have two columns 'Date' & 'Serial Number'
I want to search the 'Date' column for entries that contain todays date & then display the corresponding 'Serial Numbers' in the first column of Sheet2.
View 9 Replies
View Related
Apr 7, 2009
Imagine the columns below are labelled A - D. Is there a way to search for the value 'SPLT' and return the value in column D?
Great Scot Dried Peas 3610.6366
GT SCT GRN SPLT PEAS480.151
GT SCT MRWFT PEAS1230.2158
GT SCT SPLT PEAS 1900.3157
Haven 370.10
View 9 Replies
View Related
Oct 17, 2006
I'm trying to create a spreadsheet using several others in several different formats. What i need to do is search for a text string eg "EVP" in one column and return the numerical value of the cell in the column next to it - then sum all the values and create a table in the new sheet eg.
EVP sheet1!(value)+sheet2!(value)...etc...
View 3 Replies
View Related
Sep 3, 2008
how to find text from a row in column, where is a lot of text and return row number, of located text?
View 15 Replies
View Related
May 20, 2009
I have tried several methods to delete the entire row if the cell in column 'A' is blank ...
View 9 Replies
View Related
Apr 3, 2014
in one column of my spreadsheet and it is returning the correct concatenation for the appropriate lines. However, I wish to have the column's cells return an answer of blank (" ") if B15, B16... etc is blank. In other words, at the moment, a correct answer would read something like '1810sd0000' C10 and B15 used, while an incorrect answer would still display '1810', but I want it to be a blank cell.
View 8 Replies
View Related
Feb 5, 2013
Ok, imagine this data (say column A). I want to have a cell that automatically searches for the least value (will always be the first number from top) and returns it in another cell. And then repeat the same for the second number from the top.
Beg Search STA
-
-
300
-
-
1050
-
-
2200
View 5 Replies
View Related
Sep 13, 2013
I have a workbook that has 2 sheets. The main sheet is titled Data, and the second sheet is titled Archive.
I need a macro that will let me search all of Column A on Sheet2(Archive) and copy all rows whose Column A contains the value I input into Sheet1 B19, and paste it on Sheet1(Data) starting in cell A21. It can replace any information already on those rows on Sheet1 and I want to copy the information from Sheet2 not cut it.
This part isn't necessary but would be great, on Sheet2 in column D there is a Date it would be great to have another macro that would let me pull the same information as above but only pull a certain number of rows starting with the most recent dates.
View 2 Replies
View Related
Feb 13, 2008
I would like to search cells in column D for the partial string, "PIPE," (A full string may look like this: 'PIPE, 24"ODx0.375"WT API-5LX-65,ERW OR SMLS'). Then, if it's there, return the value "LF" in the corresponding cell in column C. If that string isn't found, then I'd like it to return "EA".
I know this seems pretty easy, but there's a small problem. The word "BENT PIPE," could be in Column D, in which case, I would want it to return "EA" instead of "LF".
View 3 Replies
View Related
Nov 2, 2008
I am trying to write a macro to do the following:
Search column "I" for blank cells. If one is found then all adjacent cells to the left in that row are shifted to the right by one. (in other words for that row cells A-H are shifted right to become cells B-I).
View 3 Replies
View Related
Nov 2, 2008
Search column "I" for blank cells. If one is found then all adjacent cells to the left in that row are shifted to the right by one. (in other words for that row cells A-H are shifted right to become cells B-I).
View 2 Replies
View Related
Jun 18, 2013
So this is what I am trying to do, I have a column in my data that is for telephone numbers. When I receive the file some of the fields are blank in that column. I need to add to my current macro a part that evaluates the column for blanks and adds a static telephone number in the blanks (up to the last row of data in the file). I have been able to accomplish this with the following:
Range("N2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.FormulaR1C1 = "1112223333"
However, when the file that I receive has a telephone number in all the fields the code breaks at this point. I have tried On Error Resume Next, but that just replaces all the remaining cells in the column with the static 1112223333..
View 3 Replies
View Related
Feb 14, 2014
I have a spreadsheet that contains data for a fiscal year broken out by month and quarter. I want the formula to return the data from that month and use whatever the latest quarter is. For example in the data below:
If it has only pulled data only thru Q1 it would return Q1 for Jan-Mar, however once I have Q2 data I would want the formula to return Q2 for the months of Jan-Mar.
2013Thru Q1STOLISTOLIJan-2013STOLI BASE
2013Thru Q1STOLISTOLIFeb-2013STOLI BASE
2013Thru Q1STOLISTOLIMar-2013STOLI BASE
2013Thru Q2STOLISTOLIApr-2013STOLI BASE
2013Thru Q2STOLISTOLIMay-2013STOLI BASE
2013Thru Q2STOLISTOLIJun-2013STOLI BASE
2013Thru Q3STOLISTOLIJul-2013STOLI BASE
2013Thru Q3STOLISTOLIAug-2013STOLI BASE
2013Thru Q3STOLISTOLISep-2013STOLI BASE
2013Thru Q4STOLISTOLIOct-2013STOLI BASE
2013Thru Q4STOLISTOLINov-2013STOLI BASE
2013Thru Q4STOLISTOLIDec-2013STOLI BASE
2013Thru Q4STOLISTOLIDec-2013STOLI BASE
2014Thru Q1STOLISTOLIJan-2014STOLI BASE
View 3 Replies
View Related
Feb 18, 2013
I am trying to search for a phrase in a particular column but the excel search function is returning !Value as an error...
Both fields are text so nit sure what is wrong here.
I have a list of cities that i want to cross reference across a cloumn of notes on another sheet. So, I really want to search for the city (Sheet1 Cell A1) in Notes Sheet2 Cell A1) and I want to return it in Sheet1 Cell B1 Whether it exisits in the notes. I then want to copy this down my list of cities in Sheet1 Column a.
View 5 Replies
View Related
Apr 30, 2008
Basically, I need to look for an item, lets use "apple" as an example, in a range of cells, A1:D100. Then the corresponding result would be in another column in the same row.
View 10 Replies
View Related
Oct 7, 2008
I'm looking for a formula to search for a value in the SECOND (instead of first) column of a table array and return a value in the same row from FIRST (instead of the same or another) column in the table array. Formula would be searching for the unique production order number in the column B and return production line id from the column A.
A1 production line_id
B1 poduction_order_number
A2 L1
A3 L2
A4 L1
B2 505212
B3 504234
B4 505663
I was trying vlookup(504234;B2:A4;2;0) to make formula go search from right columns to the left but then excel is switching the search table to A2:B4 and gives #N/D!
One remark-there is no possibility to switch these 2 columns to simplify. I have to leave them as they are.
View 2 Replies
View Related
Dec 14, 2011
Is there a way to have a formula search an entire row containing certian text, for example "x" and have the formula return the letter of the first column that text is found?
View 8 Replies
View Related
Mar 13, 2014
I have a data set with the weeks of the year as my column headers and a value of 1 (from a pivot table) in a distinct row value (removed for privacy) showing whether it existed in a given week based on pre defined criteria. What i need to do is ID the first (from the left) non blank cell in each row and then return that cells column header. I then need to do the same for the last (from the left) non blank cell in each row and then return that cells column header. On the attached I've put values in column N and O to show an example of what I need to return using a formula.
View 7 Replies
View Related
Mar 25, 2009
I have a worksheet which shows multiple tests and whether they are completed or not[code]Test 1 Person1 Test 2 Person2 Test 3...Person12. I want to search for a specific test, e.g. pH which could be in any of the "Test" columns but only return it if it is not completed. The "Person" columns will be blank if no one completed the test.
I set up the Advanced Filter criteria with all the appropriately labeled columns and for the Person columns, I put ="" but it is returning all rows which have pH in them whether they are completed or not. What should I be putting in the Person columns of the criteria to only return those rows which have the pH test but it is not completed. I attached an example of the criteria range and the filter window to help clarify (the full spreadsheet is huge)
View 3 Replies
View Related
Dec 1, 2006
It's finals week and I need to figure out this last VBA problem for my excel class. (Yes, we are allowed any outside help.)
The code below is something I tried to develope to do the following:
1. Search "N" column for blank cell
2. If cell not blank then select
3. Copy select cell information 3 cells to the left
4. Continue function to next row
I cannot figure out two things with this code. I need the code to copy/paste the information in the cell versus equalling values. Sometimes there is a small .jpg or .gif file inserted into the cell, so copy/paste is desired. Second is I need the function to not copy formulas if possible. I cannot figure either of things commands out. Hoping message boards can help me as they always have in the past.
Code: .....
View 9 Replies
View Related
Jun 18, 2014
I am trying to search a sheet for a given name and return the total point for that marking period. The main problem i am having is that the names aren't in the same cell on each sheet. It is a list of students and each sheet represents a marking period.
I have attached the file
View 3 Replies
View Related
Oct 22, 2009
The below code is what I have and it works but what I need is for it to loop until it doesn't delete anything. How can this be done?
View 2 Replies
View Related
