PDA

View Full Version : Solved: need help with a check



wolf.stalker
11-13-2010, 09:58 AM
Greetings and thanks for the help!

need help putting something together. i have enlcluded the test workbook so that you can see my data. so, if you are looking at the workbook, this is what i need to "code"

on the "Data" sheet will contain some 10k rows of data. I want to look at comumn L and take that value and see if it's in the list on the "temp" sheet. if it is NOT then i need to delete all info up the new test point.

Example. I am looking at cell L1. that value "T077" is in my list on sheet "temp" so i will move on to the next value in column L which is "T190" at L4. That value is NOT in the list so i want to delete rows(4:8) or up where the next value in L is.

thanks again for any help you can provide!

mdmackillop
11-13-2010, 10:23 AM
Would I be right in thinking that =MATCH(D1,Data!$L:$L,0) in E1 of temp, copied down, will show by #N/A all rows to be deleted?

Bob Phillips
11-13-2010, 12:18 PM
I think you have it back to front MD.

I would insert a row #1 in Data, and then in M2

=IF(L2="",M1,MATCH(L2,temp!$D:$D,0))

copy down. Filter column M on the #N/A and delete them

wolf.stalker
11-13-2010, 12:49 PM
I think you have it back to front MD.

I would insert a row #1 in Data, and then in M2

=IF(L2="",M1,MATCH(L2,temp!$D:$D,0))

copy down. Filter column M on the #N/A and delete them

hmm. i think i can probably make that work. all but teh filter part anyway.

there is more data and junk in columns A:I that i ommited but for each row that has a value in L, there could be 2-6 rows of data for that item. the only think i know for SURE is that the record starts where you see an item in L and ends the row above the next item in L.

wolf.stalker
11-13-2010, 01:47 PM
thanks gusy for your help. i used the following and it worked great.



For iCnt = 1 To x
If Range("L" & iCnt).Text <> "" Then
Range("P" & iCnt) = Application.Match(Range("L" & iCnt), Sheets("temp").Range("D:D"), 0)
End If
Next iCnt




sometimes i forget that i can use tools already created instead of always trying to make a new tool for each and every problem :-)