A very particular way to select data

G

gregmartjim

New Member
Joined
Jun 14, 2018
Messages
9
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

[TABLE="width: 343"]
<colgroup><col span="4"><col></colgroup><tbody>[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0.64[/TD]
[TD="align: right"]41.93[/TD]
[TD="align: right"]1274.895[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0.66[/TD]
[TD="align: right"]41.12[/TD]
[TD="align: right"]1274.8375[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0.97[/TD]
[TD="align: right"]40.68[/TD]
[TD="align: right"]1290.7075[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0.93[/TD]
[TD="align: right"]41.88[/TD]
[TD="align: right"]1290.7075[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1.11[/TD]
[TD="align: right"]44.38[/TD]
[TD="align: right"]1311.235[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0.83[/TD]
[TD="align: right"]41.92[/TD]
[TD="align: right"]1311.12[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1.18[/TD]
[TD="align: right"]42.83[/TD]
[TD="align: right"]1331.245[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1.58[/TD]
[TD="align: right"]43.26[/TD]
[TD="align: right"]1331.4175[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0.57[/TD]
[TD="align: right"]54.07[/TD]
[TD="align: right"]1270.98[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0.57[/TD]
[TD="align: right"]55.14[/TD]
[TD="align: right"]1270.98[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0.68[/TD]
[TD="align: right"]56.19[/TD]
[TD="align: right"]1270.98[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0.52[/TD]
[TD="align: right"]53.8[/TD]
[TD="align: right"]1290.96[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0.56[/TD]
[TD="align: right"]53.36[/TD]
[TD="align: right"]1290.78[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0.76[/TD]
[TD="align: right"]58.58[/TD]
[TD="align: right"]1310.94[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0.64[/TD]
[TD="align: right"]56.7[/TD]
[TD="align: right"]1310.76[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]0.75[/TD]
[TD="align: right"]49.14[/TD]
[TD="align: right"]1331.1[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]0.8[/TD]
[TD="align: right"]46.67[/TD]
[TD="align: right"]1331.1[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0.48[/TD]
[TD="align: right"]52.05[/TD]
[TD="align: right"]1271.34[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0.52[/TD]
[TD="align: right"]51.57[/TD]
[TD="align: right"]1271.34[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0.52[/TD]
[TD="align: right"]50.41[/TD]
[TD="align: right"]1271.34[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0.7[/TD]
[TD="align: right"]50.84[/TD]
[TD="align: right"]1291.14[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0.6[/TD]
[TD="align: right"]51.76[/TD]
[TD="align: right"]1291.14[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0.69[/TD]
[TD="align: right"]54.79[/TD]
[TD="align: right"]1311.12[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0.8[/TD]
[TD="align: right"]55.45[/TD]
[TD="align: right"]1311.12[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]0.83[/TD]
[TD="align: right"]51.36[/TD]
[TD="align: right"]1331.1[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1.05[/TD]
[TD="align: right"]52.73[/TD]
[TD="align: right"]1331.1[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0.47[/TD]
[TD="align: right"]39.46[/TD]
[TD="align: right"]1271.0425[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0.41[/TD]
[TD="align: right"]39.52[/TD]
[TD="align: right"]1270.985[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0.58[/TD]
[TD="align: right"]53.1[/TD]
[TD="align: right"]1290.995[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0.6[/TD]
[TD="align: right"]52.65[/TD]
[TD="align: right"]1290.995[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1.05[/TD]
[TD="align: right"]52.44[/TD]
[TD="align: right"]1311.12[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0.98[/TD]
[TD="align: right"]53.37[/TD]
[TD="align: right"]1311.12[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1.07[/TD]
[TD="align: right"]48.86[/TD]
[TD="align: right"]1331.015[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1.1[/TD]
[TD="align: right"]47.46[/TD]
[TD="align: right"]1331.015[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0.42[/TD]
[TD="align: right"]56.86[/TD]
[TD="align: right"]1270.98[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0.44[/TD]
[TD="align: right"]54.53[/TD]
[TD="align: right"]1270.98[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0.42[/TD]
[TD="align: right"]50.12[/TD]
[TD="align: right"]1290.96[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0.43[/TD]
[TD="align: right"]51.05[/TD]
[TD="align: right"]1290.96[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0.58[/TD]
[TD="align: right"]58.82[/TD]
[TD="align: right"]1310.76[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0.54[/TD]
[TD="align: right"]57.63[/TD]
[TD="align: right"]1310.76[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]0.79[/TD]
[TD="align: right"]48.76[/TD]
[TD="align: right"]1331.1[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]0.79[/TD]
[TD="align: right"]48.21[/TD]
[TD="align: right"]1331.1[/TD]
[/TR]
</tbody>[/TABLE]
 
Sort by date Sort by votes
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.
 
Last edited:
Upvote 0

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
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.
 
Upvote 0
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")
 
Upvote 0
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
 
Upvote 0
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.
 
Last edited:
Upvote 0
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.


[TABLE="width: 535"]
<colgroup><col span="6"><col><col></colgroup><tbody>[TR]
[TD]NNK6-000[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0.94[/TD]
[TD="align: right"]-0.94[/TD]
[TD="align: right"]1270.8[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]NNK6-000[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0.96[/TD]
[TD="align: right"]-0.96[/TD]
[TD="align: right"]1270.8[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]NNK6-000[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0.93[/TD]
[TD="align: right"]-0.93[/TD]
[TD="align: right"]1270.8[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]NNK6-000[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]10.92[/TD]
[TD="align: right"]39.93[/TD]
[TD="align: right"]1336.5[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]NNK6-000[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]10.93[/TD]
[TD="align: right"]39.97[/TD]
[TD="align: right"]1336.5[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]NNK6-027[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0.64[/TD]
[TD="align: right"]41.93[/TD]
[TD="align: right"]1274.895[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]NNK6-027[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5.63[/TD]
[TD="align: right"]-0.48[/TD]
[TD="align: right"]1273.2275[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]NNK6-027[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0.66[/TD]
[TD="align: right"]41.12[/TD]
[TD="align: right"]1274.8375[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]NNK6-027[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]210[/TD]
[TD="align: right"]-165.34[/TD]
[TD="align: right"]1272.48[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]NNK6-027[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0.97[/TD]
[TD="align: right"]40.68[/TD]
[TD="align: right"]1290.7075[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]NNK6-027[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0.93[/TD]
[TD="align: right"]41.88[/TD]
[TD="align: right"]1290.7075[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]NNK6-027[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1.11[/TD]
[TD="align: right"]44.38[/TD]
[TD="align: right"]1311.235[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]NNK6-027[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0.83[/TD]
[TD="align: right"]41.92[/TD]
[TD="align: right"]1311.12[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]NNK6-027[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1.18[/TD]
[TD="align: right"]42.83[/TD]
[TD="align: right"]1331.245[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]NNK6-027[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1.58[/TD]
[TD="align: right"]43.26[/TD]
[TD="align: right"]1331.4175[/TD]
[TD]x[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
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","")



 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Deleting rows from a Range variable
Replies
4
Views
273
Umma123
U
J
  • Solved
VBA to search for duplicates, concatenate cells from another column and delete row
Replies
5
Views
191
Alex Blakenburg
A
C
  • Question Question
Linking two sheets in different workbooks takes a long time even with only 500 rows of data. Is there a quicker, more efficient way to do this?
Replies
0
Views
164
chazrab
C
B
  • Solved
Use Sheet1:D5 to find matching cell in Sheet2 Column A:A, and select cell (or row)
Replies
1
Views
407
brianv
B
R
  • Solved
VBA: Merging rows after a particular row, based off of matching entries in column
2
Replies
19
Views
956
Georgiboy
Georgiboy

Forum statistics

Threads
1,223,911
Messages
6,175,322
Members
452,635
Latest member
laura12345

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