Search A Column Of Dates & Return Data From Another Column
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 Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Search Column For Dates And Colour Fill
Column A = Dates in ascending order Im after the code for searching this coloumn for 8 dates i have listed elsewhere and then i would like it to apply rose colour just to the dates in column a. how do i link with specified dates? With Selection.Interior .ColorIndex = 38 .Pattern = xlSolid End With End Sub
View Replies!
View Related
Search Column And Return Values
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 Replies!
View Related
Search For Text Return Value In Next Column
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 Replies!
View Related
Search A Column & Return Many Results
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 Replies!
View Related
Vlookup: Search For Value In SECOND Column Of Table Array And Return Value From FIRST
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 Replies!
View Related
Search Partial String, Return Value In Next Column, Vba
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 Replies!
View Related
Column Search For Row Data
I have attached a small example. I have a list of data of employees. I want to be able to input a number (in column A in example) and to search the data records for this number. When the number has been found, the corresponding info data from that Row will show in columns B,C & D. I have tried this using LOOKUP etc but find that it is "hit & miss". I can input one ID number and the corresponding details will appear, but very often if I enter any other ID numbers further down the sheet I sometimes get the correct data or I might get the "N/A" error. The error seems to occur, I think, if the next input ID number is higher than the last. The ID numbers I input down column A will NOT be in numerical oredr.
View Replies!
View Related
Find To Search And Return Multiple Dates
I am working with a macro into which you input a date, the macro searches for that date (in column D of the data sheet), then copies any row with that date onto a new sheet. This works great, but now I need to be able to search for more than one date at a time and return any rows that cantain ANY of those dates. For example, I would want to search for any row containing 01/01/07 - 01/07/07 or any row containing 01/01/07, 01/02/07, 01/03/07...01/07/07.
View Replies!
View Related
Configuring Dates: Enter A Date In One Column, Another Column Will Automatically Populate With The 1st Of The Next Month
how to make a certain type of date automate. It's kind of hard to explain, but basically, I'd like to make it so that when I enter a date in one column, another column will automatically populate with the 1st of the next month. For example: If I enter 4/26/2009 in the 1st column, column 2 will read: 5/1/2009 If I enter 1/19/2008 .................................................. 2/1/2008 Also, it's very important that if the FIRST date is already the first of the month, then the second column will read the same. For instance: If I enter 3/1/2009 in the first colum, the second column will ALSO read 3/1/2009.
View Replies!
View Related
Macro To Search A Column And Copy Row Data
I have a dataset that is large. I want to write a macro program that checks the number in cell A1, copies the number and then the rows contents to another worksheet/spreadsheet. Then jumps to cell A2 and if the number is different to that of A1 copy the data of that entire row over from that row to the next worksheet/spreadsheet. If the number is the same as A1 then jump over to A3 Then continue this process but rather than comparing it to A1, compare it to the new number that has been found in coloumn A. For example 7 A 7 A 6 AB 7 A So starting at the first line then jumping the next one then to 6 and finally 7.
View Replies!
View Related
Match Text In Column Of Another Workbook & Return Nth Column Same Row
I have two excel files. File #1 is a varying length and has 4 columns with ID #s in Column A. File #2 is a two column file that contains a list of ID #s in column A AND modified ID#s in column B. I need to match the ID #s in column A from file# 1 to the list in column A of file#2 then replace ALL instances of the matching ID #s in column A of file#1, with the values found in column B of file #2. I've tried using the substitute function but I could only replace one found ID at a time.
View Replies!
View Related
Find Value In Column & Return Cell On Same Row- Different Column
I have an excel sheet I am working on and in columns F1:F2000 I have an IF statment, I need to be able to add more "IF"s to it but I will exceed the 1024 char limit. Is there a way I can put this formula into VB as a function called DocumentType() and then in excel F1:F2000 =DocumentType()? Is it as simple as: Function DocumentType() If(...........) End Function
View Replies!
View Related
Create A User Defined Function To Search A Column Of Data For A Part Number
I want to create a user defined function to search a column of data for a part number. If it exists I want to have a the UDf returna "fail" otherwise "pass" Here is the code I was trying to use Function firstpass(SN As String) As String ws = Worksheets("Defects") c = "" With ws.Range("a1:a9999") Set c = .Find(SN, LookIn:=xlValues, lookat:=xlWhole) End With If Not c Is Nothing Then firstpass = "Pass" Else firstpass = "Fail" End If End Function This function only returns a "#value" and I don't quite know how to troubleshoot it.
View Replies!
View Related
Lookup & Return Data From Different Column
I have a book with multiple tabs in it. The first is a master customer file, including customer numbers. The next two tabs include multiple references to the same customers because they list multiple shipping addresses and contacts for each customer on a separate line. I want to be able to have the additional tabs refer to the master list and find the customer name, then return the corresponding customer # in the previous column for that customer. This is what I came up with: =IF(B:B=Sheet2!B:B,Sheet1!A:A,0). But the results simply returns numbers that increase by 1. It should repeat the same customer # for the number of times that customer name is listed based on how many addresses or contacts there are. What am I not doing right?
View Replies!
View Related
Return A Dynamic Column Via Data Validation
I am a personal trainer/strength coach, and i use excel to write my workout programs. To save time in writing programs i have made lists of all of my exercises in a separate workbook. I have them all linked via data validation in drop down lists to different templates for different populations ;weight loss, injury prevention, sports performance, ect. And it has been working great! However here is my problem, I would like to be able to return an entire list of an exercise circuit with one drop down list and have it input all the exercises in cells below the drop down list. So for example i have three columns on a separate sheet. Column one Circuit A exercise 1 exercise 2 exercise 3 Column two Circuit A exercise 4 exercise 5 exercise 6 Column Three Circuit A exercise 7 exercise 8 exercise 9 In cell A1 i want a drop down list that that has circuit A, B, C, using the drop down list if i pick circuit A it will return exercise 1, 2, 3 in the cells A2, A3, A4, receptively.
View Replies!
View Related
Lookup Data Without Knowing Return Column
Got a bit of a stinker here. (Well, it is for me anyway. I haven't used Excel in months)! I want to use a VLOOKUP function (I think) to retrieve data from a list of Exchange Rates on a separate sheet. The user enters a 'Period' number, selects an 'exchange' variable (the blue text cells). The hope is to lookup the data on the 'FX RATES 06-07' sheet and return the result in the green cells. I've tried using VLOOKUP but one of the arguements specifies that you nominate a column to return the information - that's the problem, we don't know that information when asking Excel.
View Replies!
View Related
Formula To Go Through Each Row And If The 2nd Column Is Yes Then Return The First Column
I have a collection of data like the following: Week1 | yes Week2 | no Week3 | no Week4 | yes Week5 | no ... Week28 | yes I need a formula to go through each row and if the 2nd column is yes then return the first column. I could then copy and paste this formula into a the next cell, add an argument saying the returned value cannot be the same as the previous ones, and so on. Essentially this is a series of nested IF statements. However, Excel prevents more than 7 statements from being nested, when I need all 28 to be searched. I cannot think of a way to get VLOOKUP, INDEX & MATCH, or defined names to make this work, because it needs to step through each row.
View Replies!
View Related
Return Column Header Of First Column That Doesn't Contain 0
I'm looking for a formula that will return the column header (a date) of the first instance of a number greater than 0. For example if columns A through S had dates as in row 1 (header row), and in row 2, every column contained a 0 up until column P, I would want the date (Row 1, Column P) returned? I think this might be a sort of index/match formula, but I am not very familiar with these.
View Replies!
View Related
Set 2 Column Listbox To Return 1st Column Value
I am trying to set up a listbox on a userform that will draw from 2 columns. The first column is an abbreviation of the phrase in the second column as shown below. When the user clicks the listbox arrow I want them to see the descriptor of the 1 - 3 character code since they are not always intuitive. When the selection is made I want the form to capture only the code. IP In Progress AMAwaiting Materials ADAwaiting Design CPCompleted.
View Replies!
View Related
Lookup Partial String To Return Data From Corresponding Column
I have a simple lists containing two columns. One column contains a five digit number and the other a vendor name. The vendor name in most cases is two to four words. I am wanting to type in a partial string of the vendor name and it return to me the 5 digit 'vendor' number. Col A Col B 20567 3M Electrical, Inc. I want to type in '3m' or '3M' or 'electrical' or 'ELECTRI' and it return the 20567. The other part of this is that there may be two rows with the same info in which I would need to see both... Col A Col B 20567 3M Electrical, Inc. 21789 3M Tape Division Is this possible with standard lookup features in Excel or does someone have a VbScript or macro that will accomplish this?
View Replies!
View Related
VLookup Return Multiple Hits From Same Column Of Data
I am running a Vlookup on a large set of ID#'s that exist accross multiple servers. The data is structured in (2) columns how you see below , and you will notice that the same ID# can show up on multiple servers. However, the VLOOKUP is only returning the first match it finds. Assuming that I want to keep the data structured as is, how do I make the vlookup return all matches? ID# LOCATION 23 SERVER1 34 SERVER4 23 SERVER2 13 SERVER1 17 SERVER3 34 SERVER1
View Replies!
View Related
Return Uniqe From Column B Based Column A
i tried to search on forum abou unique function but i didnt find what i need. I have 2 columns - column A - is "ID" and column B is "name". I want results in column C. I need formula, which look at ID and give me in C in the same row the unique „name“ from B. If is the name by this ID first time in column, it return the name. If it second or more, it return blank.
View Replies!
View Related
Find Value In Column And Return Value From Adjacent Column
I have are two worksheets, "Sheet 1" which is a bill of lading form and "Sheet 2," in which columns A and B contain Part Numbers and Weights respectively. What I'm wanting to have happen is when a user inputs a part number into Sheet 1 (say in cell B6), it calls a UDF that looks up the part number on sheet two column A and returns the corresponding weight from column B (to say cell C6 on sheet 1).
View Replies!
View Related
Find Data In Column Range & Return Cell Same Row But X Columns To Right
i have is 3 sheets in the same excel document. Sheet 1 is the mater sheet, which is a compilation of sheets 2 and 3, however the sheet layouts are different. The sheets consist of a list of names and details. Sheet 1 has all the names in the list, however sheet 2 and 3 only have partial lists that are in a different order from the original list. The details listed next to the names in sheets 2 and 3 are different and hence a straight forward row copy and paste will not work. What i need is to write a vba script that can take the name from the master list, search sheets 2 and 3 for the name and lookup the variables placed in next to the name, then update the master sheet accordingly, then continue to the next name on the master sheet and do the update again, and so on until all is updated. I have already thought about using lookup functions in excel however there is 1000 names on the list and around 60 details so it would be messy.
View Replies!
View Related
Merge Two Workbooks. Copy Column Data Based On Numerical ID Match Of Another Column.
I am trying to get excel to search a workbook/(or worksheet if easier) for a matching unique value and fill in its associated data. My first workbook has the SKU (A) filled in but not the UPC (B). My second workbook has both the SKU (A) and the matching UPC (C) filled in. I need to take both workbooks/(worksheets), compare the SKUs, and if a matching SKU is found, extract the UPC from Workbook 2 and fill in the UPC field in Workbook 1, and if no UPC is present in Workbook 2, then it leaves the cell in Workbook 1 blank.
View Replies!
View Related
|
TRACKER
