Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
ebook!
Our Top 15 Excel Tutorials
Instant Access!
E-mail:
Subscribe for Free Excel tips & more!
E-mail:
Advertisements



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

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

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?

View Answers     

Similar Excel Tutorials

Run a Macro from Another Macro in Excel
I will show you how to run a macro from another macro in Excel.  This means that you can run any macro when you ne ...
Linking Cells in Excel
In this tutorial I am going to cover how to link cells together. This is a useful feature of Excel as you can link ...
Stop a Macro from Running in Excel
How to stop a macro from running after it has started. This method uses the keyboard and has nothing to do with th ...
How to Make Macros Run A LOT Faster
Here is a very simple and easy-to-use tip to make all of your Excel macros run A LOT faster.  It is very simple and ...

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

Similar Topics







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: 240-243
Column 2: 244-251
Column 3: 252-255
Column 4: 256-259
Column 5: 260-263
Column 6: 264-273


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 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 y-axis of a graph, with Column A on the x-axis. 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.


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 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 auto-update, 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 40k-150k 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,

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



Is it possible to create a macro to check the value of a specific column and depending on the value, then no row need to be added or one row or two rows need to be inserted as well as the new row data will be filled with some same data and calculated data from the above row.

For example, the worksheet may have about 1000 rows with column A contains person ID, B contains OrderCode, C contains OrderDate, D contains TotalHrs, E contains NorHrs, F contains OTHrs, G contains PreHrs. If TotalHrs has value of 11.5 then on the current row the NorHrs will have the value of 9, then a new row will be inserted below which has the same value of the above row from column A to C while the TotalHrs and OTHrs will have value of 1, finally a second row will also be inserted similar as above but TotalHrs and PreHrs will have value of 1.5.

The main idea is if in the checking row, if TotalHrs is less than or equal to 9, no new row need to be inserted, if TotalHrs is greater than 9 but less than 10 then only one row needs to be inserted, and if TotalHrs is greater than 10 then 2 rows need to be inserted.

Hey all

I want to create a macro that will allow me to compare two columns of data from 2 different worksheets and then if it finds a match creates a new column in the 2nd worksheet containing the value from the 2nd column in the first worksheet which corresponds to the first matching column. For Example

Worksheet 1 Worksheet 2

Column A Column B Column A New Column
On 1 Off 2
Off 2 Reset 3
Reset 3 On 1

So I kinda tried to create a graphical representation of what I want it to look like. Basically I want to compare Column A of worksheet1 with Column B of worksheet 2, with Column A from worksheet 2 not necessarily in the same order as Column A in worksheet 1. Column B in worksheet 1 is the corresponding numerical value for the text in Column A. I would like the macro to compare both Column As and if there is a match return the corresponding value in Column B to a New Column in worksheet 2. I'm kinda new at VB, so any help with be great.


I am not sure what to call this functionality so I was unable to effectively search, so I appologize if this has been asked and answered before.

For ease of example, here is what I am looking to do.

Worksheet with multiple rows of data.
Column A contains a quantity
Column B contains a qualifier number 1-5

I want to be able to, in a seperate cell, (on another worksheet page, but not the issue) have a list that states:
Qualifier#1: sum of column A rows with column B = 1

And then subsequently, do the same for the other levels of qualifier
Qualifier#2: sum of column A rows with column B= 2
Qualifier#3: sum of column A rows with column B= 3
Qualifier#4: sum of column A rows with column B= 4
Qualifier#5: sum of column A rows with column B= 5


Is this possible and if so how?

thanks in advance.


Hi guys,

Was wondering if anyone could help me create a macro that pulls certain data of a certain varying limit to different sheets, e.g.

I have a sheet titled 'Data Dump' which contains lots of information specific to certain models of a product. (By the way using an IF statement is not suitable here for reasons that become apparent). I would like two other worksheets, one called '1' and another called '2' to pull the following information:

Worksheet 1 should pull data in columns A, B and I based on whether column H in Data Dump is = 1 to the total of 100 (i.e. 100 rows of information. (here an if statement would pull 100 and include empty rows, I do not want empty rows).

Worksheet 2 should pull data in columns A, B and I based on whether Column H in Data Dump is = 2 to the total of 50 (i.e. 50 rows of information.


If anyone would know what script I need to run this I would be very grateful.

Many Thanks,

Greg


I'll admit I know very little about creating a macro in Excel so I hope someone can help me out with creating what I consider a fairly simple macro to adjust values in a column or columns.

What I am using this worksheet for is a merchandise database that contains 3 columns of pricing values, ie. Column headers Small, Medium, Large.

The columns are approximately 2000 rows deep.

I need to change the pricing values in all 3 of these columns by a specific amount. However there are some values in the "Large" column that will not be adjusted. Those values not adjusted will be based on the contents of another cell in the same row that would only contain the data "OT,JB" or only "JB".

I've considered using find/replace but think it would be very inefficient.

Right now I am looking to lower the price values by 2 dollars. All values are numerical and formatted as Number/General. ie, 11.95, 7.95

I've considered creating another column or linked worksheet and forumla-ize it and then copy/paste my value results back, but I am hoping for something less cumbersome, something that is expandable as the worksheet grows, etc. I think a macro, but I do not have any idea what to do or how to get started.

I am not familiar with VB either.

Thanks.


Hi all,

I am having trouble coding my latest macro project. I have a long list of various movie titles which are assigned commissions. I need to select the values in one column and apply that value to several other columns in the same worksheet. However there are some caveats to this process. For example:

A B C D
100 (100) 5 (5)

I need to select the values in Column C where the value in Column D is negative and add the value (Column C) to Column A and subtract the same value from Column B. The problem I am having is that Column A and B have formulas that need to be maintained to see the previous months adjustments. For instance: Column A: =10+80+10 and similarly Column B: = -10-80-10. The end result I am hoping to achieve would be Column A: =10+80+10+5 and Column B: =-10-80-10-5 thereby totaling 105 and (105) respectively. The last process would be to zero out Column C. If this is not possible please advise, and any assistance would be greatly appreciated!!!!!

I have attached an example excel sheet to help clarify. Thanks again!Example Commission Sheet.xls

Complete newbie , but there are anumber of things i would like to do with a spreadsheet i have
Currently i have a macro which inserts a column, copies and pastes formulas to the new column from the one beside it.
I then want it to hide the column beside it (left of it) and lock the contents if possible

However when i run it it inserts the column and copies the formula but hides the original column. This only becomes an issue when you do it again and again you find it just goes back to the original column rather than the one beside the the column i have just inserted
Any ideas on how to resolve this . Its a relative cell issue i think Also it would be great that the column which i wish to be hidden wi could also locked the cells once the macro has been run . Any ideas
I have been staring blankly at a screen for hours i might add !!!
Thansk again


Hi ,

I need an help in creating macro for doing two tasks

1.To copy the data from one sheet to new sheet in same excel based on a particular column.Column C will have multiple values and these values might repeat ,so i need the macro to put filter on that column and copy paste the relevant details to new sheet for all the values in that column.

2.The other macro has to paste the data in a specified order in a different excel sheet.For this the output given by first macro will be used, the new sheets which the first macro created will be pasted in a order in different excel by using second macro.

First Macro Order to copy paste in second Macro
Column B Column C
Column E Column G
Column D Column E

The column B of first macro has to be pasted in column C by using the second Macro in the similar way the other two columns also has to pasted.Once this done it has to leave a line and again it has to paste the same data.

Can you please help in doing this.
I have attached the outputs of the two files to show how it looks.
I hope this helps in creating a macro.
Eagerly waiting for a quick reply.


I have a large data set in Excel. I would like to filter the data and copy the filtered data into a new worksheet so that I can then create an Excel XY plot. (I already have a macro to do that.)

The two fields of interest in the spreadsheet are in columns Q (Location) and AX (Chemical). The first row of data is headers.

If I were to do this manually, I would do the following:

1. Using autofilter, filter the 1st location from column Q. Then, filter the 1st chemical from column AX. Copy this data set to a new worksheet. Create the plot.
2. Change the filter on column AX (chemical) to the 2nd item in the list. Copy this updated data set to a new worksheet. Create the plot.

This would repeat until I have cycled through all of the chemicals in column AX.

3. Change the filter on column Q (Location) to the 2nd item on the list. Filter the 1st chemical from column AX.
Copy this data set to a new worksheet. Create the plot.

This would repeat until I have cycled through all of the chemicals in column AX.

Repeat this procedure until I have cycled through all of the locations in column Q (and their accompanying chemicals in column AX).


Considering that the number of location-chemical combinations is variable, is this possible to do with code?

I appreciate any help you can offer.
Christine


Hi ,

I need an help in creating macro for doing two tasks

1.To copy the data from one sheet to new sheet in same excel based on a particular column.Column C will have multiple values and these values might repeat ,so i need the macro to put filter on that column and copy paste the relevant details to new sheet for all the values in that column.

2.The other macro has to paste the data in a specified order in a different excel sheet.For this the output given by first macro will be used, the new sheets which the first macro created will be pasted in a order in different excel by using second macro.

First Macro Order to copy paste in second Macro
Column B Column C
Column E Column G
Column D Column E

The column B of first macro has to be pasted in column C by using the second Macro in the similar way the other two columns also has to pasted.Once this done it has to leave a line and again it has to paste the same data.

Can you please help in doing this.
I have attached the outputs of the two files to show how it looks.
I hope this helps in creating a macro.
Eagerly waiting for a quick reply


Hi,

if I filter a worksheet based on the data in one column, how do I use a
macro to copy data in other columns (and the filtered column) into another
worksheet? I only want the data which has been filtered, not blank cells
below the filter. Then the macro would need to copy the detail from some
other columns and paste the data alongside the filtered data in the new
sheet. What code, stops the process at the end of the filtered data and then
moves to the next step of the macro? Also it would need to copy the same
amount of rows for each column so the data remains intact.

Hope I have made some sense.

Matt


Hello,


I want to create a Macro that inserts five new columns after COLUMN F in the ACTIVE WORKSHEET. In addition, after these columns have been inserted, I wanted the Macro to insert the following Titles into the following cells:


Column G Row 1: Cars


Column H Row 1: Models


Column I Row 1: Types


Column J Row 1: Activity


Column K Row 1: Destination


If further explanation is needed please let me know.


Thanks!




I have a spreadsheet with values in columns A-F.

In column A is a closed workbook name/directory path.
In column B is the sheet name within the workbook specified in column A.

I need a macro which will run with a SUBMIT button in column G which will write all row values in columns C-F to a new row in the worksheet specified by A and B. Any further additions should be added to the next available row in the specific worksheet without overwriting previous data.

Thanks
Phill

I want to write a formula that will look in a specific column on a worksheet for the top 5 text occurrences of text based on a relationship to another column on the same worksheet and list the top 5 in specific cells on a different worksheet within the file. Example:

Worksheet 1 column "D" has one of 5 texts that are selected from a drop list. I have already entered an OFFSET formula that then restricts the drop list selections available in column "E" based on column D selection. I now want to count the top 5 column E occurrences based on Column D and list those top 5 in column F on worksheet 2.

Any ideas?




Dear Sir,

My current problem is that,I want basically to put values from a XFD column (data can be any) to Column Q of 'Details' sheet and also to other four sheets which are-

-also putting values of XFD column at Printing sheet at column B after matching each rows of E,F G columns from Details sheet A,C,D columns
-also putting values of XFD column at Lamination sheet at column B after matching each rows of E,F G columns from Details sheet A,C,D columns
-also putting values of XFD column at Pouching Sheet at Column A after matching each rows of C,D,E columns from Details sheet A,C,D columns
-also putting values of XFD column at Finish Sheet at Column A after matching each rows of B,C,D columns from Details sheet A,C,D columns

In each of 4 worksheet,it should prompt for the range from where to where the range should be taken inorder to put the values of XFD column of Details sheet to be appear automatically.This is so becoz in each of 4 worksheet,I have mentioned 2 sample data as the data goes on filling in each rows day by data and accordingly I have update the data by changing the range as old range I do not require and only new data would be taken.

But sometimes it does not match leave it blank so that I can do manually and do necessart changes myself.it,but message if prompt would be beneficial.Pls note that,if in column Q of Details sheet already contains value of XFD column of Details sheet then no need to put but it should ensure the data are well matched.This is so becoz I have already done previously in Q column.

I have higlighted with yellow where data is required to filled up after matching.

Here a macro would be preferred with button.

Pls find enclosed in attachment.Only few data in 4 spreadsheet is given as a sample.

I am layman in VBA but would be most interested in using it.

If any futher info if required kindly let me know.

With Best Rgds



Hi,
I'm looking for a way to combine (not concatenate) the data from 5 columns in a single column. In other words, if I had 5 rows of data in column A, 3 rows of data in column B, 11 rows of data in column C, 8 rows of data in column D and 9 rows of data in column E, I want column F to have the 36 values (5+3+11+8+9 = 36) from the other columns.

The data has significantly more rows than I've used in my example and cutting and pasting is getting a little tedious every time I need to combine the data.

The data starts in row 3 for each column and any given row has no more than 258 values.
There are no intermingling blank cells in a given column; the blanks are at the end of each column.
The data is text, not numeric

(Back story: The data in columns A-E is generated using array formulas that pulls data from other worksheets. I need to combine those columns in to one, then remove duplicate values and sort the resulting combined column. The removing duplicates and sorting part I can handle; it's the combining part I'm having problems with. I was about to drop into VBA to solve this but since when it comes to writing formulas I'm a noob, I thought that I check here first. I saw this thread but no one solved it.)

Any help would greatly appreciated!
Thanks!
Paul

I'm not very good with macros and I need to create a macro that copies data from one excel worksheet into multiple other worksheet tabs in the same workbook. I have 8 columns and thousands of rows of data. The spreadsheet is sorted by column E.
In column E, there are about 25 different values going down throughout the spreadsheet. I would like the data for each of these Column E categories to be copied over to a new tab in the spreadsheet with the tab name as the value in E. So in the end there would be the main tab, and then 25 new tabs with the filtered data. Does anyone already have a macro that will do this?

I'd also like to create a pivot table for each tab as well. Any thoughts/suggestions?


I have a sequence of worksheets, each one represents one month. On all the worksheets there is a list of stocks and their prices at month end in column A then B respectively.

I'm wanting to show what the % return each stock is getting for one month in column C, 2 months in column D, and three month % return in column E. This suggests that worksheet 4 will be able to present this data, so from worksheet 4 to 150 I need these calculated.

I'm struggling with the code needed to select the proper worksheet than repeating the process with +1 worksheet, if you know what I mean. Selecting the previous worksheet when recording a macro just selects that specific worksheet, so I need a better way.

Any help would be appreciated,

Thanks