Macro or filter to extract/delete data on value match

[baked_dog]

Screenshot: http://iphoneapp.net78.net/test/Capture.JPG
File: http://iphoneapp.net78.net/test/Problem1.xlsx


Hi Everyone,

I'll try and explain my problem as best I can.

Per my attachment (which is a dummy sample of data from a sheet I hav
of over 3000 lines). I would like to extract data on a 'found' matc
(between columns B & E) or delete on a 'not' found ...which ever i
easier to do.

The Data in blue is from my system (about 400 lines in my real exce
sheet). The data in Green is from a clients (goes down to about 2,50
lines), and this is the data I am trying to extract/delete.

I have not dealt with macros before but I believe the solution would li
in a script that would
check (as an example) if E7 is in Column B,
if ('Not' is found): delete rows including and between C103428(E7)
'Total'(E9)
This would then leave the data I would like to use ...or is there a wa
to group r filter as such?

Sorry if this is convoluted, but I'm pulling my hair out trying to fin
a solution. I appreciate any help given.

Cheers

+-------------------------------------------------------------------
|Filename: Capture.JPG
|Download: http://www.excelbanter.com/attachment.php?attachmentid=391
+-------------------------------------------------------------------

 

[pascal baro]

Hi there,

I don't know I might come late with this. Anyway, here is what I quickly did with a vba script not knowing if the data blue can be deleted as well. I guess not because it would then change your match function result... but you say in your query to delete the entire row for the data value and data set you want to delete.

The code is below. In brief, I think it can be done without vba, however itmight not be easy since there are merged cells and filtering doesn't do well with merge cells. The thing would have be to insert a column with ascending rows numbers, most likely before column A then insert another temporarycolumn between your match formula and the client data, have a formula based on the first letter of the text in the right column and the result of your match function in the left column return a number that you will also increment using a counter somewhere on the sheet.
Then you sort based on this column counter and it also on row numbers to make sure you delete everything related to the value not found. And here in consecutive order, you should have your range that you can manually delete.
Last, you sort everything in column A (the earlier increment value)in ascending order and things should be as you want. It takes a bit of additional functions, maybe split them into smaller functions and you can have something easier to go with.

Here is a code below, you can uncomment the line with 'A mark and comment out the line below if you can to delete the data in you blue range.


Option Explicit


Sub DataInGreen()

Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Sheet1")

With Application
.Calculation = xlCalculationManual
.DisplayAlerts = False
.ScreenUpdating = False
End With

Dim rC As Range, rcF As Range
Dim rTot As Range
Dim RangeF As Range
Dim i As Long, j As Long
Dim k As Long
Dim lastr As Long


Set RangeF = ws.Range("B:B")
lastr = ws.Cells(Rows.Count, 5).End(xlUp).Row

For i = lastr To 2 Step -1

If Left(CStr(UCase(ws.Cells(i, 5))), 1) = "C" Then

Set rC = ws.Cells(i, 5)

Set rcF = RangeF.Find(rC, LookIn:=xlValues, lookat:=xlPart, _
searchorder:=xlByRows, searchdirection:=xlNext, _
MatchCase:=False, searchformat:=False)

If rcF Is Nothing Then

j = rC.Row
k = j

Do
k = k + 1

Loop Until InStr(1, CStr(UCase(ws.Cells(k, 5))), "TOTAL") <> 0

'ws.Range(ws.Rows(j), ws.Rows(k)).Delete 'A uncomment if data blue should be deleted as well

ws.Range(ws.Cells(i, 5), ws.Cells(k, Columns.Count)).Delete xlUp

End If

End If

Next i


With Application
.Calculation = xlCalculationAutomatic
.DisplayAlerts = True
.ScreenUpdating = True
End With

Set ws = Nothing

End Sub


This might take time to run, I don't know. Further optimization can be madeto match the Total and delete rows between the value and Total.

Hope it helps and I don't come to late.

Pascal Baro