FIRST & LAST occurence

H

hsandeep

Well-known Member
Joined
Dec 6, 2008
Messages
1,226
Office Version
  1. 2010
Platform
  1. Windows
  2. Mobile
B2:B9999 contains NAMES
C2:C9999 contains values

Output required (D & E)
D2=First Occurrence of the C column (matching B)
E2=Last Occurrence of the C column (matching B)

How to accomplish?
Thanks in advance.
I am using Excel 2007

Sheet1

BCDE
Peter
James
England
Peter
England
England

<colgroup><col style="FONT-WEIGHT: bold; WIDTH: 30px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="align: right"]55[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]88[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]77[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="align: right"]88[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]88[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="align: right"]99[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]77[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

[TD="align: right"]77[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]77[/TD]

</tbody>
 
Sort by date Sort by votes
Maybe:

Excel 2010
ABCDE
Peter
James
England
Peter
England
England

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]

[TD="align: right"]55[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]88[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]

[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]77[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]

[TD="align: right"]88[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]88[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]

[TD="align: right"]99[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]77[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]

[TD="align: right"]77[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]77[/TD]

</tbody>
Sheet3​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D2[/TH]
[TD="align: left"]=INDEX($C$2:$C$7,MATCH(B2,$B$2:$B$7,0))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]E2[/TH]
[TD="align: left"]=INDEX($C$2:$C$7,LOOKUP(2,1/($B$2:$B$7=B2),ROW($B$2:$B$7)-ROW($B$2)+1))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Put the formulas in D2 and E2, then copy and paste them down the columns.
 
Upvote 0

​
[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]D2[/TH]
[TD="align: left"]=INDEX($C$2:$C$7,MATCH(B2,$B$2:$B$7,0))[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]E2[/TH]
[TD="align: left"]=INDEX($C$2:$C$7,LOOKUP(2,1/($B$2:$B$7=B2),ROW($B$2:$B$7)-ROW($B$2)+1))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Put the formulas in D2 and E2, then copy and paste them down the columns.[/QUOTE]

Not May Be; It WORKS. Thanks Eric W
 
Upvote 0
Also needed,
In Column F, 'total count' of names in column B.
 
Upvote 0
Do you mean the total number of times "Peter" shows up, or the total number of unique values? Both those formulas are included below.

Excel 2010
BCDEFG
Peter
James
England
Peter
England
England

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]55[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]88[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]77[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]88[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]88[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]99[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]77[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]77[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]77[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]

</tbody>
Sheet3​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]F2[/TH]
[TD="align: left"]=COUNTIF($B$2:$B$7,B2)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]G2[/TH]
[TD="align: left"]=SUMPRODUCT(1/COUNTIF($B$2:$B$7,$B$2:$B$7&""))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Eric W said:
Do you mean the total number of times "Peter" shows up, or the total number of unique values? Both those formulas are included below.


[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]F2[/TH]
[TD="align: left"]=COUNTIF($B$2:$B$7,B2)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]G2[/TH]
[TD="align: left"]=SUMPRODUCT(1/COUNTIF($B$2:$B$7,$B$2:$B$7&""))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

Both needed. Thanks once again. It WORKS.
 
Upvote 0
Also needed,
In Column H, total numbers of 'UNIQUE' names in column B. (answer = 3 for above table)
 
Last edited:
Upvote 0
hsandeep said:
Also needed,
In Column H, total numbers of 'UNIQUE' names in column B. (answer = 3 for above table)
Click to expand...

How is that request different to what Eric W gave you in G2 of post #5
 
Upvote 0
hsandeep said:
Also needed,
In Column H, total numbers of 'UNIQUE' names in column B. (answer = 3 for above table)
Click to expand...
If there is a possibility that the range in column B greater (eg. B2:B20), then I suggest the following formula in cell G2
Code: 
=SUMPRODUCT((B2:B20<>"")/COUNTIF(B2:B20,B2:B20&""))
This formula ignore blank cells
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
If blank, find next matching data set and fill in the blanks
Replies
3
Views
104
CascadeDiver
C
I
  • Solved
Excel Filtering for a Summary Tab
2 3 4
Replies
35
Views
1K
ItalianPlatinum
I
ibmy
  • Solved
Count number of occurence of value with condition
2
Replies
10
Views
405
Alex Blakenburg
A
K
  • Solved
VLookup and Count formula
Replies
5
Views
120
etaf
etaf
A
  • Solved
Last occurence if two criteria match
Replies
8
Views
265
etaf
etaf

Forum statistics

Threads
1,226,895
Messages
6,193,553
Members
453,807
Latest member
PKruger

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