Find and Match

miztiik

New Member
Joined
Jul 4, 2007
Messages
20
Code:
     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
Code:
     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
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Krishnakumar

Well-known Member
Joined
Feb 28, 2003
Messages
2,615
Hi,

A formula based approach..

In B1 and copied down,

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

HTH
 

Lewiy

Well-known Member
Joined
Jan 5, 2007
Messages
4,284
Try this:
Code:
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

Board Regular
Joined
Aug 26, 2006
Messages
63
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

New Member
Joined
Jul 4, 2007
Messages
20
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

Well-known Member
Joined
Jan 5, 2007
Messages
4,284
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:
Code:
If UCase(Cells(b, 1)) = UCase(Left(Cells(d, 3), 3)) Then
 

miztiik

New Member
Joined
Jul 4, 2007
Messages
20
his[ smartchap ] code did some matching
i m not sure with [ Lewiy ] code its not working for me
 

Lewiy

Well-known Member
Joined
Jan 5, 2007
Messages
4,284
What exactly is not working with my code? Perhaps we can fix this!
 

miztiik

New Member
Joined
Jul 4, 2007
Messages
20
Code:
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

New Member
Joined
Jul 4, 2007
Messages
20
Code:
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?
 

Forum statistics

Threads
1,181,733
Messages
5,931,726
Members
436,800
Latest member
abowalid98

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top