I organize monthly audits of work completed by the team. Essentially we all audit each others work; but we cannot audit our own work.
I would like a formula that will evenly distribute the jobs for audit without passing a job back to someone that owns the job originally. e.g. Gareth cannot audit a job Gareth owns, but can audit Zach or Claire's jobs. The Excel sheet attached should make it easy to understand.
So far I have tried: =INDEX(A2:A24,RANDBETWEEN(2,24)) but this duplicates data and I cannot get it to evenly spread the jobs amongst the team.
I have column A with 1000 cells tall with a bunch of random whole numbers from 1-100. In cell B1 I enter the formula:
This goes through the data and outputs the location of the first instance of "25". However my data has 8 instances of "25", and I want to know all of them. So basically in B2, B3, B4, ... I want to enter:
I need to be able to create a list of all combinations (where position doesn't matter, therefore its not a permutation) of a list of numbers with repetition enabled. I need to provide anywhere up to at least 10-15 numbers if possible and they are integers normally between 1 and 72. Example: (for numbers 1, 2, 3)
Arranging row wise an array of numbers from 1to 11 randomly with no repetition. I need it for 700 rows.
Sub test() Dim a1(1 To 11, 1 To 2) As Double, a2(1 To 11, 1 To 2) As Double, a3(1 To 11, 1 To 2) As Double Dim a4(1 To 11, 1 To 2) As Double, a5(1 To 11, 1 To 2) As Double, a6(1 To 11, 1 To 2) As Double Dim a7(1 To 11, 1 To 2) As Double, a8(1 To 11, 1 To 2) As Double, a9(1 To 11, 1 To 2) As Double Dim a10(1 To 11, 1 To 2) As Double, a11(1 To 11, 1 To 2) As Double, i As Integer Randomize
If i have a string of information in 2 workbook and I need to check if the details in column a in workbook 1 has a duplicate entry in column b in workbook 2 and if there is a duplication, then highlight it in both workbook. How can I go about it to create the codes so that I will be able to use it for different workbooks without changing the codes every now & then?
Scenario: - Monthly invoice verifications for a few vendors. To verify info from the manual invoice againts the auto invoice (2 different workbook for each vendor) Need to verify info from Column A in Manual invoice against column B in Auto invoice. If there is a same data in both column, then data to be highlighted.
I have 2 cells with numbers. In a 3rd cell I want to create a formula which looks at the 2 data cells and shows a value. The rules are the following: If C1 or C2 are bigger than Xthen C3=value1 else C3=value2. I have some basic excel knowledge but im not very familiar with functions. I'm using Excel 2007.
I have a list of numbers from 1-7 would look like this each number in a seperate cell.
3 5 1 2 3 5 6 7 2 4 5 6 7 6 7 4 6 6
I want to use one number from each row (which there is only 6 rows) and then find every number from 1-7 that will complete the sequence 1-7. So with the numbers above (using one number from each row) the only other numbers that could be used would be 3 or 5.
The combos that would work: row 1 use 3 ----------- row 1 use 5 row 2 use 1 ----------- row 2 use 1 row 3 use 2 ----------- row 3 use 2 row 4 use 7 ----------- row 4 use 7 row 5 use 4 ----------- row 5 use 4 row 6 use 6 ---------- row 6 use 6 5 would complete ---------- 3 would complete
Remember the numbers and how many numbers in each row can change but will always be 1-7 and I always need to find every number that can complete the sequence 1-7 by using one number from each row.
on the extreme left column, i have 23 numbers from A1:A23. All 23 numbers are in the form of 4 digit. For example A1 there is 1234, i need to display the possible 3 digit combination of this in the same row (like say 123,124,234,134 in B1,C1,D1 AND E1).
Another example in A2 there is 3545, i need to display 354,455,355 in the same row in B2,C2,D2
I need to perform this operation for the 23 numbers on the extreme left row. Can give me some hint on the code.
I am trying to show the all possible combinations of a set of numbers in Excel, in my case I think permutations are more appropriate to use. For example: there are three numbers 1, 2, 3 I want to show results like:
1, 2, 3 1, 3, 2 2, 1, 3 2, 3, 1 3, 2, 1 3, 1, 2
The functions in Excel available only give the total number, but I want to see these combinations!
I was just wondering if it was possible to only allow cells in a worksheet to only allow values that are a combination of 2 arrays whether it be through data validation or other means. For example, if I have an array that has a b and c and a second one with 1 2 and 3, is it possible to only allow values a1 a2 a3 b1 b2 b3 c1 c2 c3?
I'm trying to use a combination of Hlookup and COUNTIF. I'm selecting a date value in a cell using data validation. I'm then wanting to write a formula to lookup that value in a row of dates, and then use a countif to find all the '1' values in that column.
I would like to check a combination of two cells, if these two cells are both empty (not zero, just blank) then it will return a blank in another cell. I tried using AND but am unsure how it works. I would like to use a "Case" Function.
Function FirstCheck(Count1, Count2) Select Case FirstCheck Case Count1 = "", Count2 = "" FirstCheck = "" Case Else FirstCheck = Abs((Count1 - Count2) / (Count1 + Count2)) End Select End Function
I have created a program where there is a spreadsheet containing all of the items in a loan tools store, which are issued out to people. I have created a "Search/Find" function within the "Issued Items" sheet. Within this search function, you are able to search all or any combination of the following criteria: Serial; Description; Name; Location. For example: If you just type data in the "Serial" field, it will just search that column and select the cells which contain that value.
The problem I am having is when searching multiple criteria, each and every cell in the columns which are searched is selected. Whereas, I would like only the cells which match all of the criteria to be selected.
For example: If I was to type "1" into the "Serial" field, "2" into the "Description" field, "Liam" into the "Name" field, and "Workshop" into the location field: Current: Serial column is searched and all cells with "1" in are selected. Description column is searched and all cells with "2" in are selected. Name column is searched and all cells with "Liam" in are selected. Location column is searched and all cells with "Workshop" in are selected. What I Would Like: Program to search each of the specified columns and only select data which meets the searched criteria. For example: Rows 20 & 28 to be selected as they both contain, Serial "1", Description "2", Name "Liam", Location "Workshop". Note: The sheet will have password protection.
i've attached a worksheet yet removed my attempt at formulas as it would have made most of you cry... what i'd like to do is select an item from a dropdown list (B1) (that i've built and it works, phew) and display the summary data (B3:B10) from the column or sum of columns in array (A12:F21) as explained in the relationship matrix.
I've tried ifsum and dsum (copied in each cell B3 to B10 of course) yet it doesn't extract items from the array with the drop-down selection. nor does it add columns together.
I am trying to sum 12 columns based on looking up a reference that is in one column. Basically I have 2 files where on both files Column A has a G/L account number. On the data file I have credits for each month going from column C to Column O. On the other I have one column where I want to bring in the sum of all the months based on looking up the G/L number in column A.
write a udf function to deal with the above ?I know large(a1:e1,1) for picking up the largest one in the row,but no idea to find the largest one when these five numbers are combined to build a 5-digit number.
[ want to find largest 5-digit (also 4 ,3 digit ) numbers combined by thess five numbers]