Computer Support Forum

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.

I've used it in the past to identify and fix everything from blue screens (BSOD's), ActiveX errors, corrupt files and processes, dll/exe/sys errors, recover lost memory, Windows update problems, defragging, malware removal etc.

You can download it direct from this link http://downloadreimage.com/download.php. (This link will automatically start a download of Reimage that you can save to your computer.)

Answer: Excel address data - single column to rown

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.

9 more replies
Relevance 91.43%

Hi Everyone,

Seriously need your help ><"
I am using MS Office 2010 and Windows 7

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)
 

3 more replies
Relevance 89.38%

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/

2 more replies
Relevance 80.77%

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.

2 more replies
Relevance 79.95%

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)

4 more replies
Relevance 77.08%

HI,I have the following datasetup in Excel 2007: A B C D Product Description Category Price1 B12 Base 12 1 102 B12 Base 12 2 203 B12 Base 12 3 304 B27 Base 27 1 155 B27 Base 27 2 256 B27 Base 27 3 357 B30 Base 30 1 408 B30 Base 30 2 509 ....if possilbe the new format should look like this A B C D E Product Description (Category) "1" (Categroy) "2" (Catagory) "3" 1 B12 Base 12 10 20 302 B27 Base 27 15 25 353 B30 ... Read more

Answer:Copy Row data to Column Data, Excel 2007

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

3 more replies
Relevance 77.08%

I have some date extracted from running a script:A: 1234B: 2345C: 3456A: 5678B: 6789C: 7890A: 2345B: 4567C: 6789I want to get it converted into table format:A B C1234 2345 34565678 6789 78902345 4567 6789Appreciate your response.

Answer:Converting row data fields to column data in excel

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 D
1 Heading1: Value1 Heading1 Heading2 Heading3
Now 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

8 more replies
Relevance 75.85%

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/

5 more replies
Relevance 75.03%

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)?
 

Answer:Excel column to rows - address information

In a perfect world there should only ever be one piece of data in a column. For example; Column A = Vendor Name, Column B = Street Address, Column C = Street Address 2 (Apt. # , Suite # etc), Column D = City, Column E = State, Column F = Zip. There shouldn't be any blank columns in between.

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

1 more replies
Relevance 75.03%

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/

2 more replies
Relevance 75.03%

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.

2 more replies
Relevance 74.21%

Hi,

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

a spreadsheet with dummy data would be useful to see
 

1 more replies
Relevance 74.21%

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

Answer:Excel: Amalgamating data to one column.

In E1 enter=B1&C1&D1and copy the formula down.

7 more replies
Relevance 74.21%

Can anybody tell me an easy way to extract a list of names in a column in Excel and put them into a Word format or plain text??

Answer:Extract a column of data from Excel

If you highlight all of the names in the column, "copy" them, open a new Text Document (Notepad file) and "paste", that should give you the list in plain text format.

3 more replies
Relevance 74.21%

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 B
1) 5 200
2) 4 140.2
3) 1 139
4) 3 107
5) 2 59.2
6) 6 -5.6
7)
8) 2 59.2
In 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/

4 more replies
Relevance 73.8%

Hi,

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 1040
Lesand 1099
Lesand 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.

Needsaccess
 

More replies
Relevance 73.39%

Hey Guys,

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.

- Castleheart
 

2 more replies
Relevance 73.39%

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 Range
Dim rngEnd As Range
Dim rngCell As Range
Dim rngDestStart As Range
Dim intDestOffst As Integer
Dim intColsToMove As Integer
Dim n As Integer

On Error GoTo ErrHnd

'turn off screen updating to reduce flicker
Application.ScreenUpdating = False

'set start of source data
Set rngStart = Worksheets("Source").Range("A2")
'find end of sourc... Read more

11 more replies
Relevance 73.39%

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!

Answer:Excel - converting column data to rows

Run this code against the data:Sub ColumnsToRows()'Determine last row with data lastRw = Range("A" & Rows.Count).End(xlUp).Row'Initialize Column and Row variables colNum = 1 rowNum = 1'Loop through data For nxtItem = 1 To lastRw'If there's data in Column A, copy it to B, C, D, etc. If Cells(nxtItem, 1) <> "" Then colNum = colNum + 1 Cells(rowNum, colNum) = Cells(nxtItem, 1)'If Column A is blank, Reset Column variable'and Increment Row variable Else: colNum = 1 rowNum = rowNum + 1 End If NextEnd Sub</pre.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

6 more replies
Relevance 73.39%

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.

Example

Input Data
Col1 Col2 Col3
1------2------A
2------7------B
7------1------C
5------9------D

The result should be
Col1 Col2 Col3
1------1------C
2------2------A
7------7------B
5------9------D
Please help me.
 

Answer:Excel :: compare 2 columns and get data from 3rd column?

6 more replies
Relevance 73.39%

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 Date
Dim 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

4 more replies
Relevance 73.39%

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

More replies
Relevance 72.57%

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.
 

Answer:Solved: VBA Excel Find Dup in Column and get adjacent data

8 more replies
Relevance 72.57%

If you check the file attached.

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!

Code:
Sub test()
Sheets("Report Data").Select
Dim trow As Long
trow = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row
For i = trow To 1 Step -1
If i = 1 Then
Exit Sub
End If
If Cells(i, 1).Value = Cells(i - 1, 1) Then
Rows(i).Select
Selection.Cut Destination:=Sheets("Sheet3").Range("A65536").End(xlUp)(2)
End If
Next i
End Sub
 

2 more replies
Relevance 72.57%

Okay - still very new to excel formulae so bear with me...

ColA.......Col B
Open..... 23
Closed... 7
Open......4
Open..... 8
Closed... 19

Okay let us assume in C1 I want a single formulae that wil:
a: count the rows that have 'Open' in column A
b: 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....

Thanks in advance

Ric
 

Answer:Solved: Excel - count and sum separate column data

=SUMIF(A2:A5000,"*Open*",B2:B5000)
 

3 more replies
Relevance 72.57%

I have a excel file like below.

Column1 |Column2|Column3|Column4|Column5|Column6|Column7
Row 1 EEEE 1 2 3
Row 2 4 5 6

I want to move data in row 2 (4,5,6) to Row 1 columns 5,6,7.

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.

your assistance is greatly appreciated.

thank you
 

Answer:Excel Macro to Move data in Rows to column

8 more replies
Relevance 72.57%

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

13 more replies
Relevance 71.75%

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.aspx
and 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?

16 more replies
Relevance 71.75%

Hi all,

I need help to validate that the data in excel column is always a 4 characters alphanumeric only.
eg: abc1
ADG2
123R
1we2
2WER
3ER3
etc
Kindly help in this by giving function to accomplish this check.
thanks in advance.
 

Answer:4 characters alphanumeric data validation check on one excel column.

Welcome to the forum

LEN(A1)=4 would give you the length, so you could combine it with another formula using AND to only allow letters and numbers.
 

3 more replies
Relevance 71.75%

Hi,

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
 

More replies
Relevance 71.75%

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:

Eg.

From this:

col1-----col2-----category1-----category2-----category3-----etc... up to 12 categories
aaa------bbb---------------------$55
ccc------ddd------$44--------------------------$66
To this:

col1------col2------category------amount
aaa-------bbb------2-----------------$55
ccc-------ddd------1-----------------$44
ccc-------ddd------3-----------------$66
I 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.

Sub test()
LastRow = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LastRow

For Each Cell In Range("C" & i & ":N" & i)
If Cell <> "" Then
DestRow = WorksheetFunction.CountA(Columns(16)) + 1
Cells(DestRow, 16).Resize(, 2).Value = Cells(i, 1).Resize(, 2).Value
Cells(DestRow, 18) = Cell.Column - 2
Cells(DestRow, 19) = Cell.Value
End If
Next Cell

Next i
End Sub

HTH (welcome to the board)
 

2 more replies
Relevance 71.75%

Hi all,

I have an excel sheet attached as an example. What I want to do is
take the data in each column and put them together into one
column.

In my attached excel sheet I would like to have the data in each
column look like this in one separate column each line for example 1-72-bk-1803

any help is appreciated....thanks
 

Answer:Excel question: how would you connect data in defferent columns into one column?

In E1 enter
=A1&"-"&b1&"-"&c1&"-"&d1
copy down
 

2 more replies
Relevance 71.75%

Hi,

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 names
In column B the number of building on that road
In column C the last date and work was doneon the road
In Column D is an =IF formula thatbasically gives a result of 1, 2, 3 or 4.
On another sheet I want four lists

Sheet 2
In column A is a list of road names thathave a value of 1 in Sheet1 column D
In column B is a list of road names thathave a value of 2 in Sheet1 column D
In column C is a list of road names thathave a value of 3 in Sheet1 column D
In 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

does that work out

see attached

if it works we can extend the range and cleanup the errors
 

3 more replies
Relevance 71.75%

Hi, I need a macro to use in Excel 2003 that will select all data in a column except the header row. At the moment i am using the following command which works well in most cases

Range("A1").Select
ActiveCell.Offset(1, 0).Select
Range(Selection, Selection.End(xlDown)).Select

The catch to this is that if the column contains only one row of or no data then the command selects the entire column! Eek, can anybody please help me to fix this?
 

Answer:Solved: Excel macro to select data in a column with a header row

Dim lastRow as long
LastRow = worksheetfuntion.Max(2, Range("A" & rows.count).end(Exlup).Row

The variable will contain the last filled row in column A, the Max function will make sure that it will at least be row 2 just in case the column is empty

Then all you need is Range("A2:A" & lastRow).Select
Or if you need more columns then Range("A2:H" & lastRow).select
 

3 more replies
Relevance 71.34%

I have an entire column of data that I need in one cell. When combined I need each piece of data to be surrounded by " " and a , between each one.

151
183
2001

results = "151","183","2001"
 

Answer:Solved: Excel Column of data place in one cell with "data", "data" results

ASAP's "Merge row data" ("Columns and rows" category) will do 99% of the work for you.

http://www.asap-utilities.com/

(edit: "entire column" -- do you mean full from top to bottom? )
 

2 more replies
Relevance 69.7%

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
 

3 more replies
Relevance 67.65%

ive never done formulas in excel before except for basic =sum stuff

im trying to make a wage sheet which calculates hours worked and pay

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

7 more replies
Relevance 67.24%

Hello, I've been fiddling with a udf someone on here designed for a specific question but I can't get it to work for my purposes.

So here is my situation:

Column 1 = Set1 (Values = either "true" or "false")
Column 2 RoutineName (text values)

I want to concatenate into a new cell all the values in column 2 that have a corresponding "true" value in column 1.

Any help on this greatly appreciated!
 

Answer:Concatenate cell data from column based on criteria in adjacent column

Which version of Excel are you using? If you have Excel 2016 you could use the TEXTJOIN function combined with an IF function to select the data you want.
 

5 more replies
Relevance 66.42%

I have a list of 39525 emails in column A and a list of 19909 emails in column B. If an email exists in column B that also is in column A I want it removed from column B. PLEASE HELP! THANK YOU!!!!!!
 

Answer:Solved: Excel 2010 - Remove duplicates from column B that exist in column A

9 more replies
Relevance 66.42%

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 String
Dim CmtCount As Integer, i As Integer
Dim CommCell As String

CmtCount = ActiveSheet.Comments.Count
For i = 1 To CmtCount
CommCell = ActiveSheet.Comments(i).Parent.Address
Range(CommCell).Offset(0, 1) = ActiveSheet.Comments(i).Text
Next i
End Sub ​
 

More replies
Relevance 66.42%

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:

=OFFSET(INDEX(MyRange,1,1),MOD(ROW(A1)-1,ROWS(MyRange)),(INT(ROW(A1)-1)/ROWS(MyRange)))

Jimmy
 

2 more replies
Relevance 66.01%

Hi Rollin_Again and everyone in this forum,

Hoping someone could help me in my problem.
I have a workbook in Microsoft Excel 2010 (Windows) of about 60.000 rows of words in different languages.

Some words have 5 different languages and some are only 4 or 3, all are written in Column A of the workbook.

Column A
English -------(in color Orange)
Spanish -------(in color Red)
Italian ------- (in color Blue)
German ------ (in color Black)
Papiamento ---(in color Green)
etc.

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.

Like for example:

Column A --- Column B ---Column C --- Column D ---Column E
English -----Spanish ----Italian ----- German --- Papiamento
etc.

Thank you very much in advance and I'll appreciate all the helps you could give me.
sukisuki
 

Answer:Transfer texts from Column A to Another Column, Excel 2010 (Windows)

9 more replies
Relevance 66.01%

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: ND
The 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.

Let me know if this is what you're looking for!
 

8 more replies
Relevance 66.01%

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:

=A1&E1

or

=A1&" "&E1 (puts a space between two words)

Then count the individual field. Make sense? Will it work for you?

------------------
~dreamboat~
Brainbench MVP for Microsoft Word
Brainbench
 

3 more replies
Relevance 65.6%

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.

Thanks in advance.
 

Answer:Excel - Translate English in Column A to Spanish in Column B

8 more replies
Relevance 65.6%

COLUMN A
5
5
6
9
COLUMN B
A
A
B
B

A=10
B=15
Hi there, I need help creating a formula that adds Column A, only if Column B = 'A'.
I then need to multiply that counted value by another cell.

Can someone please help me? I think I'm over-thinking this.
 

Answer:Excel: Count Values In One Column On Basis Of Other Column Value

=SUMIF(B1:B4,"A",A1:A4)

Where B1:B4 is where your A and B values are, and A1:A4 is where your numbers to sum are located.

HTH
 

1 more replies
Relevance 65.19%

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?

Answer:Excel 2010 sort issue data in columns prior to sort column change

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

1 more replies
Relevance 64.37%

I hid all but three columns and if I right-click on column A to insert a column, it becomes column B and no column is to the left.

Answer:In Excel 2007, how do I add a column to the left of a column when there is only 3?

I have Excel 2007
I put the cursor in column A, right click, and then insert.
Insert entire column. It appears to the left. Is this what you are doing

4 more replies
Relevance 64.37%

Dear all,

How do I sort a column with "#.#.#" according to descending values in excel, while keeping the other columns on the same row?

Number range is 0<= # >=100,000.

Example for 2 columns:
B 1.668.901
A 8.515.492
V 0.0.35
K 1.579.319
P 0.7.82
C 4.263.57
G 1.925.60
H 0.0.0
D 29589.292.235
L 0.29.834
J 1.870.904
M 0.635.5
S 0.0.355
X 0.0.5

Required Output sort only second column:
D 29589.292.235
A 8.515.492
C 4.263.57
G 1.925.60
J 1.870.904
B 1.668.901
K 1.579.319
M 0.635.5
L 0.29.834
P 0.7.82
S 0.0.355
V 0.0.35
X 0.0.5
H 0.0.0

Code can sort by ascending but does not pull the column.

Code:

Sub sortColumn()
Dim arrData As Variant
Dim i As Long, j As Long
Dim temp As Variant
'Range name is "ID"
arrData = Range("ID").CurrentRegion.Value

For i = 1 To UBound(arrData, 1)
For j = i + 1 To UBound(arrData, 1)
If getDesc(arrData(j, 1), arrData(i, 1)) Then
temp = arrData(i, 1)
arrData(i, 1) = arrData(j, 1)
arrData(j, 1) = temp
End If
Next j
Next i

Range("G1").Resize(UBound(arrData, 1), 2).Value = arrData
End Sub

Function getDesc(a As Variant, b As Variant)
Dim aWords As Variant, bWords As Variant
Dim i As Long
aWords = Split(a & "..", ".")
b... Read more

More replies
Relevance 64.37%

I have a very large spreadsheet with thousands of rows and about 20 columns
Col A has either of 4 values North, South, East, West
The other Cols have numbers
I want to get a total for North, South, East, West for Col B, C etc
Is 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 Subtotals
Hope I have explained it well enough

Thanks

Brian
Dublin, 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 sample
Please 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 anyone
We would like to see an example of your data and also a manual mock up of the expected results you want to achieve.
 

3 more replies
Relevance 64.37%

Hi,

I have a query but I'm not sure if it's even possible to do.

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?

Cheers
Paul
 

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

yes, SUMIF() should do that

i'll edit - and add an example

enter into J2

=SUMIF(H2:H9,J2,B2:B9)

see attached example file
 

1 more replies
Relevance 63.96%

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

7 more replies
Relevance 63.14%

I have my Plaxo Address Book in an Excel 2013 file. Is there another Address Book I can move the Plaxo data into (since Plaxo is shutting down)? Berniebs

More replies
Relevance 62.73%

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.

5 more replies
Relevance 62.73%

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?
 

1 more replies
Relevance 62.73%

In book 1, sheet 1, cell A1, you have the entry $F$1

In book 2, sheet 2, cell F1, you have the entry "apples"

Is there a way to have cell B1 in Book 1, Sheet 1 return the value "apples" from book 2?

Thank you!

markus
 

Answer:Excel - Is it possible to retrieve data from book2 with an address reference in book

='C:\[Book1.xls]Sheet1'!$A$1

open book1 and book2
Is there a way to have cell B1 in Book 1, Sheet 1 return the value "apples" from book 2?Click to expand...

In cell book1 sheet 1 B1 put
='C:\[Book2.xls]Sheet2'!$F$1
 

3 more replies
Relevance 61.5%

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.

6 more replies
Relevance 60.68%

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.

Can anyone be of assistance?
 

Answer:[Resolved] Start / All Programs / Single Column?

8 more replies
Relevance 60.68%

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?

Answer:Word 2000 single column default

Check the printer settings.Check the paper size settings.

2 more replies
Relevance 59.86%

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

2 more replies
Relevance 59.86%

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 ... O
1 Emp_No Emp_Name DOB Jan Feb ... Dec
2 200012 Rajesh 1-Jan-99 300 200 ... 111
3 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.

2 more replies
Relevance 59.45%

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

13 more replies
Relevance 59.45%

I have a group of email addresses that are lsted separated by columns. Here is an example:

[email protected], [email protected], [email protected],

When I import them into excel, they come out as one long string of rows.

Any ideas of how to fix that?

I want one long column.

bob
 

Answer:Importing comma delimted text file in a single column

Bob, are you telling Excel's Import Wizard that the File is Comma seperated?
When you say one column, can you show how you want your 3 examples to appear please?
 

2 more replies
Relevance 59.04%

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.


Code:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDV As Range
Dim oldVal As String
Dim newVal As String
Dim strVal As String
Dim i As Long
Dim lCount As Long
Dim Ar As Variant
On Error Resume Next
Dim lType As Long
If Target.Count > 1 Then GoTo exitHandler
lType = Target.Validation.Type
If lType = 3 Then
Application.EnableEvents = False
newVal = Target.Value
Application.Undo
oldVal = Target.Value
Target.Value = newVal
'Select Case Target.Address
'Case Is = "$b$16", "$b$17","$b$24"
'If Target.Cells = "$b$17" Or "$b$18" Or "$f$4" Then
If Target.Column = 2 Then
If oldVal = "" Then
Else
If newVal = "" Then
Else
On Error Resume Next
Ar = Split(oldVal, ", ")
strVal = ""
For i = LBound(Ar) To UBound(Ar)
Debug.Print strVal
Debug.Print CStr(Ar(i))
If newVal = CStr(Ar(i)) Then
strVal = strVal
lCount = 1
Else
strVal = strVal & CStr(Ar(i)) & ", "
End If
Next i
If lCount > 0 Then
Target.Value = Left(strVal, Len(strVal... Read more

More replies
Relevance 59.04%

Hi,
I want to merge two excel files into a 3rd one.

The data are in the files Book1, Book2 and the result in Book3.

I know that i can use VLookup to do the above. But what i have pasted is just an example. The real excel files contains about 9 columns with at many repeating row!!!

Any help will be much appreciated.
Thx
 

Answer:Excel - macro to merge 2 excel files using a common column

Hi welcome to the board. How are you going to treat the duble items, If you want to merge 2 files adn duplication occurs here must be one cell whihc is common to let's say Book1 and Book2.

Do you have a common value? Index? Whatever you call it?
 

3 more replies
Relevance 57.81%

Hello.

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
 

2 more replies
Relevance 56.17%

Hi,

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.

16 more replies
Relevance 55.35%
Answer:Whats is DDR column address strobe?

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!

2 more replies
Relevance 55.35%

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 ');.
 

3 more replies
Relevance 54.53%

Afternoon. I would like to build a query in Access 2010 to move some data from one field to another.

I only want to move some data though.
My fields are named mailadd1 and mailadd2
The data that needs to be moved is in mailadd1.

These are addresses. The first field should be the street address, the second should contain the city, state, and zip.

This is correct on a lot of the rows, but on about half of the 109K records, the data that should be in mailadd2 is in mailadd1 and mailadd2 is blank.

So I need to move the data from mail1 to mail2 IF mail2 is empty. In the following example the first row and the last row's data need to be moved, the middle two rows are fine.

Again, mailadd2 is always blank if the data needs to be moved, if that helps.

Thanks in advance for any help you can offer.
 

More replies
Relevance 54.12%

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.

4 more replies
Relevance 54.12%

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

10 more replies
Relevance 54.12%

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?
 

Answer:Column changes in Excel

Select the column.
Edit - Find and Replace - Find M - Replace with O - Replace all - Close
 

2 more replies
Relevance 53.71%

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.

2 more replies
Relevance 53.71%

I have a data in a long row - about 2000 columns wide.I need to convert it into one column with 2000 rows.Please help!

Answer:Convert long row of data to one column

Your looking to TransposeHighlight your rowSelect CopySelect an empty columnPlace your cursor in the first cellSelect: Paste / Special / TransposeMIKEhttp://www.skeptic.com/

3 more replies
Relevance 53.71%

Hi - i am trying to write a macro to move data from some rows to columns, but its not a straightforward transpose. Attached is the example

and if there are any blanks not to transpose those.

Suggestions??
 

Answer:Macro column data into rows

Select your range of data to evaluate (in your attached example you would select C3:N12) and then run the macro below. The macro will create a new sheet with the transposed data.
Code:
Sub Transpose()

oldSheet = ActiveSheet.Name

Worksheets.Add().Name = "NewSheet"

Sheets(oldSheet).Select

For Each vcell In Selection.Cells

If vcell.Value <> "" Then

vRow = Sheets("NewSheet").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Row

Sheets("NewSheet").Range("A" & vRow).Value = Range("A" & vcell.Row).Value
Sheets("NewSheet").Range("B" & vRow).Value = Range("B" & vcell.Row).Value
Sheets("NewSheet").Range("C" & vRow).Value = Cells(2, vcell.Column).Value
Sheets("NewSheet").Range("D" & vRow).Value = vcell

End If

Next vcell

End Sub
Rollin
 

1 more replies
Relevance 53.71%

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

Answer:How to transfer four column data in two rows

This should be posted here:http://www.computing.net/forum/offi...

2 more replies
Relevance 53.71%

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/

2 more replies
Relevance 53.71%

g'day everyone!

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

thanks for looking!

regards,

Hardy
 

Answer:MS Access: referencing data from same column

15 more replies
Relevance 53.71%

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

Maybe Excel but if could also be Access . . .
 

3 more replies
Relevance 53.71%

Hi!

I need to compute the weighted average of every other column that has data. (sample file attached)

What formula can I use in Excel?

Please help!
 

Answer:Weighted Average of Every Other Column With Data

Please explain more.
the attached data is uniform and can be averaged by the normal Average formula.
 

3 more replies
Relevance 53.71%

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!

Answer:In layman's terms please explain Column Address Strobe Latency in RAM

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

1 more replies
Relevance 53.71%

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.
 

2 more replies
Relevance 53.71%

how to always see column a when viewing

Answer:how do i make the first column in excel always appear

You want to Freeze PanesOn the Ribbon, Select ViewSelect Freeze PanesSelect Freeze First ColumnMIKEhttp://www.skeptic.com/

3 more replies
Relevance 53.71%

Excel 2007.
Is it still possible to change data from a row to a column or from a column to a row?
If so, how do I do this?
Thank you for your help.
 

Answer:Solved: Excel Row to Column

Hello!

Graphical explanation here

http://office.microsoft.com/en-us/e...s-to-rows-or-rows-to-columns-HP005203138.aspx
 

1 more replies
Relevance 53.71%

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?
 

Answer:Excel Column Trouble

7 more replies
Relevance 53.71%

I have a column in an Excel spreadsheet that I would like to be able to put a "check" in when I receive an order. Is it possible to put a check box in a column?
 

Answer:Excel: Is it possible to put a check box in a column?

7 more replies
Relevance 53.71%

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 Range
Dim strFilterName As String

'set unique filter column header text
'change as appropriate
strFilterName = "Filter Data"

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 If
End With
End 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 appropriate
strFilterName = "Filter Data"Regards

3 more replies
Relevance 53.71%

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?

Answer:"Lost" column in Excel

Click ob B and Insert column ?

8 more replies
Relevance 53.71%

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

5 more replies