Steve Eckerman
asked on
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!
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks! Great Answer!
ASKER