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 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 RelatedI'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
I am trying to create a formula for a work report and I am afraid I am a bit of a novice with the more complex formulas. Here is what I am trying to do: Sheet 1 has Sales order numbers that duplicate based on the sales lines. Sheet 2 has single lines of sales order numbers and their assigned PO number. I would like to have Sheet 3 where it combines all data and duplicates and fills in the PO number to match all of the sales order numbers that repeat on the other sheet. I hope this makes sense. I can't seem to figure out if I need a VLookup or If or Match formula (or all of the above).
View 1 Replies View Relatedtable1.jpg
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
I'm trying to create a formula in cell f13 of my attached spreadsheet "Sample 1" that will search the 2nd attached spreadsheet "Sample 2" and return the correct serial number based on both the matching PO # (located in cell E10 on Sample Sheet 1 and in Column 5 on Sample Sheet 2) and Product # (cell A13 on my Sample Sheet 1). My current formula is not returning the correct result and I'm not sure why.
View 4 Replies View RelatedINDEX($D$19:$D$22,MATCH((LARGE($E$19:$E$22,1)),$E$19:$E$22,0))
I use this formula to give me a "name" associated with the largest number in a list. But I can't get it to work when I use it on a win/loss number such as
"7-1". It won't read it.
The simple table (attached) represents our problem.
We want the letter in Column-B that corresponds to the last non-zero entry in Column-A. So in the example provided, the answer will be cell B12 which is "L". Also, if there are blanks (rather than zeros) below the last non-zero entry, it still needs to work.
I have a a spreadsheet with content (text) in one cell pr line. The content can be in the range column 1 to 8. I want to make a formula where I get the number of the column where there is content. I know how I can make this by using nested IF's, but are there any better proposal?
View 3 Replies View RelatedI'm holding a Reverse Auction where people pay a dollar to place a bid, BUT the twist is they are giving me a number that they how will be the lowest number but it has to be the only occurrence of that number (greater than 0).
During the party people can guess as much as they want to pay. I think i'll use a spreadsheet with their names in column A and go out in the row with however many cells for how many numbers they guess (so there would be blank cells in the overall range of the whole list if one guy buys 10 numbers and another only 1, for example).
So, i need a cell at the bottom that tells me the lowest number that wasn't guessed more than one time.
I've found how to FIND duplicates and the lowest number but i don't know how to write it so that it discards the duplicates.
I'm looking for a formula, which says if a number is within a range, perform a calculation. I have a table of ranges (0-6000,6001-10000,10000-12000, and so on). My formula looks at the current month cumulative balance, and inserts the value into the line of the range it falls into. However, the formula now contains lots of nested "IF" statements, due to the number of ranges I have. Also, if my cummulative balance is 6005 for example, but was 5005 in the previous month, I would like to see 95 in the 0-6000 row, and 5 in the 6000-10000 row. I making a complete nonsense of trying to explain myself, so I'll attach a quick example of what I'd like to see.
View 4 Replies View RelatedCol 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
how 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 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.
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.
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.
In need of help on formula to look up matching numbers between cells.
Cell H3 has 6
I3 has 7
J3 has 5
-
D3 has 6
E3 has 7
J3 has 3
Would like a formula on cell L3 to showcase how many matching numbers in H3:J3 when matching up with D3:J3. In this example, there are 2 numbers that matched, 6 and 7. So on L3 it shows 2. How many matching numbers are there L3 shows it.
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...
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.
Macro: Copy selected cell to another sheet next blank row
I'll just make this simple to understand.
*Again, I have first sheet, named "SUMMARY"
*On Column A, I have list of names
A1: Star
A2: Moon
A3: Sun
A4: Clouds
*these cells corresponds to the Cells of different sheets
A1: Star = E3 of Sheet 1
A2: Moon = E3 of Sheet 2
A3: Sun = E3 of Sheet 3
A4: Clouds = E3 of Sheet 4
this is what I should get:
1. I select the cell A1 as an active cell
2. press the button (with the macro on it)
3. I will automatically go to the sheet where "Star" is located
I'm looking for code to find a copy of the text in cell AH3 in a range AH7:AH100 then delete all copies of the text in that range AH7:AH100 leaving other cells in that range which contain different text intact.
View 3 Replies View RelatedI 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.
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 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:......................