Formating data

I have a set of data which is not formated in columns.
Want to know how to apply a formula to format it into 
columns form.


In column A, first row, the fist data is e.g. A1100. Then 
the next data is 10 rows down, follow by the third data in 
the next 10 rows down.
Similarly in column B, the data starts at second row is 
e.g. Brandon.

I want to copy the data to a new column format such that 
the data run continuosly.
Column N   Column O
A1100      Brandon
A1101      Steve

KH Puah

KH Puah
4/6/2004, 5:54:51 AM

try the following on a separate sheet:

copy both formulas down. After this copy the resulting range and insert
it again as valuse ('Edit - Paste Special - Values')

Frank Kabel
Frankfurt, Germany

"KH Puah" <a*****************> schrieb im Newsbeitrag
> I have a set of data which is not formated in columns.
> Want to know how to apply a formula to format it into
> columns form.
> e.g.
> In column A, first row, the fist data is e.g. A1100. Then
> the next data is 10 rows down, follow by the third data in
> the next 10 rows down.
> Similarly in column B, the data starts at second row is
> e.g. Brandon.
> I want to copy the data to a new column format such that
> the data run continuosly.
> Column N   Column O
> A1100      Brandon
> A1101      Steve
> KH Puah

Frank Kabel
4/6/2004, 6:50:52 AM
Did not got it to work. I tried that. Only got the first 
data using this.
=OFFSET(Sheet1!A1:A100, (ROW()-1)*5,0)

where did I go wrong?
sheet 1
 A    B
2    D11
3    D12
7    D21
8    D22

I want the result to be like this
D10  D11  D12
D20  D21  D22


>-----Original Message-----
>try the following on a separate sheet:
>copy both formulas down. After this copy the resulting 
range and insert
>it again as valuse ('Edit - Paste Special - Values')
>Frank Kabel
>Frankfurt, Germany
>"KH Puah" <a*****************> schrieb 
im Newsbeitrag
>> I have a set of data which is not formated in columns.
>> Want to know how to apply a formula to format it into
>> columns form.
>> e.g.
>> In column A, first row, the fist data is e.g. A1100. 
>> the next data is 10 rows down, follow by the third data 
>> the next 10 rows down.
>> Similarly in column B, the data starts at second row is
>> e.g. Brandon.
>> I want to copy the data to a new column format such that
>> the data run continuosly.
>> Column N   Column O
>> A1100      Brandon
>> A1101      Steve
>> KH Puah
KH Puah
4/8/2004, 1:55:25 AM

Formatting, formatting, formatting
3 replies , 2/21/2005, 6:42:17 PM
I apologise if this has been inquired of previously, I wasn't able to phrase my searches well enough (due largely to ignorance of Excel's capabilities) to find anything useful on the web. The Issue -- I'd like to format certain rows based on the content of a cell within the row. Below is a sample of row data. 052159000011 6 YOGURT,OG,WM,PLAIN 32 OZ 1 Case NO 052159004682 12 YOGURT,OG,WM,STWBRY&CREAM 6 OZ 12 Case YES 052159004668 12 YOGURT,OG,WM,VAN TRUFFLE 6 OZ 12 Case YES 052159004613 12 YOGURT,OG,WM,WLD BLUEBERY 6 OZ 1 EA NO 036638222118 6 YOGURT,PLAIN 32 OZ 1 EA NO 027434011297 1 YOHIMBE FUEL 50 CAP 1 EA YES 798311111712 1 Y-SNORE NASAL SPRAY 20 ML 1 EA YES 728229678984 12 YUKON GOLD BARBECUE 5 OZ 1 EA NO 728229678946 12 YUKON GOLD ONION & GARLIC 5 OZ 1 EA NO When column 2 in a row matches column 5, the IF formula calculates the value of the last row as YES. When 2 is not equal to 5, it calculates as NO. I've found that using conditional formatting I can format one single cell - for example, coloring the NOs as bright bold red, and the YESs as bright bold green. Where I'd really like to go with this is to hilight the entire row when the value is not equal. Any insights would be appreciated. Regards Ben/ND


Conditional Format - Copying Formats
5 replies , 4/9/2007, 7:10:03 PM
Hello, In Excel 2003, I would use CF to compare two columns of numbers. Assuming colA and colb had the numbers, I would put the following CF in cell B1: =if(a1<>b1,1,0) and format the cell with a red background. This would tell me when the values were not equal. I could then copy that formatting down the column and it would highlight any cell that was variant. Trying to do that in 2007 and am being caught by the absolute reference. I try and take it off, but the formula does not change. For example, every cell in column B looks at the comparison of B1 to A1, and the CF is applied to the whole column. I'm sure it is something basic I am missing, but cannot figure it out. Any help would be appreciated.

Formatting the result of a VLookup / Custom Formatting
0 replies , 3/10/2006, 3:18:39 PM
Hi, I am having difficulty formatting a cell which contains both text and a VLOOKUP formulae. I have an example of the cell below: ="Here lies the text before Vlookup " &VLOOKUP($M$20,sheet1!$B$10:$L$1445,8,FALSE)&" Here lies follow-up text" The VLookup should return a date but instead is returning a string of numbers. I'm sure there is a way of setting the format within the formulae using dd/mm/yy somewhere. Could anyone tell me how to do this?

Can anyone help me to change some photos from BMP to JPG? I've done this before but I can't remember how. Cheers.

Please add format Painter to OneNote formatting tools
2 replies , 1/28/2006, 5:59:26 PM
I find OneNote a wonderful tool but would like the easy of quick formatting with the Format Painter found in the other Office applications I am also concerned that as a new program. File formats will change and data will be lost over the versions . . .or the program dropped altogether due to lack of use/understanding ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane.

Can I change a BMP file format to a JPEG format?
4 replies , 2/14/2007, 6:20:36 PM
Hello, I have an image in BMP file format yet I want to change it to JPEG. Can this be done and if so, can you help me? Thanks -- Phil Holberton

Number Formats
1 replies , 8/19/2004, 4:17:02 PM
I have imported a spreadsheet into to excel - the spreadsheet contains a column of dollar amounts however if the dollar amount is just cents. It is displays as 30&#65378; - how do I change the to say $.30?

numbers format
3 replies , 3/2/2004, 6:10:02 PM
I formatted a group of cells, using currency,2 decimal places, the $ sign. When I clicked okay only one cell formatted; the rest have ##### in each cell. Can anyone tell me what to do to correct it?

Mary Jo
Custom Formats
1 replies , 12/13/2004, 9:36:14 PM
Is there a source one can look up the rules of making cutom formats? i need to display negative percents as (2.1%) and in red Thanks!

Stan Altshuller
Conditional Format
6 replies , 9/28/2004, 5:29:02 PM
****This question is not answered by other posts. If it is I need help seeing how. I do not have any data in any of the cells. Can someone please help or tell me it is hopeless. Thanks- I am trying to make a gantt chart in excel that will put a percent finished for status. To do this I need to have excel count the number of cells that have a certain background color. This works fine when I do not use conditional formatting, but to make the chart user friendly I need the conditional formatting. Is there anyway to accomplish this? ( I have two bars one for how long the project should take and one for the current date. I want to comapre the two bars to get a percent finished.) -- Thanks, Nikki

Formatted Printing
0 replies , 12/18/2003, 7:36:21 PM
Hi, I'm trying to print using coordinates, without using the excel built-in printer scheme. I'd do something like this on Visual Basic: Private Sub CommandButton1_Click() Printer.Height = 279 * 56.7 Printer.Width = 216 * 56.7 Printer.Copies = 1 Printer.CurrentX = 32 * 56.7 Printer.CurrentY = 55 * 56.7 Printer.Print "Testing" Printer.EndDoc End Sub But this doesn't work as it doesn't recognize printer. Is there a way to print in a more "raw" way using VBA? Regards, -- Beto Reply: Erase between the dot (inclusive) and the @. Responder: Borra la frase obvia y el punto previo.

Format conversion
12 replies , 10/18/2010, 9:35:17 AM
I have a column of numbers defined "date" by a "dd/mm/yyyy" format, now for my commodity I enter dates by hitting "dd-mm-yyyy" where often dd and mm are made of only one figure, now it often happens that these dates are converted in the wanted dd/mm/yyyy format, but often they don't where am I wrong? il barbi

il barbi
Time Formating
2 replies , 8/8/2005, 1:47:03 PM
I have created an excel spreadsheet to calculate hours worked. When entering the start and end times, the formating automatically assumes an AM. So if I type "5:00" the cell formats it as "05:00 AM". But if I want a PM hour entered such as 5:00 PM, I have to enter the P. Is there some way to format the "End Time" cells so that it assumes a PM, unless an AM is entered? I would appreciate any suggestions. Marianne Castrovince

conditional formatting...
1 replies , 12/1/2003, 10:24:42 AM
I am wondering how I can format a cell based on if it has a DATE or a TIME or a text string. basically, if value is between 0 and 1, it is TIME if the value is strictly number and greater than 1, it is a DATE otherwise, it is text. Thanks, Phil

Format of DQY files?
0 replies , 2/15/2005, 4:17:28 PM
Hello, I recently discovered that I could not use existing Excel Query files. Excel appears to hang and nothing appears in the ODBC traces. I've since decided to create a new Query file which works. When I compared the old one to the new one, I discovered that the connection string between the two files differed: From the one that fails: DSN=P21 PROD;DRIVER={C:\Program Files\OpenLink\olod3032.dll};UID=;Database=;FetchBufferSize=30;Host=hal;NoLoginBox=Yes;Options=;Protocol=TCP/IP;ReadOnly=Yes;ServerOptions=;ServerType=Progress 8; This one works: DSN=P21 PROD;DRIVER=C:\Program Files\OpenLink\olod3032.dll;UID=;Database=;FetchBufferSize=30;Host=hal;NoLoginBox=Yes;Options=;Protocol=TCP/IP;ReadOnly=Yes;ServerOptions=;ServerType=Progress 8; Note the difference in the DRIVER parameter. Well and good -- I see what the problem is, but I have hundreds of queries using the { ... } braces and I'd rather not have to make this change to all of them. My searches online indicate that enclosing the DRIVER parameter in { ....} should work. In other words, all examples of DQY files show that being used ... but all the DQY files that I've created recently, using this ODBC Driver and other drivers (like the Microsoft SQLServer one) lack the {...}. Therefore, I'd like to know -- what is the format of a DQY file? Should the DRIVER parameter have the {...} around it? If so, why is Excel *not* like it now? Regards, James

copying page formatting
1 replies , 5/19/2004, 7:19:18 PM
I need to know if there is a way to copy the contents of a worksheet including the page formatting and print settings into another worksheet in the same workbook or even into a different workbook. I have tried sereral times and can get cell contents, column widths and formulae coppied with no problem but the page formatting and print settings all must be set individually and this is taking my employee far too long to set the page formatting and print settings exactly like the prior worksheets. Any help would be appreciated.

ben rankin
General Number Format
2 replies , 8/2/2005, 7:31:58 AM
Folks, I have a problem with the format of the numbers in my spreadsheet. Every number seems to be dividing itself by 1000. eg. if i put 4 into a cell, it changes itself to 0.004. I have checked to format of the general number and it is set to 0.001 as its example. HOw can I get this back to normal? Any help??? -- johnerr ------------------------------------------------------------------------ johnerr's Profile: View this thread:

Conditional Format - new
1 replies , 7/20/2004, 7:52:46 PM
I am using the conditional formatting option to make all cells that are below a cell with a similar value be blank, or more precisely, white. However, I would also like to have the fill in the rows alternate between white and gray for style appearances, thus making the conditional format output be white if the background fill is white and gray if the background fill is gray. However, there does not appear to be an option that allows for conditional formatting based on the cell format, rather it is based off of cell content. Is there a way to make my styling preference happen without too much effort?

Validating email address formatting
5 replies , 7/12/2005, 10:55:03 PM
Is there an easy formula to tell you if an email address is formatted correctly, with t*******t.text? I don't even need to know more than that (such as valid domains, etc.) for now. My original thought was just a basic IF statement to drop in to the next column that tells me if the cell is formatted correctly. But I don't know how to look for text in a particular sequence or format. Any ideas for something like this, or better? Thanks! (You all always have the best answers!) -bethp (Inadvertently x-posted to Excel Programming too; sorry for the dupe request!)

Missing Styles in Excel Format
1 replies , 3/10/2008, 1:00:52 AM
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Where is the cell styles located in the excel worksheet. I can't find the tab that displays the cell styles options.

updating phone number formatting
5 replies , 8/7/2006, 5:30:00 PM
Hi everyone, I need help, again. I have several worksheet that I need to combine the information in order to upload into a access database. I problem that I'm having is the phone numbers are formatted differently therefore they are not all of the are uploading. The Access data base field is classified as text. The phone numbers that did upload correct are formatted as special / phone numbers. I tried changing the cells formatting but it doesn't update the existing information. It does update if I type the number in by hand. Is there away of updating the existing numbers to be formatted like the example below. (555) 777-4444. All suggestions are always appreciated. Thanks Lostinformulas -- lostinformulas ------------------------------------------------------------------------ lostinformulas's Profile: View this thread:

pasting data changes format
5 replies , 3/16/2009, 11:08:20 PM
I create a new empty workbook and trying to populate data for MLB teams and having an issue - anyone tell me why ? For example: the HEIGHT field ( 6' 5") becomes a date ( 5 Jun ). The Weight column is fine, the Date column is fine - why is it messing with the Height column ? Thanks for any insight.

- Bobb -
"Date Format" v.s. "##/##/####"
5 replies , 1/11/2008, 6:53:01 PM
Hi everyone, Is it the same when you type in 01/01/2000 as in a date format AND when you pick up three different numbers from three different cells and combine them using & sign? For ex, A1 & "/" &A2 & "/" &A3. If after combining the three number, will Excel still look at the combined cell as a "date". I need to do some calculating with the combined cell, and I want Excel to look at it as a date, let's say I need to calculate the age, but the birth date need to be hidden. How can I do with this? Thanks, GU

Can't change date format in Excel footer
6 replies , 1/23/2007, 1:56:01 AM
I have a client running Office 2003 Standard Edition on Windows XP Professional. Both products are fully patched via Microsoft Update. Regional & Language Options are set to English (Canada). The short date format is shown as 22/01/2007. This is the desired format. In Excel, however, when my client inserts a Date field in a footer, the date field appears with the format 01/22/2007. How can we modify the format of a date field that appears in a footer? Thanks, Richard.

Conditional formatting numbers inputted to bold
2 replies , 5/4/2007, 9:53:01 PM
I'm sure I am repeating a question that has been asked many times, but I have been looking around the board for an hour or two and thought I would give up and get some help. I have a spreadsheet that I have created for my track team. I am trying to have the selected cells turn bold if they are less than a certain time and others are greater than a certain distance. It works for the numbers that are already there, but when I input new numbers it does not bold them. Does someone have a formula that would work and should the cells be set as number formatting, as apposed to general, text, or something else? Next question is, since I am a teacher and have several classes that I am trying to do this for, is there a way to set cells that are not in order to always bold a number under 15.50 (for example)? I hope I have explained this clear enough. Thanx for any help! T

Transferring Cell Format with VLOOKUP formula
3 replies , 3/24/2009, 1:29:37 PM
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Is it possible to transfer a cell's format with the VLOOKUP function? If so, how do I accomplish this? I need to transfer italicized text, but when I use the formula it drops all of the settings from my master list. I think it's applying the destination cell's settings rather than keeping the original. The list is comprised of Genus species names (which are italicized) vs Class, Order, Family names (regular text.) Since it's alphabetical they're mixed up. Thanks for any help you can offer!

Excel Paste Link not copying format
1 replies , 9/9/2007, 5:52:00 AM
Hi, I am using Excel 2007 and want to break up a large worksheet into many small sheets but retain the original data in one sheet. When pasting the coipied data as a link the copied data is inserted into the new worksheet but sans the original formating such as currency, number formats, text color etc. What to do? >)) many thanks

disable office X formatting palette genie animation
0 replies , 9/11/2004, 7:12:24 PM
How do you disable the genie type animation that takes place when you show or hide the formatting palette in Word/Excel/Powerpoint v.X? I have done this before, and am looking at a system where it is disabled, but I can't figure out where this option is so that I can control it on another system.

I am unable to print the circles created by formatting audit circle invalid data. I would be glad if someone could assist