Aligning numbers in seperate columns

Hi all,

I have a bit of a problem I can't figure out although I think I migh
be missing something very obvious.

Basically I have this data for example formatted like this
A          B          C
1    1          2           SAMPLE1
2    2          5           SAMPLE2
3    3          6           SAMPLE3
4    4
5    5
6    6

I want to be able to align Column B's numbers with those in A and tak
C along for the ride

--
Dr Jone
-----------------------------------------------------------------------
Dr Jones's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3474
View this thread: http://www.excelforum.com/showthread.php?threadid=54508

0
5/24/2006 2:21:57 PM
excel 39879 articles. 2 followers. Follow

3 Replies
379 Views

Similar Articles

[PageSpeed] 16

Do you mean that you want column A right-aligned and column B
left-aligned, so that in your example data it would look like you have
12, 25, 36 etc? If so, then highlight column B and click the left-align
icon - if this is not visible you can click Format | Cells etc.

I'm not sure what you want to do with column C.

Hope this helps.

Pete

0
pashurst (2576)
5/24/2006 3:51:45 PM
No as in I want to align 1 up with 1 and 5 with 5 etc, one column ha
consecutive numbers and I want to align the other column with th
consecutive numbers. At the moment I've gone through and inserted row
until it lines up but this is very time consuming.

I have a column of numbers in a random format that I want to alig
against the numbers in another column. Not all numbers are present al
the time

--
Dr Jone
-----------------------------------------------------------------------
Dr Jones's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3474
View this thread: http://www.excelforum.com/showthread.php?threadid=54508

0
5/24/2006 4:14:15 PM
Dr Jones wrote:
[snip]
> Basically I have this data for example formatted like this
> A          B          C
> 1    1          2           SAMPLE1
> 2    2          5           SAMPLE2
> 3    3          6           SAMPLE3
> 4    4
> 5    5
> 6    6
>
> I want to be able to align Column B's numbers with those in A and take
> C along for the ride.

Ok, the sample data you gave looked like so.

A          B          C
1    1          2           SAMPLE1
2    2          5           SAMPLE2
3    3          6           SAMPLE3
4    4
5    5
6    6

I'm *guessing* you meant it to look like so.

A          B          C
1          2           SAMPLE1
2          5           SAMPLE2
3          6           SAMPLE3
4
5
6

That is, the first column of 1, 2, etc. is really the row numbers.

And I'm also *guessing* (from reading downthread) you want to
know how to get it to look like so.

A          B          C
1
2          2           SAMPLE1
3
4
5          5           SAMPLE2
6          6           SAMPLE3

If that's it, and if I were doing it, I would be writing some VBA to do
it.
The steps would look like so:
- Move cols B and C to a temp worksheet, still in cols B and C.
  (Saves having to worry about moving and copying onto places
   that may have data already.)
- Sort cols B and C on col B.
- Check for duplicates in col B. Check for values out of range in col
B.
  Decide what to do if these conditions appear.
- Scan down col A here and col B on the temp sheet. Keep a pointer
  to each. When the value is the same, copy from B&C on the temp
  to here, and step both. If B is ahead, then step A till they are the
same.
  If A is ahead, there's an error someplace.
- Clean up the temp sheet, either deleting it or just clearing it and
leaving
  it for the next run.

Attach this to a butto and that should be about it.
Socks

0
puppet_sock (108)
5/24/2006 4:40:11 PM
Reply:

Similar Artilces:

Reformatting numbers in cells
We have a report which is printed without commas in the numbers but with a preceding number and a slash. For example, 09/834602. The users would like the "834602" to read "834,602" but I can't figure out how to do this because of the presence of the 09/. I am VERY new to this. Thank you very much for your help. =LEFT(C1,FIND("/",C1))&TEXT(--(MID(C1,FIND("/",C1)+1,99)),"#,##0") -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Joanne" <Joanne@discussions.microsoft.com>...

Decimal alignment
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) How do I align decimals in a word document? I know it has something to do with setting tabs, but I can't seem to get it right when I tried to figure it out myself! Thanks so much. Why are you trying to "figure it out" for yourself?... That's what Help is for :-) See the topic: Set tab stops Regards |:>) Bob Jones [MVP] Office:Mac On 2/7/10 6:37 PM, in article 59bb26da.-1@webcrossing.JaKIaxP2ac0, "brielle78@officeformac.com" <brielle78@officeformac.com> wrote: > Version: ...

Four-up postcards not aligned right
I'm using Publisher 2003 to print postcards four to a page. They are "off" by about 1/4 inch to the left. This isn't awful for the two postcards on the left side of the page, but the two on the right side of the page are unusable. How do I fix this problem? I used Publisher's postcard format, 4.25" by 5.5". I've tweaked the postcard size down a bit in Page Setup (to 5.35") in order to insert a .1" side margin and a .2" gap between the two postcards (it's called a "horizontal gap," based on a portrait orientation, even ...

Excel will not allow me to change alignment
I have a worksheet in which Excel does not allow me to change the alignment of a range of cells from, say, left-justified to centered. I am experienced with Excel/VBA and have not seen this before. The cells are not protected and do contain numerical data. If I add a leading apostrophe to the cell values to have them treated as strings, I can then align the cells however I like. When those cells contain numerical data, Excel will not allow me to change the alignment from left-justified, regardless of whether I use a toolbar, the Format tab, or VBA. Any ideas? -- zalcjm ---------------...

Determine a result of one column based on conditions in two column
Example Col A Col B Count the number of a's in Col B only when an x is in Col A x a x a Result should be 2 y a z p I can't figure it out x t x m Thanks try this =SUMPRODUCT(--(A2:A7="x"),--(B2:B7="a")) -- Hope this help Please click the Yes button below if this post have helped answer your needs Thank You cheers, francis "tel703" wrote: > Example > Col A Col B Count the number of a...

Align
In a cell, Why are texts aligned to the left and numbers aligned to the right in MS Excel spreadsheet? That's the standard for Spreadsheets. Comes in handy when you THINK a cell houses a number and it's left aligned by default. -- Regards Dave Hawley www.ozgrid.com "Diana" <Diana@discussions.microsoft.com> wrote in message news:983520B3-6FA0-4D96-B370-70057F717A55@microsoft.com... > In a cell, Why are texts aligned to the left and numbers aligned to the > right > in MS Excel spreadsheet? hi microsoft chose this as default to mimic ...

text alignment
How can I align text to the bottom of cells in Excel 2007. The bottom text alignment button places text only in the center. Make the Cell higher and then look at Center vs. Bottom. BillCT1984 wrote: > How can I align text to the bottom of cells in Excel 2007. The bottom text > alignment button places text only in the center. ...

How do I make 11.800 convert to 800 as a number not text
I want to drop the 11. and have the cell next to it be 800. I want to then be able to use that 800 and add other numbers to it. I am having two problems, 1) the 11.800 always becomes 11.8 ( I have tried increasing the decimals) and 2) if I have a different number like for example 11.255 by using RIGHT I succeed in getting 255 but as text. Please help. Thank you all in advance --- Message posted from http://www.ExcelForum.com/ Nave, =MOD(A2, 1) * 1000 will give you the 800 from 11.8 -- Earl Kiosterud mvpearl omitthisword at verizon period net -----------------------------------------...

Can't remove budget numbers
Hello, I'm using Plus 08, Advanced Budget. I set up an empty data set in a file called "Test", and use this to test various program senarios. In this file, I have 1 checking account, and 3 expense catagories; Groceries, Utilities, and Misc Expenses, and entered a monthly budget amount for Groceries and Misc Expense. I then scheduled a bill for Utilities. All seemed to be fine in Budget and Cash flow. Then, I changed the budget amounts for Groceries and Misc Expense catagories to zero, but left the Utilities catagory scheduled with an amount in Bills. Cash Flow chart...

Excel
Is there some function in Excel (VBA) that will get me the column alphabet if I provide the column number, or do I have to write one. I ask because I know that there is, yet I recall I wrote this functionality for the previous application, and I don't want to: (1) Reuse it, if there's something in-built (2) Re-invent the wheel Help! PS: I'm looking for something like: code:-------------------------------------------------------------------------------- Function GetAlphabet(ByVal ColumnNumber as Long) as String ------------------------------------------------------------------...

number of quantity decimals
Hello: Other than perhaps rounding issues, are there any problems with having quantity decimals for some items in inventory being 5, some being 4, and some being 3? childofthe1980s If it works for your parts and pricing, it works for GP. I frequently have some parts with 5 decimals and others with 2 -- Richard L. Whaley Author / Consultant / MVP 2006-2008 Documentation for Software Users Get our Free Tips and Tricks Newsletter and check out our books at http://www.AccoladePublications.com "childofthe1980s" wrote: > Hello: > > Other than perhaps rounding issue...

Any implication onf sending e-mail to large number of contact lists ?
One of my customers would like to send e-mails to contact lists that belong to my internal users. Those contact lists would contain addresses for account at hotmail, yahoo, etc. Total number of contact lists would be 2,000. Any implication if I create a DL and put all those contact lists there ? Any problem performance or security wise ? The biggest performance issue would be expansion of those DLs; I don't know how heavily-used they are or what the topology is so it's hard to say what the impact would be on your organization. As far as ongoing (and automated) maintenance of larg...

Outside page numbers
Prior to updating to Word 2007, it was a simple matter to select from the menu if you wanted page numbers to be on the "inside" or "outside" of the pages. Now I cannot figure out how to do it. Any suggestions would be helpful. You have to enable "Different odd and even" and then place the page numbers as desired in the Odd Page Header/Footer and Even Page Header/Footer. -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA http://word.mvps.org "MaryGold" <MaryGold@discussions.microsoft.com> wrote ...

How can I line up 2 columns with similar information?
I have 2 columns with names in each and I'd like to line them up next to each other. I think we'll need more information, Kathy. I don't understand what you're trying to do. Can you give exactly what's up? ************ Anne Troy www.OfficeArticles.com "Kathy" <Kathy@discussions.microsoft.com> wrote in message news:A5C0BC96-7E27-4C55-89A3-03513FC0D409@microsoft.com... >I have 2 columns with names in each and I'd like to line them up next to >each > other. Column A has employee number, Column B has the same employee numbers but has addi...

After align right, how go back to no alignment?
Thanks in advance. I have a column that was left aligned somehow, though I don't think I did it. It's filled with numbers, and I want to put it back to right aligned, so I can do that with the 'align right' button. Then if I click on a cell in that column, the 'align right' button will have a blue border around it. How can I go from a right aligned cell back to a cell where there is no alignment (no left, middle, or right alignment)? Normally cells with numbers are to the right, but the 'align right' cell is not bordered. So if I 'align right' th...

Adding totals in columns
I have a worksheet that we have hidden rows in. I want to find the sum of a column without picking up the hidden row values? I can I get a sum without including the hidden cell values? What version of Excel are you using? If you're using Excel 2003 or later try this: =SUBTOTAL(109,A1:A10) -- Biff Microsoft Excel MVP "Tanya" <Tanya@discussions.microsoft.com> wrote in message news:3797F36F-B648-469F-8F7B-6E07E5DC837F@microsoft.com... >I have a worksheet that we have hidden rows in. I want to find the sum of >a > column without picking ...

Average time from date column?
I have a column that is in general date format, 6/19/2007 5:34:23 PM. In another column I have a value between 10 and around 50. What I have to do is get the average of this number by time in a day. So if the number is 10 all day, then the average is 10. If the number is changed during the day, say its 5 for half the day then 10 for the other half of the day, then the number I'm looking for is 7.5. If the number is 5 for 1/4 of the day and 10 for the other 3/4 of the day then the number would be around 8.5..... if you follow what I'm trying to say. So far I have been a...

Menu issue: Multi-layer Menu is not aligned properly in IE 8
Running CRM 4 in IE8, when we select a menu that has a submenu and then select the submenu, the submenu items lay directly over the menu rather than being shifted slightly to the right. For eample if you are looking at the account form and then click on the Dynamics round logo in the upper left, the top item is New with the arrow indicating a submenu. If we click on that menu item to expose the submenu the submenu lays right on top of the original menu rather than being off-set slightly to the right so that you can still see the top level menu items. This seems to only happen IE 8. ...

columns #6
two questions: 1. Is there a way to print the row numbers without printing the column letters. 2. Is there a way to limit how far down the scroll bar goes on EXCEL. Hi 1. You could use a helper column containing =ROW() to create your row numbers. 2. The scroll bar will go down to the end of your 'used range'. To reset your used range (if that is your problem) visit here for information http://www.contextures.com/xlfaqApp.html#Unused Hope this helps. -- Andy. "frank" <mrfrank73@aol.com> wrote in message news:7fee01c431e7$374f0b30$a401280a@phx.gbl... > two ...

Printing two columns of data in telephone directory style
Hi, Wondering if anyone can please help? I have a long (15,000 entry) list of data in two columns that I need to print. To save space, paper and access time, I'd like to print this list in two sets of two columns - as the names and numbers in a telephone directory might appear. What I envisage is my list starting at the top left of page 1, with its corresponding data column just to the right. The list would go down to the bottom of that page and then restart at the top of the page, just right of centre. Subsequent pages would continue in similar fashion. I have trie...

How to arrange alphabet and numbers in a cell
Hi, In one cell I have numbers and alphabets and how to arrange ascending or descending for example before Cell A1 = 6532ADC After Cell A1 = ACD2356 what formula I have to use? VBA code is preferable. Thanks Try the code below, used like =SORTCELL(A1, TRUE) HTH, Bernie MS Excel MVP Function SortCell(myR As Range, OrdAsc As Boolean) As String Dim myArr() As String Dim myTemp As Variant Dim i As Integer Dim j As Integer 'Split the string into characters ReDim myArr(1 To Len(myR.Value)) For i = 1 To Len(myR.Value) myArr(i) = Mid(myR.Value, i, 1) Next i 'Do the sort For i = LBou...

How do I sum numbers based on other data
I have a spreadsheet with several thousand entries. On one sheet, call it sheet 1, there is One column with about 100 names, another column has number amounts. My other sheet, call it sheet 2, has a list of names. What I would like to do is for each name on sheet 2, sum up the number values from sheet one for that particular name. So the end result would be a column on sheet two that shows the totals taken from sheet 1 for each name. I keep adding new entries on sheet one, so the totals on sheet 2 must always show the updated totals. Any help would be greatly appreciated. I am...

rounding off numbers
Hi everyone! I was just wondering if it's possible to change the parameters of the 'rounding off' function to round up to the next number if the number is something like 24.3 instead of 24.5? Is that possible? Thanks so much for any help!!! DL "DeeElle" <donnaleigh@rogers.com> wrote in message news:a3Brb.3128$HoK.794@news01.bloor.is.net.cable.rogers.com... > Hi everyone! > I was just wondering if it's possible to change the parameters of the > 'rounding off' function to round up to the next number if the number is > something like 24.3 i...

text box won't align middle
I've got a text box in one of my documents, i've set it to middle align (they all are), but this one doesn't - it looks to me like its top align... It's got a texture background (the same as the others), and the text is arial embossed... I've tried setting the margins to 0 and 0.1 (and whatever they were as a default)... Select the text box, Format, Paragraph, be certain all these settings are correct. Zero before and after. Could be the text box has some corruption going on. If you create a new box and paste the text into it, does it work? -- Mary Sauer MSFT MV...

How do I center align email messages in Publisher?
How do I adjust the positon of email messages in Publisher in order for them to be automatically centered on screen when viewed in Outlook? Why not compose the email in Outlook? -- Mary Sauer http://msauer.mvps.org/ "Johnie" <Johnie@discussions.microsoft.com> wrote in message news:280382C1-89B7-4A0B-AAE8-0F1EE5DF701A@microsoft.com... > How do I adjust the positon of email messages in Publisher in order for them > to be automatically centered on screen when viewed in Outlook? ...