• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 252
  • Last Modified:

Excel Columns Order By Function

I have an Excel 2003 spread sheet with 4 columns the first and third columns contain the same company part numbers.  Columns 2 and 4 contain the dollar values for columns 1 and 3's part numbers.  Because the columns are extracted from two different database tables they do not match up.  I need to be able to match the columns 1 and 3 part numbers to each other with the dollar value columns remaining attached to it's respective part.  Is there an easy way to do this in excel.  Please see the attached file in the format that I would like it to look like when finished.  Thanks!
0
Steve Eckerman
Asked:
Steve Eckerman
  • 2
  • 2
1 Solution
 
Steve EckermanSystems AdministratorAuthor Commented:
0
 
jmac01Commented:
I have added two options into your workbook – copy uploaded – that you can play with. No special functions, just a few std built in Excel ones. I have split them all out into different columns to make it easier to follow. But you can obviously combine them. I usually prefer short formula  in helper columns that can be hidden. I have not used any Absolute Refs except where necessary. I wanted to give you something you could tweak and play with.

The longer option uses the Offset function to get the totals and the second one uses SUMIF which is easier.  Also, Where possible I have used full column references utilising Excel’s Implicit Intersection but when referring to specific ranges like with SUMIF and MATCH, I have referred to specific ranges to reduce the work on Excels calculation engine.

I have hooked into the data on your sample output. Obviously, the data source will be a bit different but I'm sure you can figure out how to hook it into that.

Hope this helps. The alternative is VBA.

jan012-sop-comp-top-jan012-forec.xls
0
 
jmac01Commented:
Forgot to say. The key is the full list of codes. I got them by copying and pasting both columns of codes into a fresh sheet and using the remove duplicates option in Excel 2007 to get a single list. Then pasted that into the output sheet.  Not sure how your data source gives data but in exdel 2003 you can use this method
http://www.lytebyte.com/2008/10/30/how-to-remove-duplicates-in-excel-2003/

In 2007 it is easier. Just select the Data tab and choose "Remove Duplicates"

James
0
 
Steve EckermanSystems AdministratorAuthor Commented:
Thanks!  Great Answer!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now