Extracting unique items and replacing them with ID's

[supermario65]

Hi,

I'm converting an excel file to a database structure so that I will be able to import it in my SQL database.

Right now I have a column with brands. I want to create a new sheet with all the unique brands and give them an ID. Then I want to replace all the brands in my original sheet with the corresponding ID (so basically, I want to make it a relational database).

Should it not be clear; this:
Item1 | Brand x | Price 1
Item2 | Brand x | Price 2
Item3 | Brand y | Price 3

Should become:
Item 1 | 1 | Price 1
Item 2 | 1 | Price 2
Item 3 | 2 | Price 3

Brand x | 1
Brand y | 2

Can this be done with only excel, or do I need VB? (If so, please move my thread)

Jeroen

[ron2k_1]

VBA can do that, but you will have to give these guys full details as to what may be the unique values and what you want them replaced with.

Now, if all you have is "Brand x" and "Brand y" and you want both be replaced with 1 and 2 respectively, then that's easy. So let us know.

In the event where VBA is not your cup of tea or don't want it anywhere close to your workbook, then creating a helper column may help. do the following:
1. On a fresh column insert: =IF(b1="Brand x",1,if(b1="Brand y",2,"")) OR this (chose one formula only) =CHOOSE(MATCH(b1,{"Brand x","Brand y"},0),1,2)
2. Drag formula as required.
3. Select the range where these formulas are, Press Ctrl C
4. Select Column B (where Brand x, etc) is
5. Right click, Paste Special, Values, OK

[supermario65]

Thanks for your reply.

There are of course multiple brands and they don't have a fixed name. Should I create a new topic in VB or can you move this one?

[ron2k_1]

Why don't you create a helper column and place all the brand parameters and corresponding the desired results in the CHOOSE formula I gave you, and then just copy and paste the results to the brand column.

[supermario65]

I'm a programmer, but don't have much experience with VB (but it is an option)

The way I'm reading your formule, I would have to create a really long function with brand a, brand b, brand c, etc, right? (Because you have hardcoded 'brand x', 'brand y' ?) (And that wouldn't be an option because there are hundreds of brands)

I looked up MATCH and it does seem to be useful, just not with static strings (or I'm reading your function wrong). I also came acros an example of using INDEX together with MATCH (http://www.mrexcel.com/articles/exce...ndex-match.php) which also seems to be useful.

Anyway, I'll try out some formulas, and if I'm understanding your CHOOSE formula wrong, please let me know

[ron2k_1]

Nah, you're reading the CHOOSE formula correctly. I thought you only had just a hand bunch price codes, and you didn't want to do the FIND/REPLACE business, and didn't want to spend the time in creating a lookup table. If you opted for the VB route, the same parameters would have to be hard-coded as well, or refer to a table somewhere in the workbook.

In your case, yep; you'll need a lookup table. Then you can use VLOOKUP, if the price index data is all in one column or use the INDEX/MATCH option which you've bumped into as well. The index/match route has the benefit where there is a price matrix and the selected price would depend on other parameters (like region or anything like that).

Create a lookup table, use VLOOKUP or INDEX/MATCH on a helper column and let us know how that works out. Don't hesitate to ask on the index formula as they seem intimidating in the beginning but are very easy and mighty useful.

[supermario65]

I got it to work using Match + Index as described in the article I linked earlier.

Here's what I did, should anyone have the same problem:
- In the products sheet, I create a new column named 'brandID' next to the column 'brand' (with all the regular names)
- Copied the entire 'brand' column over to a new sheet
- Used advanced filtering and filtered for unique results
- Copied those values over to another sheet (named 'brands')
- Added a column to the 'brands' sheet with ID's. (So now 'brands' is a lookup table; it has two columns, first is brand name, second is brand ID)
- Used the following function:

Please Login or Register  to view this content.

What that function does is described pretty well in the article.

Please Login or Register  to view this content.

Because I started my ID's at 1, the INDEX function isn't even needed (because MATCH already returns the correct ID (=row number)), but this approach is of course more dynamic should that change.

Thanks for the help