Macro To Fill Cells Of Rows Based On Two Other Columns
May 28, 2014
Data file with few columns. There are groups of similar ID numbers in Column J. For a group of similar ID numbers in consecutive rows there is only one row that has a number greater than 0 in its Column L cell and the rest of the cells of Column L for that set of similar IDs is filled with 0s.
First for that unique ID group I need to find out which row is it that has a value greater than zero in its Column L cell.
Then I need to use that value to fill the rest of the 0s in Column L corresponding to that set of Unique IDs.
The process continues with identifying similar IDs in Column J and this time doing the same thing for their Column M. I have attached a sample file that shows the data and how the results need to look like.
See here Fill Cells.xlsx
View 1 Replies
ADVERTISEMENT
Jan 31, 2014
I am a locksmith and attempting to write a spreadsheet app. for my line of work. The output page will use columns A-G and rows 1-?10000? depending on the size of the system. If a 5 pin system is requested then columns A+B will be blacked out. 6 pin system A is blacked out. 7 pin system nothing blacked out. Also, if any cell matches the master key that was input, that cell is filled with red. I know, confusing.
Basically, I am trying to remove the blacked out columns automatically, and to remove the enitire row is any cell in that row is filled with red. Then to remove blank lines.
View 2 Replies
View Related
May 28, 2009
I'm running a macro that opens another workbook and read data from it.How can I incorporate this code into my macro.Sorry i don't knwo VBA.
Workbooks.Open Filename:="C:Documents and SettingsmsimantbDesktopINFRACHEM_POLYMERS - DON''T DELETE.xls]Sheet1"
UserGRP_MAcro Macro
Rows("1:3").Select
Selection.Delete Shift:=xlUp
Columns("A:B").Select
Selection.Delete Shift:=xlToLeft
Columns("B:E").Select
Selection.Delete Shift:=xlToLeft
Columns("A:A").EntireColumn.AutoFit
Rows("2:2").Select
Selection.Delete Shift:=xlUp
Range("B1").Select
ActiveCell.FormulaR1C1 = "Existing userGroup"............................
View 2 Replies
View Related
Feb 26, 2008
Suppose in cell A1 I type in the number "100" and in A2 I type in the number "5"
What I want to do: Have the number "100" repeat 5 times in a vertical list (so 100 appears in B1, B2, B3, B4, B5)
View 2 Replies
View Related
Jun 30, 2006
way to automatically hide a row or column based on a specified cell.
Or even something general like: Hide all rows with cells evaluating to #n/a.
View 9 Replies
View Related
Sep 22, 2012
Why does my macro mess up on second line C23 and not places Play Equipment or the amount in the right column it stays on row C22
[URL]....
Code:
Private Sub CommandButton1_Click()
Dim SheetName As String
SheetName = "Estimate1"
SheetName = InputBox("enter the name of a sheet to use", "sheet name", SheetName)
[Code]....
View 2 Replies
View Related
Jul 10, 2008
I have two columns of data as follows:
10:57:42 273
10:57:42 263
10:57:42 253
10:57:42 241
10:57:37 273
10:57:37 243
10:57:37 249
10:57:37 261
10:57:37 253
11:04:47 241
11:04:47 253
11:04:47 263
10:54:31 254
10:54:31 240
10:54:31 265.......
View 9 Replies
View Related
Sep 4, 2013
I created a macro that analyzes some datasets and according to different parameters it puts an X at the side of every cell that goes out of parameters. These Xs are all in columns named the same way for it to be easier to find. What I want to do is to write some macro that will hide every row where the data did not go out of parameters to be able to easily see where it did. In the following example it would need to hide the second row since there are no Xs in that row. One of the problems I'm having is that the amount of columns and rows is variable.
Data
Data
Functional Upset
Data
Data
Functional Upset
#
#
X
#
#
[Code] .......
Below is the piece of code that I created to try to do this (G has the value for the amount of rows). I think it is working but it is either taking too long since it has to sometimes go through over 20k lines as much times as it needs to to cover all the rows that contain Xs or its getting stuck for some reason.
Code:
Worksheets(2).Range("I3:I" & G).EntireRow.Hidden = True
K = 0
Do Until Worksheets(2).Range("I1").Offset(0, K) = ""
If Worksheets(2).Range("I1").Offset(0, K) = "Functional Upset" Then
[Code] ......
View 6 Replies
View Related
Apr 30, 2014
I have a spreadsheet with 10 columns which are for tags for products. Some columns are filled in for 10 products and some are not. For instance, product 1 has 7 tags while product 2 has only 2 tags and so on. Now what I am looking to do is combine all the tags for each product with a "," inbetween each one. The problem is if I just use a simple =A2&","&B2&","&C2 etc formula is that if there are only 2 tags then I get 8 ","s. I also tried it with an IF statement but by the time I was done nesting items I became cross eyed and said there has to be an easier way to do this.
I have create a spreadsheet with the data and the result that I typed into the "result" column.
View 3 Replies
View Related
Mar 18, 2008
I have 4 columns. I want to add 100 to all rows in the first column, 150 to the ones in the second, etc. Is this possible? I have Excel 2007.
View 9 Replies
View Related
Apr 15, 2014
I have a pivot table full of data that needs a macro/vba/something to loop through it and hide all rows that have no fill color. The table had 210 rows so I want a quick way of hiding the irrelevant ones (only the ones filled in green or red), and so all the ones with no fill need to be hidden. Something like a button to hide/unhode would be ideal.
View 4 Replies
View Related
May 21, 2014
I want to write a macro to fill color in row 8 of the worksheet as solid black . Also columns P,T,AP,BC ,BE and BG should be filled solid black and width should be 1 for the row and the column.
View 7 Replies
View Related
Sep 18, 2012
I have a table = Columns A - E contain data in a variety of formats
Code1
Code2
Code3
Code 4
Code 5
Code6
dog
9
12/1/1987
hamster
dog,9,,12/1/1987,hamster
What Column F (code 6) should contain is a csv of Columns A-E as shown.
What formula for Column F will automatically fill the values for all data within a row in a csv?
View 6 Replies
View Related
Dec 26, 2009
I want a code to Hide the row if "sum of numbers in columns three thru last column" = 0. Means, if the sum of all the cell (except first and second cells in that row) in a row is zero, then that row should be hidden.
View 9 Replies
View Related
Jul 21, 2014
I want to fill an array from values in range A1:H10. I want to fill the array with all rows in range and only columns B,C and E. I have the code below so far using index function.
Is there a more direct way to select all rows from desired range to avoid the need to create an array of rows from 1 to LastRow and then use Application.Transpose(RowsArr) (in red) inside Index()?
View 3 Replies
View Related
Dec 20, 2007
I am trying to fill in reference cells horizontally but fix the column and increasing the row number as it fills. For examples I want to start my refence as "=A1" in cell B1. I want to copy this reference horizontally to column Z1 but want the refences to be A1, A2,...,A26. Excel only seems to increase columns when filling horizontally and rows when filling vertically. Transpose works but it's an array which would slow the spreadsheet down.
View 4 Replies
View Related
Sep 26, 2013
I need a macro that will combine/consolidate rows when cells from 2 separate columns match. example...
beginning:
Column A column B column C column D column E
row 1 Seminole 80 unleaded 1064 100100
row 2 Seminole 36 clear dsl 825 100100
row 3 Seminole 80 unleaded 1101 100100
row 4 Seminole 30 dyed dsl 3421 100100
This is what I need the macro to do:
Column A column B column C column D column E
row 1 Seminole 80 unleaded 2165 100100
row 2 Seminole 36 clear dsl 825 100100
row 3 Seminole 30 dyed dsl 3421 100100
View 2 Replies
View Related
Oct 12, 2011
I have a worksheet with 40 colums and 9200 rows. There are columns with empty data in the rows. Can I have a macro to copy data in the rows in Column E, F, I, J K and L to empty rows below?
View 3 Replies
View Related
Aug 21, 2008
i found a link to my problem in the subject title below,
http://www.ozgrid.com/forum/showthre...t=45580&page=2
The guy at the end of the post says he got it working in the end, but no one seems to have posted the final solution - could someone tell me if the solution is actually in the thread, and if so where!
My problem is outlined by this guy - basically i want to be able to delete rows in a range that have no fill color, and so keep the ones that are colored.
View 9 Replies
View Related
Feb 24, 2014
I wanted to enter value in sheet1 on A1. Columns in sheet2 from B1 until the value entered in sheet1.A1 should be automatically filled.
E.g.
Sheet1.A1=5, then Sheet2.B1=1, Sheet2.B2=2, Sheet2.B3=3, Sheet2.B4=4, Sheet2.B5=5.
if Sheet1.A1=3, then Sheet2.B1=1, Sheet2.B2=2, Sheet2.B3=3.
if Sheet1.A1=7, then Sheet2.B1=1, Sheet2.B2=2, Sheet2.B3=3, Sheet2.B4=4, Sheet2.B5=5, Sheet2.B5=6, Sheet2.B5=7
etc.
View 2 Replies
View Related
Mar 22, 2007
Is there a way to color fill an entire row based on a value in a certain column? Say I have a large file and one column is "yes" or "no" If I wanted all of the "yes" rows to be colored -
View 5 Replies
View Related
Aug 21, 2014
I have a simple spreadsheet. A column for a persons name and 31 columns, one for each day of the month. I want to apply conditional formatting, either fill color or border color, to the date column of the current date when the spreadsheet is opened.
View 2 Replies
View Related
Jan 27, 2005
how can I automatically fill cells with certain colors based on the value of the cell. (i.e. I want to search an entire workbook and fill cells with values between 80 and 99 green, 60 to 79 yellow and 0 to 59 red.)
View 9 Replies
View Related
Jul 1, 2008
I am working on a spreadsheet for a shoe company. I have separate columns for the size, model, color, and item number of a shoe. I get everything except for the item number from a written document; I then have to find the item number for the shoe from another excell document called the Master List.
I was hoping there would be a way to have Excell auto-fill the item number for me. For example, if a shoe is a Red, Athens (the shoe model),size 12, its item number (which can be a pain to find) listed in the row of the Master List is aaabbb. So I want to just enter in the size, color and model number, and have Excell find the item number for me, and fill it in.
I have enclosed an example. Sheet 1 is the sheet I would be working on. Sheet 2 is a portion of the Item master list, which is actually 50k lines.
View 8 Replies
View Related
Feb 19, 2014
I have a control chart macro which is being used for patient doses of a medication. Right now my macro automatically fills columns D-N down to row 100. However, I would like it to stop at the last row of data entered in column A. I have attached the code related to the formulae a
J
Sub Control_Chart_1()
'
' Control_Chart_1 Macro
[Code]....
View 1 Replies
View Related
Jun 9, 2014
I have two sheets:
- sheet 1 ('current')
- sheet 2 ('31May')
The data in column C is the same, although sorted differently and can't be re-sorted to be the same. Where the data in column C is the same in both sheets, I want to fill columns A and B into sheet 1.
View 4 Replies
View Related
Oct 21, 2013
My merged cells are not purely aesthetical - each merged cell (covering 6 rows in total, beginning at A3:A8) has colums and rows to the right for other details/formulae relating to that specific merged cell (which contains a customer account name).
The list I'm trying to reference is on a seperate tab - 'Projects' starting in cell A4. This list will have project names added to it, so I need my merged cells to reference the list, and subsequently pulling through all new additions to the list, rather than having to add them on a 2nd time.
View 8 Replies
View Related
Jun 25, 2007
How do I have the marco delete rows if the O column = a certain cell.
For instance my O column has a bunch of colors
Cell N1 list one color (red) and I want the macro to delete the rows that contain the color from N1 in column "O".
View 9 Replies
View Related
Dec 15, 2009
I have a set of data in column R, with an unknown number of rows that looks like this
Days Late
-28
150
3
16
41
.
.
.
and I have written a script to add an adjacent column "S". I want to fill column S with conditional values based on the value of column R, sorted into categories such as "On Time", "Less than One Month Late", etc. Here is what I have so far, it doesn't work:
View 4 Replies
View Related
Jun 24, 2009
I would like to automatically color fill a series of rows based on like information from a single column. When the information in the column changes then the rows would either stop filling (this would be best) or fill with a different color until the information changes again.
Summary: series of alternating rows would either be filled or unfilled based on changes from the column information.
Hope this isn't too confusing. Below is an example except I would want the cells filled, not the text or numbers to change. The highlight is changing based on the changing of the numbers.
ABCD 12345
ABD 12345
ABCD 12349
ABDF 12349
ABCD 12358
ABF 12358
View 9 Replies
View Related
