Im trying to work out how to write some code to find and replace from a list.
I have key data in column P on sheet 1, and a list on sheet 2 with the same key data in column C.
Basicially, every month the data on sheet 1 changes (there are usually 100 rows of data each month) and what i want to do is, write some code to look down column P on sheet 1 and for every cell, find the corrosponding value on sheet 2 (column C). When it finds the value, copy and paste it onto sheet 3 then delete the row from sheet 2.
I hve written some code that can do the first part (copy and paste) and a second macro to do the deleting....but it is only for 1 value at a time...
In column "AT" I have a list of matchups between 2 soccer teams that looks like this
Row 1:Manchester UnitedArsenal Row 2:Chelsea FCLiverpool Row 3:TottenhamAston Villa Row 4:Hull CityLiverpool Row 5:Everton FCSunderland Row 6:Bolton WanderersManchester United Row 7:PortsmouthTottenham Row 8:Birmingham CityStoke City Row 9:WiganBlackburn Rovers Row 10:LiverpoolFulham
In columns "AM" and "AN" I have a rating for the home and away team. So for example, AM row 3 would show the rating of Tottenham and AN row 3 would show the rating of Aston Villa.
Now what I want to do is set things up so that I don't have to enter every teams' rating in manually. I need a formula that would look from bottom to top (row 10 to 1), find the first row that contains Tottenham (for example), and then look to the right to either column AM or AN (depanding on if Tottenham is the first team in the matchup or second) and return the value in that cell.
I'm trying to compare 3 different worksheet in the same workbook and copying the unmatched values into a new worksheet. find the attached file. In the sample file, am comparing sheet1, sheet2 and sheet 3 with sheet1 as the base. the columns mite differ in their location but the column headers are same.. The difference, bolded cell, should be copied along with the header and the code, into a new file and has to be saved...
I am trying to track cost. A master purchase order sheet has a column with a number relative to an account. I have about 50 accounts and a seperate sheet for each account. I want to enter the account number on the master purchase order sheet and based on that number, have the following data on that row copied to the next available row in the sheet for that account.
I am trying to track a score. A master capacity sheet has two columns with identifiers (upstream and downstream nodes) relative to a pipe segment. I have a separate sheet (condition sheet) with the corresponding scores. I want to enter the upstream and downstream node numbers in 2 columns (A & B) in the master capacity sheet, and based on the the upstream and downstream node numbers, have the corresponding score (from the condition sheet) on that row copied in the master capacity sheet.
I want to find the corresponding Disc Codes from a list and copy them in the DiscName column in the summary sheet. some lab names will have more than one Disc codes so when I run the macro it should bring up all the relevant Disc Codes matching with the Lab name to DiscName column.
Col 1 col 2 col 3 Lab name Disc Name(say abcd) xxxx yyyy zzzz ppppand
The list looks something like this.
Col 1 Col 2 Lab name Disc nameabcd xxxxxabcd yyyyyabcd zzzzzabcd pppppbcda qqqqqbcda rrrrrbcda iiiiibcda jjjjjbcda kkkkk
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.
I'm having a difficult time returning COUNTIF values for a positive match between 2 columns THAT meet a certain condition. Basically I want to iterate through column A sheet 1 (ONLY for values where column B is paid) and return a count for every instance there is a match of value A sheet 1 in column A sheet 2. In other words, I'm looking to find the number of ids marked as paid from Column A sheet 1 that exist in Column A sheet 2. I don't wish to return the actual ids, just the total count.
I've tried the following but I know there's an error in iterating through Column A the way I have it:
I have a problem with the formula that lookup all values in ascending order and returning all the corresponding values. eg: I was intended to lookup for the value in ascending order under the Total Occurrence and returning all the corresponding value under the Nos Group but encountered the same Nos Group was returned when there is same value appeared under the Total Occurrence.
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
Hi there, please can you help me transfer data from 1 spreadsheet to another (I have attached a file which has 2 sheets).
I am trying to populate columns G to L (sheet B) with data from column H (sheet A) - the lookup is between 'Material' & '7410','7090','7680','7060','8630','7580' on sheet B & material & plant on sheet A.
I have tried H&VLOOKUP & INDEX/MATCH but I keep returning a #N/A..
Is there a way to use a lookup formula to return all values of a given text, not just the value of the first listing of the text. In an attempt to clarify my already confusing request, if A5 on "worksheet1" is "Land" I want E5 on "worksheet1" to return the summation of values assigned to "Land" "worksheet2" "column B" any time "Land" appears on "Worksheet2" "Column A".
I am wanting to use a vlookup or perhaps an array(?) to bring through multiple cell values based on being the maximum value. So say there are multiple employees (all with unique ids) under one manager (also with unique ID), I want to find the employee with the highest value in a particular column and return that employees details, such as name, employee number and the value itself, which are all in the same row. I have attached a basic example of the data involved.
In the attached sheet, I want to look up values from table (A1:G5) and then for each occurrence of a particular number, return the value in the same row in column H, and sum when there are multiple values (see green shaded area for desired output. For example, the number 8 occurs in E3, E4, and G5. I want to return the sum of the corresponding values in column H (1,5, and 2). I've spent hours trying to work this one out, I'm sure this is probably an array formula, but I can't manage to make it work.
I have all my data on sheet 2 which contains tables that relate to information on regions, manufacturers, etc so it can be a large amount of tables which will change from month to month depending on a monthly list. For example, if “Region 1” is in cell B134 – the resulting data I need to pull out will be contained in C138:G232 ...or ”Region 2” which is in B235 – table info is in C239:G333, etc, etc.
On sheet 1, I have a cell (L7) that is populated by another formula. This cell is effectively my lookup to pull out information from sheet2 to populate cells in (sheet1) AE119:AH212 - same sheet as the reference (L7).
I need to look up the reference/resulting value that is in L7 on sheet1, find and match against the values in column B on sheet 2 and then pull in the corresponding table information.
If the value in L7 was not changing at all I could do.. in cell AE119… =OFFSET(sheet2!B134,4,1,1,1) etc etc ... but I am at a loss as to the value in L7 changing and incorporating a lookup… or maybe I am looking at it wrong..?
I have looked up match, offset, index and lookups on the forum and have managed to confuse myself even more. I have even tried taking some of the example formulas and amending with my references but to no avail.
I am currently looking at the workings of a spreadsheet designed by someone else.
First of all i need to know,how these combo boxes are created in the attached sheet,because it cant be edited.in addition to tht in the coloured cell (F17)i'm trying to dereive a formula which is,if (C17 = doll "1"),but its not working.Please someone give me a solution.
Use a referenced lookup value from sheet "A", to return values, from several columns in sheet "B"
Things to note:
a) The lookup values sometimes repeat. I need all the associated values with each repetition as well.
b) The lookup values in sheet "A" are a comprehensive list, sheet "B" also contains some of these values but not all. Essentially, what I need to do is find a way to lookup each value in an account numbers column in sheet "A", against a different account numbers column in sheet "B".
If that value occurs in sheet "B" I want it to return the values from Columns X, Y, Z, (I want these values returned in sheet "A".
If that value does not occur in sheet B, the corresponding cells should remain blank.
If the lookup value occurs multiple times, I need all the corresponding values from each of X, Y, Z columns.
I know something very similar was posted before, but unfortunately, it wasn't what I was looking for. I have a Worksheet tracking several associates and the department they have done work in. (Our associates are contractors for other departments). I need to have a final worksheet that allows me to pull up a name, and display every dept they worked with in the past week.
Working on an export from an e-commerce application that lists the item purchased, unfortunately not by item #, but rather a lengthy description. I'm trying to construct an item number based on that description which contains several constants.
Each item ordered appears in a single row, comprised of ORDERID, QTY, PRODUCT DESCRIPTION
I have several tables to pick out PRODUCT TYPE, COLOR, SIZE, etc. I need a formula to search to see which of the unique values appears in each in the cell and return the shortcut of that value.
PRODUCT DESCRIPTION example "Sweater - Blue - Large - Wool"
TYPE TABLE: Sweater SWTR T-Shirt TSHT Jeans JEANS
COLOR TABLE: Blue BL Green GN Purple PL Black BK
I would have separate tables for each part of the description.
The formula I'm seeking would search the PRODUCT DESCRIPTION cell for the range of values for each 'part'. So it would search to see which of Sweater, T-Shirt, or Jeans was in the cell and then return the one that was - SWTR in this example. I would modify this formula for each 'part', in its own cell, and then use an =cell#&"-"&cell#&etc... to combine these returned values.
I know I can write a large if- chain using ISERROR & FIND, though some of these lists will get lengthy.
I need to lookup data based on a cells content. The answer has to output to a table as a sequential list. I have attached a sample workbook (there will be 100+ sheets in the final version!). The Register sheet lists competencies against page# and title. For each Evidence Sheet I need to populate the Competency grid (C4:D10) in a sequential manner i.e. no blank lines,and the Title (B13) based on the Page # (D2). I have looked at a great number of postings, but cannot see a solution that fits.
I have an excel sheet with cols of information e.g IDNo, FirstName,LastName etc. I have created a form in VB where you enter the IDNo (& Click on a command button called View) It should populate designated textboxes on the Form. Thus it should check the col where the IDNo is stored, find the IDNo and populate the adjacent cols i.e FirstName, LastName in designated textboxes on the form.
I need to create a macro that will loop through a list of names on Sheet 1 / Column A, and if that name is listed on Sheet 2 / Column A, then it will set Sheet 1 Column B (for that one name) to "1".
The problem I've run in to is that both the lists change in length each time I have to run this (so no set range).
I've been able to get this to work with two ( nested) loops, where the first loop goes through the Sheet 1 list and the nested Loop looks for the name on Sheet2, but it is extremely slow.
Is there any simple VBA code or formula that I'm missing that could do this? I've tried to do something with the " Find" VBA command, but I can't see how it would apply to this if the list changes in size.
I have a worksheet containing a list of states in column A and a list of companies in column B (along additional data in columns C-L). I want create a lookup formula on a separate worksheet where users can select the state from a dropdown menu and it will return the information from columns B-L for that state.
A B C D Alabama ABC Company Active Expires December 2009 Alabama 123 Company Expired Expired April 2008 Alabama XYZ Company Active Expires August 2009 Alabama Larry Company Expired Expired May 2006
How can I do this without losing my mind? I've tried various Index, Small and Match formulas and none of them work.