Macro or filter to extract/delete data on value match

[baked_dog]

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.

[Ken Puls]

I think I'd try to tackle this in the following manner:

• Split "Your" data to one sheet and "Client" data in another
• Define a named range to cover "Your" data
• Put "Client" data starting in column B
• Fill "Client" data column A with a formula to identify if the item is found or not
• Use an Autofilter to delete all "not found" rows

So the trick here would be to figure out what the formula needs to be in order to have it correctly return Found or Not Found.

Some questions:

• Is the client number always 6 digits long?
• Does it always start with a letter followed by 5 numbers?
• Is there always a Total row in the client data?
• Is there always a blank row after every total row?
• Is there always a blank row in row 2?

Now, I haven't split the sheets up, but if the questions above are all true, you could throu the following formula in D2 and copy down:

=IF(OR(OFFSET(E2,-1,0)="Posting Date",OFFSET(E2,-1,0)=""),IF(ISNA(MATCH(E2,$B$3:$B$11,0)),"Not Found", "Found"),DOFFSET(D2,-1,0)

That would give you repeating blocks of Found/Not Found. From here you could use Autofilter to filter the "Not Found" records then delete them. (And yes, we could use code to make that automatic.)

Just in case you're wondering about the formula, I used OFFSET to make the formula a bit more dynamic. This way you can delete the row about and it won't blow up the formula.

At any rate, let me know if you think that would work and we can look at automating the process.

Cheers,

[baked_dog]

Ken thanks a lot for the massive amount of help. I have been to 5 excel forums so far and you have been one of the only ones to reply and you have given by far the most comprehensive answer.

I have now been told by the client that I can in fact get all the information on one line which will help me for my short term fix, but I will make note of your solution for a future problem I can see arising.

Once again your help is much appreciated

Cheers

I think I'd try to tackle this in the following manner:

• Split "Your" data to one sheet and "Client" data in another
• Define a named range to cover "Your" data
• Put "Client" data starting in column B
• Fill "Client" data column A with a formula to identify if the item is found or not
• Use an Autofilter to delete all "not found" rows

So the trick here would be to figure out what the formula needs to be in order to have it correctly return Found or Not Found.

Some questions:

• Is the client number always 6 digits long?
• Does it always start with a letter followed by 5 numbers?
• Is there always a Total row in the client data?
• Is there always a blank row after every total row?
• Is there always a blank row in row 2?

Now, I haven't split the sheets up, but if the questions above are all true, you could throu the following formula in D2 and copy down:

=IF(OR(OFFSET(E2,-1,0)="Posting Date",OFFSET(E2,-1,0)=""),IF(ISNA(MATCH(E2,$B$3:$B$11,0)),"Not Found", "Found"),DOFFSET(D2,-1,0)

That would give you repeating blocks of Found/Not Found. From here you could use Autofilter to filter the "Not Found" records then delete them. (And yes, we could use code to make that automatic.)

Just in case you're wondering about the formula, I used OFFSET to make the formula a bit more dynamic. This way you can delete the row about and it won't blow up the formula.

At any rate, let me know if you think that would work and we can look at automating the process.

Cheers,

[Ken Puls]

Not a worry, glad to help!