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


Search Excel Forum Posts, Tutorials, Macros, Tips, and More
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?
Similar Excel Video Tutorials
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 ...
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!
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
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
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
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.
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
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.
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
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 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
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
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
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
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
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
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.
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!
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.
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
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
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
Hi,
I have a set of data in one worksheet which contains 7 columns and multiple
rows of data (over 300 rows). In a new worksheet, I need to summarize one
column of this data which is referenced by matching 2 (or more columns).
Please see below:
Sheet 1 contains the data with 7 columns:
A B C D E F G H
110 03 2A 1777 03 3000 1555 456
111 04 5A 2587 02 3156 1777 12.65
etc etc etc etc for over 300 rows of data
Sheet 2 is where I wish to pull the data from Sheet 1I would like to
return a value from Sheet 1 Column H based on criteria matching Column A,
column C and column F. In this example, I want to find all values in Column
H where Column F=3000, Column C=5A and Column A=110.
Any guidance or suggestions would be greatly appreciated. Thanks.
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!
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 and my own reference.
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.
Even if it won't 'automatically' mirror the original, I still would like to view the information in this way! Thanks for any help!
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!
Hello All,
Just joined up today. Lets get to it then! I'm trying to create a macro that will look at values in Column A which contains customer account numbers. The Macro will look at column A and if it matches a value in a list (can this list be included in the macro code or does the list need to exist in a column in the worksheet?), then it will pull all rows which contain that account number over to a new worksheet. When this data is found and copied over to the new worksheet, i would also like the worksheet to be named based on the customer name in cell B2 (assuming header row is B1).
I have attached a brief example of a spreadsheet for reference. How can i tell the macro to look for account 1987 and take all rows containing that data over to a new sheet? The example i have provided is small, and the account numbers i need to look for are many but i was thinking of just creating a list and when i have the macro code i can use this list to put into the macro.
I have looked online and can't quite find the code which does what i want  any help would be massivley appreciated
Can this be done?
Thanks in advance
Aldo

