I have a data file which I recieve regularly which needs to be cleane
up before I use it. The data looks like this

Item Description
123, Widget
124, Bodgit
125, Scarper
123, Unknown
124, Unknown
125, Unknown

Does anyone have a routine which could go through the above data an
make sure that the second field is corrected to what the descriptio
should be ?

I.e.

If first cell value equals 123, then make second cell value = widget
Or
If first cell value equals 124, then make second cell value = bodgit
Or
If first cell value equals 125, then make second cell value = scarper

The idea being that for every example of unclean data I come across,
would copy an extra line of code which deals with it in future edition
of the data file that comes in.

Hope someone can help ?

Thanks


Terr

--
Message posted from http://www.ExcelForum.com

Hi
one question: Are these to entries in two different columns? if yes you
don't need any code juyt add a helper column C and enter the formula
=IF(A1=123,"Widget",IF(A1=124,"Bodget",IF(A1=125," Scarper",""))
if you have more items you may consider using VLOOKUP for this. copy
this formula down as far as you need it



--
Regards
Frank Kabel
Frankfurt, Germany

I have a data file which I recieve regularly which needs to be
cleaned
up before I use it. The data looks like this

Item Description
123, Widget
124, Bodgit
125, Scarper
123, Unknown
124, Unknown
125, Unknown

Does anyone have a routine which could go through the above data and
make sure that the second field is corrected to what the description
should be ?

I.e.

If first cell value equals 123, then make second cell value = widget
Or
If first cell value equals 124, then make second cell value = bodgit
Or
If first cell value equals 125, then make second cell value = scarper

The idea being that for every example of unclean data I come across,
I
would copy an extra line of code which deals with it in future
editions of the data file that comes in.

Hope someone can help ?

Thanks


Terry


---
Message posted from http://www.ExcelForum.com/

Thanks Frank

I have kind of used this approach already.

I was wanting to load the raw file, hit the ctrl-a button to run th
routine (or whatever button), then bish bosh, the file is cleaned u
and I can then dive straight in with creating my pivot tables and do m
normal thing.

Thanks again.

Terr

--
Message posted from http://www.ExcelForum.com

.... and also some of the data in the second column is ok and would need
to be left alone.

Regards


Terry


---
Message posted from http://www.ExcelForum.com/

Hi Terry
though this can be done with a macro this wouldn't be faster as using
the formula :-)
But try the following (assumption column A stores the values)

Sub change_data()
Dim row_index As Long
Dim last_row As Long

last_row = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).row
For row_index = 1 To last_row
Select Case Cells(row_index, "A").Value
Case 123
Cells(row_index, "B").Value = "Widget"
Case 124
Cells(row_index, "B").Value = "Bodget"
Case 125
Cells(row_index, "B").Value = "Scarper"
End Select
Next
End Sub


Note: will replace all values in column B (regardless if o.k. or not).
Assign this macro to a button or a shortcut


--
Regards
Frank Kabel
Frankfurt, Germany

Thanks Frank

I have kind of used this approach already.

I was wanting to load the raw file, hit the ctrl-a button to run the
routine (or whatever button), then bish bosh, the file is cleaned up
and I can then dive straight in with creating my pivot tables and do
my normal thing.

Thanks again.

Terry


---
Message posted from http://www.ExcelForum.com/

With your considerable experience in matters Excel, you may think m
easily impressed !

Wonderfully useful routine indeed.

Thanks a bunch.

Terr

--
Message posted from http://www.ExcelForum.com

Frank

I wonder if I might ask a further question ? Can the code be writte
such that it refers to a named file which lists out all the items tha
need updating on the current spreadsheet.

I.e.

Start at current spreadsheet, line 1
Go to reference sheet and compare line 1 against the list in th
reference sheet and make changes as necessary.
Then go to line 2 and so on.... ?

Regards

Terr

--
Message posted from http://www.ExcelForum.com

Hi Terry
sure it can be done. Use the following code

-----
Sub change_data()
Dim target_wks As Worksheet
Dim lookup_wks As Worksheet
Dim lookup_rng As Range
Dim row_index As Long
Dim last_row As Long
Dim ret_lookup

Set target_wks = ActiveSheet
Set lookup_wks = Worksheets("lookup_sheet")
Set lookup_rng = lookup_wks.Range("A1:B30")

last_row = target_wks.Cells(Rows.Count, "A").End(xlUp).row
For row_index = 1 To last_row
With target_wks
If .Cells(row_index, "A").Value < "" Then
On Error Resume Next
ret_lookup = Application.WorksheetFunction.VLookup _
(.Cells(row_index, "A").Value, lookup_rng, 2, 0)
If Err.Number < 0 Then
ret_lookup = "N/A"
End If
On Error GoTo 0
.Cells(row_index, "B").Value = ret_lookup
End If
End With
Next
End Sub

-----

Assumption:
- the sheet with the references is called lookup_sheet (change this in
the code to your needs)
- the values are in columns A+B


--
Regards
Frank Kabel
Frankfurt, Germany

Frank

I wonder if I might ask a further question ? Can the code be written
such that it refers to a named file which lists out all the items
that
need updating on the current spreadsheet.

I.e.

Start at current spreadsheet, line 1
Go to reference sheet and compare line 1 against the list in the
reference sheet and make changes as necessary.
Then go to line 2 and so on.... ?

Regards

Terry


---
Message posted from http://www.ExcelForum.com/

Thats great Frank

It works a treat.

Have a good day.

Terr

--
Message posted from http://www.ExcelForum.com