In Stata, I have a dataset with two variables: id and var, and say 1000 observations. The variable var is of type float and takes distinct values for all observations. I would like to keep only the three observations where var is either the minimum of var, the maximum of...

I'm trying to create a vba code but I'm not succeeding. I want to search on COLUMN "F", for value: "Answered" then copy row from "COLUM B TO F" and paste on sheet "ControlAnswered" lastrow;...

I have created macro that works like a vlookup but has split values. I would like to find value from second sheet of split values (separated by semicolon ) and copy and paste the description to new sheet. The first loop goes through the list in sheet 2 and sets...

I am working with three sheets. Worksheet Start Page has dates from A4 to lastrow. I have a Fund trend sheet with dates from A11 to last row. The vlookup is searching for Dates in the Fund trend sheet based on the list of Dates in the start page sheet....

For a piece of coursework I have to complete a register of student grades. I am trying to calculate their overall grades by converting their grades from each of the four units into numbers (which I have done using the VLOOKUP function), but I need to then convert the result...

I have two columns: Date and Price. I want to pick the value of the cell in the same row as today's date: I want to show "today's" price, so supposing "today" is 12 Feb, then for the above example today's price should show 18. How can I do that...

I'm working with a two very large tables and I'm trying to use vba and vlookups to match the data in one with the other. The worksheets are each one column and are structured like below: Reference Sheet: ID_Ref (2/4) Q1234 W1234 R1234 ... Lookup Sheet ID_Lookup Q1234 P1234 X1234...

I'm trying to make a VLookup in Excel but I get everytime a #NV error. This is table EVENTS: This is table TRACK: the formula on field F2 in table EVENTS is =SVERWEIS(E2;TRACKS!$A$2:$B$52;1;FALSCH) SVERWEIS is the word for VLOOKUP in the German version. FALSCH means wrong...

This should be an easy question for the seasoned pros. 1) I'm trying to offset the active cell down one each iteration of the loop. 2) I can only move down by one because I'm not sure of the syntax available. 3) I was thinking the_result = the_result + 1...

I suspect this may be a job for VBA, which is beyond my abilities. But here's the scenario: Column A in Sheet 1 (CAS1) contains x rows of text values Column A in Sheet 2 (CAS2) contains x rows of text values Part A - For each row value in...

OK here's the situation. I've created a workbook with 5 sheets in it. The first sheet has columns for QTY, DESCRIPTION, PRICE and some other data, but those are the pertinent ones. Based on the data entered, a labor invoice and several order forms are filled out. That is working...

Can't seem to get this one. I am comparing in excel if 2 columns (A and H) have any matching document #'s with =NOT(ISNA(VLOOKUP(H3,$A:$A,1,FALSE))) If this returns TRUE, I want to compare the revision # corresponding to each document # and return TRUE or FALSE as well. I have the...

I am trying to pull the latest date from a range. That range must match the customer name and whether its live or closed. Column L (Live Date) lists all the dates. Column A (HQ Name) lists names of all the customers which can have multiple lines with the same...

I have two worksheets Book A and Book B. Book A has 3 columns Elements (duck, elephant,deer,monkey), Weight(20,70,18,25), Size(10,30,10,6) respectively. Book B has also same columns Elements (elephant,deer), Weight (80,28) and size(40,20) respectively. I need to update weight and size of Animals column from Book B in Book A. like....

I want to perform a special VLookup where the value which is found would match two conditions: The invoice number must be the same The value found from Column G must be within the tolerance -100 to 100 Precisely speaking, if the first value found from Column G (e.g. -18,007)...

Using INDEX and MATCH for lookups and came across an expression someone used in the form of : =INDEX(*range used*, MATCH(MIN(ABS(data!E2- lookup!$L$5:$L$105)),ABS(data!E2-lookup!$L$5:$L$105),0)) lookup!$L$5:$L$105 is the value lookup table range. I know what its suppose to do but the "data!E2-lookup!$L$5:$L$105" part does not make sense. How does this work? Thanks...

I have a standard VLOOKUP formula =VLOOKUP($G28,'Analysis 1'!$A$2:$CR$32,$M28+$M28,TRUE) How would I amend this so that rather than just return the answer i would want it to average the result of 3 cells, the cell above the VLOOKUP, the cell below the VLOOKUP and the cell of the VLOOKUP? I would...

I have a Powershell script which runs an Excel VLookUp function through a variable that can be of either String or Int32 type. The script runs great when I declare the $Value variable as either a String or Int32, but how can I deal with the fact that the $Value...

I've come to a point where another set of eyes might be best to determine the meaning of my results. What I don't understand is that in column B my results seem to be showing up in the C (Answer) Column. However when I look at the answer column numbers...

What I want to do (if possible) is sum each metric of each transaction with a common customer_id in ONE STEP. The output should look like the "DESIRED OUTPUT" sheet. I know some sort of lookup will need to be used to reference the "CUSTOMER LOOKUP" table. I can see...

I'm in need of some advanced lookup formula with MAX, MIN, IF and SUM. Info: Sheet1 (data): Column one(A): name Column two (B): Type Column three (D1:AO): Data/numbers Row 1: A1=Name, B1= Type, D1:AO1=name of the different data Sheet2 (output): B5: Primary Critera, Selecting from a dropdownlist of Sheet1.Row(D1:AO1) B7:...

How would I combine these together into one?: =IFERROR(VLOOKUP(B2:B11,Sheet2!A:B,2,FALSE),"No Match") =IFERROR(VLOOKUP(B3:B12,Sheet2!D:E,2,FALSE),"No Match") =IFERROR(VLOOKUP(B2:B11,Sheet2!G:H,2,FALSE),"No Match") These are just three but eventually I would need 12 in total to be joined as these are searching team names and there are 12 teams in total....

This may be a stupid question, and if it is, I apologise. I have a table in excel: Column a...........Column b 1 property1.......problem x 2 property2.......problemy 3 property3.......problemz 4 property1......problem a I was wondering if I could use sumif (or any similar formula) to add the problems, referring to a...

I'm trying to include a Dim in a Vlookup. Dim filename As String filename = Format(DateAdd("d", -6, Now()), "mm-dd-yy") filename = "NSC " & filename & ".xlsm" For Each ws In ActiveWorkbook.Worksheets ws.Activate ActiveCell.FormulaR1C1 = _ "=VLOOKUP(RC[-2],'[" & filename & "]" & ws & "'!R8C4:R500C6,3,FALSE)" Next My error comes at...

I have this Excel VBA macro which runs really well Sub PCMSLookupTool() Dim LastRow As Long With Sheets("Lookup Tools") '<-set this worksheet reference properly LastRow = .Range("A" & Cells.Rows.Count).End(xlUp).Row With .Range("J10:J" & LastRow) .Formula = "=VLOOKUP(A10, 'PCMS-dump'!A:B, 2, FALSE)" .Cells = .Value2 End With End With End Sub But I...

The data I am working with are strings, not integers. I want to take two columns in worksheet1, say column B and D and compare them to two columns in worksheet2, say C and E. If a cell in column B == a cell in column C and a cell...

I have on Sheet1 18 columns: N a list where an Order Number can be selected O a Line number (as the orders have multiple line numbers associated with them) P, Q and R, I want to pull over the data associated with the Order Number and Line number entered....

I have a data set that I want to return an indexed column using two values: a year and a name. Both these values are formatted to general (I also tried text) in my spreadsheet. In one work sheet I have a like of people: On the other, I have...

For a conduit network, I am trying to find the pipes that drain to a manhole. There can be multiple pipes that can drain to a single manhole. My data-structure are organized in the following way: - Stop Node Label ........ ................ - MH-37 CO-40 - MH-37 CO-40 - MH-39...

This is my function that to take an array with dates and return the hours per week according to an existing table in my Sheet Eng_Availability_Report. For me, it seems to be correct but VBA sends me a message with Error 1004. I used a MsgBox to return the weeknumber...

It seems like a simple problem, but I do not manage to solve it. I have the following tables: Values | Key | Value | |-----|-------| | A | 1 | | B | 2 | | C | 3 | Results | Foo | Bar | |-----|-----| | A...

Is there a way to find if the combination of 4 column cell values has a match with a different sheet's 4 column cell and display true if the match exists using vlookup? If not what would be the best way to deal with this situation?

I have to lookup values in a table wich is not in order (and can't order it) where I lookup a number which may or may not be repeated, but I need to bring the data of a specific one. The data table is more or less like this: SAP...

My code below has a vlookup which changes a cell in my data (CurrentMonth), but I can't get it to keep the formatting of the source. The return values in col 2 are 01, 02, 03 etc up to 12 in general text format, but the formula below returns 1,...

Let's say my dataset is like the following: John NA kaira carry John NA maya Sam maya leo paty leo tinker NA tinker fabo leo maya I have another dataset: John 1 carry 2 maya 3 leo 4 tinker 5 fabo 6 sam 7 paty 8 kaira 9 I want...

The user enters usernames in inputboxes ("Enter username"), which appear in the first column from row 4 onwards (specified as cells (4+k-1,1) in the code). The number of usernames he enters corresponds to the number of users he indicates were active on a certain date, specified in the first inputbox...

I'm trying to take all of the values held within SourceSheet.Range("C2:C" & LastRowSource) and cross reference them with all of the values held within ReferenceSheet.Range("F7:F" & LastRowReference). If they do not exist within that range, then I want to delete the entire row that holds that value in the C...

Can Not Get My VLookUp In Excel To Return The Requested Data I am trying to pull data from another sheet based on data selected from a dropdown on the main sheet. All the formatting is "General" =VLOOKUP(F15737,'Location Master'!$A:$J,2,FALSE) It just keeps returning me #N/A...

I have a spreadsheet that I need to pull information from another spreadsheet that is automatically generated. The problem with the automatically generated spreadsheet is that the information that I need to pull is not always the same, however the columns that contain the information that I need will always...

I'm looking for a way to return a specific cell value from a range based on an initial desired value. It looks like this: DesiredValue typed in cell: | 125| Search Range: line1 RangeA|RangeB line2 1 | 50 line3 51 | 100 line4 101 | 500 line5 501 | 1500...

I have two text files, csvurl.txt and tickerMaster.txt tickerMaster.txt H0001 Remarks: No "H0003" in tickerMaster.txt and the number are not in sequence csvurl.txt H0001, URL1 H0003, URL3 I would like to read the entries in tickerMaster.txt one by one, say H0001, H0003... and createURL by matching the data in csvurl.txt....

I have a simple spreadsheet for tracking my hours worked and pay, depending on which job function I perform that day. Tech= $10, Video= $20. I've made a dropdown list for my two different job functions in cell "H5". I have my hours worked in cell "G5" and I want...

In excel I have two sheets. In Sheet1 Column A, I have strings. In Sheet2 I have two columns, Column A contains one word that should potentially be somewhere in a string from Sheet1, and Column B has a return value that I would want if Sheet2!A:A was found anywhere...

I recently used code from a post by @LondonRob, which allows the format of a cell to be carried over with the containing data when using VLOOKUP. Origianal question - Vlookup to copy color of a cell - Excel VBA This is great and works for the majority of values....

Sub UpdateFormula() Dim CurrStr As String Dim EndRow As Long On Error GoTo 0 EndRow = Range("A" & Rows.Count).End(xlUp).Row BaseStr = UCase(Range("A2").Value) Application.ScreenUpdating = False For iter = 4332 To EndRow CurrStr = UCase(Range("A" & iter).Value) result = Application.WorksheetFunction.VLookup(CurrStr, Sheets("CustAR").Range("A2:A2499"), 1, False) '=IF(ISERROR(VLOOKUP(A2, CustAR!$A$2:$A2499, 1, FALSE)),"NotFound",VLOOKUP(A2, CustAR!$A$2:$A2499, 1, FALSE)) Next...

I am a newbie in excel macro vba. I have a problem on my vlookup code which refers to another workbook selected by a user. Here's my code: Private Sub vlookups() Dim data_file_new As String Dim i As Integer Dim a As String, b As String, path As String data_file_new...

I understand that VLOOKUP searches the first column of a table in order to find a value, then it grabs the value from the same row and a different user-specified column. The following code returns data from the 2nd column, column B. VLOOKUP(5,$A$2:B100,2) Is there a way to set the...

is there a way in sqlite to do the equivalent of a vlookup? I'm trying to do something like this: Master table: ValueA | ValueB | concatValueA&ValueB Mapping table: concatValueA&ValueB | mapped Value final table: ValueA | ValueB | concatValueA&ValueB | mapped Value In Excel, I would just do a...

Is there a formula that match data, get the value next to it, and then post that value into another cell? This is what my excel sheet looks like: Sheet A Column A | Column B | Column C | Column D ---------------------------------------------------------- Bob John Cat John Sue Dog Sue...

I'm trying to build a spreadsheet to help automate points scoring for an office F1 fantasy league we have. I've attached an example data set, but basically I need to search a range, then count how many times the constructor appears in the numbered positions (discounting R, D as they've...

I have a small list of names with no emails on sheet one, and on another sheet (sheet 2) I have the master list of names with their emails in the adjacent column. I want to look up the email associated with the name from the master list (sheet 2),...

All cells are formatted for dates, when a cell is blank I would like it to return an apparently blank cell rather than 1/0/1900. Here is what I have so far however It is still returning the date instead of a blank: = IF(ISNA(VLOOKUP($B$4,TrainingDatabase!$A$3:$S$14,3,0))= 0,"", (VLOOKUP($B$4,TrainingDatabase!$A$3:$S$14,3,0))) ...

I've taken it upon myself the learn how to "excel". That made a little more sense in my head, but hey. I have built a "lookup tool" which can be viewed here. The "Dishwasher search" is working as intended. You can search by noise level or decor panel height to...

I have a list of hours when water is used. this list does not generate hours when water was not used. I'll have list like: Hour Liters 5/3/14 6:00 PM 36.288 5/3/14 7:00 PM 15.328 5/3/14 8:00 PM 1.6 5/4/14 11:00 PM 18.752 5/5/14 12:00 AM 21.664 5/5/14 1:00 AM...

I have the below lookup condition but can't seem to get it working. =VLOOKUP(A1,'Raw Data'!A1:A3,MATCH('Submitted Data'!B1,'Submitted Data'!A1:B1)) I'm trying to pull in the B column value from Submitted Data into the B column in Raw Data based of a match on A columns, I just keep getting an error. Raw...