Advanced Find Replace in different column

[dnorton22]

I have a spreadsheet that has a column A with a unique code per row, multiple codes in column A, i.e. misc, gasr, wter etc.

Misc = Administration Fee
wter = Water
gasr = Gas

In the same row I have a column B that has data like = "10/6/2010 - 11/4/2010 Due Date -12/5/2010"

Based on the data in column A I need to change Column B to be "Gas Due Date - 12/05/2010" Removing the first part and replacing a value based on Column A but NOT equal to column A.

I need to replace either part of column B or all it, it already has the correct "Due Date - 12/05/2010", so the first part needs to be replaced with Gas, or Water or Admin Fee which is determined by the code in column B.

This sounds like an advanced search and replace but I cannot find in Mr.Excels forums on the Web an exact solution.

Thanks,

 

[tweedle]

Here's a formula version:

Excel Workbook
	A	B	C
6	gasr	10/6/2010 - 11/4/2010 Due Date -12/5/2010	Gas Due Date -12/5/2010
7	Misc	10/6/2010 - 11/4/2010 Due Date -12/5/2010	Administration Fee Due Date -12/5/2010
8	wter	10/6/2010 - 11/4/2010 Due Date -12/5/2010	Water Due Date -12/5/2010
9	gasr	10/6/2010 - 11/4/2010 Due Date -12/5/2010	Gas Due Date -12/5/2010
Sheet1

 

[motmit]

hey jus signed up n thought id try see what i could do
i know u on bout a find function or sumit but how bout simple text functions- try this:

Sub test()
    
'find last row below A2 on sheet 1   (from a1 could give value aaa = 0)
    Dim aaa As Long
    Dim bbb As Long
    aaa = 0
    bbb = 2
    Do Until aaa > 0
    If Sheets("Sheet1").Range("a" & bbb).Value = "" Then
    aaa = bbb - 1
    Else
    bbb = bbb + 1
    End If
    Loop
'do process for every row
Dim ccc As Long
For ccc = 1 To aaa
Sheets("Sheet1").Range("b" & ccc).Value = (WorksheetFunction.VLookup(Sheets("Sheet1").Range("a" & ccc).Value, Range("codetable"), 2, False) & " Date Due" & Right(Sheets("Sheet1").Range("b" & ccc).Value, 11))
Next ccc

End Sub

ahh lol while i was givin it a go seems i was beaten -- id say go with the other guy --- i 4got to add tht my code involves a table somewhere that defined as the name 'codetable' with codes in first column and what the code means in 2 column.... anyway ill leave my code up....

 

[dnorton22]

THANKS!!! This looks like it will work perfectly. I will give it a go today.

I am in a fast growing Utility Billing department of my company. I have 16 spreadsheets with about 3000 apartments and as you saw each apartment can have one or up to 4 "codes" - gasr, watr etc.

The data is exported from our managment software with a workbook for each property. What is the best way to do this for what is now 16 properties but might end up being over 100? This changes everything, no?

 

[dnorton22]

Thanks to motmit, however, my skills are not at a point where I have no clue what you did. I probably works, and might even be a better solution for the future based on my comment to "tweedle". Could you give me some direction about what I need to know or study to understand what you sent me?

Thanks again.

 

[dnorton22]

How do I get you a nice screen shot of what is happening?

It works except for the new "code".

1. I added trsh in the appropriate places and it comes up Administration
2. I need the new column of data to replace the old column and delete the new column. Manually that is easy. I am trying to remove steps and automate. The sheet is then imported into another program so the info has to be where it has to be.
3.

 

[motmit]

Thanks to motmit, however, my skills are not at a point where I have no clue what you did. I probably works, and might even be a better solution for the future based on my comment to "tweedle". Could you give me some direction about what I need to know or study to understand what you sent me?

Thanks again.

hey erm ill try and explain the code i did last night, then mayb try n figure my way around the new problem you got if i got time (i dunno bout rules or if tht should involve a different thread)

k in ur excel file where u got the list with the codes in colum a and dates in B go to a different sheet and type out all the different codes in one column and what the code means next to it. Then select all of these cells and go insert>name>define then name the range 'codetable' hit add

then go back to the sheet and hit alt+f11 (visual basic editor) click insert>module

copy and paste all the code I typed before into this module. (if your columns of codes and dates are not in sheet1 columns "a" and "b" then replace every instance of "sheet1" with the "name of your sheet" if the codes and dates are not in "a" and "b" then replace with the relevent column

save and close this visual basic editor. in excel go to tools>macro>macros then click run

hope this helps, sos i would put some images up but cnt find easy online upload without registration tht seems to work :/

 

[dnorton22]

Thanks, when I figure out how to get images up I will let you know.

 

[dnorton22]

I figured out that the lookup needs to be in ascending order. Thanks, that is fixed. Now for the automation.

 

[motmit]

How do I get you a nice screen shot of what is happening?

It works except for the new "code".

1. I added trsh in the appropriate places and it comes up Administration
2. I need the new column of data to replace the old column and delete the new column. Manually that is easy. I am trying to remove steps and automate. The sheet is then imported into another program so the info has to be where it has to be.
3.

if you are happier to not work with code do the formula method then

tools>macro>record new macro


select the entire new column (c) by clicking on the column header

edit>copy

select column b

edit>paste special>values>ok

then jus delete column c (using cut doesnt seem to allow paste special)

tools>macro>stop recording

------------
now to run the macro (automate) tools>macro>macros , select the name of the macro u jus recoded (macro1 - default) click run