yet another cell value change based on another cell question

[dayhox]

So I am attempting to normalize a database. I've brought the values
into an excel sheet to help with the replace functions. MySQL may have
similar functionality, but am too unfamiliar with it. basically I have
9800 rows of values, of those there are roughly 3000 unique entries.
those 3000 unique entries have been given a numeric value so model_id
= 1 - unique = 2.3CL/3.0CL 4Cyl / Auto(L4) --- model_id =2 - unique =
2.3CL/3.0CL 4Cyl / Manual(M5) and so on.

So:
Column A holds all 9800 entries
Column C holds the 2800 id #'s
Column D holds the 2800 unique values

For each row in column A it should be replaced with the number in
column C Where Column D = column A

Thanks for your time and assistance.
day

 

[Bob Greenblatt]

So I am attempting to normalize a database. I've brought the values
into an excel sheet to help with the replace functions. MySQL may have
similar functionality, but am too unfamiliar with it. basically I have
9800 rows of values, of those there are roughly 3000 unique entries.
those 3000 unique entries have been given a numeric value so model_id
= 1 - unique = 2.3CL/3.0CL 4Cyl / Auto(L4) --- model_id =2 - unique =
2.3CL/3.0CL 4Cyl / Manual(M5) and so on.

So:
Column A holds all 9800 entries
Column C holds the 2800 id #'s
Column D holds the 2800 unique values

For each row in column A it should be replaced with the number in
column C Where Column D = column A

Thanks for your time and assistance.
day

If I understand you correctly, a simple formula will do this:
=if(a1=d1,c1)
Put this formula in another column and fill down for all 9800 rows. Now
you'll have a column containing the codes for the stuff in A1. You can copy
this new column and then paste special values to remove the formulas, then
delete columns C and D if they are not needed.

 

[dayhox]

Thanks Bob,
But my col C has 3000 rows that do not matchup with col A, is there a
way that I can state the following?
X = specific row/cell
N = any row/cell in specific column

=if(aX=dN,cN)

or

=if(a1=d1:d3000,c1:c3000)

I'll play around with it too, thanks for your thoughts.
d

 

[Bob Greenblatt]

Thanks Bob,
But my col C has 3000 rows that do not matchup with col A, is there a
way that I can state the following?
X = specific row/cell
N = any row/cell in specific column

=if(aX=dN,cN)

or

=if(a1=d1:d3000,c1:c3000)

I'll play around with it too, thanks for your thoughts.
d

Look in help for Vlookup or match. Either of these ought to get you what you
need. If you need further help post again.

 

[dayhox]

Bob,
The match formula sort of works, however when I drag to fill in down
the line my lookup_array changes too.
I'd like to make the lookup_value change, but the lookup_array remain
static.

*No need to use colC in my old work using the match function.

this is what I have as a formula
=MATCH(A1,D13000,0)

when I click and drag to fill successive rows with the formula i get
=MATCH(B2,D23001,0)

but I'd like it to be
=MATCH(A2,D13000,0)

so that only the lookup_value changes, but the lookup_array remains
constant.

Thanks for your time,
d

 

[Bob Greenblatt]

Bob,
The match formula sort of works, however when I drag to fill in down
the line my lookup_array changes too.
I'd like to make the lookup_value change, but the lookup_array remain
static.

*No need to use colC in my old work using the match function.

this is what I have as a formula
=MATCH(A1,D13000,0)

A very simple change. Look in help for Absolute references for more
information. Your formula should be:
=match(a1,$d$1:$d$3000,0)

 

[dayhox]

Bob,
I could kiss you, but I wont.

Thanks for all of your help.
d