Reorganize spreadsheet to comply with vendors inventory system

**clutchmasters** · 10-14-2009, 03:45 PM

Basically We have a column with our part numbers and a column with the vehicle applications which contains the years, make, model, and engine.

Most of our part numbers have multiple applications that they work for.

The vendors system only uploads unique part numbers, so I need to find an easy way, if posable, to combine all the applications that use the same part number in one cell next to it's specific part number.

I don't know if I should be messing with formulas, pivot tables, VBA...?

I do feel like I know my way around Excel but this one has me stumped.

Let me know if you would like me to attach the spreadsheet.

Thanks!

**NBVC** · 10-14-2009, 03:53 PM

Maybe with the help a udf like below:

Add this UDF to your VB editor:

Please Login or Register  to view this content.

and then use formula like:

Please Login or Register  to view this content.

confirmed with CTRL+SHIFT+ENTER and not just ENTER..
and then copy down..

This will look at Sheet2, A2:A100 and find match to A2 on current sheet and return all corresponding items from B2:B100 separated by commas.

**clutchmasters** · 10-14-2009, 04:21 PM

Thanks, I have been trying this and I guess I am not sure where to past the formula. I have all my data in sheet2 and tried to just past the formula in A1 in sheet1 (which has no data). I know I am probably doing it all wrong.

Thanks again!

**NBVC** · 10-14-2009, 04:27 PM

While in your current sheet, hit ALT and F11.

A VB editor screen should pop up.

Go to Insert and select Module...

Then paste the first code above....

close the VB editor and in the cell you want the answer in, paste the formula (the second code)... then adjust the cell references to suit your actual data...then hold the CTRL and SHIFT keys and press ENTER... the formula should have { } brackets around it now and you should get a result...

**clutchmasters** · 10-14-2009, 04:55 PM

Ok, I thought I was proficient at Excel, but VB is taking a while to come back to me.

Anyways, I thought it would help if I attached a couple of snaps of what I am trying to do. The first one is how it is now, and I need to get it into the form of the second snap.

Let me know what you think.

Thanks for the quick responses!

By the way, this is just a snap of a couple applications while the entire catalog has 3000 rows! This is why I need to find a solution using formulas and perhaps VB coding.

**NBVC** · 10-14-2009, 05:02 PM

Welll posting pics ain't gonna help see if you got it right or not...

If you pasted the code in the VB editor as described then the formula in B2 of your second sheet should be something like.

=TRIM(aconcat(IF(Sheet1!$A$3:$A$26=$A$2,Sheet1!$B$3:$B$26," ")))

adjust the sheet name and ranges to suit your actual data... and confirm with CTRL+SHIFT+ENTER

Note: Formula is shorter now since you only want a space between retrievals...

**clutchmasters** · 10-14-2009, 05:28 PM

Alright, it is working now and I am close, but now the cell data is not separated by a space or a comma, so how would I separate them by a forward slash with spaces on each side?