• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to identify first instance and second instance of same value in a worksheet

ThrottleWorks

ThrottleWorks

Excel Ninja
Hi,

I have a value, for example, Yamaha, I have worksheet.
Yamaha is present twice in A Column of this worksheet.

Say, A5 and A10. cell B5 = ABC12 and cell B10 = XYZ45.
I need to find out what is the first instance of Yamaha and what is second, this is required for further processing.

I was thinking of running a loop, get row number, sort it, but I am sure this as weird way to do it.

Can anyone please suggest me a better solution.
 
Are you looking for a formula solution, or just a VBA solution? What do you want to happen when you find the Nth instance?
 
1. Assuming that your data starts in Row2, insert following formula in C2 and copy down:
=COUNTIF($A$2:A2,A2)
2. Once done, copy column C and do paste special values to remove formulas. Now you will have all items numbered as 1,2,3 and so forth.
3. Use built-in sort tool with >> Column A (first sort) and Column C (second sort ascending).

Hth,
 
Hi @Luke M sir, thanks for the help. There will be only 2 instances of string I am trying to find. I need to find this string using xlpart, can not use xlWhole.

I need values in next cell of this string. For example, when I find first instance in cell A2, value in cell B2 will be labelled as previous rate, second instance is at A5, value in cell B5 will my current rate. I need these rates for further processing.

I am looking for VBA solution. Have nice day ahead. :)

Hi @shrivallabha , thanks for the help, my mistake, not gave complete information earlier, I need to do a partial string search, I will be comfortable with VBA solution. Have a nice day ahead. :)
 
Hi @shrivallabha , thanks for the help, key word is only one. Yamaha (dummy).
In my file, Yamaha will be present only twice in B column. It will be something like Yamaha XYZ. Yamaha ABC.

Yamaha will be separated space and other word.
Such as Yamaha XYZ, Yamaha ABC. So, once I get both the Yamaha differently, I can process further and carve out required part which will be XYZ and ABC.

Good night. :)
 
Without loop and based on assumption that you will have two instances in column B, following code will write value 1 & 2 in column D.
Code: 
Public Sub FindTwoInstances()
Dim rngFrstInst As Range, rngScndInst As Range
With Range("B1:B" & Range("A" & Rows.Count).End(xlUp).Row)
    Set rngFrstInst = .Find("Yamaha", [B1], , xlPart, , xlNext)
    rngFrstInst.Offset(0, 2).Value = 1 '\\ First instance
    Set rngScndInst = .FindNext(rngFrstInst)
    rngScndInst.Offset(0, 2).Value = 2 '\\ Second instance
End With
End Sub
 
You must log in or register to reply here.
Back
Top