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!
Steve EckermanSystems AdministratorAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Steve EckermanSystems AdministratorAuthor Commented:
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.

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

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


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Steve EckermanSystems AdministratorAuthor Commented:
Thanks!  Great Answer!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.