Find and Match

[miztiik]

     A--------------b----------------c
1  abc                           klm-sometext
2  def                           nop-sometext
3  ghi                           def-sometext
4  klm
5  nop                           ghi-sometext
6                                abc-sometext

i want a macro , so that when it is run,
it matches the value in column A with value in Column C
and update the same in Column B

For example the macro should find

A1 Value = abc with C6 Value= abc-sometext
and update
B1 value as = abc-sometext

So after running the macro
the excel sheet should look like this

     A-----------------b------------------------c
1  abc           abc-sometext              klm-sometext
2  def           def-sometext              nop-sometext
3  ghi           ghi-sometext              def-sometext
4  klm           klm-sometext
5  nop           nop-sometext              ghi-sometext
6                                          abc-sometext

 

[Krishnakumar]

Hi,

A formula based approach..

In B1 and copied down,

=INDEX($C$1:$C$6,MATCH(A1&"*",$C$1:$C$6,0))

HTH

 

[Lewiy]

Try this:

Sub SortThem()
Dim a As Long
Dim b As Long
Dim c As Long
Dim d As Long
a = Cells(Rows.Count, 1).End(xlUp).Row
c = Cells(Rows.Count, 3).End(xlUp).Row
For b = 1 To a
    For d = 1 To c
        If Cells(b, 1) = Left(Cells(d, 3), 3) Then
            Cells(b, 2) = Cells(d, 3)
        End If
    Next d
Next b
End Sub

 

[smartchap]

Try the following macro, it works:
Sub macro1()
i = 1
Label1:
If Sheets(1).Cells(i, 1).Value <> "" Then i = i + 1: GoTo Label1
i = i - 1 'i is no.of filled rows.
For j = 1 To i
For k = 1 To i
If Sheets(1).Cells(j, 1).Value = Left$(Sheets(1).Cells(k, 3).Value, Len(Sheets(1).Cells(j, 1).Value)) Then
Sheets(1).Cells(j, 2).Value = Sheets(1).Cells(k, 3).Value
Exit For
End If
Next
Next

End Sub


Always believe in GOD.

 

[miztiik]

well that script works...when the number of cell values in colum A and column C are equal.

I want to know , what value should i increase if i have more values in Column C [as shown in example above]

and is this a case insensitive search?

 

[Lewiy]

My code should work for any length of lists in either column. The match up is case sensitive, but to get around this I think a simple alteration should work, i.e:

If UCase(Cells(b, 1)) = UCase(Left(Cells(d, 3), 3)) Then

 

[miztiik]

his[ smartchap ] code did some matching
i m not sure with [ Lewiy ] code its not working for me

 

[Lewiy]

What exactly is not working with my code? Perhaps we can fix this!

 

[miztiik]

Cells(Rows.Count, 1).End(xlUp).Row

i suppose this is not working properly.

is it ok to write like that without referrring to the sheet number/name?

 

[miztiik]

Sub macro1()
i = 1
Label1:
If Sheets(1).Cells(i, 1).Value <> "" Then i = i + 1: GoTo Label1
i = i - 1 'i is no.of filled rows in columnA
m = 1
Label2:
If Sheets(1).Cells(m, 3).Value <> "" Then m = m + 1: GoTo Label2
m = m - 1 'i is no.of filled rows in columnC.


For j = 1 To i
For k = 1 To m
If Sheets(1).Cells(j, 1).Value = Left$(Sheets(1).Cells(k, 3).Value, Len(Sheets(1).Cells(j, 1).Value)) Then
Sheets(1).Cells(j, 2).Value = Sheets(1).Cells(k, 3).Value
Exit For
End If
Next
Next
End Sub

can we do like this..will it work?