Variable Rows Per Column/ Automatic Combination Generation
Jun 19, 2007
The following table/ code is something which I've been trying to tailor from a previous post so I'm not taking the credit for what I think is some very good code. Unfortunately I can't find the link to it - sorry!
Right, I have a number of columns containing a various amount of data entries in each with the first row being the header. I would like to generate all possible combinations of this data in one column, the entries separated by commas, that will eventually be exported as a csv file.
The number of columns and number of rows in each column will be changed regularly ...
I have only two sheets..first sheet is the user sheet where the user gives the data...second is the sheet where in we get the result according to user given data..
there is a cell in User sheet where User gives a Number. for example " 8 "
so sheet2 : here we have only 9 rows. as user gave "8" the fisrt 8 cells in the first column should see like this
B 01/08 , B02/08 , B03/08 as so on till B08/08
when user gives 9 in user sheet
then the first sheet is finished with all the 9 rows ending with B09/08
when user gives 10...then sheet 3 should automatically appear with the first row and first colum saying B10/08.
when user gives 20 in user sheet...then sheet4 should appear with last number as B20/08
so each sheet is having only 9 rows..
first sheet ends with B09/08 second sheet ends with B18/08 third sheet ends with B27/08
What I have got is a list of exams and subjects on the first sheet from cells A3 to A whatever. This list varies in length from a few exams to many dozens.
Under each exam is a list of subjects. i.e.: Under the math exam is addition. multiplication, division etc
On columns B through ZZ and beyond. I have individuals names.
Under those individuals names (opposite the exam row) I have pull down boxes saying if the individual has passed failed or did not sit .. etc.
What I would like to be able to do is to automatically generate a second sheet with the individuals name on it with a report of what exams they took with what result. Of course it would be nice it was nicely formatted with the headings of all the different exams and the subjects taken arranged in order. If an exam was not taken it should not appear in the generated sheet
I have a fairly good knowledge of Visual Basic though I have not had much to do with VB for scripting.
The following code works very well up to a point. See previous thread Automatic Report Generation. As I use it to change my yearly summary sheet all at once. However as it stands I need to insert a seperate line of code for each month and cell that I need data from I will have over 2000 seperate lines when I am done. Also if the file does not exsist yet then the cell returns a #REF (error 2023). And none of the other formulas in spread sheet will work. Is there a way to have the cell return a zero if the file has not been created yet?
Is there a way to try and automate more of the code below? Cell references will be all over the place. All the months for the year will be needed.
But i want to save the number in a dim single variable called N. I try to put N where i have write ActiveSheet.Range("$B$2") and do N=Application.Run ... But it doesn't work.
Been trying to think of a way to get random numbers 1 to 36 generated without repeating in the same column, however also getting it to perform the same opertaion in 9 more columns (B:J) without the same number appearing in the same row.
example
1 2 5 4 2 5 3 1 3 4 1 5 4 1 2 3 5 3 4 2
Unsure if this is possible, have created a basic script for random number generation but have no clue how to expand across columns.
For example I have a data set that will grow over time say 13 rows, I want to write a formula that will only use the last 3 rows with data entered and get the Median of those rows. I know that the Median Formula for the entire 13 rows would be =MEDIAN(A3:A16). Anyone know a way to make a Median formula only look at the last 3 in any given time without manually updating the formula's range?
I would like to enter a vacation code in E5 (V12) and have it automatically enter "VACATION DAY (12 HRS * $15/HR) in K5 and calculate the amount in L5. Of course, there are variables involved here:
V indicates that it is a vacation day.
12 indicates the number of vacation hours to be taken.
The $15/hr comes from the value in I5.
I don't know if this should be handled as a VLOOKUP along with other methods or if this is something that needs to be handled in VB?
I'm currently trying to make a sort macro that sorts a row of data that has a combination of numerical data and the text entry "N.A."
e.g.:
My field: 1 2 N.A 4 5 N.A 9
I want to sort the cells by descending value. But the result appears as:
My field: N.A N.A 9 5 4 2 1
But I want to numbers to appear first and have the "N.A."s to be after the number, since I have about 100 columns and some rows have like 30 N.A., which is pretty bad for usability if all that appears in the first screen are "N.A."
EDIT: by the way by ascending the data is fine since it orders by smallest number to largest and THEN the "N.A."
I have data in a 5x5 area and I would like a VB Script or Function that can give every possible combination of one number from each column added together.
I have three columns that each have a drop down of text options; column A has three options, column B has three options and column C has two options. I want to have a fourth column that will show a text value dependent on the combination of the three columns.
It ends up being 18 variations so my IF formula just won't compute.
This is basically the format I have and works for a few values then just chucks up an error when I extend it to all 18 variations:
IF(AND(A1="text",B1="text2",C1="text3"),"show this text", IF(AND(B1="other",B1="other2",C1="other3"),"then show this", ) etc etc
Any way I can get this to work for all 18 possible outcomes??
On the attached Excel file, I have code that will insert a variable number of rows and copy and paste from and to variable positions. That all works fine when run from a command button, but when I try to run it from the Worksheet_Calculate by entering 1 in J1 or K1 (inrange cell is J1+K1 for testing purposes) the CommandButton1_Click sub runs continously until an error occurs.
to assign a variable to equal a Constant variable, then I need to find the last unused row on the worksheet, then paste that variable down the column (1-12200 or so rows). I also need to assign Strings for the first two Rows in the target column.
What I would like to do is do a summary sheet that does a little math. I can make it add but only one row at a time. Here is the general idea...
The fields of the summary sheet are 'PromoCode', 'AdvertisingCosts', 'Labor' for example. And I want to add the currency fields (add, as in math addition) of 'AdvertisingCosts' and 'Labor'.
The fields are on another sheet. What I am hoping can be done is that all those fields for every row can be made to work without having to hard code each one.
Also I would like to have it update with any NEW rows that arise. Like if the existing rows were 'row1' and 'row2' and then some other time 'row3' is added that 'row3' automatically gets included on the summary sheet.
The SHIFT column has the word DAY under it. The TIME IN column is empty. In the Shift column below if the value entered is "Day" I want the Time In column to say "1pm", If the value entered is "Night", I want the Time In column to say "7pm", I need a formula that will fill in the TIME IN column automatically based on whats on the SHIFT column.
I have a formula that I can't get to paste successfully in the forum - it keeps getting cut off?!? ... but I think I can probably simplify my explanation to get the answer I want anyway.
I need to only show the value from AUS!$H$2:$H$17 if the C2 & D2 combination are the same as the AUS!$B$2:AUS!$B$17 & $AUS!$C$2:AUS!$C$17 combination.
I want to create a row for each of the 52 weeks in a year. I want the weeks to begin and end on thursdays. Is there a way to automate this so that I do not have to maunally enter in each week? I need to do this for 2008 and 2009.
I have a very long list in columns A and B, about 1000 rows. I was just wondering how would i be able to setup an automatic formula that can produce the data in column D and E?
Ideally, i would the sequence running every 5 rows but iam doing this manually and running the sequence every 5 rows would be way to slow.
a way, without using a macro, to have a specific column automatically sort data being pulled in from another column, so that even if the data from the initial column gets changed, that the destination column will take that change into effect and update the sort automatically?
I want to create an automatic page break at each value change in Column A, and also a page footer that "restarts" at each change in the same column. For example, I would like a "Page x of y" footer, but I don't want "Page 1 of 3,500", I want "Page 1 of 3", etc...every time that Column A value changes.
How can I create a Makro that looks for values (e.g. values > 90) and that then generates a list with: all the values greater than 90, the row names and column names of the values?
I am pretty sure I will need a Makro for this, but how to create it. I am using excel 2010.
I have attached a file to give you an example. The red table is the table with the values. The blue table is what I want to be generated automatically. In the example I did it manually.
I have multiple users that use a spreadsheet to enter data. I am trying to figure a way to assign a case number to each row of data that is entered. I cannot really do sequential numbers, because often people are entering data at the same time, and often these people are working offline.
So I was thinking if I could somehow have the case number be a few letters (to identify a specific person) and then a series of numbers (to signify which case number it is). That would allow multiple users to enter data offline simultaneously without having to worry that the case numbers are going to be the same. Maybe I could use the Application.UserName and somehow parse it to give me the letters (perhaps Intials or something), and then somehow apply sequential numbers to that...
I have an excel file with many worksheets. I want to get a pop up message providing definitions of different subjects when they are entered from a drop down list in a specific range within a column to improve the users understanding of the subjects.
Worksheet 1 Range (where I want the pop up message to be valid): J85:J385 Subjects from drop down list in specified range: "x,y,z" Pop up message: "Definitions of x,y,z"
Do I have to make a new module, or write the macro in the selected worksheet? What should be the settings of the macro (general, worksheet, declarations etc.)
I m trying to achieve is generating ref numbers automatically.
I have attached an example.
In Cell A if the user choose yes it gives a ref number. The trouble im having is that say in cell A5 a Yes is choosen at a later date, it will throw the numbers out of order and the ref numbers get mixed up, there any way of stopping this from happening or any function that prevents this from happening
I have multiple files that I need to get data from. My dream is to push a button and gather the data from variable files. (I know how to make the button and get data from single file).
The file name is below:
Cell A1 contains this formula ='C:ReportsYear 2009[CityWaterReports Jan 2009.xls]PHFlows'!$B$44
Cell A2 contains this formula ='C:ReportsYear 2009[CityWaterReports Feb 2009.xls]PHFlows'!$B$44 and so on.
I would like the customer to be able to push the button and be asked to enter the year(eg: 2008) and have the formula update to the new year in each cell.
