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>

 

[Tom Urtis]

You can do this without a macro if you want, but if this is a recurring task I recommend the VBA route.

But manually for starters, assuming your data as you presented it starts in row 1, then in cell F2 enter and copy down
=IF(OR(B2=B1,B2<>B3),"X","")

Then filter column F for X

Then, select from the second visible row to the end of the filtered range, hit F5 > Special > Visible cells only > OK

Then from your Ribbon, select the Home tab > Delete > Delete sheet rows.

Finally, exit Filter mode and delete or clear column F.

 

[gregmartjim]

Hello Tom
Thanks for the answer, i am kind of lost, what should i put instead of the "X", also i need to do the same for all the values in column B, some times i got 2 some others i got more.

Regards

 

[gregmartjim]

Also i need to keep all the values of the selected row

 

[Tom Urtis]

The formula should be taken literally. You put the X in it just as I wrote.

This does evaluate column B and it does what you said in your first post: it deleted the rows you said you want to be deleted. On a copy of your workbook, try following the steps just as I wrote them, it should do what you said you want.

 

[gregmartjim]

well i tried as you told me but i just get an X in the column, this is a shorten version of the real data, i change the formula you gave me for this

one =IF(OR(D2=D3,D2<>D3),"X","") , and i put it in column H, notice that in H2 i just got an X.

NNK6-027	2	1	1	0.64	41.93	1274.895	X
NNK6-027	2	1	1	0.66	41.12	1274.8375
NNK6-027	2	1	2	0.97	40.68	1290.7075
NNK6-027	2	1	2	0.93	41.88	1290.7075
NNK6-027	2	1	3	1.11	44.38	1311.235
NNK6-027	2	1	3	0.83	41.92	1311.12
NNK6-027	2	1	4	1.18	42.83	1331.245
NNK6-027	2	1	4	1.58	43.26	1331.4175
TA01-001	3	1	1	0.57	54.07	1270.98
TA01-001	3	1	1	0.57	55.14	1270.98
TA01-001	3	1	1	0.68	56.19	1270.98
TA01-001	3	1	2	0.52	53.8	1290.96
TA01-001	3	1	2	0.56	53.36	1290.78
TA01-001	3	1	3	0.76	58.58	1310.94
TA01-001	3	1	3	0.64	56.7	1310.76
TA01-001	3	1	4	0.75	49.14	1331.1
TA01-001	3	1	4	0.8	46.67	1331.1
TA01-002	4	1	1	0.48	52.05	1271.34
TA01-002	4	1	1	0.52	51.57	1271.34
TA01-002	4	1	1	0.52	50.41	1271.34
TA01-002	4	1	2	0.7	50.84	1291.14
TA01-002	4	1	2	0.6	51.76	1291.14
TA01-002	4	1	3	0.69	54.79	1311.12
TA01-002	4	1	3	0.8	55.45	1311.12
TA01-002	4	1	4	0.83	51.36	1331.1
TA01-002	4	1	4	1.05	52.73	1331.1

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

 

[Fluff]

@gregmartjim
Note that sometimes posts from new users require Moderator approval before you can see them on the public forums. When this happens, you should see a message to that effect when you try to post it.
Please be patient and do not attempt to post the question again.

 

[Tom Urtis]

You changed the data range from your first post to involve more columns which is no big deal except that you did not adjust the formula accordingly.

You changed the formula to this:

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

but it should have been changed to this:

=IF(OR(D2=[COLOR=#b22222][B]D1[/B][/COLOR],D2<>D3),"X","")

When you do that and follow the rest of the steps from my first post on this thread, you should get the result you said you want because it works for me.

 

[gregmartjim]

Thanks again Tom

It works nice when i have to select between 2 but not when i have 3 of the same, when this happens i get the x in the last two. herean example, from D3 to D5 i have the same number, and i would like to select just all the row from D5, right now i get D4 and D5. The column D i some times have two rows with the same number, some times 3, some times 4 or even more and i just want to select the last one.

Regards

TA01-010	12	1	1	0.38	40.75	1270.9275	-
TA01-010	12	1	1	0.54	39.16	1270.985	x
TA01-010	12	1	2	0.58	52.8	1291.0525	-
TA01-010	12	1	2	0.61	50.39	1291.0525	x
TA01-010	12	1	2	0.67	50.9	1291.11	x
TA01-010	12	1	3	0.86	54.9	1310.89	-
TA01-010	12	1	3	1.09	55.09	1310.9475	x
TA01-010	12	1	4	0.99	46.5	1330.6125	-
TA01-010	12	1	4	0.99	45.44	1330.6125	x
TA01-010	12	1	4	1.18	46.83	1330.6125	x

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

 

[Tom Urtis]

If in cell H1 (notice, that's H1 this time, not H2) you enter this formula and copy it down, does it place the X's where it should, meaning for those rows where an X would be in column H are the same rows you want to delete, and where there are not X's are for rows you want to keep (I assume your word "select" is the same as keep).

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