Data consolidation / parsing

[demitri]

I am so frustrated as I'm not a programmer and I've had an issue at work that I can't seem to resolve. They gave the project to me because the "expert in everything data programming guru" left and took everything with him. I just happen to have some decent excel skills so I'm the interim choice. Ok here is the scenario:

I received an excel file from a supplier with a tab containing over 10,000 rows of product information (sku, price, weight, short description, etc). My challenge is that we don't use all these products. We only use 1000 or so from this supplier. So I've been using standard formulas and data sorts to update this information daily and it takes forever, most of my day in fact (not very efficient when you have tons of other responsibilities). So I thought it would be great to create another tab that includes one column with only the skus that we need to use and work with (again about 1,000), then to automate a program or macro that looks at my single column of skus and matches them with the main supplier csv tab. If the program finds the sku on the main csv, then it would pull the entire row of product details into a new worksheet or writes the output completely to a new file.

I hope this is clear. Any help or ideas would be very much appreciated.

Cheers!

 

[jeffreybrown]

I'm not the one to ask about a macro, but if your 1000 products always remain the same then why not pull the info over to the new sheet with a vlookup?

 

[Norie]

Well one thing that's clear is that whoever gave you this job doesn't have a clue, ask them to try it.

This probably shouldn't even be done using Excel.

If the data was imported into Access, or some other database application, and there was a table with the SKUs you were interested in were in another table then you could set up a query that would take seconds to give you the results you appear to want.

 

[demitri]

@jeffreybrown: Thanks for your reply. I think this would simply return a single value and not the entire row. So my new output would basically be a duplicate of the skus that I already have created. Is there a way to use a more advance feature of vlookup that could pull not only the sku but all the other columns in a particular row?

Cheers!

 

[jeffreybrown]

A vlookup will look at the value in the farthest left of a table and then return values to the right.

If you put this formula in B1 it will look up that value against the second argument, table array. Drag this fromula to the right and this part COLUMNS($A1:B1) of the formula creates the third argument col index num (2,3,4,5, etc.,) and will change as you drag to catch the whole row.

=VLOOKUP($A1,$A$11:$D$21,COLUMNS($A1:B1),0)

 

[PaddyD]

using the techniques here:

http://www.mrexcel.com/forum/showthread.php?t=38643


...and assuming you have a list of the SKUs you deal with, you can simply stick the formula on the sheet & filter for 'on list'.

 

[demitri]

@jeff: Thanks for your help on this. nice solution that I can use! Good for when the skus are static, which to my knowledge is for now. Cheers!

 

[demitri]

@paddyD: I'll check out the link to new thread. Cheers!