populate values in col C based on values contains in B

aravindhan_31 · Dec 15, 2009

Thanks for helping me so many days, I need ur help again...
I have 3 columns in my excel sheet,
Col A( Data)
Col B ( Invoice Description)
Col C( Result)

I have date and Invoice description column filled with data, I need to filter Column B> custom filter> contains > "Dup" and enter value as "Twice" in Column C.
again i customer filter> contains > " Canteen" then enter value in Column C as "Tea"
similarly i have 45 conditions and the number of rows are not fixed, I would appreciate very much if someone could help me to build a code with case function i can add all those conditions:

few Eg:
If values in Column B contains :"uncash" then in C column it should populate Credit in the respective rows
If values in Column B contains :"Dup" then in C column it should populate Twice in the respective rows
If values in Column B contains :"bug" then in C column it should populate Wow in the respective rows
If values in Column B contains :"RQ" then in C column it should populate Pay by Check in the respective rows
If values in Column B contains :"AP13" then in C column it should populate Payment in the respective rows
If values in Column B contains :"Canteen" then in C column it should populate Tea in the respective rows OF
If values in Column B contains :"RSV" then in C column it should populate Bus in the respective rows

Thanks for you help
The cross post is here: http://www.excelforum.com/excel-pro...ased-on-values-contains-in-b.html#post2216736
Arvind

xld · Dec 15, 2009

Code:

Sub IdData()
Dim LastRow As Long

    Application.ScreenUpdating = False
    
    With ActiveSheet
    
        LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
        
        Call UpdateData("uncash", "Credit", LastRow)
        Call UpdateData("dup", "Twice", LastRow)
        'etc
    End With
    
    Application.ScreenUpdating = True
End Sub

Private Sub UpdateData(LookFor As String, Replacement As String, LastRow As Long)

    With ActiveSheet
    
        .Range("B1").AutoFilter Field:=1, Criteria1:="=*" & LookFor & "*", Operator:=xlAnd
        .Range("C2").Resize(LastRow - 1).Value = Replacement
        .Range("B1").AutoFilter
    End With
End Sub

aravindhan_31 · Dec 15, 2009

Hi,

Thanks for your help,

I am getting "Twice" as result for all the values"

Please help me on this

Arvind

xld · Dec 15, 2009

Don't know what to say, I got Twice and Credit in my tests.

aravindhan_31 · Dec 15, 2009

Hi,

I am still getting "Twice" as result for all the value, would you be able to test your code with my sample file, which has been attached in my cross post :http://www.excelforum.com/excel-prog...ml#post2216736

thanks for ur help
Arvind

Jonmo1 · Dec 15, 2009

Thanks to the 5th post by BarryHoudini's in the last challenge of the month
http://www.mrexcel.com/forum/showthread.php?p=1597483#post1597483

Book1

A

B

C

D

E

F

1

Data

InvoiceDescription

Result

FindVal

Result

2

whateverbugIsCool

Wow

uncash

Credit

3

whateverDupIsCool

Twice

Dup

Twice

4

whateverCanteenIsCool

Tea

bug

Wow

5

whateverRqIsCool

PaybyCehck

Rq

PaybyCehck

6

whateverRSVIsCool

Bus

AP13

Payment

7

whateverAP13IsCool

Payment

Canteen

Tea

8

whateveruncashIsCool

Credit

RSV

Bus

Sheet1

xld · Dec 15, 2009

aravindhan_31 said:
Hi,

I am still getting "Twice" as result for all the value, would you be able to test your code with my sample file, which has been attached in my cross post :http://www.excelforum.com/excel-prog...ml#post2216736

thanks for ur help
Arvind

Page Not Found.

aravindhan_31 · Dec 16, 2009

Hi Sorry,

Here is the link
http://www.excelforum.com/excel-pro...ased-on-values-contains-in-b.html#post2216736

Thanks jonmo, the formula looks really fine, I am just checking with my original data for the right result... i will post my reply after this....

ARvind

aravindhan_31 · Dec 16, 2009

Hello jonmo,

would appreciate verymuch if you could explain this formula to me for my learning...

I could not find what is 2^ 15 here

xld · Dec 16, 2009

32767 is the maximum length of a cell, but any large number should work.

populate values in col C based on values contains in B

aravindhan_31

Well-known Member

Excel Facts

xld

Banned

aravindhan_31

Well-known Member

xld

Banned

aravindhan_31

Well-known Member

Jonmo1

MrExcel MVP

xld

Banned

aravindhan_31

Well-known Member

aravindhan_31

Well-known Member

xld

Banned

Similar threads

Forum statistics

Share this page

populate values in col C based on values contains in B

Well-known Member

Excel Facts

Banned

Well-known Member

Banned

Well-known Member

MrExcel MVP

Banned

Well-known Member

Well-known Member

Banned

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock