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 ...

View 9 Replies


Automatic Sheet Generation

Nov 3, 2008

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

View 13 Replies View Related

Automatic Generation Of A Report Sheet

Jun 14, 2007

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.

View 11 Replies View Related

Followup On Automatic Report Generation

Jun 24, 2009

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.

View 6 Replies View Related

Automatic Report Generation From Excel Worksheet

Apr 24, 2014

want to generate automatic report from excel work sheet

View 1 Replies View Related

Pass Random Generation In Cell To Variable

Jan 1, 2008

I want to generate a random number based in a poisson and i use this

Application.Run "ATPVBAEN.XLA!Random", ActiveSheet. Range("$B$2"), 1, 1, 5 , , 35

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.

View 5 Replies View Related

Random Numbers Generation In Column

Oct 24, 2012

The code below generates numbers in the range (A2: H2)

View 4 Replies View Related

Random Number Generation Without Repeating Across Same Row Or Column?

Nov 24, 2011

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.


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.

View 9 Replies View Related

Formula Only Look At Last 3 Values In The Column? (variable Rows)

Nov 14, 2008

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?

View 3 Replies View Related

Automatic Data Based On A Variable

Jan 3, 2008

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?

View 9 Replies View Related

Sorting Rows With A Combination Of Text And Number?

Aug 3, 2013

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."


My field:

I want to sort the cells by descending value. But the result appears as:

My field:

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."

View 5 Replies View Related

5 Column Combination?

Nov 13, 2013

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.


500 400 300 200 100 1500
400 300 200 100 500 1900
300 200 100 500 400 1800
200 100 500 400 300 etc...
100 500 400 300 200

View 5 Replies View Related

IF Formula - Column To Show Text Value Dependent On Combination Of Three Columns

May 12, 2014

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??

View 6 Replies View Related

Insert A Variable Number Of Rows And Copy And Paste From And To Variable Positions

Aug 8, 2009

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.

View 4 Replies View Related

VBA Code For Automatic Deletion Of Rows

Apr 1, 2009

My data is in the following format,

Company Name Bill Status

I need a vba code if the in Bill status column rejected is there all those rows to be deleted.

View 9 Replies View Related

Assigning A Variable And Pasting Variable To Last Unused Column

Nov 19, 2008

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.

View 14 Replies View Related

Automatic Adding And Subtraction Entire Rows

Jan 23, 2009

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.

View 14 Replies View Related

Autofill With Range That Is Column Variable And Row Variable

Apr 4, 2008

I am trying to autofill dynamic ranges that have column variables (d) and row variables (x)... I am having a hard time with the syntax on this

View 9 Replies View Related

Automatic Formula For 1 Column

Dec 29, 2008

I have two colums as shown below:.....

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.

View 4 Replies View Related

Only Display Value If Combination In Two Columns Matches Combination In Two Other Columns

Jul 12, 2012

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.

View 9 Replies View Related

Create A Week Column - Automatic?

Jul 22, 2008

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.

For example

7/31/08 - 8/7/08
8/7/08 - 8/14/08
8/14/08 - 8/21/08

View 4 Replies View Related

Automatic Formula That Can Produce Data In Column D And E?

Jan 12, 2012

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.

View 7 Replies View Related

Automatic Column Data Sort Without A Macro

Mar 6, 2008

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?

View 9 Replies View Related

Create An Automatic Page Break At Each Value Change In Column A

Mar 18, 2009

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.

View 9 Replies View Related

Excel 2010 :: Automatic Generate List - Row / Column And Values

Jul 8, 2013

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.


View 2 Replies View Related

Unique No Generation

Apr 5, 2007

my requirement is attached here with.
in generating the unique no?

View 14 Replies View Related

Number Generation

Apr 26, 2007

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...

for instance, JJH00001, JJH00002, etc

View 9 Replies View Related

How To Get Automatic Popup Message When Cell Text Is Entered To Column Range

Jul 10, 2013

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.)

View 7 Replies View Related

Automated Ref Number Generation

Oct 7, 2009

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

View 14 Replies View Related

Automate Report Generation

Jun 17, 2009

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.

View 4 Replies View Related

Copyrights 2005-15, All rights reserved