Find just "last row number"

K

Kishan

Well-known Member
Joined
Mar 15, 2011
Messages
1,648
Office Version
  1. 2010
Platform
  1. Windows
Using Excel 2000<o:p></o:p>
<o:p></o:p>
Hi,<o:p></o:p>
<o:p></o:p>
I want a Formula or may be VBA that can locate the only the "last row number" <o:p></o:p>
<o:p></o:p>
In the column C, there are repeated values<o:p></o:p>
In the column E, there are unique values; I need to search the "last row number" of each value column E, and result value be placed in column F<o:p></o:p>
<o:p></o:p>
Note: cells are coloured shown just to show example clearer <o:p></o:p>
<o:p></o:p>
Result data example<o:p></o:p>


Book1
ABCDEFG
1
2
3
4
5Join Count Patt VariUnique PattLast Row Number
60|6|10|0|0
72|0|10|0|143
80|0|10|1|0
94|1|21|0|0
102|4|00|0|2
111|3|01|0|1
121|1|61|1|060
132|4|02|0|0
142|0|10|0|3
153|4|00|2|1
161|1|01|0|2
172|4|21|1|1
182|2|01|2|0
192|2|02|0|140
200|1|22|1|0
212|0|13|0|0
220|1|10|0|4
231|5|00|1|3
241|4|10|2|2
250|0|10|4|0
264|0|11|0|3
270|3|11|2|1
282|0|11|3|011
291|6|02|0|2
302|4|02|1|1
312|2|22|2|019
324|0|23|0|1
331|3|34|0|0
342|3|10|0|5
352|3|20|1|4
361|3|10|4|1
372|0|10|5|023
381|6|01|0|4
390|3|01|2|2
402|0|11|3|151
410|3|12|0|3
422|2|22|1|2
430|0|12|2|1
441|4|02|3|0
450|2|33|0|2
461|6|03|1|1
473|4|13|2|0
484|0|14|1|0
492|1|35|0|0
502|2|30|0|6
511|3|10|1|5
520|1|20|2|4
531|0|60|3|3
541|1|00|4|2
550|5|20|5|1
560|2|00|6|0
571|1|01|0|5
580|3|21|1|4
593|1|01|2|3
601|1|01|3|2
613|5|01|5|0
621|1|22|0|4
63
64
Sheet2


Thank you in advance<o:p></o:p>
<o:p></o:p>
Regards,
Kishan
 
Last edited:

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Sort by date Sort by votes
Try this:-
Code: 
[COLOR="Navy"]Sub[/COLOR] MG09Sep32
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range("C6", Range("C" & Rows.Count).End(xlUp))
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    .Item(Dn.Value) = Dn.Row
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng.Offset(, 2)
    Dn.Offset(, 1).Value = .Item(Dn.Value)
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
How about
Code: 
Sub Getlastrow()
   Dim Cl As Range
   
   With CreateObject("scripting.dictionary")
      For Each Cl In Range("C6", Range("C" & Rows.count).End(xlUp))
         .Item(Cl.Value) = Cl.Row
         Debug.Print Cl.Value, .Item(Cl.Value)
      Next Cl
      For Each Cl In Range("E6", Range("E" & Rows.count).End(xlUp))
         Cl.Offset(, 1).Value = .Item(Cl.Value)
      Next Cl
   End With
End Sub
Beaten2it :(
 
Last edited:
Upvote 0
MickG said:
Try this:-
Code: 
[COLOR=navy]Sub[/COLOR] MG09Sep32
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range, Dn [COLOR=navy]As[/COLOR] Range, n [COLOR=navy]As[/COLOR] [COLOR=navy]Long[/COLOR]
[COLOR=navy]Set[/COLOR] Rng = Range("C6", Range("C" & Rows.Count).End(xlUp))
[COLOR=navy]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
    .Item(Dn.Value) = Dn.Row
[COLOR=navy]Next[/COLOR]
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng.Offset(, 2)
    Dn.Offset(, 1).Value = .Item(Dn.Value)
[COLOR=navy]Next[/COLOR] Dn
[COLOR=navy]End[/COLOR] [COLOR=navy]With[/COLOR]
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]
Regards Mick
Click to expand...
Thank you Mike, Prefect solution!! </SPAN></SPAN>

Thank you for your help</SPAN></SPAN>

Have a nice day</SPAN></SPAN>

Kind Regards,</SPAN>
Kishan :)</SPAN></SPAN>
 
Upvote 0
Fluff said:
How about
Code: 
Sub Getlastrow()
   Dim Cl As Range
   
   With CreateObject("scripting.dictionary")
      For Each Cl In Range("C6", Range("C" & Rows.count).End(xlUp))
         .Item(Cl.Value) = Cl.Row
         Debug.Print Cl.Value, .Item(Cl.Value)
      Next Cl
      For Each Cl In Range("E6", Range("E" & Rows.count).End(xlUp))
         Cl.Offset(, 1).Value = .Item(Cl.Value)
      Next Cl
   End With
End Sub
Beaten2it :(
Click to expand...
Thank you Fluff, yes also, it is working Prefect!! </SPAN></SPAN>

Thank you for your help</SPAN></SPAN>

Have a nice day</SPAN></SPAN>

Kind Regards,</SPAN>
Kishan :) </SPAN></SPAN>
 
Upvote 0
Glad we could help & thanks for the feedback
 
Upvote 0
You asked for either a formula or VBA solution. Everyone gave you VBA solutions... here is a formula solution that should work with your early version of Excel. Put this formula in cell F6 and copy it down...

=IF(ISNUMBER(MATCH(E6,C:C,0)),LOOKUP(2,1/(C:C=E6),ROW(C:C)),"")

Note: Your posted example has an error in it... the cell with the last row number of 23 is identifying a pattern that does not exist... the 23 belongs in cell F61.
 
Last edited:
Upvote 0
Rick Rothstein said:
You asked for either a formula or VBA solution. Everyone gave you VBA solutions... here is a formula solution that should work with your early version of Excel. Put this formula in cell F6 and copy it down...

=IF(ISNUMBER(MATCH(E6,C:C,0)),LOOKUP(2,1/(C:C=E6),ROW(C:C)),"")

Note: Your posted example has an error in it... the cell with the last row number of 23 is identifying a pattern that does not exist... the 23 belongs in cell F61.
Click to expand...
In thinking about it, it might be more efficient to limit the top end of the ranges to the last row with data (leave the start of the ranges at C1)...

=IF(ISNUMBER(MATCH(E6,C$1:C$62,0)),LOOKUP(2,1/(C$1:C$62=E6),ROW(C$1:C$62)),"")
 
Upvote 0
Rick Rothstein said:
In thinking about it, it might be more efficient to limit the top end of the ranges to the last row with data (leave the start of the ranges at C1)...

=IF(ISNUMBER(MATCH(E6,C$1:C$62,0)),LOOKUP(2,1/(C$1:C$62=E6),ROW(C$1:C$62)),"")
Click to expand...
Thank you Rick, Post#8 formula is giving an error #¡NUM! but the post#9 above formula worked perfect!!</SPAN></SPAN>

Yes posted example has an error in it... the 23 belongs in cell F61... not to cell F31</SPAN></SPAN>

Thank you for your help</SPAN></SPAN>

Have a nice day</SPAN></SPAN>

Kind Regards,</SPAN>
Kishan :)</SPAN></SPAN>
 
Upvote 0
You must log in or register to reply here.

Similar threads

Seafox
  • Question Question
Glide Path Calculation
Replies
3
Views
159
RobP
R
H
  • Question Question
Transposing Data - Evaluating formula to an array error.
Replies
1
Views
42
Skyybot
Skyybot
M
  • Question Question
Pivot table not showing as Numbers
Replies
1
Views
274
shift-del
shift-del
D
  • Question Question
Compile error: Wrong number of arguments or invalid property assignment
Replies
2
Views
71
daveyc18
D
D
  • Question Question
Formula help needed for working with days and times.
Replies
0
Views
319
dshtesy
D

Forum statistics

Threads
1,223,911
Messages
6,175,324
Members
452,635
Latest member
laura12345

Share this page

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
Back
Top