i have a worksheet that is about 100rows x 100columns.
i would like to create some code to find a certain record. for example..
a1 a2 a3 a4
the record might be " tony tony alex tony"
basically, this group of cells could be any where in the group and i need some code to search the worksheet and then outline it. i just want to be able to find a certain block of cells and outline it.
I'm looking for another excel game changer (for the work I do anyway).
I have a dollar amount, and I want to know if any combination of dollar amounts in a particular range of cells will equal that dollar amount. Is this possible in Excel?
Example: I have 20 different dollar amounts in a column. I want to know what combination of those 20 different dollar amounts, if any, will equal $257.97. The dollar amount I'm looking for and the numbers in the range will change with each use. I'm hoping for a formula, but VBA will work too as I could just make a template and copy / paste the numbers in.
I have an excel file, where there is a section "Additional Requirements". This section is repeated like 20-25 times in the sheet and has a group of merged cells below it to enter data as shown in the picture. Issue is that majority of these "Additional Requirements" are not filled by the user. Hence maybe only 3 or 4 are filled and I have to manually delete the rest. I tried the code below, but it doesn't do anything.Running the macro just jumps the cells slowly downwards the sheet. Maybe if the macro runs 10-15 times it reads the whole code and then moves on to the next row.8-1-2014 12-43-27 PM.png What I'm trying to achieve is:
1. for the macro to find "Additional Requirements"
2. Then check the Merged cell below it (thus the offset)
3. If this cell is blank, select this cell and the cell containing "Additional Requirements"
4. Delete entire rows of these cells/delete the selection (entirerow.delete). (I read somewhere to set a variable as selection and clear it, hence ive included in the unreadable code. This wasn't working either)
Conditional formatting. I want to change the color of a group of cells based on data in another group of cells. Example:
If cells G8 and G9 (which are merged) are between 80% and 94%, then I9,I10,I11 (which are merged) will turn Yellow. Also, under the same scenario, IF G8 and G9 is greater than 94%, then cells I9, I10, I11 will turn Red.
I want to read information from the "Mapping" sheet, then find information in the "GLEX" sheet that correspond to the read information, then copy the information that was found in "GLEX" to the "Calc" sheet and add it all together and display the sum in a particular cell in the "Report" sheet.
The "Mapping" sheet is a mapping of information to show what makes up a particular total and where it must be entered in the "Report" sheet. The "GLEX" sheet is the output from a financial system and the source of the information that must be "reformatted" to the "Report" sheet. The information is only an extract as the whole totals to around 10MB. I have also deleted the "Report" sheet as it is fairly big on it's own, but the principal will be the same, I think. (If i should upload the "Report" sheet,)
Step 1:
"Column A" and "Column B" of "Mapping" are the starting points. The macro must read the values in "Mapping" "Column A" and "Column B" and remember them. Then it must find the row in "GLEX" "Column A" where the information from "Mapping" "Column A" matches and where the information from "Mapping" "Column B" matches with the information from "GLEX" "Column E".
Step 2:......................
Following Excel task I am trying to complete:
I have an Excel file with multiple sheets and I want to find and replace matching cell data on the same row across all of the sheets. For example, I have two columns, Column A and Column C and 10 sheets. I want to only replace the content in Column A if text matches both Column A and Column C on the same row. So, I want to be able to search for the following data across all sheets:
Column A = "car"
Column C = "yellow"
If both "car" and "yellow" are found in Column A and Column C on the same row, then replace "car" in Column A with "truck".
Is there a way to do this automatically as I have few hundred to find and replace?
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.
View 5 Replies View RelatedI am trying to write VBA code that groups cells between blank rows and inserts a message if a string of text (a name) is missing from the entire group. I want the code to search cells in column A and group the cells between blank cells. Use the name I input in an input box as the search criteria and insert a message in the Column C next to the last blank cell in the group and then move on to the next group and highlight the cell in red with bold text. I am including a spreadsheet with an example of what the sheet should look like before and after the code is run.
View 11 Replies View RelatedI am trying to select all cells in a range whos contents match the contents of a specific cell so I can group the respective rows of these cells. I did a forum search but I am not sure if I have found what I am looking to do. I am a very basic user of VB so keep answers as simple as possible.
View 8 Replies View RelatedI'm trying to find a way to search a second sheet in a workbook for specific criteria outlined in a first sheet (in my attached example, from A3 downwards within the 'list of search criteria' sheet), and then to copy any secondary data found against a successful search match to the original sheet, transposed against its corresponding matched search term.
As you can see in the example, the search term 'bindi' (A4 in the 'list of search criteria' sheet) appears in the 'data' sheet 3 times - the secondary data for these occurences ('feathery', 'Fibonacci', 'glassy') is copied to the 'bindi' row on the first sheet and is offset with each copy to produce a transposed-esque effect of copy and paste.
If it's any help, there are a maximum of 9 matches for a single search term in the real document.
Thanks in advance for your help... I tried to adapt a previous solution given to me for a similar question but failed miserably. I bow humbly to your expertise!
There are groups of similar ID numbers in Column J. For a group of similar ID numbers in consecutive rows there is only one row that has a number greater than 0 in its Column L cell and the rest of the cells of Column L for that set of similar IDs is filled with 0s.
First for that unique ID group I need to find out which row is it that has a value greater than zero in its Column L cell.
Then I need to use that value to fill the rest of the 0s in Column L corresponding to that set of Unique IDs.
The process continues with identifying similar IDs in Column J and this time doing the same thing for their Column M. I have attached a sample file that shows the data and how the results need to look like.
I have been trying to solve this problem for the past couple of weeks and I have not been able to do so. I have come to do the operation manually, and it really takes a lot of my time, so if you guys could help me, I would really appreciate it.
Basically the problem is this:
In column "A" I have what I call point numbers for all the data entries.
In column "B" I have the station (or location) at which I vary slightly a parameter (represented by column "E") to give me a value shown in column "D" (the "Z" value, as shown in the sheet).
So basically you can see that my station 0 has 11 different "E" parameters (0, -1, -2.62, -5, etc) and each of them has a "Z" parameter (390.76, 390.775, etc).
What I am trying to do is to come up with a table that shows a summary of the following:
A column with each of the stations (0, 20, 40, etc -- notice that I have some weird numbered stations like 411.89).
Another column with the "E" parameter corresponding to the lowest (or minimum) "Z" parameter within the station range.
And a final column with the minimum "Z" parameter corresponding to its "E" parameter, which is included in the station range
You can see in yellow the way I have been doing this and you can see that it would take me so much time to accomplish the task.
I have a database where I have groups defined. For example, Parent 0001 is called Bag1. It contains an apple, orange, grape, and a pear. Parent 0002 is called Bag2, containing an apple, orange, and a peach. So on and so on...
Below is an illustration of what I explained above:
Parent
Level
Description 0001
1
Bag1 0001
2
apple 0001
2
orange 0001
[code]....
I need a way to input items that are in the groups and find the groups have all of the items or the group with the best match. I need to know if the match is 100% or if it is only a partial match.
The bag might have 2 items or it could have 20 items and they can be in any order.
So what I have is a large list of items, and each item has the price of the product, and a product type.
What I want to do is find the median price for each specific product type, using a formula, without having to sort or filter my large list of products.
Col cCol dFormula
Lease TermCompany #6023%
3637%
4843%
6052%
12620%
247FALSE
368FALSE
48105%
609FALSE
Residual Table
Company #
127
438
569
10
Col b
Lease TermResidual %
1210%20%9%
245%9%6%
364%7%3%
483%5%2%
602%3%1% ......
I have a list for example...
Ian
Andy
John
David
Ian
Andy
John
David
Max
I want to search or filter the list and find the only non matched record in the list and put this in another sheet ie max as this is the only unique name in the list, all the others are matched. Everything I have tried eg filter for unique records returns ian andy john david max, which is not what I want.
I have a Meeting Place and Cities Table(too large).An example attached as a file and explained waht is wanted.
View 2 Replies View Related=MID(M11,FIND($V$6,M11),LEN($V$6))
I use the formula above to return the the matching word in v6, from text within m11. If theres is no match it returns #value. is it possible when theres no match to return "Not Match" in the cell instead
See the attached workbook.
Alright: Sheet2 is a list of lines that have a parallel match, sometimes more than one. This example has 3 lines with slope 0.18 and I've added a few extra at the bottom for the sake of the example.
Worksheet Intersect is a working page where in the Bordered Box, highlighted in Green is full of parallel lines from Sheet 2. Sometimes there's none so I have a column to the right that says true if the line is on sheet2 and false if not. There's a helper column to the right to count up the trues and false.
I need to have laid out below, all the matches on Sheet2 to the green highlighted lines but not the original line that made the True. There could be lots of them, there could be one, this example has two for the slope of 0.18 and one for all the rest.
How do I write formulas to pull the matching slopes (column A) and y-intercepts (column B) from sheet 2, if there's an unknown amount and then list them on the intersect page?
The results will look something like:
True 1...............True 2...............True 3..............True 4................True 5
.36........2.00.......18.......24.01.....-0.32.....3.00....1.04......4.00 .......0.83.......5.00
..........................18.......21.32
I want to loop through ID Column and find the matching number in Record No.column, and hightlight the entire row when match is found. The code I am running can only work for one cell at a time, but I want to run one time.
View 11 Replies View Relatedhow to reference the drang (named range) and the cell propertly as i keep getting an error.
Everything in the below code works except the parts below in blue
details:
I have in column X lots of names and in column Y (dynamic column
anges as the users chooses them as per the code) and I want to loop through all names in 'X' and if there are multiple of the same names then for the corresponding 'Y' row entry of each be checked and if they are equal (same $ value) then for one of them of cells in the Y column to be cleared.
Basically if the same name appears more than once it will most likley have the same $ value and if i do a calculation on the $'s it will be 2,3,10 times the amount it should be, hence the need for this .
As per the below code I am trying to use 2 for Each loops though I keep getting an error when trying to reference the drng range which has the values in them..
I have a total of six columns divided in to 3 columns. Each column has a number in it. I need a formula to tell me which number from the 2nd 3 columns match the number or numbers from the 1st 3 columns.
EX.
123 367 ..... the #3 is a match. the number 3 is in the 1st position. I need a column to relay this information
I have two worksheets each of which includes a left column of unique email addresses (at least, unique within each lsheet)
The data on each sheet is structured identically. What I want is to generate a list of records that appear on both sheets.
How do I create a formula that will count the occurrences on each row within the range F7:H106 where the single digit in FJ6 is paired with the 2 digit value in FF7. Match in any order.
example
If FF7=00 and FJ6=1 (Match 00 with 1 in any order)
f7:h7=010, match, count 1
f8:H8=059, no match
f9:h9=100, match, count 1
f10:h10=001, match, count 1
f11:h11=007, no match
Result=count=3
In a row on the spreadsheet I have a range of dates from 19/09/2008 to 19/06/2008 mainly with a weekly interval.
I want to incorporate into the macro a find function so that it can determine the first occurrence of a specific month and return the row number.
Using the Find Function in VBA with pattern matching, I want to find the End statements in my code. How do I stop it finding the End If statements, of which there are hundreds maybe thousands. I tried End[ ][ ][ ] and End[!I] etc but they exclude the End statements as well.
View 3 Replies View RelatedI have a spreadsheet with 65000 potential client surnames in column c, with contract details in neighbouring columns. I have also a spreadsheet of 6000 actual client surnames. I would like to search the large spreadsheet to make sure that none of the clients are contained in it, or if some are to highlight them.
Do I use advanced filter or vlookup? I use excel 2000 and due to the size of the first spreadsheet it takes 6 minutes to open/save e.t.c.
I am an inventory specialist for a dish network company and as such I track inventory in and out of technicians vans, both serialized and not. I've done a great deal of work updating a broken excel sheet they use so that it functions again but I didn't build it. I've learned a lot but I'm only self taught with Excel and had never even heard of VBA code until I dived into this project. It's a huge puzzle and is now my "baby".
Anyway, basically I have one sheet that has a list of all the items I need to keep track of. One section of this Sheet1 I've designed to have cells with dependent drop down lists that are Named Ranges on Sheet2. The tech can choose item A B or C in the first dropdown box and then the next cell shows only the serial numbers from the named range on Sheet2 of A B or C. (Was that english?)
Since the receiver comes out of the techs van once its used I want to figure out a way to delete the serial number that the tech has chosen without deleting the row or cell, just the value in it so that it can then have another serial number typed in. How can I do that?
Also, since I'm here, my 2nd drop down list seems to always start scrolled down and I have to scroll up to see my serial numbers. Why is that? The receiver list starts at the top but the dependent one doesn't...
I've got a problem which I'd like to do without using VBA if possible. I have 2 rows, one with text and one with numbers. I need to go through all the text, find the text that matches and add the number in that row.
That's very poorly explained so I've given an example below:
text 1 20
text 1 10
text 2 10
text 1 20
text 2 10
I need the formula to do this:
text 1 50
text 2 20
Need to find average (see attached file) of certain rows of column C that correspond to letters on column B that match any letter on column A. Basically if column B has the same text as in one of the cells in column A then number across in column C should be used to calculate the average. In column D I have entered(manually) all values that satisfy the above condition and obviously the average should be 1 if formula is correct.
I have tried to use AverageIF function but not able to do it as there are many conditions. Another thing to consider is that conditions change (column A content) so I need to use cells that have information rather then actual text in it.