Automatically renaming Cells

[JulianUK]

Hi,

My first post here, so i guess I should start off with a hi !

So anyway, I'm here because I need some guidance on how to automaticall
rename cells.

In the morning, I pull down two separate spreadsheets, from tw
different systems.

One system has a material number that is named differently from th
other spreadsheet.

Example

BK818/80 on one sheet is exactly the same as 5679 on the other sheet.

ATM I've been using the replace all, but its a manual laborious task
since we are talking about replacing about 50 odd materials. I don'
need the BK818 number, only the 5679 number... Is there a script I ca
automate that will every day run and have it automatically rename thos
numbers for me, so that I don't have too?

I'll be honest with you, I'm not that good on Excel, so please keep i
simple. I only use it at a very basic level.

Thanks in advance

 

[julied d]

Hi Julian

While you could write a script to do this I would think that initially
anyway it would be easier to use a formula.

What you would need to do is create a list on a separate sheet with
two columns, e.g.
A....................................B
OldNumber....................NewNumber
BK818...........................5679
etc

Once you have listed these save the sheet (give it a good name, e.g.
REFLIST). This sheet will then need to be used every day in the file
that you download your data into.

Once you have downloaded your daily sheet, insert a new column to the
right of the column that has the 'old numbers' in it
e.g
A...................B.........................C
Date.........OldNumber...........NewNumber
1/1/12.......BK818...................
etc

Now in the "new number" column use a formula to look up the old number
in the list and put the new number in:
=VLOOKUP(B2,REFLIST!$A$1:$B$51,2,0)

Then copy this formula down all your data.

You can then copy / paste special values the data in column C and
delete column B.

Hope this helps.
JulieD

 

[JulianUK]

Hi Julian

While you could write a script to do this I would think that initially
anyway it would be easier to use a formula.

What you would need to do is create a list on a separate sheet with
two columns, e.g.
A....................................B
OldNumber....................NewNumber
BK818...........................5679
etc

Once you have listed these save the sheet (give it a good name, e.g.
REFLIST). This sheet will then need to be used every day in the file
that you download your data into.

Once you have downloaded your daily sheet, insert a new column to the
right of the column that has the 'old numbers' in it
e.g
A...................B.........................C
Date.........OldNumber...........NewNumber
1/1/12.......BK818...................
etc

Now in the "new number" column use a formula to look up the old number
in the list and put the new number in:
=VLOOKUP(B2,REFLIST!$A$1:$B$51,2,0)

Then copy this formula down all your data.

You can then copy / paste special values the data in column C and
delete column B.

Hope this helps.
JulieD

Wow thanks for your help Julie ! You are a star