I have three cells in a column. A1, A2 and A3. There are only 4 possible numbers that these can have in them, 0,1,2,and 3. Here is the catch. None of them can repeat, although they can be in any order. And the column may only have data in the first cell or the first two or all three. Is there a simple macro that will alert me to when a number is repeated? It needs to be a macro rather than a apreadsheet function.
I am coding a macro that would take all information needed from an existing pivot to put in in a new report file.
Now the problem is that I couldn t get to paste the data (for some weird reason), so I did a loop that terminates with IsEmpty...
My problem is I have to write on loop per column, because I don t know how to tell excel to go to next column (that I know!) AND to the first row, which means doing an offset of (x,1), but how do you set the x for it to be adaptable if I change the length of the pivot table?
I really hope someone can help me with this, I tried to replace range("first cell") by cell(x,... and set x as a variable but i am not good enough obviously....
Here is what I have so far:
Dim k Dim w As Integer
Workbooks.Add ActiveWorkbook.SaveAs "C:Dokumente und EinstellungenCustomerServiceDesktopMonthlyReportAutomated.xls" 'iniatiation before loop Workbooks("ChannelProductionlastestupdateJAN2007").Sheets("pivot").Activate Workbooks("ChannelProductionlastestupdateJAN2007").Sheets("pivot").Range("a20").Select k = Selection
I have a column where i have to put document numbers, Column A, i want to avoid accidently capturing something twice, so the only thing thats unique is the document number. Can i get an error if i try to enter the same number twice?
I need to make a list that has 90 people and each day I need it to pick 4 random people. I need it to keep rotating people daily for the entirety of the monthfrom the first day of the month without utilizing the same people over again. I not excel savy at all so I need it dummy proof
I have an excel assignment that has about 4000 orders for wood. I am suposed to find the company that ordered the most wood and of what kind. I am really at a loss for how to do this and have combed over my book quite afew times trying to figure it out. However it has little if any excel info and nor does the ** that came with it outside of how to install data analysis tools. I figure I need to find a way to get excel to combine the numbers together for orders from the same company but after trying for afew hours I have gotten no where.
I have a spreadsheet that lists numbers in a column. They increase 1 number at a time, however the same number is listed 24 times before increasing to the next. Scaled down to 3 times it would look like this: 111, 222, 333, 444, 555, each one of those individual numbers would have their own cell. When I highlight the column and drag down, it starts doing decimals..how do I get it to recognize it? sample.xlsx
I want to drag it down to continue the pattern. Right now, I type a couple of numbers and drag to finish the section.
I am trying to do something that is probably impossible, but I figured if anyone could come up with a solution, it would be you folks here at Mr. Excel, so here goes.
I'm trying to set up two columns in Excel 2000; for simplicity sake, let’s just call them Column A and Column B. Each row in Column A will contain a code (a combination of letters and numbers) that represents one of 65 different workstations, and they could repeat multiple times. Each row in Column B will contain a number that represents a specific tool, and there are potentially hundreds of different tool numbers.
What I am trying to accomplish is to have something alert me when any tool number in column B appears alongside more than one workstation code in column A. It would really be nice if something like conditional formatting could be used to change the text to red or something when this happened. I know I can use filtering to accomplish this, but with hundreds of different tool codes, filtering just takes too long.
I am busy with a very large dataset +-20 000 records showing the movement data and I am having problem with inserting missing data: I have columns titled day hour and position, but multiple records for each as data was initally recorded by second. Data looks like this
What I need to end up with is a sequential dataset running from day 1 to 388 with hours 1-24 of each day. Positions need to be filled in for all data recorded (see above). The big problem is that movements between positions need to be recorded and accounted for i.e if more than >80 of time was spent at the position Z3 then that is the position for the hour.
I need a function or VB code to parse through a column of numbers and find a repeating pattern. The column has some initial numbers that do not fit the pattern and I need those returned along with the repeating pattern.
I have a set of data in once cell and its strung out. I'm trying to pull the numbers that follow certain words from that data. That's easy enough with the find and mid function for the first one. But after that I have a repeating word (see below) and different numbers that follow it. So I want to pull the second number string after that word, the third and so on.
I've attached an excel file that explains this better.
sorting data I use in a workbook for athletics. I've really chopped down my workbook for upload, In the worksheet "Leaderboard" I can call up stats for different lifts, and it finds (in this case) the top 5 lifts and the names for the kids that have those corresponding lifts.
My problem is that when two or more kids have the same lift, it will only call up the name of the first instance of that lift. You can see this in the "Leaderboard" worksheet, and the name "Adams, Andrew" appears for both lifts of 75. I would appreciate any help on how to correct this, as I've searched the Internet for weeks now looking for a solution. I've tried experimenting with different things as well, all to no avail.
If I have a list of 6 values and a number that I want to get as near to as possible (above or below) using any, and as many as needed, of these 6 values - is there a way of calculating the nearest value? And, if there is, is there also a way of showing which values would be used?
Example: Value 1 = 0.190 Value 2 = 0.278 Value 3 = 0.593 Value 4 = 1.079 Value 5 = 2.075 Value 6 = 2.998
Value I want to get as near to as possible = 3.644
I now can open all the workbooks in a filefolder and look at each of the sheets in each file and build an array to consolidate a range from one sheet to another. The problem is that the one range is hardcoded in the script (because that is what I was using to test.) I have written code to build an array of 66 rows and 5 columns that are stored in a worksheet in a named range.
how arrays work so I can use both of these arrays - one array ConsolParams(), to identify the range in the Sources part of the consolidate statement. The other array MyArray() stores the sheet names. Actually ConsolParams also stores "to" range now hardcoded as "J11".
Option Explicit
Private Sub cbConsolidateToRollups_Click() Dim MyArray() As Variant Dim Source As Variant Dim sToPath As String Dim sRollUpWB As String Dim sRollUpToWs As String Dim sRollUpFromWS As String Dim sShtName As String Dim sSourceWS As String Dim nShts As Integer Dim lCount As Long Dim wbResults As Workbook Dim wbCodeBook As Workbook Dim WB As Workbook Dim i As Integer Dim r, c, p As Integer Dim ConsolParams(5) As Variant Dim CostType, RangeName, FromRange, TargetCell, TargetReport Application. ScreenUpdating = False Application.DisplayAlerts = False Application.EnableEvents = False.......................
In the above table I want to enter in another column or row, a number which is not available in the given table (i.e. no duplication is allowed). (This table may grow both column & row wise). If, duplication is there the message box should say that the value already exist. And, if possible, the existing value can be colored with any color, so that we can easily find out where it is.
1) how to name a offset table and 2) how to avoid duplicate value when entered? Is data validation is one solution?
I'm going to be using a spreadsheet to keep track of where different people are at. So if Person 1 is in Room 3, I will stick a 3 in the box next to their name and then can look at the spreadsheet whenever I need and see what room they are in. When I'm deciding what room to put a person in, though, I need to be able to quickly glance at a list of Room #'s and see what one's are still available. So I have a bank of Room #'s in the spreadsheet....1,2,3, etc.
What I'd like, is some way to set this up so that when I put, for example, "3" in the cell next to "Person 1" the spreadsheet automatically removes "3" from the bank of available Room #'s and when I delete the "3" because the person has left, it adds "3" back to the bank of available Rooms.
I have a mass of data which look something like this:
table removed
and I require the ranges of reference numbers to be listed in a column one above the other, which requires inserting new rows. I also need the date & description columns copied down into the newly inserted rows.
So basically for example I would want the top row to now read:
table removed
and then apply the same procedure to the other ranges below this.
I'd like to use the SMALL() function in excel to pull out the second lowest unique value in a list, but I'm not sure there is a way to do this. For example, if the array is {1,1,3,10,2,6}, then SMALL(array,2) returns 1, but I'd like it to return 2. Is there a way I can modify this function or use a different one to achieve what I want?
I have a cell, M87. The score in M87 can be less than 13 or greater than 25. I need a formula within M94 which refers to M87, and outputs depending on the the following criteria. If M87 is less than 13 then output as D. If M87 is 14, 15, 16, or 17 then output as C. If M87 is 18, 19, 20, 21, 22, 23 or 24 then output as B. If M87 is greater than 24 then output as A.