Find first observance of a cell value and delete row

[cshap23]

I have a a very large time series spreadsheet with many observed values of many variables. For each variable, I want to find and delete the first observation. It looks something like this

A * (x)
A *
A *
B * (x)
B *
B *
C * (x)
C *
C *

Each letter is one variable and each * is an observation. The (x) indicate the value I want to find and delete the entire row. Any suggestions? Thanks.

 

[Momentman]

I ont know what column you have that value or how your data really is

But you can use a filter, filter to show all occurrences of (X) and then delete all matching rows. After which, you remove the filter

 

[cshap23]

The (x)s are not actually in the spreadsheet. I just used them to illustrate which rows I want to delete.

 

[Momentman]

Are they the first instances of those variables or what?

I think they are. Just re-read your post

Now I see

 

[Momentman]

I might just set it up like this, knowing that the data has been sorted

Excel 2010
	A	B
1	A	0
2	A	1
3	A	1
4	B	0
5	B	1
6	C	0
7	C	1
8	D	0
9	D	1
10	E	0
11	E	1
12	F	0
13	G	0
14	H	0
15	H	1
16	H	1
17	I	0
18	I	1
19	J	0
20	K	0
Sheet3

Cell Formulas
Range	Formula
B2	=IF(A2<>A1,0,1)

Any cell that has 0 is a first instance, so i would eventually filter and delete

 

[cshap23]

Great. Thank you. That worked. Could you add a little color to the "<>" function?

 

[Momentman]

Great. Thank you. That worked. Could you add a little color to the "<>" function?

The "<>" simply means NEQ ("not equal to") .....So you are looking for instances where the data on a row is not equal to the data just above it.

There's not much more to it, or so I think

 

[cshap23]

Thank you. Everything is working as expected and was just able to run 8800 rows of inputs through my code.

 

[Momentman]

Thank you. Everything is working as expected and was just able to run 8800 rows of inputs through my code.

Great, good to know and Thanks for the feedback