A very particular way to select data

[gregmartjim]

Hello Mr Excel and everyone

I have a problem , i am trying to select some data from a set but in a very particular way.
the data i have is something like the data below, what id like to do is to delete the whole row if the number in the second column is repeated, but i want to keep the last repeated value and delete the other values above.

For example the first and second row have the same value in column 2 (the value is 1) i want to delete all the first row and just leave the second one. An other example, the rows 9 to 11 have the same value in column 2 (the value is 1 again) here i would like to delete row 9 & 10. Final example, the rows 3 and 4 have the same value (2) i would like to delete all row number 3 and keep row 4. I will really appreciate if someone could help me with this problem. Thanks for reading

1	1	0.64	41.93	1274.895
1	1	0.66	41.12	1274.8375
1	2	0.97	40.68	1290.7075
1	2	0.93	41.88	1290.7075
1	3	1.11	44.38	1311.235
1	3	0.83	41.92	1311.12
1	4	1.18	42.83	1331.245
1	4	1.58	43.26	1331.4175
1	1	0.57	54.07	1270.98
1	1	0.57	55.14	1270.98
1	1	0.68	56.19	1270.98
1	2	0.52	53.8	1290.96
1	2	0.56	53.36	1290.78
1	3	0.76	58.58	1310.94
1	3	0.64	56.7	1310.76
1	4	0.75	49.14	1331.1
1	4	0.8	46.67	1331.1
1	1	0.48	52.05	1271.34
1	1	0.52	51.57	1271.34
1	1	0.52	50.41	1271.34
1	2	0.7	50.84	1291.14
1	2	0.6	51.76	1291.14
1	3	0.69	54.79	1311.12
1	3	0.8	55.45	1311.12
1	4	0.83	51.36	1331.1
1	4	1.05	52.73	1331.1
1	1	0.47	39.46	1271.0425
1	1	0.41	39.52	1270.985
1	2	0.58	53.1	1290.995
1	2	0.6	52.65	1290.995
1	3	1.05	52.44	1311.12
1	3	0.98	53.37	1311.12
1	4	1.07	48.86	1331.015
1	4	1.1	47.46	1331.015
1	1	0.42	56.86	1270.98
1	1	0.44	54.53	1270.98
1	2	0.42	50.12	1290.96
1	2	0.43	51.05	1290.96
1	3	0.58	58.82	1310.76
1	3	0.54	57.63	1310.76
1	4	0.79	48.76	1331.1
1	4	0.79	48.21	1331.1

<colgroup><col span="4"><col></colgroup><tbody>
</tbody>

 

[gregmartjim]

Tom
The rows i will keep are not the "-" (these are the first of every set of repetition) i want to keep the last of every set of repetitions, so far your formula gives me the first one, not the last one. An example is the row 4 and row 9 are rows i would like to delete.

 

[Tom Urtis]

My first formula did what you now say. We've come full circle on this because this last iteration kept the last and deleted the previous repeated items. Maybe something else is going on with your workbook that you are either not saying or not aware of. Otherwise, I cannot assist further without a more complete representation of your data and what you expect.

 

[Joyner]

Hello, am I reading this wrong, or do you want a formula like this to filter for the "X" (in column "H" for row 2)

=IF(D2=D3,"","X")

 

[gregmartjim]

Tom
Sorry about this could you please put the old and the new formula to see the difference, old one selecting the last data, and new selecting the first.

Joyner

I want a formula to select the last row of the repeated number, tom help me selecting this with an "x" or a "-" of each , after this selection i already now how to filter the data

 

[Joyner]

Ok, I think my formula is putting an "X" for rows to keeps and Tom's was putting an "X" for rows to delete (I guess I usually use ones and zeros). If that is what you want try my formula like this:

=IF(D2=D3,"X","")


If I am applying Tom's formula correctly, it looks like it is missing some where there are multiple instances and there is only one of the next number - I'm not sure if you would have that case.

 

[gregmartjim]

Joyner

Your formula separates the first with a "-" from all the set of repeated values but i want to keep the last one , not the first.

NNK6-000	1	1	1	0.94	-0.94	1270.8	-
NNK6-000	1	1	1	0.96	-0.96	1270.8	x
NNK6-000	1	1	1	0.93	-0.93	1270.8	x
NNK6-000	1	1	4	10.92	39.93	1336.5	-
NNK6-000	1	1	4	10.93	39.97	1336.5	x
NNK6-027	2	1	1	0.64	41.93	1274.895	-
NNK6-027	2	1	1	5.63	-0.48	1273.2275	x
NNK6-027	2	1	1	0.66	41.12	1274.8375	x
NNK6-027	2	1	1	210	-165.34	1272.48	x
NNK6-027	2	1	2	0.97	40.68	1290.7075	-
NNK6-027	2	1	2	0.93	41.88	1290.7075	x
NNK6-027	2	1	3	1.11	44.38	1311.235	-
NNK6-027	2	1	3	0.83	41.92	1311.12	x
NNK6-027	2	1	4	1.18	42.83	1331.245	-
NNK6-027	2	1	4	1.58	43.26	1331.4175	x

<colgroup><col span="6"><col><col></colgroup><tbody>
</tbody>

 

[Joyner]

Post Deleted...

 

[gregmartjim]

Success

Thanks guys you help me a lot to solve it , actually what i was looking for was the opposite of what you were giving me, the right formula should be in this configuration

=IF(D2<>D3,"X","")