# Find and Match

#### miztiik

##### New Member
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
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
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
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
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
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
his[ smartchap ] code did some matching
i m not sure with [ Lewiy ] code its not working for me

#### Lewiy

##### Well-known Member
What exactly is not working with my code? Perhaps we can fix this!

#### miztiik

##### New Member
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
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?

Replies
10
Views
260
Replies
9
Views
219
Replies
7
Views
301
Replies
5
Views
174
Replies
5
Views
285

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?

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