Extracting Data using 4 or more criteria

[emirmansouri]

Hi there everyone,

This is my first post so please excuse me if I have no experience in posting Threads.

Ok, I have a workbook that contains a Master Data Sheet (my main data)
I want to extract the same data and format using different criteria. For example:

On the Master Data In Column N, I have the functional location of an asset such as:

REF2/B/01/0103C
REF2/C/18/1803C
REF2/D/12/1203E

I want to count all of them excluding anything that is REF2/*/18/* and REF2/*/19/*

The star * is a wildcard to indicate it could be any character.

I have managed to accomplish alot so far but I need some help, so I have compiled my comments onto the Worksheet for any one willing to have the time to do this.
I am in urgent need of this sheet as we have alot of records and I need to organize it accordingly. I spend almost 5-6 hours behind my desk all day trying to do this manually, so any help will be greatly appreciated.

24WLA FILE ORGANIZER NEW.xlsx.zip

[emirmansouri]

Please anyone who considers themselves an expert excel user kindly requesting help.
Im really stuck here...

[FDibbins]

im thinking that if you add a helper column on MASTER DATA sheet col O and used this folrmula, copied down, to exclude "18" and "19" and whatever values in your data that DONT match sheet REF2, then you could perhaps simplify your formulas on REF2 as well as extract just the data you want?

i tried to folow your logic in the formulas in REF2, but i think you probably understand your logic better than i do

=IF(OR(MID(N2,FIND("/",N2,FIND("/",N2,1)+1)+1,2)="18",MID(N2,FIND("/",N2,FIND("/",N2,1)+1)+1,2)="19",M2<>'REF2 - METC'!$B$4,L2<>'REF2 - METC'!$C$4),"",N2)

let me know how you make out?

[emirmansouri]

I can see where your going with this but I need it to be a continuation in my formula on the sheet, im certain that someone has come across such a problem and would love to see it.
I really appreciate your help and the time you took just to look at it.

Thank you, but if you could find away to do it from the master data table as is without adding any helper it would be greatly appreciated.

I have been trying at this for over 3 days now and its driving me nuts.

How do you exclude REF2/*/18* & REF2/*/19* from the count and from being on the table with the extracted data as they both belong to REF1 group. So on REF1 METC worksheet they would be included in the respective count and also in the data table. This is my biggest problem.

[emirmansouri]

Willing to pay whom ever solves this $100 by paypal only, if allowed by the forum rules.
Immediate payment upon the requested queries being solved.
PM me the solutions and I will deliver.

Thank you so much I really appreciate your effort.

If I have broken any rules in my post kindly delete it and let me know & I will refrain from any such thing in the future.
Just very desperate for a solution here...

[FDibbins]

it seems to me that your calcs are based in part on the value in REF2, F4, and this is the number that you are having trouble with?

if thats the case, and i know you said you didnt really want a helper column in your master sheet, but i have come up with a sollution that gives you tha answer of 16 that you wanted, and reduces the rows of info returned by 4. i changed the formula in the helper column to...

=IF(OR(MID(N2,FIND("/",N2,FIND("/",N2,1)+1)+1,2)="18",MID(N2,FIND("/",N2,FIND("/",N2,1)+1)+1,2)="19"),N2,"")

and then changed your formula in REF2 F4 to...

=COUNTIFS(MASTER_DATA!M2:M104,B4,MASTER_DATA!L2:L104,C4,MASTER_DATA!O2:O104,"")

let me know if this is something you can live with?

[vlady]

=COUNTIFS(MASTER_DATA!$M$2:$M$9987,"="&$B$4,MASTER_DATA!$L$2:$L$9987,"="&$C$4,MASTER_DATA!$N$2:$N$9987,"<>"&D4,MASTER_DATA!$N$2:$N$9987,"<>"&E4)

will yield 16

[emirmansouri]

The count is good, but I need it to also extract the data excluding anything that is REF2/*/18 or 19.
If you click on the table you will see the formula I used, but cant figure out how to exclude the 18 & 19 from them being shown.
If thats accomplished ill send you the $100, just read my comments and you will see what I mean.

Thanks a ton guys

[FDibbins]

the sollution i offered you will do that for you

[emirmansouri]

I have marked this as solved and posted a new thread.