CODEWITHSUNDEEP
excelcountexcel-formula
Alex
Alex

Reputation: 51

Select Last of each ID in Excel

I have a table in Excel with multiple map units, and a value corresponding to each map unit. However map units are listed multiple times, and I only want that last value for each map unit to be selected.

For example:

MAPUNIT = 1 ; VALUE = 2

MAPUNIT = 1 ; VALUE = 4

MAPUNIT = 1 ; VALUE = 1

MAP UNIT =2 ; VALUE = 3

MAPUNIT=2 ; VALUE = 4

MAPUNIT = 3; VALUE = 2

MAPUNIT = 4; VALUE =1

MAPUNIT = 4; VALUE = 5

And I want the output to look like:

MAPUNIT =1 ; VALUE = 1

MAPUNIT = 2; VALUE = 4

MAPUNIT =3 ; VALUE = 2

MAPUNIT =4 ; VALUE = 5

Is there a formula in Excel or a SQL query to do this?

Thank you!

Upvotes: 0

Views: 255

Answers (2)

pnuts
pnuts

Reputation: 59485

If you won't provide a proper example I can only guess that, perhaps,: 

=IF(A2<>A3,B2,"")

might be suitable in Row 2 and copied down to suit, combined with filtering out the blanks in the relevant column.

Upvotes: 0

Nicholas Flees
Nicholas Flees

Reputation: 1953

OK, I think this should work. My assumptions are that the cells you reference above are in A1:B9 (with column headers in row 1) and I'm putting the unique MAPUNIT numbers in column E and the formulas are going into column F. 

=INDEX(OFFSET($B$1,MATCH(E2,$A$2:$A$9,0),0,COUNTIF($A$2:$A$9,E2),1),COUNTIF($A$2:$A$9,E2))

Please let me know if you need any clarification.

EDIT:

In case your list of unique MAPUNIT values is much longer, you can make the list by copying the original column and then doing a remove duplicates operation.

Upvotes: 1

Related Questions