Simple Macro Placing Value In Cell Based On Values In Other Cells 


Simple Macro Placing Value In Cell Based On Values In Other Cells  Excel 
View Answers 
I have a list of data in four columns (multiple rows), e.g.,
1 2 3 4
I inserted 2 additional columns after the data to categorize the data according to specific customer feedback, e.g.,
5 6
I am trying to create a macro that takes the original 4 columns of data, copies it into a second worksheet, inserts a column between column 1 and 2 (lets call it column 1.2 for now), and fills column 1.2 in based on the data in column 5 and 6. What I mean by the "filling in column 1.2" is that I would like for the macro to see what the value is in the first row in column 5 and column 6, go to a specific area on a third worksheet based on the combined information in column 5 and 6, row 1, see what the value is in that specific location, and put that value in row 1 of column 1.2 in worksheet 2
I would like the process to repeat as long as there is a data value (other than 0) in column 1. Can anyone help?
1 2 3 4
I inserted 2 additional columns after the data to categorize the data according to specific customer feedback, e.g.,
5 6
I am trying to create a macro that takes the original 4 columns of data, copies it into a second worksheet, inserts a column between column 1 and 2 (lets call it column 1.2 for now), and fills column 1.2 in based on the data in column 5 and 6. What I mean by the "filling in column 1.2" is that I would like for the macro to see what the value is in the first row in column 5 and column 6, go to a specific area on a third worksheet based on the combined information in column 5 and 6, row 1, see what the value is in that specific location, and put that value in row 1 of column 1.2 in worksheet 2
I would like the process to repeat as long as there is a data value (other than 0) in column 1. Can anyone help?
Similar Excel Video Tutorials
Excel 4 GET.CELL Macro Function
 See how to count cells with a custom number format using the Excel 4 GET.CELL Macro Function. ...
Recorded Macro Basics
 Learn about:
1.How to Record a Macro
2.Macro = VBA code
3.Macros are great for repetitive tasks
4.What file extension to use for E ...
1.How to Record a Macro
2.Macro = VBA code
3.Macros are great for repetitive tasks
4.What file extension to use for E ...
Recorded Macro And Formulas To ReArrange Data Set
 See how to create a Recorded Macro that uses formulas to rearrange a proper data set to one listed in a column. Learn about:
1. Relative Referenc ...
1. Relative Referenc ...
VLOOKUP Return Two Values to One Cell or Two Cells
 See how to lookup two values at once and return then both to:
1. One Cell using 2 VLOOKUP functions and concatenation (Ampersand &)
2. T ...
1. One Cell using 2 VLOOKUP functions and concatenation (Ampersand &)
2. T ...
Helpful Excel Macros
Reverse Row or Column Order in a Worksheet
 This macro reverses the order of a selected row or column in excel. It will completely reverse the data.
 This macro reverses the order of a selected row or column in excel. It will completely reverse the data.
Filter Data Sets on Multiple Columns with Multiple Criteria at Once in Excel  AutoFilter
 This Excel macro allows you to filter a data set on multiple columns and criteria at once. This means that you can filt
 This Excel macro allows you to filter a data set on multiple columns and criteria at once. This means that you can filt
Vlookup Macro to Return All Matching Results and Stack them with Previous Results
 This is very similar to the other Vlookup type Macro in that it returns all of the results that match a particular se
 This is very similar to the other Vlookup type Macro in that it returns all of the results that match a particular se
Delete Entire Rows Based on Predefined Criteria (Text)
 This macro will allow you to specify certain criteria and then to delete rows based upon that criteria. You will choose
 This macro will allow you to specify certain criteria and then to delete rows based upon that criteria. You will choose
Sort Data that Doesn't Have Headers in Ascending Order in Excel
 Sort data that doesn't have headers in ascending order in Excel with this macro. This is a simple sort macro that assum
 Sort data that doesn't have headers in ascending order in Excel with this macro. This is a simple sort macro that assum
Similar Topics
Hello,
I am working with two worksheets, I will call A and B. Worksheet A contains stationary column labels with specific formatting. Worksheet B is an Excel table which contains columns of data of which the column label order and number of rows can vary each time the table is updated based on the pivot critera selected by the user.
I would like to write a macro which will find a specified column label, we can call "Area" in worksheet B, move one cell down and copy all the data below the label, then past only the values (not formatting) into Worksheet A, just under a label with the same name "Area". As I mentioned earlier the location of the column in Worksheet A will remain consistent so I will have an exact cell reference to paste to.
Thanks ahead of time for any advice...greatly appreciated!
I am working with two worksheets, I will call A and B. Worksheet A contains stationary column labels with specific formatting. Worksheet B is an Excel table which contains columns of data of which the column label order and number of rows can vary each time the table is updated based on the pivot critera selected by the user.
I would like to write a macro which will find a specified column label, we can call "Area" in worksheet B, move one cell down and copy all the data below the label, then past only the values (not formatting) into Worksheet A, just under a label with the same name "Area". As I mentioned earlier the location of the column in Worksheet A will remain consistent so I will have an exact cell reference to paste to.
Thanks ahead of time for any advice...greatly appreciated!
Hope you can help
Have a spreadsheet with 2 worksheets. In worksheet 1, I have 4 columnsColumns 1 & 3 contain city names and Columns 2 & 4 contain numbers corresponding with the city in in either Column 1 or Column 3 (1 with 2; 3 with 4). All columns have equal amounts of data (all the same cell length). Although the columns remain the same, each week, the location of the data (rows) and the actual data in each column changes.
In worksheet 2, I have a listing of the cities found in Worksheet 1 all in one column. In the column next to the city names, I want to bring over the number data corresponding with the specific city name.
My thought was to use a combined =if and =vlookup formula to arrive at the correct number being connected with the city name in Worksheet 2. However, I am not clear on the formula syntaxwhat I want the formula in Worksheet 2 to indicate is: If the city name is found in Worksheet 1, Column 1, give me the corresponding number from Column 2, otherwise, if the city name is found in Column 3, give me the corresponding number in Column 4.
As I mentioned, the Column 1 & Column 3 data in Worksheet 1 will change weekly (both rows and what cities are found in each), so I am looking for a universal formula.
Thanks for your help
Have a spreadsheet with 2 worksheets. In worksheet 1, I have 4 columnsColumns 1 & 3 contain city names and Columns 2 & 4 contain numbers corresponding with the city in in either Column 1 or Column 3 (1 with 2; 3 with 4). All columns have equal amounts of data (all the same cell length). Although the columns remain the same, each week, the location of the data (rows) and the actual data in each column changes.
In worksheet 2, I have a listing of the cities found in Worksheet 1 all in one column. In the column next to the city names, I want to bring over the number data corresponding with the specific city name.
My thought was to use a combined =if and =vlookup formula to arrive at the correct number being connected with the city name in Worksheet 2. However, I am not clear on the formula syntaxwhat I want the formula in Worksheet 2 to indicate is: If the city name is found in Worksheet 1, Column 1, give me the corresponding number from Column 2, otherwise, if the city name is found in Column 3, give me the corresponding number in Column 4.
As I mentioned, the Column 1 & Column 3 data in Worksheet 1 will change weekly (both rows and what cities are found in each), so I am looking for a universal formula.
Thanks for your help
I realize that similar questions have been asked all the time, but I can't get it sorted and adjusted to work properly.
I have:
a worksheet 'x' with multiple columns (variable) with multiple rows (variable as well) different columns have different row lengths there are no blanks in the columns I need:
a macro that selects all columns with all values copies them (not move!) to a predefined column where A and its values (A1,A2,A3...) are followed by B and the values (B1,B2,B3,B4...), followed by C... right untill the last column with values in it Example:
Worksheet 1:
Column A:
value a1 value a2 value a3 value a4 Column B:
value b1 value b2 Column C:
value c1 value c2 value c3 value c4 value c5 Worksheet with result:
value a1 value a2 value a3 value a4 value b1 value b2 value c1 value c2 value c3 value c4 value c5
I have:
a worksheet 'x' with multiple columns (variable) with multiple rows (variable as well) different columns have different row lengths there are no blanks in the columns I need:
a macro that selects all columns with all values copies them (not move!) to a predefined column where A and its values (A1,A2,A3...) are followed by B and the values (B1,B2,B3,B4...), followed by C... right untill the last column with values in it Example:
Worksheet 1:
Column A:
value a1 value a2 value a3 value a4 Column B:
value b1 value b2 Column C:
value c1 value c2 value c3 value c4 value c5 Worksheet with result:
value a1 value a2 value a3 value a4 value b1 value b2 value c1 value c2 value c3 value c4 value c5
Hi,
I really need someone to assist me with this if possible.
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
10:54:31 263
10:54:31 258
11:07:38 273
11:07:38 261
11:07:38 241
11:07:38 253
11:12:41 252
11:12:41 242
11:12:41 244
I need to transpose the second column into a row with the time grouped together in the same row so only a single time is shown per row.
Example:
10:57:42 241 253 263 273
10:57:37 243 249 253 261 273
11:04:47 241 253 263
10:54:31 240 254 258 263 265
11:07:38 241 253 261 273
11:12:41 242 244 252
The other issue is that I need these number put into 6 columns where each column houses a specific range:
TIME : Time
Column 1: 240243
Column 2: 244251
Column 3: 252255
Column 4: 256259
Column 5: 260263
Column 6: 264273
Example:
Time
Column 1
Column 2
Column 3
Column 4
Column 5
Column 6
10:57:42
241
253
263
273
10:57:37
243
249
253
261
273
11:04:47
241
253
263
10:54:31
240
254
258
263
265
11:07:38
241
253
261
273
11:12:41
242
244
252
Thanks,
Cameron
I really need someone to assist me with this if possible.
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
10:54:31 263
10:54:31 258
11:07:38 273
11:07:38 261
11:07:38 241
11:07:38 253
11:12:41 252
11:12:41 242
11:12:41 244
I need to transpose the second column into a row with the time grouped together in the same row so only a single time is shown per row.
Example:
10:57:42 241 253 263 273
10:57:37 243 249 253 261 273
11:04:47 241 253 263
10:54:31 240 254 258 263 265
11:07:38 241 253 261 273
11:12:41 242 244 252
The other issue is that I need these number put into 6 columns where each column houses a specific range:
TIME : Time
Column 1: 240243
Column 2: 244251
Column 3: 252255
Column 4: 256259
Column 5: 260263
Column 6: 264273
Example:
Time
Column 1
Column 2
Column 3
Column 4
Column 5
Column 6
10:57:42
241
253
263
273
10:57:37
243
249
253
261
273
11:04:47
241
253
263
10:54:31
240
254
258
263
265
11:07:38
241
253
261
273
11:12:41
242
244
252
Thanks,
Cameron
Hey guys,
Hope you can help here. Suppose I've got a table:
Column 1 column 2
x.................2
x.................8
x.................0
y.................3
y.................1
y.................4
This is obviously much longer and many more values in column 1. It is an export from a database. I'd like to find a macro that will create a worksheet for each group of items in column 1, copy the rows for each of those values and paste them in the new worksheet named the same as the grouped value in column 1. So worksheet 1 would contain 3 rows (from above) based on the value "x" and that worksheet would be named "x".
Is there a way to do this?
Thanks!!
J.
Hope you can help here. Suppose I've got a table:
Column 1 column 2
x.................2
x.................8
x.................0
y.................3
y.................1
y.................4
This is obviously much longer and many more values in column 1. It is an export from a database. I'd like to find a macro that will create a worksheet for each group of items in column 1, copy the rows for each of those values and paste them in the new worksheet named the same as the grouped value in column 1. So worksheet 1 would contain 3 rows (from above) based on the value "x" and that worksheet would be named "x".
Is there a way to do this?
Thanks!!
J.
I am a novice when it comes to making macros so I am in desperate need of help. I have two worksheets, one with data that will be updated with new data everyday and the other, a template that the data needs to fill in by each row accordingly. However the template needs to be a copy of the unique name from column A and renamed by that name. If the template worksheet already exist it will take the information from that matching name and copy certain information to the template. If there is no worksheet(template) existent then it will create a new worksheet according to that unique value from column "A".
The data in worksheet(data) will first need to be filtered by two types, "Redemption" and "Full Liquidation" from column "I" before it can start transferring data.
From worksheet(data) to worksheet(template),
column "A" will be the new worksheet(template) name,
column "E" will be put in Worksheet(template) column "A" starting with row 4,
column "F" will be in column "B",
column "B" will be in column "C",
column "O" will be in column "D",
column "L" will be in column "T".
So far I have this as a Macro, but it does not copy the template and it takes the whole row instead of the specific columns that I need.
Please Login or Register to view this content.
Please help I'm in desperate need of some guidance
I have 80 columns of raw data with static headers that are produced out of an ERP system, however the report run is not consistent in placing the data in consistent column letters. I am looking for a solution to autocopy the raw data into a formatted worksheet that can identify the data based on the sheet name and column header, similar to Paste Link where a column would populate, but only based on the Column Header name alone.
In a beautiful world it would look something like "='Raw Data'!"Data Column Header".
I'm a total Excel idiot so if you can recommend a macro, please be gentle.
Thanks so much in advance!
Todd
In a beautiful world it would look something like "='Raw Data'!"Data Column Header".
I'm a total Excel idiot so if you can recommend a macro, please be gentle.
Thanks so much in advance!
Todd
Hope you can help
Have a spreadsheet with 2 worksheets. In worksheet 1, I have 4 columnsColumns 1 & 3 contain city names and Columns 2 & 4 contain numbers corresponding with the city in in either Column 1 or Column 3 (1 with 2; 3 with 4). All columns have equal amounts of data (all the same cell length). Although the columns remain the same, each week, the location of the data (rows) and the actual data in each column changes.
In worksheet 2, I have a listing of the cities found in Worksheet 1 all in one column. In the column next to the city names, I want to bring over the number data corresponding with the specific city name.
My thought was to use a combined =if and =vlookup formula to arrive at the correct number being connected with the city name in Worksheet 2. However, I am not clear on the formula syntaxwhat I want the formula in Worksheet 2 to indicate is: If the city name is found in Worksheet 1, Column 1, give me the corresponding number from Column 2, otherwise, if the city name is found in Column 3, give me the corresponding number in Column 4.
As I mentioned, the Column 1 & Column 3 data in Worksheet 1 will change weekly (both rows and what cities are found in each), so I am looking for a universal formula.
Thanks for your help
Have a spreadsheet with 2 worksheets. In worksheet 1, I have 4 columnsColumns 1 & 3 contain city names and Columns 2 & 4 contain numbers corresponding with the city in in either Column 1 or Column 3 (1 with 2; 3 with 4). All columns have equal amounts of data (all the same cell length). Although the columns remain the same, each week, the location of the data (rows) and the actual data in each column changes.
In worksheet 2, I have a listing of the cities found in Worksheet 1 all in one column. In the column next to the city names, I want to bring over the number data corresponding with the specific city name.
My thought was to use a combined =if and =vlookup formula to arrive at the correct number being connected with the city name in Worksheet 2. However, I am not clear on the formula syntaxwhat I want the formula in Worksheet 2 to indicate is: If the city name is found in Worksheet 1, Column 1, give me the corresponding number from Column 2, otherwise, if the city name is found in Column 3, give me the corresponding number in Column 4.
As I mentioned, the Column 1 & Column 3 data in Worksheet 1 will change weekly (both rows and what cities are found in each), so I am looking for a universal formula.
Thanks for your help
I have one column of data that is the date of a specific event. Multiple events may occur on the same date and no events may occur on a given date. Here's a simple example (Column B is arbitrary):
Column A: Column B: Column C: Column D: Column E:
1/16/2010 5
1/16/2010 3
1/16/2010 8
1/17/2010 2
1/20/2010 3
1/29/2010 9
Columns A and B are entered manually based on some occurrence on that particular date. Columns C, D, and E are formulas based on Columns A and B, as well as other data. The catch is that Columns C, D, and E require each day to be present in Column A because they are updated daily, regardless of whether or not there is an occurrence on a day not manually input in Column A.
This is also important because I then plot Columns C, D, and E on the yaxis of a graph, with Column A on the xaxis. I need to have a point for every day, as Columns C, D, and E change even though there is no new value in Column B.
Is there a way, given the above Columns A and B, to automatically create new versions of A and B with rows inserted for every day that is not manually entered? Thanks for the help. Let me know if you need any more information and I'll try to provide it.
Column A: Column B: Column C: Column D: Column E:
1/16/2010 5
1/16/2010 3
1/16/2010 8
1/17/2010 2
1/20/2010 3
1/29/2010 9
Columns A and B are entered manually based on some occurrence on that particular date. Columns C, D, and E are formulas based on Columns A and B, as well as other data. The catch is that Columns C, D, and E require each day to be present in Column A because they are updated daily, regardless of whether or not there is an occurrence on a day not manually input in Column A.
This is also important because I then plot Columns C, D, and E on the yaxis of a graph, with Column A on the xaxis. I need to have a point for every day, as Columns C, D, and E change even though there is no new value in Column B.
Is there a way, given the above Columns A and B, to automatically create new versions of A and B with rows inserted for every day that is not manually entered? Thanks for the help. Let me know if you need any more information and I'll try to provide it.
Sorry for the vague title, not sure even what I need for this! Before I get into it, let me first say thanks for all the help I've received on this forum, you guys have gone above and beyond what I expected from the internet!! Now to the issue:
I have a need to pull specific data from a worksheet based on criteria in one of the columns. I'm not exactly sure what I need to do to for this, whether VLOOKUP would work (I'm thinking it won't) or if I need a macro (in which case I'm lost).
The worksheet that I need to pull data from has 16 columns and varying numbers of rows depending on the day. I actually only need the information from 3 of the columns, and only for rows that meet certain criteria.
That criteria can change but can range from the top 5 or 10 numbers in a certain column or simply be anything (in that same column) over a certain amount ... say $80.00...
Now Based on which rows meet this criteria, I'l like to take that data and have it appear on another worksheet for easier viewing. The data needs to come to this other page because the user will need to explain the different amounts. Normally this is done by hand and is a lengthy process, being able to easily transpose the specific information from one sheet to the other would speed the process up a bit.
Couple of other notes:
*** While the column that I would select the data by is a number, everything else will likely be text.
*** On the new page where I want the user to be able to view the data, there is other information that needs to stay there, so I can't just copy all of the data to a new sheet.
*** Someone mentioned a pivot box (I think thats what it was called)  this worked for eliminating certain types of data but does not work for my needs.
As always, thanks a ton in advance for any and all help! I'll subscribe and do my best to respond to any questions or clarification ya'll need!
Andrew Barham
I have a need to pull specific data from a worksheet based on criteria in one of the columns. I'm not exactly sure what I need to do to for this, whether VLOOKUP would work (I'm thinking it won't) or if I need a macro (in which case I'm lost).
The worksheet that I need to pull data from has 16 columns and varying numbers of rows depending on the day. I actually only need the information from 3 of the columns, and only for rows that meet certain criteria.
That criteria can change but can range from the top 5 or 10 numbers in a certain column or simply be anything (in that same column) over a certain amount ... say $80.00...
Now Based on which rows meet this criteria, I'l like to take that data and have it appear on another worksheet for easier viewing. The data needs to come to this other page because the user will need to explain the different amounts. Normally this is done by hand and is a lengthy process, being able to easily transpose the specific information from one sheet to the other would speed the process up a bit.
Couple of other notes:
*** While the column that I would select the data by is a number, everything else will likely be text.
*** On the new page where I want the user to be able to view the data, there is other information that needs to stay there, so I can't just copy all of the data to a new sheet.
*** Someone mentioned a pivot box (I think thats what it was called)  this worked for eliminating certain types of data but does not work for my needs.
As always, thanks a ton in advance for any and all help! I'll subscribe and do my best to respond to any questions or clarification ya'll need!
Andrew Barham
I have posted this question before with no response....so..let's try again.
I am trying to create a macro and having a problem. I have 100 columns of data. Each column has a header name. How do I create a macro find a specific column(s), and move it to a new worksheet via a macro?
I am looking to select specific columns searching by header name instead of column letter as the identifier. I then want to copy all that column data, including the header name into a new worksheet. I will do this with 50 of the 100 columns and I want to place the columns of my choice into specific order in the new worksheet.
Someone once mentioned using the advanced find feature, but I have had no success. Could you help me with this problem?
Tony
I am trying to create a macro and having a problem. I have 100 columns of data. Each column has a header name. How do I create a macro find a specific column(s), and move it to a new worksheet via a macro?
I am looking to select specific columns searching by header name instead of column letter as the identifier. I then want to copy all that column data, including the header name into a new worksheet. I will do this with 50 of the 100 columns and I want to place the columns of my choice into specific order in the new worksheet.
Someone once mentioned using the advanced find feature, but I have had no success. Could you help me with this problem?
Tony
I have a userform that I created which has a lot of checkboxes on it. Each checkbox corresponds to a column of data in my sheet. As of now, when I check the box, the column of data is made visible. So I can select which columns of data I want to see.
My question is how can I make the reference apply to a specific column of data rather than just the specific column.
Now my code has Columns("C:C").hidden=false.
How can I change this so that I can add columns before column C. As of now, if I insert a column at B then when I check off that box in my userform it hides the wrong column (since the data that was in column C has now been moved to column D).
Thanks for your help
My question is how can I make the reference apply to a specific column of data rather than just the specific column.
Now my code has Columns("C:C").hidden=false.
How can I change this so that I can add columns before column C. As of now, if I insert a column at B then when I check off that box in my userform it hides the wrong column (since the data that was in column C has now been moved to column D).
Thanks for your help
I am trying to create a macro and having a problem. I have 100 columns of data. Each column has a header name. How do I create a macro find a specific column(s), and move it to a new worksheet via a macro?
I am looking to select specific columns searching by header name instead of column letter as the identifier. I then want to copy all that column data, including the header name into a new worksheet. I will do this with 50 of the 100 columns and I want to place the columns of my choice into specific order in the new worksheet.
Someone once mentioned using the advanced find feature, but I have had no success. Could you help me with this problem?
Tony
I am looking to select specific columns searching by header name instead of column letter as the identifier. I then want to copy all that column data, including the header name into a new worksheet. I will do this with 50 of the 100 columns and I want to place the columns of my choice into specific order in the new worksheet.
Someone once mentioned using the advanced find feature, but I have had no success. Could you help me with this problem?
Tony
Good day, I am new to this forum so if I have places this in the wrong area I apologise. I am looking for a formula to retrieve the latest data within a row over four columns.
Each column has a date header, with data associated with the date. The may be data, there may not be data. For each row I am trying to pull out the latest data.
For example
Row 1 has data in column 1, and column 3 but not column 2 and 4.
Row 2 has data in column 1, and column 4 but not column 2 and 3.
Row 3 has data in column 1, and not in column 2, 3 or 4
and the permutations continue.......
To the left of column 1 for each row I would like to display:
Row 1 Column 3 data
Row 2 Column 4 data
Row 3 Column 1 data
All data are numbers, where no data is represented as null or "nothing" values.
Any help in this matter is appreciated.....
Cheers
Each column has a date header, with data associated with the date. The may be data, there may not be data. For each row I am trying to pull out the latest data.
For example
Row 1 has data in column 1, and column 3 but not column 2 and 4.
Row 2 has data in column 1, and column 4 but not column 2 and 3.
Row 3 has data in column 1, and not in column 2, 3 or 4
and the permutations continue.......
To the left of column 1 for each row I would like to display:
Row 1 Column 3 data
Row 2 Column 4 data
Row 3 Column 1 data
All data are numbers, where no data is represented as null or "nothing" values.
Any help in this matter is appreciated.....
Cheers
Hi, this is my first post on this forum.
I am looking for solution to the following problem.
I have row with 31 data columns. Column(A) is row name. Column(B) through Column(AF) are data columns and will be manually populated daily with a numeric value as needed or "0" if no data is required.
I need a vba code, macro or conditional formatting that will autoupdate, on worksheet change, and display the following:
In column (AG) a running count of columns with "0's" , in column (AH) a column count between last two columns with numeric data, then in column (AI) running sum of all columns with numeric data within specific row.
Always the staring column will be column(B) with "0" or with numeric data.
The cells will get populated with a numeric data or "0's" randomly.
Number of data rows may grow from 1 to n.
Any help appreciated greatly.
Thanks,
Tom
I am looking for solution to the following problem.
I have row with 31 data columns. Column(A) is row name. Column(B) through Column(AF) are data columns and will be manually populated daily with a numeric value as needed or "0" if no data is required.
I need a vba code, macro or conditional formatting that will autoupdate, on worksheet change, and display the following:
In column (AG) a running count of columns with "0's" , in column (AH) a column count between last two columns with numeric data, then in column (AI) running sum of all columns with numeric data within specific row.
Always the staring column will be column(B) with "0" or with numeric data.
The cells will get populated with a numeric data or "0's" randomly.
Number of data rows may grow from 1 to n.
Any help appreciated greatly.
Thanks,
Tom
Hi,
Wondering if any one can help. I have been searching online for quite sometime but can't seem to find a solution to what I'm looking for.
Apologies for the long description but I hope it will reduce the amount of Q&A.
I have written a macro to get me to a point with my raw data whereby I have identified the number of instances that it appears using a concatentation of 3 columns and have seperated these from the unique instances.
What I would like to do with the data now, and where I'm having a problem, is to remove duplicates and lower values based on additional columns.
Column A represents columns B, C & D concatenated and by which I have determined the duplicates thus far. What I now need to do is remove duplicates based on column A and on columns E and F and also remove rows whereby column A is duplicated but the value in column E is lower. To add to the complexity, for a particular duplicate in column A, if one of the values in column E is text eg Other or NA, then I need to leave and NOT remove all instances of column A.
To Summarise:
1. Remove Duplicates whereby Columns A, E and F match and column E does not contain text
2. Remove Rows whereby Column A and F match and where column E is not the highest value and that none of the values in E for duplicates of A are text
3. All rows removed to be copied to a worksheet called "Removed"
I have attached an example spreadsheet, however the data I work with will vary in length, usually 40k150k lines, and usually have additional columns although the additional columns would be irrelevant.
In this small example based on the rules above rows 5, 11, 17, 20, 23, 24 & 25 would be moved to the "Removed" worksheet and the others would remain.
Any help would be grealty appreciated because I have been trying to do this for over a week and being fairly new to VBA it is doing my head in.
Thank you in advance
Rich.
PS Using excel 2007
Wondering if any one can help. I have been searching online for quite sometime but can't seem to find a solution to what I'm looking for.
Apologies for the long description but I hope it will reduce the amount of Q&A.
I have written a macro to get me to a point with my raw data whereby I have identified the number of instances that it appears using a concatentation of 3 columns and have seperated these from the unique instances.
What I would like to do with the data now, and where I'm having a problem, is to remove duplicates and lower values based on additional columns.
Column A represents columns B, C & D concatenated and by which I have determined the duplicates thus far. What I now need to do is remove duplicates based on column A and on columns E and F and also remove rows whereby column A is duplicated but the value in column E is lower. To add to the complexity, for a particular duplicate in column A, if one of the values in column E is text eg Other or NA, then I need to leave and NOT remove all instances of column A.
To Summarise:
1. Remove Duplicates whereby Columns A, E and F match and column E does not contain text
2. Remove Rows whereby Column A and F match and where column E is not the highest value and that none of the values in E for duplicates of A are text
3. All rows removed to be copied to a worksheet called "Removed"
I have attached an example spreadsheet, however the data I work with will vary in length, usually 40k150k lines, and usually have additional columns although the additional columns would be irrelevant.
In this small example based on the rules above rows 5, 11, 17, 20, 23, 24 & 25 would be moved to the "Removed" worksheet and the others would remain.
Any help would be grealty appreciated because I have been trying to do this for over a week and being fairly new to VBA it is doing my head in.
Thank you in advance
Rich.
PS Using excel 2007
Hi all,
I need to evaluate values from a list on one worksheet and summarize the answers on to another worksheet using specific logic. I don't write code so I seem to be lost. I can't figure this out based on other examples given because it's not an exact match to mine. here goes...
Working with two worksheets, First worksheet is worksheet 1 and it has two columns, A for "Area" and B for "Status."
Second worksheet has 3 columns, A for Area, B for Task, C for Status. Example data:
Column A
Area
Project Team
Column B
Task
Documentation
UAT
Support
Training Materials
Column C
Status
G
G
Y
R
Values in "Status" of Column c of worksheet 2 are selected from a named list that contain the values G, Y, R and a blank. each has associated conditional formatting so G=Green, Y=Yellow, R=Red and Blank is no fill.
I need the status in column B of worksheet 1 to disaply automatically based on selections made in column C of worksheet 2 based on the following logic:
If one or more cells in the range (C2C5) in column C worksheet 2 equals G or blank, with no R or Y, display G (with green background) in cell 2b on worksheet 1.
if at least one of the cells in range (C2C5) in column C equals Y, with no R, display Y with yellow background) in cell 2b on worksheet 1. (overrides any green or blank)
if at least one of the cells in range (C2C5) in column C equals R, display R with red background in cell 2b on worksheet 1. (ignores green, blank and yellow)
I'd also like to add this condition to the listing if possible:
if all cells in the range are G/green, display Completed with green background in cell 2b on worksheet 1.
I am not an advanced excel user so any help you can give would be greatly appreciated.
I need to evaluate values from a list on one worksheet and summarize the answers on to another worksheet using specific logic. I don't write code so I seem to be lost. I can't figure this out based on other examples given because it's not an exact match to mine. here goes...
Working with two worksheets, First worksheet is worksheet 1 and it has two columns, A for "Area" and B for "Status."
Second worksheet has 3 columns, A for Area, B for Task, C for Status. Example data:
Column A
Area
Project Team
Column B
Task
Documentation
UAT
Support
Training Materials
Column C
Status
G
G
Y
R
Values in "Status" of Column c of worksheet 2 are selected from a named list that contain the values G, Y, R and a blank. each has associated conditional formatting so G=Green, Y=Yellow, R=Red and Blank is no fill.
I need the status in column B of worksheet 1 to disaply automatically based on selections made in column C of worksheet 2 based on the following logic:
If one or more cells in the range (C2C5) in column C worksheet 2 equals G or blank, with no R or Y, display G (with green background) in cell 2b on worksheet 1.
if at least one of the cells in range (C2C5) in column C equals Y, with no R, display Y with yellow background) in cell 2b on worksheet 1. (overrides any green or blank)
if at least one of the cells in range (C2C5) in column C equals R, display R with red background in cell 2b on worksheet 1. (ignores green, blank and yellow)
I'd also like to add this condition to the listing if possible:
if all cells in the range are G/green, display Completed with green background in cell 2b on worksheet 1.
I am not an advanced excel user so any help you can give would be greatly appreciated.
I currently have a macro in the attached file that populates cell A23 in the Data Finder worksheet based on column A in the Employees worksheet and prints them out (the graphs from each valid entry Data Finder sheet). Rather than print them out, I want to place the data that would be found in column AI in the Data Finder sheet and match it up with the corresponding number in column A of the Employees sheet placing the values in columns E, F and so on....The way the file is currently setup (this is the smaller file, copied from larger ones), it calculates the data based on the 'clock number' input from the employee sheet. I'm not sure how to simplify it, but I believe a change to the macro that already functions may be the way to go. I add each month into this file so the macro may need to be tweaked so that i could add onto the data in the employee sheet (additional columns for subsequent months).
Any suggestions?
Thank You
Hi All. i am fairly ignorant in Excel! i have a worksheet with several columns. Column C may have a comma seperated list of values (e.g. 5, 15, 12, etc).
What i need to do is for instances where there are multiple values, create a new row with the value and all other data is the same as the previous row. For instance:
ROW 1
Column A: 123
Column B: 456
Column C: 12, 5
This would create two different rows:
ROW 1
Column A: 123
Column B: 456
Column C: 12
ROW 2
Column A: 123
Column B: 456
Column C: 5
I assume this is possible in a Macro, but my expertise are not great enough. Can someone please help? Thanks in advance!
What i need to do is for instances where there are multiple values, create a new row with the value and all other data is the same as the previous row. For instance:
ROW 1
Column A: 123
Column B: 456
Column C: 12, 5
This would create two different rows:
ROW 1
Column A: 123
Column B: 456
Column C: 12
ROW 2
Column A: 123
Column B: 456
Column C: 5
I assume this is possible in a Macro, but my expertise are not great enough. Can someone please help? Thanks in advance!
This may be simple, but I cannot figure it out.
 On worksheet one, I have 3 columns of data in columns A, B and C.
 There are about 2600 rows of data in these columns.
 Column A in these rows all contain serial numbers
 On worksheet two, I have 3 columns for potential data in columns A,B and C.
 In the rows in column A of worksheet two, I will type in one of the serial numbers that is already in one of the cells in column A on worksheet one.
 When I type in that serial number in column A, I would like the data in columns B and C, in the same row as that serial number in worksheet one, to automatically populate columns B and C in the row that I just typed the serial number in worksheet two.
These serial numbers are for items that will arrive at different times. After the data automatically populates B and C, I will put the date it arrives in column D. The data in columns B and C are long strings of alphanumerics. I know they are correct in worksheet one, I want to make sure they stay that way in worksheet two. Thanks in advance for any help you may be able to give me.
 On worksheet one, I have 3 columns of data in columns A, B and C.
 There are about 2600 rows of data in these columns.
 Column A in these rows all contain serial numbers
 On worksheet two, I have 3 columns for potential data in columns A,B and C.
 In the rows in column A of worksheet two, I will type in one of the serial numbers that is already in one of the cells in column A on worksheet one.
 When I type in that serial number in column A, I would like the data in columns B and C, in the same row as that serial number in worksheet one, to automatically populate columns B and C in the row that I just typed the serial number in worksheet two.
These serial numbers are for items that will arrive at different times. After the data automatically populates B and C, I will put the date it arrives in column D. The data in columns B and C are long strings of alphanumerics. I know they are correct in worksheet one, I want to make sure they stay that way in worksheet two. Thanks in advance for any help you may be able to give me.
Hi,
I'm trying to write a macro that copies the last column of a spreadsheet containing formulas and inserts it into the next column, then copies the original column and hard copies the data. ie the formulas are moving along each week I obtain new data.
The problem I am incurring is that after the 2nd week it selects the original column and inserts there (because that column's id is in the macro) and not the last column thereby copying older incorrect data.
Is there any way I can get it so that it will always select the last column and use that?
Thanks in advance
Andy
I'm trying to write a macro that copies the last column of a spreadsheet containing formulas and inserts it into the next column, then copies the original column and hard copies the data. ie the formulas are moving along each week I obtain new data.
The problem I am incurring is that after the 2nd week it selects the original column and inserts there (because that column's id is in the macro) and not the last column thereby copying older incorrect data.
Is there any way I can get it so that it will always select the last column and use that?
Thanks in advance
Andy
Hello everyone,
I hope I am posting in the correct place, but I am guessing that what I need needs VBA ( i am just starting with VBA btw)
So my problem is this:
I have a column that generates QuotationCodes ( so all cells contain a formula).lets say its column A.
columns B, C have other data like customer, and issue date etc.
Column D is OrderConfirmation and the user fills it in ( the date) only when the quotation is confirmed.
What I need is to copy (in another worksheet) a list of column A (QuoatationCodes) '(and other columns, but if I know how to do it for 1 column I suppose I will be able to do it for the rest as well)
only if it gets a confirmation date in column D. ' (basicaly generate an order list where i generate uniquencodes what i have now is a list of orders with blank rows for the quotations that have not been confirmed)
I need this to refresh automatically/ dynamicaly. either on new data entry ( In column D) or by a control button ...
note that column A will be ever expanding and data entry is on a daily basis.
Thank you in advance,
Angelos
I browsed through existing threads and other sites and didn't find this exact scenario. I'm sorry if this type of question is common, but I wouldn't know. I appreciate any help and thanks for trying and offering your 2 cents!
OK. What I have is a table with over 20 columns that I use to sort and AutoFilter the 200+ rows of data. Several of the columns use Data Validation which require data to be from lists. Conditional formatting is used very often throughout the sheet.
Now, I want to have a second worksheet that will be almost identical to the original, but only accept rows that have certain values (let's say 3 of the 8 possible values) a specific column. These values are text values that appear in a list, and the column uses Data Validation to accept values only from that list. This second worksheet should be like if I Autofiltered the original table by checkmarking 3 of the 8 available filter options for that column, but I need this specific 'view' of the data for certain purposes.
In a perfect world, this second sheet would automatically mirror the original sheet's changes... But I don't know if that's possible? Although Excel is a very flexible and powerful tool, flexibility is relative once your tastes in data organization become more and more specific! Please let me know if any of this is impossible, so I'll stop trying to figure out how, haha.
Also, I am very good with formulas and finding my way around Excel's features, but I'm not very experienced with the VBA programming or custom macrowriting.
Sorry if this is too specific or picky, but any help is appreciated, thanks!
OK. What I have is a table with over 20 columns that I use to sort and AutoFilter the 200+ rows of data. Several of the columns use Data Validation which require data to be from lists. Conditional formatting is used very often throughout the sheet.
Now, I want to have a second worksheet that will be almost identical to the original, but only accept rows that have certain values (let's say 3 of the 8 possible values) a specific column. These values are text values that appear in a list, and the column uses Data Validation to accept values only from that list. This second worksheet should be like if I Autofiltered the original table by checkmarking 3 of the 8 available filter options for that column, but I need this specific 'view' of the data for certain purposes.
In a perfect world, this second sheet would automatically mirror the original sheet's changes... But I don't know if that's possible? Although Excel is a very flexible and powerful tool, flexibility is relative once your tastes in data organization become more and more specific! Please let me know if any of this is impossible, so I'll stop trying to figure out how, haha.
Also, I am very good with formulas and finding my way around Excel's features, but I'm not very experienced with the VBA programming or custom macrowriting.
Sorry if this is too specific or picky, but any help is appreciated, thanks!
Sounds pretty straigthforward, but there's a catch:
I want the column to be pasted on the first empty column in the other sheet.
Additional information:
I'm constructing an excelfile that has 4 worksheets.
First sheet has all the workable data. Everything is ordened in columns. Column 1 has all the 'first information', column 2 all the 'second information'...
Second sheet has all the formulas. On this sheet I only have to select the data I want to use, for example the data of the 'first information'.
On the third sheet will all the results be punt. All the results will be put in a single column again.
The fourth sheet needs to contain all the results, column by column.
IMPORTANT: all columns will be of the same number of rows. I think this information could be helpful.
Let's say that on the fourth sheet there are already three columns with results.
I need a macro which takes the column of the third sheet (always the same column) and copies (only the values) this column onto the fourth page after the already present results, in this case the fourth column.
Thanks in advance.
Sincerely,
Dreekenkorin
Edited: spelling
I want the column to be pasted on the first empty column in the other sheet.
Additional information:
I'm constructing an excelfile that has 4 worksheets.
First sheet has all the workable data. Everything is ordened in columns. Column 1 has all the 'first information', column 2 all the 'second information'...
Second sheet has all the formulas. On this sheet I only have to select the data I want to use, for example the data of the 'first information'.
On the third sheet will all the results be punt. All the results will be put in a single column again.
The fourth sheet needs to contain all the results, column by column.
IMPORTANT: all columns will be of the same number of rows. I think this information could be helpful.
Let's say that on the fourth sheet there are already three columns with results.
I need a macro which takes the column of the third sheet (always the same column) and copies (only the values) this column onto the fourth page after the already present results, in this case the fourth column.
Thanks in advance.
Sincerely,
Dreekenkorin
Edited: spelling
Sounds pretty straigthforward, but there's a catch:
I want the column to be pasted on the first empty column in the other sheet.
Additional information:
I'm constructing an excelfile that has 4 worksheets.
First sheet has all the workable data. Everything is ordened in columns. Column 1 has all the 'first information', column 2 all the 'second information'...
Second sheet has all the formulas. On this sheet I only have to select the data I want to use, for example the data of the 'first information'.
On the third sheet will all the results be punt. All the results will be put in a single column again.
The fourth sheet needs to contain all the results, column by column.
IMPORTANT: all columns will be of the same number of rows. I think this information could be helpful.
Let's say that on the fourth sheet there are already three columns with results.
I need a macro which takes the column of the third sheet (always the same column) and copies (only the values) this column onto the fourth page after the already present results, in this case the fourth column.
Thanks in advance.
Sincerely,
Dreekenkorin
Edited: spelling
I want the column to be pasted on the first empty column in the other sheet.
Additional information:
I'm constructing an excelfile that has 4 worksheets.
First sheet has all the workable data. Everything is ordened in columns. Column 1 has all the 'first information', column 2 all the 'second information'...
Second sheet has all the formulas. On this sheet I only have to select the data I want to use, for example the data of the 'first information'.
On the third sheet will all the results be punt. All the results will be put in a single column again.
The fourth sheet needs to contain all the results, column by column.
IMPORTANT: all columns will be of the same number of rows. I think this information could be helpful.
Let's say that on the fourth sheet there are already three columns with results.
I need a macro which takes the column of the third sheet (always the same column) and copies (only the values) this column onto the fourth page after the already present results, in this case the fourth column.
Thanks in advance.
Sincerely,
Dreekenkorin
Edited: spelling