Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Macro or filter to extract/delete data on value match
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 have of over 3000 lines). I would like to extract data on a 'found' match (between columns B & E) or delete on a 'not' found ...which ever is easier to do. The Data in blue is from my system (about 400 lines in my real excel sheet). The data in Green is from a clients (goes down to about 2,500 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 lie 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 way to group r filter as such? Sorry if this is convoluted, but I'm pulling my hair out trying to find a solution. I appreciate any help given. Cheers. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro or filter to extract/delete data on value match
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 it might 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 temporary column 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 made to match the Total and delete rows between the value and Total. Hope it helps and I don't come to late. Pascal Baro |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Advanced filter data match | Excel Discussion (Misc queries) | |||
Match and Extract Data | Excel Programming | |||
filter/find/highlight/copy/extract a range of data | Excel Programming | |||
criteria to filter and extract row data into separate worksheet | Excel Worksheet Functions | |||
formula to extract specific data if match occurs | Excel Worksheet Functions |