Need help sorting data into another excel sheet

**PrivateLucky** · 07-12-2013, 11:19 AM

So I have started working for a medical supply company and sorting through the prices and item numbers of thousands of line items is difficult to do manually. We have a computer guy who can take two files and match certain columns up. For example, I have a file with the Item Code and Our Costs, and a different file with Item Code and a different companies prices (so I know what prices to beat while maintaining a higher margin). Every time I need to do this sorting I need to go to my computer guy and give him the two files to merge them together so he can match the thousands of items of our competitors prices to line them up with our costs for the proper item. There has to be an easier way to do this. Could anyone direct me towards a macro or a function which could do this automatically? It would save many hours of work.

Example is attached as an excel file, although it is a hundredth of the size of many files I work with.

merging and sorting files.xlsx

**jeffreybrown** · 07-12-2013, 11:27 AM

With your sample data, on File2 (A11:B28) you can use...

=IFERROR(VLOOKUP(A12,$A$1:$B$9,2,0),"")

...to match the "item codes" between the two table and pull over the "Our Cost" into table (A11:B28)

Place this formula in C12 and copy down.

Now if you want to get ride of the blanks where the competitor did not match an "item code" of yours, just filter the sheet.

**Sam Capricci** · 07-12-2013, 11:28 AM

it sounds like if you take your file and the competitor's file and you paste the competitor's info into sheet2 (a couple seconds) then do a vlookup from your info on sheet1 it should return the info you're looking for. Am I missing something? Do you need help with a vlookup stmt?