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.
View 9 RepliesI 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
Turn 6 random numbers in 2 columns in to 1 column of these numbers from small to big.with a function so i could move it.
View 9 Replies View RelatedHow can I avoid letters in a cell (read only numbers)
I have a formula that converts the interval between two times into a whole number of minutes:
View 3 Replies View RelatedI 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?
View 9 Replies View RelatedI 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
View 1 Replies View RelatedI 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.
View 3 Replies View RelatedI 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.
In my worksheet in column CC in C1:C20 I want Excel to ganerate 20 random numbers between 1 and 80 without repeating the same numbers.
At present I have the following formula entered and it works, but after I click F9 some repeated numbers are generated.
=INT(RAND()*(80-1)+1)
How can I change the formula or replace it?
I have repeating 7 names in A column.(Like a name group)
Each group names repeating after a blank(empty) row.
I want to write to B column for each repeating names from 1 to 7 as number value.
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
Day Hour Position
1 2 Z5
1 2 Z5
1 3 Z5
1 3 Z4
1 16 Z1
12 2 Z3
13 0 Z3
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.
I need a formula that will add .1, .2, .3, etc. to a repeating list number. See the example below.
Project Work Id
8069000
8069000
8069000
8069000.1
[Code]....
How to get the small value excluding zeros?
small(Convention!$I$3:$I$1000,rows(Dashboard!$D$41:$D41))
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
Ok, I want to use a dynamic range in my Vlookup formula.
I want to use this formula: ....
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.......................
I have a range (offset based) which may increase by rows or columns whenever necessary.
In that I have data table like this:
HUNONETWOTHREEFOURFIVESIX
100110021003100410051006100
200120022003200420052006200
300180028003800480058006800
400140024003400440054006400
500150025003500450055006500
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.
following issue:
The following table is given:
flower
20
rose
flower
21
rose
[Code] ........
Which needs to be turned into:
flower
20
22
rose
flower
31
32
blossom
tree
1
3
apple
The last column is the one that dictates when a new range of numbers start. There should be one range of numbers for Rose, One range for Blossom etc.
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?
View 4 Replies View RelatedI have the G column listed with number from 1-100. On the B column I have the corresponde titles for each number in G.
What I want to do is =Look at the G(1:100) take the top 10 lowest value and write the correspondant title nearby.
I think it s possible with Min or Small functions, but I dont know how to, especially taking the title names nearby.
is there any way of using the =SMALL function to rank only numbers above zero so that the zeros don't keep showing up as the smallest figures?
View 2 Replies View RelatedCriteria:
U4= 12/01/2013
Data:
(A) (B) (C)
01/01/2013 02/01/2013 12/01/2013
1 3 4
2 2 1
4 1 1
Result:
Small - looking for smallest to fifth smallest (separate cells B5=1, B6=2 etc.)
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.
View 3 Replies View Related