Ordering columns.

Dear all,

I have the following problem: I have a column of data several thousands 
cells long with values (let's call that D1), indexed by another column (let's 
call that I1). I want to perform a division operation of the values in that 
column by the values in another column of values (call that D2), which is 
indexed in the same way by another column (let's call that I2). But I want to 
do this operation only for equivalent index codes (le's say, divide the vale 
indexed as I1=0001 in D1 by the value indexed as I2=0001 in D2). 

Nevertheless, the index is not necessarily continuous (as example, if  D1 
has no value for the index I1=1495, this index doesn't exist in I1, but 
I2=1495 will exist if there is a non-zero value for this item in D2, so this 
index number and the related value exists in I2 and D2). How a can I order 
and divide these series using EXCEL (my version is the 2003 one, BTW)? Can I 
do this writing a logical function like 'IF' in some way?

Best,

Lucio.
0
Lucio (7)
8/18/2008 2:53:04 PM
excel 39879 articles. 2 followers. Follow

2 Replies
775 Views

Similar Articles

[PageSpeed] 44

Lucio wrote:
> Dear all,
> 
> I have the following problem: I have a column of data several thousands 
> cells long with values (let's call that D1), indexed by another column (let's 
> call that I1). I want to perform a division operation of the values in that 
> column by the values in another column of values (call that D2), which is 
> indexed in the same way by another column (let's call that I2). But I want to 
> do this operation only for equivalent index codes (le's say, divide the vale 
> indexed as I1=0001 in D1 by the value indexed as I2=0001 in D2). 
> 
> Nevertheless, the index is not necessarily continuous (as example, if  D1 
> has no value for the index I1=1495, this index doesn't exist in I1, but 
> I2=1495 will exist if there is a non-zero value for this item in D2, so this 
> index number and the related value exists in I2 and D2). How a can I order 
> and divide these series using EXCEL (my version is the 2003 one, BTW)? Can I 
> do this writing a logical function like 'IF' in some way?
> 
> Best,
> 
> Lucio.

Hi Lucio,

Let's assume the following:
"I1" values are in column A
"D1" values are in column B
"I2" values are in column C
"D2" values are in column D

If the indexes are unique in each index column, this should work in cell 
E1 and fill down:

=B1/vlookup(A1,C:D,2,false)

You will need to clean up for failed matches and possibly division by 
zero errors.

This does not require sorting the lists, but it does require that your 
index I2 is in a column to the left of your values D2. If your indexes 
are arranged the other way around, there is another way.
0
smartin108 (170)
8/18/2008 10:34:12 PM
Thanks, worked like magic!

"smartin" wrote:

> Lucio wrote:
> > Dear all,
> > 
> > I have the following problem: I have a column of data several thousands 
> > cells long with values (let's call that D1), indexed by another column (let's 
> > call that I1). I want to perform a division operation of the values in that 
> > column by the values in another column of values (call that D2), which is 
> > indexed in the same way by another column (let's call that I2). But I want to 
> > do this operation only for equivalent index codes (le's say, divide the vale 
> > indexed as I1=0001 in D1 by the value indexed as I2=0001 in D2). 
> > 
> > Nevertheless, the index is not necessarily continuous (as example, if  D1 
> > has no value for the index I1=1495, this index doesn't exist in I1, but 
> > I2=1495 will exist if there is a non-zero value for this item in D2, so this 
> > index number and the related value exists in I2 and D2). How a can I order 
> > and divide these series using EXCEL (my version is the 2003 one, BTW)? Can I 
> > do this writing a logical function like 'IF' in some way?
> > 
> > Best,
> > 
> > Lucio.
> 
> Hi Lucio,
> 
> Let's assume the following:
> "I1" values are in column A
> "D1" values are in column B
> "I2" values are in column C
> "D2" values are in column D
> 
> If the indexes are unique in each index column, this should work in cell 
> E1 and fill down:
> 
> =B1/vlookup(A1,C:D,2,false)
> 
> You will need to clean up for failed matches and possibly division by 
> zero errors.
> 
> This does not require sorting the lists, but it does require that your 
> index I2 is in a column to the left of your values D2. If your indexes 
> are arranged the other way around, there is another way.
> 
0
Lucio (7)
8/19/2008 7:35:07 AM
Reply:

Similar Artilces:

auto paste text in similar columns
I have a workbook that I input customer sales. I then have to look up in another tab (same workbook) what county that customer is taxable in. I then have to copy the base sales amount into that county. Is there a way for me to merge the infomation on the county taxable worksheet with the master sales data worksheet so that when I input the following scenario it would automatically copy the base amount I enter into the corresponding county taxable column?: Customer Sales Tax Taxable in (County Name) ABC Co $1.00 .08 $1.00 ...

Order Management Error
Hi All! We are testing if we can use BP3 for our customers to enter their orders, everything goes well until the point when we click the Check Out button to complete the order, then we get an error, "The order cannot be processed at this time. Please try again later.", then when we look at Event viewer to see the details, there's "The Microsoft Business Framework must be started using EnterpriseSession.Initialize()." on the description. Any ideas on how to get around this issue? Zama, Can you confirm the event is directly related to clicking the "Check Out&...

Text to column where to put result
I am using text to column. Have name-name2 They are separated by a "dash". Want them in two separate columns. What do I put in the destination cell? Assume that your data is in a1 cell like the below. A1 cell name-name2 Copy and paste the below formula in B1 cell =IF(A1="","",TRIM(LEFT(A1,FIND("-",A1)-1))) Copy and paste the below formula in C1 cell =IF(A1="","",TRIM(MID(A1,FIND("-",A1)+1,255))) -- Remember to Click Yes, if this post helps! -------------------- (Ms-Exl-Learner) -------------------- ...

Not allowing duplicates to be entered in a column
I am trying to ensure that users entering data into an Excel workshee only enter unique values in a particular column (values provide earlier in the column cannot be duplicated). Does anyone know a effective way to approach this requirement -- Message posted from http://www.ExcelForum.com Check out http://cpearson.com/excel/NoDupEntry.htm In article <blstamps.156mte@excelforum-nospam.com>, blstamps <<blstamps.156mte@excelforum-nospam.com>> wrote: > I am trying to ensure that users entering data into an Excel worksheet > only enter unique values in a particul...

"Advance Find" Listing columns from other entities
Hi, I would like to know how could i add some columns in Advance find, so that it can be listed. Like In the advance find of Contacts, I would like to list related products for the contacts in the result. Please let me know if this is possible in CRM 3.0 This option isn't available in the current CRM version. Hopefully in the next one -- Patrick Verbeeten (MCSD) Lead Developer Aviva IT Web: http://www.aviva-it.nl "Affy" wrote: > Hi, > I would like to know how could i add some columns in Advance find, so that > it can be listed. Like In the advance find of Co...

Tab order for purchase orders and sales orders
Is there a way to set the tab order while entering items in a purchase order and sales order. My client would like to be able to have the order taker be able to be prompted for a quantity while entering in line items to purchase orders and sales orders. Marc, For POS, enter the quantity first, then the * key, then the ILC So for 5 of 11212, enter 5*11212 <enter> For PO's see if QuickScan works better for you. -- * "Marc Arbesman" <MarcArbesman@discussions.microsoft.com> wrote in message news:05BD48AA-08BB-4CFE-A04D-46BBCC5B95CC@microsoft.com... Is there a w...

Modify back order transfer to order process
Got a question for anyone who is familiar with the transfer process from back order to order. I'd like to make a modification in that process to clear the PICTICNU and PCKSLPNO fields in SOP10100 for the new SOP order created. Currently those fields retain the values from the original SOP order. Has anyone made a similar mod? Any pointers? Thanks. -- Jim@TurboChef Jim - I am assuming you are on Version 8 or higher. Go to Tools - Setup - Sales - Sales Order Processing On this window, click on the numbers button - the last two options are Packing Slip and Picking Ticket. You pr...

Excel 07 3D Column data label alignment
Hello, I have a 3-D Column graph made in Excel 2007. I turn on the data labels and try to place them in the middle of the column but the only way I can do this is manualy one data label at a time. If Right-Click on a data label > Format Data Labels > Alignment and change Vertical alignment nothing happens. Text direction and Angle work fine just not Vertical Alignment. How do I place all data labels to be in the middle of the columns without having to drag each and every one to the middle of it's column? Cheers, Hi, Only way to get the data labels in the center of the c...

CListBox
Hello, Can you tell me how to create a ListBox with 2 columns? One is for the item's name, the other, item's status? Thank you Although you can simulate more than 1 column in a listbox [ LBS_MULTICOLUMN style + CListBox::SetTabStops() ], it is easier to use a CListCtrl in report mode. You also get the benefit of naming your columns. -- Cheers Check Abdoul [VC++ MVP] ----------------------------------- "MilkyWay" <NoSpamPls@email.com> wrote in message news:bpglvf$e9n$1@dns3.cae.ca... > Hello, > > Can you tell me how to create a ListBox with 2 columns...

Purchase Orders Slowness
I just installed this module for version 8.00g7. Now when I access the company, it takes 2 to 3 minutes to log you on. The access time before was less than 40 secs. Does anybody have a clue? It created these views: CPO10113 CPO10114 CPO10600 Also I noticed when you change user in the same company is fast. -- Thanks in advance. Luis Sorry I forgot to tell that the module is Purchase Orders Enhancenments. -- Thanks in advance. Luis "Luis" wrote: > I just installed this module for version 8.00g7. > Now when I access the company, it takes 2 to 3 minutes to log you on. >...

100% Stacked column with 3D-effect, multiple colums?
Hello all, I just ran into an interesting issue, and was wondering if someone knows if it is possible, and how. I did not find anything similar when searching through here. I am using the above mentioned chart. I have 12 columns, representing the 12 months of a year. Each column has 3 stacks, for past, current and future numbers. Now, these columns representing the year 2008, I would like to add the same set with numbers for 2007, either next to each 2008 column, or behind it. Again, they would have to be stacked as each column consists again of past, current and future numbers. Any...

Sales Order unfulfillment
We have customer send us projections of what they would be buying thru next sixth months. We would like to block (allocate) this quantities for this particular customer. At the moment we create a fake order and we put all of these items on this order. When we receive actual orders, we unallocate the fake order for the quantity on the master order, create a brand new order again put the unallocated quantity on the new order. Can we simplify this process? If so, how? Third party solutions? System: GP 8.0 SP1 SQL 2000 Modules: SOP, Inventory, POP, RM, PM, GL We have thought about creating a new...

How can I combine a 3-D column and stacked column chart
Imagine that your income comes from two streams. To make clearer the year-on-year differecnes you like to display a 3D chart by year by month, but use a stacked column to show the two income streams: X = month y = stacked two incomes z = year on year This doesn't seem to be one of the options. Any ideas? 3D charts cannot be combined with other chart types. They also tend to skew the data and add lots of shapes that are more distracting than elucidating. Can't you use a different chart type, in 2D? Your data isn't strictly XYZ data. A clustered column chart shows data just as ...

Back Order Work Order
Hello, When adding an item with 0 inventory to a sale I get a prompt to create a back order. When I add the same exact item to a work order there is no prompt to create a back order. Is that correct? I expect to have some back orders on work orders is there a work around method (or maybe I am missing a fix)? Thank you, Jim This is a multi-part message in MIME format. ------=_NextPart_000_0572_01C89517.067BEB30 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable JimF, In RMS' mind, an order, is an order, is an order, and not a sale. --=20...

Sorting by more than 3 columns
1) Is it possible to write a macro sorting by more than 3 columns? 2) Can I write input boxes into the macro so that I can determine each time which columns I want to sort by first, second, third, etc.? Thanks Matthew --- Message posted from http://www.ExcelForum.com/ This file might be a help: http://www.bygsoftware.com/examples/zipfiles/MoreThanThreeColumnSort.zip It's in the "Worksheet with VBA" section on page: http://www.bygsoftware.com/examples/examples.htm This workbook demonstrates a method to sort four or more columns. -- Regards Andy Wiggins www.BygSoftware.co...

Autofill from column to row
I have a worksheet with data in a column and want the data on another sheet in a row, sorted in another way. I tried to use autofill to get data from every 25th cell in the column to the row on the other sheet, ['sheet 1'!F2"] in cell B2, ['sheet 1'!F27] in cell C2, ['sheet 1'!F52] in cell D2 and so on. Excel autofill wont understand what I want, is there any other way of solving this? One way In the destination sheet: Put in B2: =OFFSET('Sheet 1'!$F$2,COLUMNS($A$1:A1)*25-25,) Copy B2 across to D2 B2:D2 will return the desired contents, viz.: in...

Sumif on values in multiple columns
I have 3 columns: Column A is a number, Column B is a text, Column C is text. I want to sum Column A based on the values of Columns B AND C. For example A1 = 100 B1= Y C1=T A2 = 50 B2 = N C2=T A3 = 10 B3=Y C3=T A4= 20 B4=N C4=T I want to sum A1:A4 only if B1:B4 is "Y" and C1:c4 is "TA". I know how to write the formula for 1 sumif statement, but can't get a second string to work with it. Hi as SUMIF only accepts one condition you may use SUMPRODUCT: =SUMPRODUCT((B1:B4="Y")*(C1:C4="TA"),A1:A4) Frank macs wrote: >...

Purchase order
can we have the site ids addresses show on the po deliver to: address PO form. If, so how? -- Crystal Roach Crystal, This requires a modification of the PO form in Report Writer. Change the Ship To address field to reflect the Site address. Hope this helps, Frank Hamelly MCP-GP, MCT, MVP East Coast Dynamics www.eastcoast-dynamics.com blog: www.gp2themax.blogspot.com The site ID address field does not appear in RW for POs. Do I need to link tables? If so, what tables and how. -- Crystal Roach "Frank Hamelly, MCP-GP, MCT, MVP" wrote: > Crystal, > > This requ...

Find and search by column
When I execute Edit then Find, Excel begins to search by row. Can anyone suggest how: (a) to directly change this default or (b) to incorporate a few lines of VBA codes so as to make it search by column without having to select Options first, and change the search command to "By Column"? Thank you for your help. Brian Saved from a previous post: Excel tries to help by remembering the last settings you used--except for the first search in that session. You can use that to your advantage. You could make a dummy workbook and put it in your xlStart folder. Have a macro in tha...

Unidrv's function order?!
Hi all I have some questions about Unidrv (GPD's minidriver + OEMDLL). Could somebody please answer my question? Now I'm making Unidrv driver for miniprinter (like Epson's TM88III) with ESC/POS command. I completed printing with only image (maybe raster data), so I added device font in it. Then I have got bad print result, which I am, researching driver's action and its execution sequence. My driver does execute device font module first and then image. Exactly, like below 1. When I print test_RASTERFIRST.doc to my miniprinter, functions' order is like below. ...

Replace Columns
Hi, I need some help. I need to replace an unsorted, unfiltered column from one worksheet with a column from a second worksheet. My problem is that I need to keep the rest of the columns in the worksheet exactly the same. Is there a way I can link, group, or associate (not sure of the precise word) two or more columns? In order to understand the spreadsheet, the order must not change yet I have to replace the contents with unfiltered content. Is there an easy way to do this? For example, I always want A1 to be associated with C1, D1, E1, F1, G1. Thanks in advance! In the cell...

Associate a purchase order with a work order or back order
When creating a purchase order, I would like to create one purchase order for only my outstnading work orders, and/or create a purchase order for only those items which were backordered from the POS screen. Better yet, I would like a one-to-one relationship, where I can create a purchase order for a specific work order or back order. However, there doesn't seem to be that level of filtering. The closest I can get it "Generate based on re-order information". That lumps work order items, back order items, and replenishment of low inventory items all together, which makes our...

open purchase orders
We are brand new to RMS, and have question that is nagging us. Is there a way to get a report that shows what is currently on order, preferably able to sort by department and category? MP wrote: > We are brand new to RMS, and have question that is nagging us. Is there a > way to get a report that shows what is currently on order, preferably able to > sort by department and category? > > Try with this code... //--- Report Summary --- // Begin ReportSummary ReportType = reporttypeItems ReportTitle = "Documenti Magazzino" PageOrientation = pageori...

sort order
hi guys, i'm trying to import from several sheets with tables of names into another sheet using: =sheet1!D1 =sheet1!D2 --> =sheet5!D10 etc... in the respective cells of the new sheet... this seems fine. the only prob is the empty cells from the reference sheets are shown in the new sheet as 0. when i do a sort (eg: a-z) i get all the 0 entries first then the start of the names. ideally i would like to not show these values as 0 (rather as empty cells), but if i could change the sort order to have numerals after alphabet that would suffice. regards harold Harold Try =if...

Find all roots of a high-order equation
The math behide this solution is quite simple: if a polynomial equation f(x)=0 has roots, let's say x1,x2,��xn,then f(x) can be converted to another format as (x-x1)*(x-x2)*(x-x3)*�...*(x-xn) = 0 So what we do here is use goal-seek to find it's first root(x1) then use f(x) / (x-x1) as a new equation, easy to say the new one is (x-x2)*(x-x3)*�...*(x-xn) = 0 repeat the procedure above,we can get all the roots one by one. +-------------------------------------------------------------------+ |Filename: Find all the roots of High-Order Polynomial Equation by Dragon.Pan.zip| |Download: h...