First and Last value with offset.

[Luke777]

Hi all

Below is my data

12	blue	orange
12	green	lamp
12	chicken	sheep
12	yellow	epsilon
4	guru	door
4	switch	bar
4	token	island
8	park	coal
8	snow	plug
9	number	salt
9	gorge	ear

I'm looking for a simple way to get the first entry in column B and last entry in column C for any given reference in column A

so for "12" the results would be "blue" and "epsilon", which I would like to be displayed in E1 and F1 followed by the results of "4" in E2 and F2.

The last part isn't a strict requirement - by that I mean, if the result of 4 ends up being displayed in E5 and F5, that's fine, I can deal with the blanks easily enough

For a bit extra context, I have a tiny bit of code that finds the unique entries in column A (below) and I was imagining combining it with the range.offset function - but I'm afraid this has defeated me

Public Sub EN()

ActiveSheet.Range("A1:A65536").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=ActiveSheet.Range("D1"), Unique:=True
 
 
End Sub

If the results of this code in D can be used as a reference point for the offset and some sort of loop, that would be useful lol. I should also note that if this bit of code is at all useful, then what I said about the blanks above is no longer relevant

Hopefully this all made sense.

Thanks all!

 

[Alex Blakenburg]

I am not sure I understand the requirement. Is this what you mean ?

Book2
	A	B	C	D	E	F
1	12	blue	orange	blue	epsilon	4
2	12	green	lamp
3	12	chicken	sheep
4	12	yellow	epsilon
5	4	guru	door
6	4	switch	bar
7	4	token	island
8	8	park	coal
9	8	snow	plug
10	9	number	salt
11	9	gorge	ear
Sheet1

Cell Formulas
Range	Formula
D1	D1	=VLOOKUP(A1,$A$1:$C$11,2,FALSE)
E1	E1	=LOOKUP(2,1/($A$1:$A$11=A1),$C$1:$C$11)
F1	F1	=COUNTIF($A$1:$A$11,A1)