Excel address data - single column to rown

Question:Excel address data - single column to rown

I have an excel worksheet with hundreds of addresses in a single column, each address separated by a single row. I need to get the addresses into mail-merge format so each address (which ranges from 5-7 rows in the single column) ends up on a row. I need the country of each address to end up in the same column. I am struggling badly with this! Can anyone help? Currently there are two columns to the left of the address column. Each column represents a book version - English & Spanish and below these headers is a qty of each type each address has to receive. I need to retain the qty per address in the output file!

Relevance
100%

Preferred Solution:
Excel address data - single column to rown

I recommend downloading and running Reimage. It's a computer repair tool that has been proven to identify and fix many Windows problems with a high level of success.

re: each address ... ranges from 5 - 7 rows in the single columnThat's the issue that concerns me. If one "address group" looks like:1 - Name2 - Address3 - City4 - State5 - Zipand another looks like1 - Name2 - Address Line 13 - Address Line 24 - City5 - State6 - Zipand yet another looks like:1 - Name Line 12 - Name Line 23 - Address Line 14 - Address Line 25 - City6 - State7 - ZipThen it would be difficult for a macro to simply loop through each address group and rearrange the data.However, if there is something in each address group that the macro could look for, then things get a little easier. Perhaps the code could use the 5 digit zip code to determine that is at the bottom of an address group.Please give us a couple of examples of address groups so we can see how the sheet is laid out. Please include the location of the extraneous data that has to be moved with the address group.

I have a data like this (The number of Products and Shops are much more)

I want the result to be like this

This is the attached file for you to download the data or you can simply download from the attachment in this post http://www.sendspace.com/file/iqvlce

I would really appreciate the help of the community Kudos to everyone!!!!

Answer:Excel Macro - Merging multiple column into single column

in a new cell, create the following formula.=A1&" "&B1&" "&C1

Or isn't this what you want?(the cell-numbers are those which you want to combine).You can copy the formula for all cells (haven't seen your file, find the link a bit tricky, maybe attach the file in a post here)

I have a single excel sheel with one single column containing words with some special characters. I want to search for multiple words from that single column.

Answer:Search multiple words in a single column in a single excel s

A bit of clarification:Does each cell contain multiple words or just one word?After you find your target word/s what do you want done with them?And, in the future, Excel questions are best asked in the Office Software forum.MIKEhttp://www.skeptic.com/

Hello! Apologies from a computer gumby. I am a PhD researcher conducting a recruitment mailout, with an excel participant database of 300 names. Participants' addresses have been entered (in entirety from house number to ZIP/postcode) in a single column.I need to conduct a mail merge, but cannot find a way (short of changing each cell individually) to reformat the addresses.Current address format: 32 Brook St, Lee Park 3111 Required format: 32 Brook St, Lee Park 3111Had I been forward-thinking, I would have entered data in separate columns :-(Sincere thanks for any advice.

Answer:Excel; addresses in single column; merge?

If all you want to do is separate the addresses into 2 columns, and not move the city underneath the house address, try:Data...Text To Columns...Delimited...Next...Comma...FinishClick Here Before Posting Data or VBA Code ---> How To Post Data or Code.

How do I convert a date that appears in 3 columns to a single column date i.e. column A has MONTH( Jan shows as a 1, Feb as a 2), column B has DAY(I.E. 3 OR 4 OR 10) and column C has Year (2 DIGITS 2010 shows as 10 or 2011 as 11)..want to convert to signle column 01/03/10...

Answer:in excel convert 3 column date to single colu

Concatenation (&) of values from multiple cells creates a "text string".DATEVALUE converts a text string into a date as long as Excel can recognize the structure of the text string as a date.Putting those 2 facts together, try this:=DATEVALUE(A1 & "/" & B1 & "/" & C1)

If you use the pre tags found above the text entry box you can line up your data in columns to make it easier for us to read.That said, take a look at this thread and see if either of the solutions (VBA and formula based) will work for you.http://www.computing.net/answers/of...

Here's a way to do it with formulas. This will work for any number of headings, you'll just have to adjust the "helper column" formula, which will be explained below.The formulas suggested are based on the example data that you posted, but as long as you grasp the concept, you should be able to modify them to fit your exact situation.Let's assume your example data is in A1:A6.In B1, enter this formula and drag it across to D1:=LEFT(INDIRECT("A"&COLUMN()-1),FIND(":",INDIRECT("A"&COLUMN()-1))-1)In reality, you can drag it across as far as you need based on how many "headings" you have. Your example shows 3 headings, so you drag it across 3 cells.You should now have this: A B C D1 Heading1: Value1 Heading1 Heading2 Heading3Now we're going to create a "helper column". This column will be used to increment the formula below so that we can drag it around and make it update automatically.Based on your example, I will use Column E for the helper column since it is open. You can use any column you want, you'll just have to adjust the following formulas to match.In E1 enter 1In E2 enter =E1+3 and drag it down. Drag it down as far as you need so that the last number in the helper column is a bit higher than the last row of your data.The "3" is based on the number of headings you have. If you had 4 headings, the helper column would have to increment by 4, etc.Your example data has 6 rows of data, so I dragged it down until I saw the number 7.You ... Read more

I have an excel spreadsheet with multiple address in one column for ex:Jones Mary 166 New Lane Dallas TX 11111I need to seperate it into seperate cells by last name, first name, street address, state & zipHow do you do this? I ahve tried text to colums but it seperates the street address into 3 different colums

Answer:excel address 1 column to multiple

How exactly does your data look?If it is the same as you posted, with spaces between each, the Text to Column should work.But instead of using Delimited click on Fixed WidthWhen you press Next, your data will be separated by Arrow Lines, just click on the Arrow Lines and move them to the location you want them and you should get what you want.MIKEhttp://www.skeptic.com/

In my excel file, I have a vendor name in column A and the address information (street, city/state/zip) in column B. I need to move city/state/zip to column c. The issue is that some vendors have 2 lines of information, some have 3 (apartment number, for example) and some have no address at all. Each record is separated by a blank row though.

How can I get the address information to go across to columns C and D (if necessary)?

That said, there's a function in Excel in the Data menu or ribbon (depending on if you're using 2003 or 2007) called Text To Columns which lets you to break out data from one cell to many cells. So, if you have address/city/state/zip in one cell - you can tell it to put the address in one cell, the city in the next cell, the state in the next cell and the zip in the next cell.

However - you have to tell Excel how to separate the data in your cell. It can be by a "delimiter" or by counting character spacing. Delimiter means... You can tell it to look for a comma, or a space, or a dash or something that's placed between each piece of data and wherever it sees that delimiter it will put the next piece of information into the following cell. By spacing you're telling it to count a certain number of characters to the right then split the data into the next cell. This probably won't work because address information is all across the board with regard to character counts.

Address information is difficult with delimiters as well because more often than not the only thing you'll have between each piece of data is a space. That works for the city sta... Read more

Dear all I have a list of the following data in cell A1, any quicker option for me to extract only the email address in to cell A2? 2;"msriegler876";;"[email protected]";;"12782";;"0";"30351";"1";;"States";"Australia";;"msriegler876";;;;;;;;;;;"1";;;"0000-00-00 00:00:00";"0";"0";;"0";"0"ThanksClarence

Answer:Export email address to a new Excel column

Use the Text To Column function on the Ribbon:Select cell A1Select Data TabClick on Text To ColumnSelect Delimited, Click NextSelect Semi-colon, Click NextClick FinishAlso, Excel questions are best asked in the Office Software forum.MIKEhttp://www.skeptic.com/

I have an address list in col A in excelwith a blank row between each addressI need to put them in up to 9 columnsname address1 address 2 etcname col 1 through to postcode in col 9postcode is last data of each addressnot sure what to dois there a macro available

Answer:need to convert 1 column of address data to multiple columns

There is a feature in Excel known as Data...Text To Columns which is typically used to split long strings in one column into multiple columns.Read the Excel Help files on that feature and see if it works for you. If you need any further assistance with your specific situation, please click on the blue line at the end of this post, and after reading the instructions found via that link, post some examples of your data and we'll see if we can offer any other suggestions. Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

I've used Tech support guy for quite a while and almost always find what I need.I guess it's about time I join and post a question

So this is my problem.I send out an excelreport every day.In it I use the content in column X to populate the hyperlink address in column Y.I've done this manually but now the report is getting bigger and a macro seems in place.Problem is I just don't know enough about the syntax used in excel.

What I want to do is loop though each row - if there is data in column Y then check in column X.If X is populated use the data as hyperlink address in column Y (info in Y should still be what is shown).This should be done for each row where Y is populated.Then the X column shall be deleted (but I guess I could do that manually )

It feels pretty straight forward and I could do it in RPG or SQL but the syntax in excel befuddles me Any tips?

Answer:Excel macro - use info from one column as hyperlink address in another.

Assume Y2 and X2 cells=if( Y2 = "", "", IF(X2="", "" , do Y2)

but you need to have the formula in Y and change Y - i think that may need a macro, as you are testing Y and also rewriting Y

I have a sheet with 100 rows and several columns of data. Column A contains names. Column B has data in some of the cells, column C also has data but not in the same rows as the data in column B. Column D also has data in some of the cells (but not in the rows where column B and C have data.The data consists of a numbers and letters, e.g. 25Q9/AB and are always in this format.I would like to amalgamate the data from columns B C D into column E Is there a way to achieve this please? I have tried using filters and copy/paste to column E but no joy. (Excel 2003).Thanks in anticipationJohn

Hello!! First, thank you all in advance for any advice you are able to provide. My dilemma: Using MS Excel, I have been attempting to write a formula that will search for a value in a row and return the value in the cell next to it, no matter how the data it is sorted. I have not had any luck and I'm hoping you all wouldn't mind providing some guidance.Example:01 139.002 59.203 107.004 140.205 200.006 -5.6?through 27.I would like to extract the information in column B by searching for a specific value in column A. For example, if I search for 03 I would like it to return 107.0. Writing that formula would be simple if it were not for needing to change the way the data is sorted, either by aircraft number or by aircraft hours depending on the situation. 05 200.004 140.201 139.003 107.002 59.206 -5.6?through 27.The formula would always search for values in column A (01, 02, 03, etc.) and return the correlating value in column B. The trouble I?m having is the location of the value I am searching for changes rows depending on how it?s sorted. My overall goal is to create three graphs with this information. I will use data for all 27 numbers to create the first graph, 1 through 9 to create the second, and 10 through 29 to create the third.Hopefully this wasn?t an overload of information! I wanted to provide as much as possible just in case someone wanted to suggest another route. Any advice would be greatly appreciated and thank you all in advance!!Nate (USMC)

Answer:MS Excel, search column A and get data from B

If your data looks like this: A B1) 5 2002) 4 140.23) 1 1394) 3 1075) 2 59.26) 6 -5.67) 8) 2 59.2In cell B8 enter the formula: =SUMPRODUCT(--($A$1:$A$6=$A8),(B1:B6))In cell A8 enter the number to search for.How you have the data sorted should not matter.Just be aware that you cannot use 01, 02, 03 as numbers in column A, Excel will strip the leading zero off and you will end up with 1, 2, 3.To retain the leading zero you must have column A formatted as TEXT.It will only cause problems when you enter a NUMBER to search for and the target cells are TEXT or vice versa. MIKEhttp://www.skeptic.com/

First of all, I am trying to concatenate fields in several records. Currently, I have a query that pulls out the specific account, appending to a table called "Control Log" and it looks like this:

Client Form------- -------Lesand 1040Lesand 1099Lesand G-45

I want to get to look like this:

Client Form------- ------Lesand 1040,1099, G-45

where I will append to another table called Control Log1 where an automatic number will be assigned to just that one record. I then want to populate a form with the autonumber and client info, and the remaining information relating to that client will also populate (address, etc.) from the query that pulled their info from the beginning.

I am fairly new to the new features to Access 2007-2010, but am eager to learn. Thank you for taking the time.

I scanned in some phone numbers/names using OCR and need some help in Excel. Here is example I made real quick of what the list looks like:

Column A is fine. However, do you see Column B? It has the grade and then the phone number.

Here's what I mean in column B:Grade [one space] phone number

So, it looks like this in the excel spreadsheet:10 555-5555

Here's what I need to do. I need to find a way to automate the process of taking out the phone numbers from column B and moving them all to a new column (C). There are four grade numbers (9, 10, 11, and 12), so I can run the process on each grade to separate them.

How can I do this? Thanks.

Answer:Moving partial data from one column to another in Excel

Put the formula:

=RIGHT(B2,8)

in C2. Adjust the 8 for the phone number if it includes area code. Then drag that formula down the number of rows you'd like.

i have a large set of data that I pull. I need to take the large list and create a new line for every new number and duplicate the first set of data. This will be easier to show:Example data:GASTROINTESTINAL AGENTS DIGESTIVE AIDS CREON 2 51200024006740 51200024006760 51200024006720 GASTROINTESTINAL AGENTS DIGESTIVE AIDS LIPRAM-UL 2 51990003206774 51990003206785 51990003206787 GASTROINTESTINAL AGENTS DIGESTIVE AIDS PANCRELIPASE 2 51990003200310 GASTROINTESTINAL AGENTS DIGESTIVE AIDS PANCRELIPASE MST-16 2 51990003206780 GASTROINTESTINAL AGENTS DIGESTIVE AIDS PANCRON 2 51990003206772 51990003206786 GASTROINTESTINAL AGENTS DIGESTIVE AIDS ULTRASE 2 51990003206748 GASTROINTESTINAL AGENTS DIGESTIVE AIDS ULTRASE MT 2 51990003206774 51990003206785 51990003206787 Each number is pull into a separeat column to the right of each product. There could be up to 20 columns of numbers related to only 1 product. I need to convert that 1 row into x number of rows (based on number of columns) and show as separate row entries.Ex) Creon 2 90 caps 51119999119 51111111111needs to be: creon 2 90 caps 51119999119 creon 2 90 caps 51111111111etc etc.any help???????

Answer:Excel: moving column data into new rows??

HiHere is a macro that copies your data from a worksheet named "Source" to a worksheet named "Destination".Each code number is on a separate line. along with the name in column A and the value in column B.(From the data sample you posted it appears that there is a value (2 in each case) following the product name and before the code number - if this is not the case, please confirm which column always contains the first code number.You will need to name the Source worksheet "Source" or else edit all instances of "Source" in the macro. You will need to have an empty worksheet named "Destination" - the macro does not create it and won't work without it.To run the macro, I suggest you add a button to your source worksheetFrom the Ribbon select Developer (If it's not visible go to the Office Button, select Excel options at the bottom and select the Popular tab and check the 'Show Developer tab in the Ribbon' box)In Developer - Controls select Insert and choose the button icon.Draw the button on the worksheetIn the 'Assign Macro' dialog box select 'New'In the code window that opens enter this:Option Explicit

Sub Button1_Click()Dim rngStart As RangeDim rngEnd As RangeDim rngCell As RangeDim rngDestStart As RangeDim intDestOffst As IntegerDim intColsToMove As IntegerDim n As Integer

Hi Experts,I have an Excel sheet with the following data:A1: Name1A2: Company1A3: Address1A4: Telephone1A5: A6: Name2A7: Company2A8: Address2A9: Telephone2A10: Fax2A11: Email2A12: A13: Name3A14: Company3A15: Address3A16: Emailand so on..There's always a blank row separating each block of data. I want to arrange these data into rows. Any idea on how to do it faster? Doing the copy+paste special+transpose seems to be very tedious. Looking forward for your suggestions.Thanks in advance!

Hi,I have an excel sheet with 3 columns. What I want to do is, I need to be able to find value in column 3, that corresponds to a value in column 2. The value in column2 should match the value in col 1.

How do I...Have an Excel table with dates in A (col 1) and financial data in column G (col 7). I wish to enter 2 dates into a macro and from these find cell references to enable me to eg print partial table, display charts etc.There may be a simple way to do this but I have written a dummy macro below to achieve this. Can use this to return rows / column data for the dates but not do anything with this. For simplicity I have just tried to highlight the range chosen but excel does not recognise my attempt (last line before end of macro). NB I have tried numerous variations on the theme. Msg boxes etc are used for diagnostics and would be removed from code.Sub dateTest()'' Select a range depending on Date''VariablesDim D1 As DateDim D2 As DateDim R1 As SingleDim R2 As SingleDim C1 As SingleDim C2 As Single'find D1 (first date)D1 = InputBox("Enter Start date")Range("A4").SelectDo Until ActiveCell.Value >= D1 ActiveCell.Offset(1, 0).SelectLoop'if specified date does not eexist go up one rowIf ActiveCell.Value > D1 Then ActiveCell.Offset(-1, 0).SelectElse'do nothingEnd IfR1 = ActiveCell.RowC1 = ActiveCell.ColumnMsgBox "The First Row is " & R1MsgBox "The FirstColumn is " & C1'find D2 (second date)D2 = InputBox("Enter End date")Do Until ActiveCell.Value >= D2 ActiveCell.Offset(1, 0).SelectLoop'if specified date does not eexist go up one rowIf ActiveCell.Value > D2 Then ActiveCell.Offset(-1, 0).SelectElse'do nothingEnd IfActiveCell.Offset(0, 6).SelectR2 = ActiveCell.RowC2... Read more

Answer:I wish to return row/column references from Excel data

First a posting tip:Please click on my signature line at the bottom of this post and read the instructions on how to post VBA code in this forum so that it is easier for us to read.In addition, you might want to check out this How-To as a means to help you troubleshoot your code:http://www.computing.net/howtos/sho...As for one of your questions, this line fails because the syntax is wrong:Range("C1&R1:C2&R2").SelectSince C1, R1, etc. are integers, you should use the Cells method to refer to the Range:Range(Cells(R1, C1), Cells(R2, C2)).SelectFinally, I would use the .Find method to directly find the dates instead of looping through Column A. I would also use the Application.InputBox method instead of just InputBox since that will allow you click on the date cell instead of manually entering it. A manual entry will still work assuming the user doesn't make a typing mistake, but clicking the desired cell is much safer. The code below should work regardless of which date you enter/select first.This code is pretty basic, with no error checking, etc. but it seems to get the job done.Sub DateRangeSelection()Dim D1 As Date, D2 As DateDim startDate As Range, endDate As Range'Get Start date from user D1 = Application.InputBox("Enter Start date")'Get End Date from user D2 = Application.InputBox("Enter End date")'Find dates in Column A With Columns(1) Set startDate = .Find(D1) Set endDate = .Find(D2) End With'Select the range Ra... Read more

I have a similar issue - however the part that makes my issue a little more complicated is there is not a uniform length for the text to be extractedExample [email protected][email protected][email protected] What I want is to pull all information from between the @ and the . to a separate column for say company name. So basically to come back with place1place2groupabcdIs this possible?Thanks

I would like to find duplicates in a column for fields that contain "_" only and place on new sheet in column A. If found, I would like to then get the data in the adjacent field only if it I not blank and put the first instance value of adjacent column in Column B on new sheet, second instance Column C, etc. Probably no more than three instances. I have an example spreadsheet that has sample data and a results tab of how I would like the valid data to look. I am not sure where to start on coding it and which formula would be the right ones to use. I don't necessarily want the exact code but some guidance on the steps to take and formulas to try.

please can you help me in moving data with duplicate names to have the 2nd and 3rd email address moved to a new column and delete the row

need an excel macro to automate this as i am working with a large number of records like this in one excel sheet. i.e, i want to select the sheet and move data in every 2nd row to the columns in row above at the end of columns in that row.

Answer:Excel Macro to Move data in Rows to column

I'm unable to understand the entire requirement, but for moving the records with duplicate names, I've written few lines. This will cut the duplicate record rows (Consider "Pupil Forename" for removing)from the "Report Data" and will post into "Sheet3". Before running this macro please insert "Sheet3". Let me know your requirement, hope I will be able to resolve it out. Thanks!

Okay let us assume in C1 I want a single formulae that wil:a: count the rows that have 'Open' in column Ab: sum the corresponding values in column b

With this example, my visible value in C1 should be 35. I am hoping to not have to 'duplicate' data in another column/area and the sum that new column (such as copying only the 'B' values that have "Open" in the 'A' column and then running a sum off that new column....

I am sure there is an easy way to do this but I keep getting zeros. I have been trying this:=SUM(IF(A2:A5000="*Open*",B2:B5000,0))...

I have one other formula in D1:=COUNTIF(AF1:AF5000,"*Open*").. which displays 3....

Could you help me with an excel macro to automate this as i am working with a large number of records like this in one excel sheet. i.e, i want to select the sheet and move data in every 2nd row to the columns in row above at the end of columns in that row.

I have a spreadsheet with 14 columns. Twelve of the columns contain categories of data that correspond to the entries in the other two columns. I need to change the list so that each item in each category occupies its own row, as follows:Eg.From this:col1 col2 category1 category2 category3 etc...aaa bbb $55 ccc ddd $44 $66To this:col1 col2 category amountaaa bbb 2 $55ccc ddd 1 $44ccc ddd 3 $66Can anyone assist?Many thanks,jeannie

Answer:Excel macro - column data to multiple rows

You could write a macro to do this, but if it's a one time excercise, it might be easier to do it manually. Turn on filters, one by one for each of the category columns; choose non-empty then hide non-relevant columns, copy and paste the results to a new sheet. Let me know if it requires further explanation (or a macro).

Hi, I am new here. I need help to write macro/code which can submit query in the application status feedbox at http://124.124.193.235/eregister/eregister.aspxand click on SUBMIT. From the results, i want to copy STATUS data to excel sheet.

For example query numbers are 1274032, 1460986, 1522002 in the excel column.

For 1274032 the result page shows STATUS as REGISTERED. I want REGISTERED copied to adjacent cell of 1274032 cell

Please help me.

Answer:Solved: How to pull data from webpage(.aspx) for a excel column?

I need help to validate that the data in excel column is always a 4 characters alphanumeric only. eg: abc1 ADG2123R1we22WER3ER3etcKindly help in this by giving function to accomplish this check.thanks in advance.

I am trying to standardise data outputs. Therefore I have created a userform in which i can fill in my data in multiple text/comboboxes. Whay I want to do is, using a commandbutton, copy the values to a seperate excel sheet. There I want the value to be copied to the first available empty row.

For instance:

I have a textbox (textbox1) called 'name'. I can copy the value to a specific cell using Range("A2") = TextBox1 'test_id

What i want is to copy the value from textbox1 to the first available free cel (preferrably the first free row) in column A.Cheers,Xcptpack

In Excel 2007, I have a spreadsheet with 14 columns. Twelve of the columns contain categories of data that correspond to the entries in the other two columns. I need to change the list so that each item/amount in each category occupies its own row, as follows:

col1------col2------category------amountaaa-------bbb------2-----------------$55ccc-------ddd------1-----------------$44ccc-------ddd------3-----------------$66I know how to do this manually, but it needs to be done frequently on many different sheets. So, I need a way to do it automatically.

Many thanks for your help jeannie

Answer:Excel macro - change column data to multiple rows

I have your data mapping from A:N to P:S, with no headers. In case that's wrong, make sure your work's saved before trying this.

I&#8217;m trying to work out how I can look at one column in a spreadsheet and display the text in another field but without a load of blank cells in the results column. Let me give you an example.

On Sheet1 In column A are road namesIn column B the number of building on that roadIn column C the last date and work was doneon the roadIn Column D is an =IF formula thatbasically gives a result of 1, 2, 3 or 4.On another sheet I want four lists

Sheet 2In column A is a list of road names thathave a value of 1 in Sheet1 column DIn column B is a list of road names thathave a value of 2 in Sheet1 column DIn column C is a list of road names thathave a value of 3 in Sheet1 column DIn column D is a list of road names thathave a value of 4 in Sheet1 column D

I can do this with a simple IF statement in columns A,B,C & D on sheet2 and drag it down but there are a couple of problems with this method. 1, If the value in sheet1 doesn&#8217;t match I get a blank cell on sheet2 (as there should be) but this leaves a large number of cells with no value. I need a list in each column A,B,C & D onsheet2 without any blank cells.2, I&#8217;m going to be pasting different data into columns A,B & D on sheet 1 a number of times and the number of rows in will be different each time.

I remember seeing something before thatinvolved using the IF and COUNTIF function in a formula but not sure how.Any help would be appreciated

Answer:Excel 2010 separating data from one column to multiple columns

should be able to use an array formula - so use control + shift + enter key and the formula gets { } brackets

Code:

=INDEX(Sheet1!$A$2:$A$14,SMALL(IF(Sheet1!$D$2:$D$14[COLOR="Red"]=1[/COLOR],ROW(Sheet1!$A$2:$A$14)-ROW(Sheet1!$A$2)+1),ROWS(F$4:F4)))where you change the red =1 to =2, 3, 4 for different columns

I am trying to hide rows of my worksheet that show a date in the completed column.The orders that are complete have a date in column F up to this point I highlight them green so I know they are completed but it would be nice if they highlighted green and were hidden when a date was entered into the completed column.Can someone assist me with creating a macro that will do this?I am very new to macros so please forgive my lack of knowledge.

I have attached a sample of what I am doing however inf has been changed.

I am using Excel 2007

Thank you,

Answer:Solved: Macro for excel 2007 to hide rows based on any data entered in a column

Hi, welcome to the forum,

I put some simple code in the Sheet's vba

Make sure you allow macro's to be run

Just enter a date in the last row of column F for testing.

For the other green rows, just click in column F and update the date by retyping it

i have a row of cells under the titles 'monday' to 'sunday' with digits underneath saying how many hours were worked on those days. however, in the cells which say the hours worked each day i would also like to include overtime hours as a seperate digit but still remain in the same cell under the correct day

e.g. under 'monday' i might write '6(+3.5)'

'6' being the normal hours worked, and the value in the brackets being the overtime.

however, id like these numbers to be calculated seperately as i have a column which gives a total normal hours worked and another column which says how many overtime hours were worked

so basically, in one column id like a formula which would work like:

=sum(first number in cell a1 + first number in cell a2 etc..)and in another column id like a formula which would work like:

=sum(second number in cell a1 + second number in cell a2 etc..)

how can i do this?ps. im sorry if this has being asked before but i dont know how to word my search query cuz i dont know what this is called

Answer:Excel 2003: Formulas which use 2 seperate bits of data from a single cell

Hi, I am hoping someone can help me with a macro for Excel 2007. I wish to "reverse" the following macro, so that it sends the contents of an entire column (in this case the second column) back to the previous column as Cell Comments, without interfering with the contents of the cells.

Sub CopyCommentText()Dim CmtText As StringDim CmtCount As Integer, i As IntegerDim CommCell As String

Hello,I have a very large spreadsheet of data, 365 columns and 290 rows. I would like to convert the data into 1 very long column, with all of the data consecutively listed. Is there a function that allows me to tell the formula to look to the first line of the next row of data, and copy down from there, and then go to the next column, etc? Right now, I'm just referencing the first cell in each column, dragging down to fill the 290 following cells, and then referencing the next column. It's really time consuming because I have 48 of these spreadsheets. Any suggestions would be greatly appreciated!

Answer:Solved: How to move data in a series of column into one column

Hi,

if you name the range of 365x290 cells as, for example, 'MyRange', then the following (downwards extendable) formula should work:

What I want is a Macros Program that can put all Spanish words (red color) in Column B, all Italian words (blue color) in Column C, all German words (black color) in Column D, and all Papiamento words (green color) in Column E.

I have a large spreadsheet where I need to search Column B for any cell containing the word "County" and if a cell is found, move that cell over to Column A. The spreadsheet has more than 10,000 rows, so I don't relish the thought of doing this manually.

The cells I am pulling out have more than just the one word. Example: Column B contents: County: MOUNTRAIL State: NDThe recurring word is "County". Any cell in Column B needs to be moved to Column A without comprimising the rest of the document.

Any suggestions?

Thanks!

Answer:Excel: Need to search text in column B and if found, move to column A

You could use the following to either copy the contents of Column B into Column A or show a blank if the word "County" is not in Column B:=IF(ISERROR(FIND("County",B2)),"",B2)You would place this formula in A2 (assuming you have column headers) and copy it down the column to get either a blank (if County is not in the cell next to it) or the contents of the cell in column B. Then, you could select all of column A and do Copy and Paste Special > Values to paste the results of the formula and get rid of it.

I need some help. I am trying to come up with a formula that counts the times that a "string" occurs based on a value in another column.....Example:Everytime column a = "Jones", check if column e = "tom".....if so count it.

Answer:Counting strings in one column based on Condition in another column in Excel

The EASY way to do this is to concatenate columns A and E using (for instance) this formula:

I have a spreadsheet that I would like to give to both English and Spanish speaking workers. Is there a way to convert the English text in column A to Spanish in column B? I will be changing the text in column A on a weekly basis and will need to translate then distribute to all workers.

I have discovered some disconcerting behavior in how excel 2010 sorts. I have information in a worksheet that gets added to everyday. Once a week that information gets copied in bulk and pasted into a new sheet in the workbook to get sorted. On a straight copy paste operation some of the information that is in columns bfore the sort column are changed from their original values. When I copy then paste special values and formatiing this does not occur. Has anyone else observed this behavior and if so does anyone know a better fix?

BFTried to replicate this in my Excel 2010 without success. Copied cells Sheet1/A1:A12 to Sheet2/C1:C12 with data in preceding columns A & B. On selecting 'Sort' for C1:C12 I get a window informing me there is data next to my selection and since it is not selected will not be sorted. There are then two options: Expand selection or Continue with current selection. Selecting second option sorts data normally.

I have a very large spreadsheet with thousands of rows and about 20 columnsCol A has either of 4 values North, South, East, WestThe other Cols have numbersI want to get a total for North, South, East, West for Col B, C etcIs there a formula I can use something like (if Col A = North , total ColB is )Don't want to keep resorting by Cola and putting in SubtotalsHope I have explained it well enough

Thanks

BrianDublin, Ireland

Answer:Excel Total in a column based upon value in another Column

yes you can use SUMIF()

SUMIF(A:A, "north", B:B)

OR if you put the values North, South etc into a new column - Say Z2,Z3,Z4,Z5

SUMIF(A:A, Z2, B:B)and copy down

if not - please upload a samplePlease upload a small clean sample of your data / workbook (not a picture) to the forum, Make sure you have removed any private information, remember this is a public forum and so available to anyoneWe would like to see an example of your data and also a manual mock up of the expected results you want to achieve.

If I enter a name into cell J1 I want to check for this name in column H then for each row that is found on I would like to sum column B and output the result to cell J2. E.g. If I enter Paul into J1 I would like to search for this name in column H. For every row this is found on I would like the B cell for that row to be included in a sum of which the result is output to cell J2.

Is this possible and if so does anybody know how?

CheersPaul

Answer:Solved: Excel - Search for value in one column, sum a different column

Excel 2010I have a worksheet with address data arranged as one long list, with the title in Column A and the info I need in Column B, like so...A BName: Bob JonesAddress: 123 Main StreetCity: Northshoreetc...How can I extract and arrange the info in columns, like so...?A B CBob Jones 123 Main Street Cityetc...Thanks in advance for your kind assistance.message edited by SMoran

Answer:How to Extract Address Data from an Excel Worksheet?

As an alternative to Mike's fine suggestion, enter these formula in C2:C5C2: =B2C3: =B3C4: =MID(A7,FIND(":",A7)+2,LEN(A7))&", "&MID(A8,FIND(":",A8)+1,LEN(A8))&" "&MID(A9,FIND(":",A9)+1,LEN(A9))&" "&MID(A10,FIND(":",A10)+1,LEN(A10))C5: =""(C4 was stolen from Mike)Next, select C2:C22Next, grab the lower corner of C22 and drag downward.Assuming (as Mike noted) that each block of data contains the same number of lines, you should get something like this next to each block of data:360 Sign Group Inc.6525 Santiago CourtApollo Beach, FL 33572(A Blank Cell)Next:Select Column CPress [F5].In the resulting Go To dialog box, click Special.Click the Blanks option and click OK. Doing so highlights the blank cells in the selected range. Right-Click, Choose Delete, Choose "Shift Cells Up"You should end up with this:360 Sign Group Inc.6525 Santiago CourtApollo Beach, FL 33572 AAA Mobile Auto Glass IncP O Box 10574 St. Petersburg, FL 33733etc.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.message edited by DerbyDad03

Hello,Have a list of consolidated data in column A and a longer list of the same data in column B, that's connected to an email list in column C. I'm trying to match the data in columns A & B, to connect the data in column C to column A. Can anyone help?Example:Column A Column B Column C123 123 [email protected] 124 [email protected] 125 [email protected]

Answer:Trying to match column A to column B + C data

I'm not clear as to what you are trying to do.Is the example you provided the input or output of your task?Before you post anymore data, please click on the following line.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

I have just entered all my friends' names, adresses, etc into an excel spreadsheet to use for labels.How can I create a new phone directory from that data for me to print out to replace my delapidated phonebook?

Answer:Creating a new address/phone directory fron excel data

BagelAnne, do you mean that you just want to Print out the Spreadsheet?Or use Excel to find their addresses?

For some of use, reading a two-column PDF on a table is torture.My vision is very low. Using a computer screen and the visual aids available, I can get by. But going to a conference or public meeting is a problem. The only thing one can take into some places is a small tabletTwice a week I go to a local congregation and use either my tablet of oversize print outs to follow along with the lessons. The means I have to do some manhal editing ahead of time to have a document I can easily read.Some materiel is in PDF format on a web site. If it is just on or two pages, I edit it by hand. But a lot of the material is in book form and we do about 16 pages a week. So I depend of getting the PDF in an oversize format. But it is not always available. But a standard two column format is available.Here is my question. What program or service will convert dual column PDF to a single column and also increase the font size?Trying to increase font size in a dual column document results in a mess. Single column behaves the way you expect.Articles I have found make it seem very hard to automate. Any ideas are welcome.

Answer:PDF conversion. double to single column

Is there a way that the owner of the PDF can send you a specially sized version or a word document instead of the PDF in which you can select all and simply increase the font of the document?Years ago one of my clients had poor vision and used the magnifying glass in Windows, but this can be a pain because you have to move it around as your reading etc.

For some reason, the list of 'All Programs' in my Start menu is presented in multiple columns. It has been this way since I installed WinXP Home and I have not been able to find the configuration menu that I need in order to get the list to display instead in a single column, as was the case when I ran Win98.

Microsoft Word 2000 will not default to a single column when opening a new, blank page. I've checked out all of the options, properties, and toolbars, but cannot locate an area to change the column default settings. I've reinstalled a fresh copy of Office 2000, but Word still defaults to two columns. What am I missing?

first i will select some range of cells then if i run the macros it should ask for the word if i enter the word then the system should search for the word in the column c in the selected range and it should higlight the words.Please give me the program for this.

Answer:how to find the word in single column in a selceted cells

Here are some to try. Have you tried Ctrl+F?InfoRapidhttp://www.softpedia.com/get/Intern...http://www.softpedia.com/progScreen...http://www.inforapid.de/html/search...wReplacehttp://www.softpedia.com/get/Office...http://www.softpedia.com/progScreen...http://www.sharktime.com/us_wReplac...DocFetcherhttp://www.softpedia.com/get/File-m...http://www.softpedia.com/progScreen...http://docfetcher.sourceforge.net/a...Swish-e http://swish-e.org/download/index.htmlhttp://swish-e.org/docs/readme.html...http://swish-e.org/support.htmlhttp://swishewiki.com/sw/index.php/...Mo-Searchhttp://www.softpedia.com/get/Deskto...http://www.softpedia.com/progScreen...http://www.meauxsoft.com/21701/inde...Filehand http://www.softpedia.com/get/Deskto...http://www.softpedia.com/progScreen...http://www.filehand.com/description...Batch Replacer for MS Excel http://www.softpedia.com/get/Office...http://www.softpedia.com/progScreen...http://www.replsoft.com/index.html

excel sample dataHi,I need a serious help from you guys, i have a data where i have emp number, emp name and emp dob, along with sales values month on month. however i have the first three columns data in row and months i have in columns... i need to have those months along with their figures on the rows adjacent to the existing emp name and emp number.Have attached a sample data in excel which can clarify my issue more.

Answer:Copy some column data & append in new rows wth existing data

These formulas may need some modifications to match your spreadhsheet, but the concept should be valid. The main thing you will probably need to deal with is the offset values for the ROW() function. The formulas below are based on your example, so that's why the offsets that I used work.Let's say you start with this: A B C D E ... O1 Emp_No Emp_Name DOB Jan Feb ... Dec2 200012 Rajesh 1-Jan-99 300 200 ... 1113 200112 Vijay 1-Feb-98 222 333 ... 222...9 Emp_No Emp_Name Months Value 1 - Enter this in A10 and drag it over one column to B10:=INDEX(A$2:A$3,CEILING((ROW()-9)/12,1))Now drag both of those formulas down to Row 33. You should have 12 rows for each of the 2 employees.2 - Enter this in C10 and drag it down to C33:=IF(MOD(ROW()-9,12)=0,"Dec",INDEX($D$1:$O$1,1,MOD(ROW()-9,12)))You should get a set of 12 months (Jan - Dec) for each employee.3 - Enter this in D10 and drag it down to D33:=INDEX($B$2:$O$3,MATCH(B10,$B$2:$B$3,0),MATCH(C10,$B$1:$O$1,0))This should return the monthly values for each employee/each month. Let us know how that works out for you.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Match data in spreadsheet by sorting. Have a spreadsheet I need to import into accounting program. Amazon uses ASIN numbers and everyone else uses ISBN numbers. I have a spreadsheet with all the ASIN and ISBN numbers and book title listed along with all the data listed in columns for each book needed for the accounting program. When I receive a sales data spreadsheet from Amazon I then have four columns of data I need to match with the list of ASIN and ISBN and tile in the accounting spreadsheet. However, not every book sells that month so there would be blanks. When I sort the data doesn't always match up.

Answer:Sort & Match one column of data with columns of data

AlexandriaLet's see if I can explain this better:=VLOOKUP(ItemToFind,RangeToLookIn,ColumnToPickFrom,SortedOrUnsorted)ItemToFind is the first ASIN Number in your list on the current sheet, Column F Row 2 which we use to cross reference.RangeToLookIn is the array of cells which contains all the data you will need, in this case from: Amazon Sheet Column B Row 2 (which is the ASIN cross reference) to Amazon Sheet Column J Row 4ColumnToPickFrom is dependent on the data you wish to extract from the above array. If you want the Price, then starting at Column B on the Amazon Sheet, which is the 1st column in the array, you count the number of column to the Price column, which (I think) is column G, which is 6 columns to the right.SortedOrUnsorted The Sorted/Unsorted is whether the column headings are sorted. 1 = TRUE for Yes, 0 = FALSE for No, just use 0 = False.So your new =VLOOKUP() will look something like:=VLOOKUP(F2,Amazon!B2:J4,6,0)The $ signs are used to anchor a cell so when the formula is copied the cell reference does not move.Now, since we always want the ASIN column, we use $F2, which anchors ONLY column F not the row number. This way we can now copy it down.And since we need to search ALL of the array on the Amazon sheet, we anchors both Columns and Rows, like $B$2:$J$4 so nothing will move whenwe copy the formula.So the formula to get the Price now looks like:=V... Read more

Hello All, hopefully you can help, I have visited many forums and found the below code that works, but I have tried tweaking for my specific needs and I am unable to:

Basically it is allowing me to use any data validation cells in column 2 as multi select cells with comma separation (i.e. select1, select2, select3) , problem is that I have a few data validation cells in column 2 that I only want as a single select.

I currently maintain 15 websites, for various different DIY products. Each website has its own email address. We have some one who deals with all email enquiries. We are noticing large amounts of spam. It has been suggested that we use a single email address for all 15 websites.

Other than its going to be a nightmare editing all of the websites to 1 email address, what other pros and cons are there for this idea.Thanks

Michael

Answer:Multiple Email Address' or a single address

not sure how this will reduce the spam necessarily.

a good spam filter may help.o you know which email is giving you the most spam

I have about 5 websites and emails and get spam in most of them, infact one email I use most of the time for webuse and that gets most of the spam

I want to know how i can export the data from Access to excel using Access VBA for the specified sheet using data linkage with access database. Like we used to do it manually in excel as external data from access.

So that the excel size won't be that big and also it saves processing time.

Answer:Solved: Access data export into Excel as the data linked to excel.

I am running a new build PC chips841, 2000xp,60 seagate,Me,512 ddr2100. All is ok except I don't know what the right setting should be for the Ram CAS in BIOS. 2T seems to be default. What if I went tyo 2.5T or 3T . Would it improve performance?Thanks.P.sWell impressed with ease of build with 841 board!

I'm trying to load a table that I dumped from the original database. I'm moving my domain to a new host and need to populate the new database with the data from the old host. I dumped it okay and saved the file. When I try to upload it, I get an error where the single quote is in the a field called comments. phpmyadmin thinks it is the end of a field but it isn't.

So my question is, how do I upload data to a table with single quotes embedded in a field? Before you tell me to change it, there are a ton of records so that would be a task. An example of the insert statement is:

INSERT INTO calendar_tbl (rec_id,entry_text) VALUES('44','Meeting at St. John's Church');

It is thinking the single quote in John's is the end of the field. I have a few databases where this is going to be the same problem and need to move them all. Thanks for any help.

Answer:Can't upload data with single quotes in data

bayridge said:

INSERT INTO calendar_tbl (rec_id,entry_text) VALUES('44','Meeting at St. John's Church');Click to expand...

Take a look at the following link if you are using mysql: http://dev.mysql.com/doc/mysql/en/String_syntax.html

I think it should be something like INSERT INTO calendar_tbl (rec_id,entry_text) VALUES('44','Meeting at St. John\'s Church');

If that above insert command works and you want a quick and dirty solution, I suggest a few search and replaces. Search for (' and replace them with something like OPEN_QUOTE, search and replace ', with CLOSE_QUOTE, and search and replace '); with FINAL_QUOTE. Then replace ' with \'. Now to add the quotes back. Replace the OPEN_QUOTEs with (', CLOSE_QUOTE with ', and FINAL QUOTE with ');.

I'm trying to make a chart to track the workouts of a group of people over the course of the summer. I want the first column and the last column to be fixed, but i want all the columns in the middle to scroll. How do I fix the last column (their total points) when I don't know how many columns there will be between now and the end of the summer? i only want 4 or 5 columns showing between their names and the total column at the end.

Answer:fix first and last column in excel

Directly from Microsoft Online Help..."You cannot freeze rows and columns in the middle of the worksheet."Why not freeze columns A & B for their Names and Points and scroll the rest of the worksheet for the other columns?Maybe if you gave us some more details as to what you are trying to do with the columns in the middle, we could come up with some other ideas.

I have a cell that I want to always reflect the value of the last entry in another column. How can I get the cell to return the last number of the column, it is always being added to so will move further down as more data is added.I have looked through the functions but can't find one that appears to give me what I am after. Would prefer to use a function and not a bit of VBA or a macroCraig

Answer:Excel - Look up last value in a column

I know of no function but assuming that the cell you wish the information to be in is A1 and that the column is B then the following small bit of code placed in the code of the worksheet itself will do the jobPrivate Sub Worksheet_SelectionChange(ByVal Target As Range) [A1] = Range("B" & Cells(Rows.Count, 2).End(xlUp).Row)End SubJust change the A1 to the cell and the figure 2 to the column number.HTH

In an Excel spreadsheet I have a column which contains an identifying symbol such as M or O. What is an easy way to change all the M's to, say, O's without doing it one by one or disturbing anything else?

I have a spreadsheet with a column of multiple data points. What I need to do is take every 360 column entries and put them into a single row. I know how to 'transpose' paste, but due to the amount of data, I need a macro to actually do the work for me. I have no experience with macros, but didn't really figure that this was a very detailed one. EX.A-------B----C----D--E--F0-------6----------6--7--2180----7----------4--5--1360----2----------9--8--30-------4180----5360----10-------9180----8360----3

Answer:Column Data to Multiple Rows

Sorry, but I don't understand your example. Is that a "before", an "after" or a "before and after"?What do you mean by "take every 360 column entries and put them into a single row"?A posting tip:Please click on the following line and read the instructions on how to post example data in this forum. Thanks!Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Hello, I required help to resolve following detailed issue in excel sheet I have one query for Excel sheet I have in one row A B C D E F G H which I want to transfer in to two columns like A B C D E F G H here critical point is A & B are related terms which need to be in one row and adjacent columns is must Pls help me for resolving this it will be great help since I need to do it for 1000s of parts Awaiting for fast reply Hemant

hi,I have 2 column data to compare with another 2 columnsEg: there is a patient number in column A and Page number in column B of about 1000 rows and other two columns also consist of the same- column C and D. i would want to compare the column A,B data with that of Column C,D to take out the odd ones.Is there any Macro that can help me out in this comparison

Answer:Comparing 2 column data with other 2 columns

How is the data arrainged?Are the Patient Nmbr and Page Number in A & B on the same row as the Patient Nmbr and Page Number in C & D?If they are then try this:=IF(ISNA(AND(MATCH(A2,C2:C10,0),MATCH(B2,D2:D10,0))),"No Match","Match")MIKEhttp://www.skeptic.com/

this forum has been awesome in helping me do my project. hence im back, seeking answer.

im currently doing in MS Access, a Report about profit and loss. this report obtain its information by Query, matching the specified information gathered from a Table.

the following is a screenshot from the Reportas you can see, the 2 informations i want to use are given in the query (named: items bought & items sold), my question is, how to reference those 2 different data, but within the same column so that i can make an iif expression for the profit and loss.

This may have been asked/answered Previously but I didnt see an answer, I know its possible to Hide/unide column's But I need to do it based on if there is a null Value. IE there are 9 columns only 1 has manditory Value, Name. In my query that pulls the data (a search Box) it pull records that dont have data in the Location field. so in my Subform frmSearchdata I want the location column hidden. Ive tried several ways of coding it in VBA and I dont get errors but it doesnt hide the columns either. Any tips?

Answer:Hiding a Column In a data Sheet

I see this is not your first post and of course we can all make assumptions but you know what the assume stands for.

it makes an a s s out of u and me

Leaving that for what it is, I could assume you're talking about Excel but ... further more no version of the (unknown) application mentioned which is also quite relevant due to added functionality

I'm looking to buy new DDR3 memory but I am totally puzzled by the CAS Latency and the numbers....

It seems like the difference between a stick of DDR3 with 11 11 11 and 7 7 7
is only measured in nanoseconds, so how would the end user, we humans actually notice the difference between these specifications, or in fact can we at all.

I'm not a complete geek so please try and explain this in plain English if you can. Thanks!

Hope this will help some.CAS latency - Wikipedia, the free encyclopedia.As it states the lower the latency the better.As for being able to tell in real world synarios...well that's very subjective gaming being probably the best place to see a performance gain.Fabe

Is there any way to add new fields, such as mobile phone number, to the default columns that show up when you open up the built-in Windows address book in addition to its default of name, email, business, and home phone numbers?

Answer:Putting mobile numbers as a default column in address book

The business, and home phone columns can be configured to another item type such as mobile phone. Right click the column heading and select the option. You can only have 4 columns and you cannot add an item type. I hope I anwsered your question.

Okay...I am taking a ton of data and placing it into excel spreadsheets. I need to make a "side-by-side" comparison of a few tests. I need to take a vertical cell that I have got vertical text in and put the entire COLUMN on a slant so that it is easier to read. Any ideas?

I am using macro for filtering items in E column, now i need to add some column before E somewhere. Filtering columns has been moved to F now macro is not working. How can i resolve this problem without making again macro. Suggesstion pls

Answer:By using Macro in the Column E - Excel

Hi,The simple answer is to edit the macro to point to the new column. You don't have to write a whole new macro.Alternatively if the filter column has a unique header on row 1 then this macro will find it and apply the filter:Sub FiltTest()Dim rngFilter As RangeDim strFilterName As String

With Worksheets("Sheet1") 'find column to filter (in row 1) Set rngFilter = .Range("1:1").Find(strFilterName, _ LookIn:=xlValues, _ LookAt:=xlWhole, _ MatchCase:=False) If Not rngFilter Is Nothing Then 'filter column header found - use its column for the filter With rngFilter.EntireColumn 'change the filter criteria to match 'or use a reference to a cell containing the filter criteria .AutoFilter Field:=1, Criteria1:="B" End With Else 'filter heading name not found - display warning message MsgBox "Your filter column header: " & vbCrLf & _ strFilterName & vbCrLf & _ "was not found" End IfEnd WithEnd SubYou should be able to use the find part of this code with your existing macro.You can now add or remove columns and the filter will still work.Change the unique column header name on this line:'set unique filter column header text'change as appropriatestrFilterName = "Filter Data"Regards

I have a problem with an Excel spreadsheet, I cannot view the first column, A. I have tried highlighting the whole sheet and using Unhide. I have also tried hovering the cursor over the line between column B and the "select entire spreadsheet" button, but I don't get the two vertical lines you would normally use to adjust column width.Any ideas?

Hi I have a spread sheet which may become huge once and i cannot reveal it.However i would like to sort the spread sheet based on the coloumns, with a conditional "OK" in some occassions1. selected 2 coloumns, if data is ok/OK the the entire row has to be copied to another sheet2. on some occasions selected 3 coloumns has "ok/OK" then entire row has to be copied to another sheet3. Some occassions if the selected 2 or 3 coloumns have "ok/OK" && AND if the a coloumn "x" is marked as "I" then the entire row has to copied to another sheet.Please can i get help .. Please want a generic Macro so that i decide which coloumns to look for and what condition.I created a sample spreadsheet, not sure how to attach it here.Thanks in advanceNE

Answer:Excel Column Sorting

Hi,I have created a macro which will move rows of data based on values in several columns.The macro contains comments that should allow you to modify it.The code first tests two columns for OK or ok (you can add extra tests if required within each bracketed OR group). The two column tests are ANDed together so extra columns can be tested by adding further AND operators.For any row that meets these conditions it is then tested for the value in another column. The value in that other column is used to decide which worksheet to copy the row to. If there is nothing or not a specified value in that column, the row is moved to a default worksheet.You did not say how this macro is to be used. In this example, all existing data on the destination worksheets is cleared before the data is added. You could remove this initial code if each time the macro was run, the source data was new and needed to be added to existing data.If new data was added to existing data you would have to include a piece of code to ensure that rows already copied, were not copied again.The macro assumes that the source data is on a worksheet named "Source"The default destination is a worksheet names "DestAll"For this example if column G contains "x" the row is copied to a worksheet named "Dest1" and if column G contains "y" the row is copied to a worksheet named "Dest2".This logic can easily be extended to other destination worksheets, and the column(G), the values (x and y) and the destination (Dest1 or De... Read more