Finding The N! Routes
Sep 30, 2007let say i have six cities.
A-B-C-D-E-F
a guy in city A, must visit all other cities and come back to A..
ABCDEFA
ABCDFEA
ABCEDFA
ABCEFDA
ABCFDEA
ABCFEDA
so that this gives us 120 route.
let say i have six cities.
A-B-C-D-E-F
a guy in city A, must visit all other cities and come back to A..
ABCDEFA
ABCDFEA
ABCEDFA
ABCEFDA
ABCFDEA
ABCFEDA
so that this gives us 120 route.
I have been trying to write two formulas in one cell. I have been able to write them both separately but have been unable to join them both together. What I am trying to do if first search name them how many reoccurring numbers appear. I have provided an example below
a b c d
1 Tom 333
2 Sam 22
3 Sam 22
4 John 5
5 Sam 22
6 Sam 1
7 Tom 3
8 Tom 333
So the answer would be
Tom = 2
Sam = 2
John = 1
I have sheet with rows and column from cell(1,1) to Cell(8,18) and it has #N/A inside these data. Is there a way to use Find function to find #N/A and replace it with empty space?
View 11 Replies View RelatedI am working on a macro that has a VLookup in it.
The sheet that this will be applied to comes in weekly and can have anywhere from 10K to 30K.
I want the VLookup equation to be able to find the last possible row with data in column A and then copy the Vlookp equation in column B to the last row.
Can someone please provide the correct code that will allow me to do this?
What formula will return the the first value to the right?
For example: A1=empty cell, B1=3, C1=empty cell, D1=0, E1=5, F1=empty cell.
What does the formula need to be in G1 to return the value of the first cell to the left than contains 0 or a value, or in this case 5 (E1).
In my code I have a very useful line:
[other code stuff here]...Range("J" & Rows.Count).End(xlUp))
That helps me find the value in the last row that actually has data in my spreadsheet. This is great, but I want to try to accomplish the same thing without vba. In other words, in cell A3, I always want the value that is in the J column, but the last row that has data. (In other words sometimes the last data row ia 97, sometimes it is 23.) Since the value is always changing this won't be vlookup. Is there another excel function that can find the end without the use of vba?
This might seem goofy to need to do since I already know how I can in vba, but in my (admittedly pathetic code) I keep have an endless loop and if I can figure out a way to get this value on the ss.
I need to find, using VBA, three highest numbers out of entire column (L), select 13 appropriate ranges like for L9 - A9:M9 , for L8 - A8:M8 and for L7 - A7:M7 and copy to a different sheet.
View 9 Replies View RelatedBook1.xlsx
I have two sheets of data and two tables (1=finial 2=data comparison) I am trying to figure out some kind of formula or vba that can easily tell me that the location point in table one data comparison matches the location in table two. The VPMDFS (location footage) in table two or NDE data can be a range, say vpmdfs.NDE + or - 3 ft. so the ILI or source record should fall in between this range. Once the match has been made I need to copy and paste that record onto sheet one all in the same row. I have been scratching my head and researching for weeks and cannot come up with a simple solution. My problem with all the scenarios that I've tried is the formulas what the data in the data comparison sheet to already be in the same row. Please reference my attached work book for examples.
How to find a particular cell value, example: i have sheet and in that we have 30 employee names and i need to map the data of work from home, which is in dark bule color and need to make a total of it. rather than counting the all the sheet details from month start till end. I need to find it in a quick shot.
I would like to prepare a shift schedule for 3 months it should be a automated.
I'm currently using a formula which is exactly what i want but i need it to do something additional.
The formula is: =IF(ISNUMBER(SEARCH("Level 1 ",$I:$I)),$K:$K,"")
My understanding of this formula is that the cell the formula is in then searches column I for the value "Level 1 ", if it's true it returns what is in column K, if false then it returns the cell blank/0. This is a great function and works well.
What i want to know, is there a way to add another rule on to it that instead of returning the value blank/0, it finds the next cell in the column that has a TRUE value and omits it instead.
For example:
At the moment it shows:
Level 1A
Level 1B
Level 1C
(Blank Cell)
Level 1D
(Blank Cell)
Level 1E
But i want it to show like this:
Level 1A
Level 1B
Level 1C
Level 1D (Replaces blank cell with the next TRUE value)
Level 1E (Replaces cell that previously had "Level 1D" in it to the next TRUE value seeing as "Level 1D" is now above)
I don't want the cell to appear as blank.
In column K, i would only have the data for "Level 1 ", i can then copy the formula to other columns for "Level 2 " and so on. I don't need it to return the cells in alphabetical order, just the next true value in the column that matches the values in the reference column.
I have a working formula that gives me the last value in a column using Index formula below. How can i slightly adapt the formula to find the maximum value in that column instead?
=LOOKUP(10^99,INDEX('Data Drop'!$A$15:$AZ$10000,,MATCH($H$9,'Data Drop'!$A$15:$AZ$15,0)))
I have data that has about 10 duplicate values (UTC Time) in one column and another column with number values (depth ft) that vary. I need to obtain the maximum (highest) value in the depth column and remove the other duplicates to filter out the low values. So for the data example below for UTC 15:56:28 I only want the 5.7 row, for 15:56:29 I want5.3 row and so on. I can attach the sheet. - this is a huuge dataset so manual filtering wont work. Data is from a sonar that gives 10 depth readings per second - I only need one depth tat is the highest value.
IDUTCDepth (FT)
115:56:284.3
215:56:284.4
315:56:285
415:56:285.4
515:56:285.7
615:56:285.5
715:56:285.6..........
I tried "googling" this, but I can't seem to find an answer. Is there a way in VBA to refer to the "subtotal" row(s) in a sheet? I have a large sheet that has a varied number of rows. Each month the data changes and I have to go in to the report, subtotal by one column and then enter a specific formula into the subtotal row.
Is there a way to reference the subtotal row in VBA so I can write a macro that will do this all for me? There are typically a varied number of subtotal rows and the locations of them change depending on the amount of data we have each month.
maxvalue = Application.WorksheetFunction.Max(Range("D:D", "A:A", "G:G"))
This gives me 'Compile Error:Wrong number of argruments or invalid property assignment'.
What I'm trying to do is generate a 2D array of values in VBA from data in a spreadsheet. (Array is not pulled from sheet)
What I would like to do next is look at each row or column of the 2D array and find the max value. I've tried using the worksheetfunction.MAX to no avail.
I am creating a simple userform wherein when I clicked the save button, it will look for the value of a textbox in the excel column then it will save the details on that row. For example, the value of textbox is "12345" and it is located on D5, the value of textbox2, textbox3, textbox4 will be saved in E5,F5,G5.
View 2 Replies View RelatedI can put the Data in F21, but then I need to skip a cell and put the next Data in H21 and so on, the cells are in yellow that I need to string out the data in. so every time I open the form the data skips a cell.
View 6 Replies View RelatedHow can i know name of the column.
Example:
I want to be able to find all the 0 differences (i.e debit = credit) cut that group out of that sheet and paste it to a new sheet in the same workbook. I have the code to find the 0 difference but I don't know how to select the range that makes up that difference and cut and paste it to a new page.
View 2 Replies View RelatedI name a range "MyList".
In another cell, I use Data.Validation.List and use "MyList" as the source.
Now I find another range named "HisList". Is there some way to determine if "HisList" is being used as a source for some other validation somewhere in the workbook?
"Trace Dependents" doesn't work.
If i have a range, lets name it "Names" and i want to find if the name
George is in that range, what formula should I use?
I have a Sheet which has a Header Row which consists of Dates like this: ....
View 14 Replies View RelatedIn a table 1 column are dates and another are machine parts. The dates are when that machine part broke last and a lot of the machine parts are repeated. Something like this
Column 1......Column 2
11/19/2010..Part 1
4/13/2010.....Part 1
9/13/2010.....Part 3
1/20/2010.....Part 2
10/11/2008...Part 2
2/1/2011.......Part 3
I'd like to find the last date the part broke. so the newest date. It would be nice if excel had a =maxifs() button. Can this be done without using a sub?
I have a spreadsheet which contains a Table. I would like to find the bottom last cell in Column B that has a value...however,
Code:
LR = Range("A" & Rows.Count).End(xlup).Row
only tells me the bottom of the Table, but there are empty cells above that.
I have a number of spreedsheets that may have a 1000 worksheets. Is there a way to find a particular worksheet using a search by the name?
View 9 Replies View RelatedIs there a function in excel that can look at an array of values and find the two values that equal a number.
I have a number 159. I have an array of 100 numbers but only two of them added together can equal 159. Rather than sort through every possible combination is there a function in excel that can do that?
I have this formula on my worksheet to test if column A="-" or "+"
Code:
=IF(OR(A:A="+",A:A="-"),NA(),"")
I would like to enhance this so that the test looks for the the + or - inside column A which might be a whole string rather than just one character and returns the same result (an NA# error if found).
I am doing this so I can then use SpecialCells to clear the cells containing the + or -
The data I need is in the middle of the worksheet (A16:B41), is there way to define the range so that excel only looks at that range and finds the last row of data?
Code:
Worksheets(i).Activate
lastRow = ActiveSheet.Columns("A").Find("*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows, LookIn:=xlValues).row
Range("A16:B16" & lastRow).Select
Selection.Copy
Sheets("difference Check").Activate
[code]....
I want a formula to find the Max value in a row based on the condition from a different row.
Example:
I want to get the highest value from row 2 that also has the value A from row 1.
A
B
C
A
A
B
1
3
5
2
5
6
This would result with 5.
I have tried using MAX and IF, INDEX and MATCH but cannot get it to work.
I'm having issues with finding anything on the loop that I'm going to need to run.
I need to open each xls file in a directory with a date in 2007. Inside of each file I need to copy a cell and paste that number into a new workbook in cell A1
Then close the first file, open the second, copy the cell and paste the data into A2. I would need this loop to continue until all files have been opened and had the data copied.