Extracting Data with Multiple Criteria - $150 Reward

[emirmansouri]

Weekly Cycle Organizer.

I have attached My Workbook so that you can read the comments and try to update my formulas to give the correct result.

1) - Must use the formulas used in the cells or similar to give exact results (Extract the correct Data leaving out the unwanted criteria & paste it into the corresponding Worksheet).
2) - No modification to the Master Data work sheet at all is a must.
3) - Please read and understand the comments on the worksheet.

Now ill explain in more details so everyone gets the picture of the requirements:

The master data is a Table for the Maintenance Activities for the week.
It has an Order Number, a Sort Field, a Functional Location,a Work Center, a Frequency (Std text key column), a Plant Section and Plant.

The functional location such as the first from the master data worksheet = REF1/B/01/0108J

REF1 is the Plant
/B/ is the Process type
18/ is the Unit
and 0108J is the asset (in this case its a Pump)

So for REF1 plant I want to extract onto the REF1 - METC worksheet all the rows that are:

METC (Mechanical) & is REF1 but I also want to include two Units onto this worksheet which are:

REF2/*/18* (Any unit 18 assets) as well as REF2/*/19* (Any unit 19 assets) that are METC (Mechanical), in Cells B4, C4, D4, & E4
So extract rows if its = B4 & C4 and also if it is = B4 & D4 or B4 & E4

This is for REF1 - METC Worksheet

Now for REF2 - METC Worksheet I want it to:
Extract the rows from Master Data if it is:

METC (Mechanical) & is REF2 but I also want to exclude two Units onto this worksheet which are:

REF2/*/18* (Any unit 18 assets) as well as REF2/*/19* (Any unit 19 assets), in Cells D4, & E4
So extract rows if its = B4 & C4 and exclude if it is = B4 & D4 or B4 & E4


Then there is the count Cell F4
This counts how many records fit the criteria for each worksheet.

The last thing left is to update the frequency for each record.
The Frequency is the last two characters in the cells of Column K.

Column K is the Std text key, example : PMPUU6M, where 6M means Six Monthly (Frequency) task list to maintain the asset.
If you look at some Order Numbers, some are duplicated but the data in column K is not available, I want to update Column K
if the Order numbers are the same and one of them has the cell populated. This can be from above or below the Row we are trying to populate.

Thats about all for this Workbook. Once this is complete I have another Challenge. The Performance Charts Data Table.
So if anyone is interested in this & happens to solve my queries I will give the $100.

Lets see if it can be done easily, I think if your an advance excel user it should not take more than one hour of your time to make a $100.

Best of luck.


24WLA FILE ORGANIZER NEW.xlsx.zip

[FDibbins]

i know this is a new thread and you closed the previous 1, but is IS still the same thread

[emirmansouri]

its exactly the same requirements, yes.
Just I would put a clearer description..

[emirmansouri]

Ok, I have increased the reward to $150.
Seems no one has been able to do it, now what shall I do?
Anyone, please help.....

[FDibbins]

is there a reason you dont want a helper column added to sheet1? you can always hide it, or put it far to the side.

[emirmansouri]

Hi FDibbins, if you think you can do it lets go for it.
I tried putting in the formulas you provided and it does part of the job but when you look at the table it still shows rows of data for 18 & 19.
I want to extract only REF2 and not those. So they should not show up on the REF2 - METC Worksheet.
If we can do that it would be great.

[protonLeah]

This is a pretty big request in one chunk. I'm sure you'll get better response if you break down your question into several parts, so that they can be tackled one after the other.

Also, we're happy to help you with your effort, but nobody here will be keen on developing a solution from scratch. This forum is about helping people learn how to use Excel, not a free development service.

Have you started any work on this project yet?
How far did you get?
Where are you stuck?

We can help you develop your own code, but if you want someone to code this for you all the way, you should try and find a commercial developer.

[emirmansouri]

Hi Ben,
If anyone takes the time to look at my sheet they would understand that there is only 3 formulas to update. Its not from scratch at all. Yes the query is long but its three equations.

The request & description are long so as to explain it as good as I could, to be clear.
Thank you for your time.
But im really despatate to get it up and running thats all.
Im not asking anyone to do it from scratch.

[hatrickx]

Is it usual to pay for help on this forum ?

[emirmansouri]

hatrickx, no its unusual. I asked a mod and he said it was fine.
Why have I done anything wrong by offering a reward!

[Ace_XL]

Just to get you started..

Count on REF1 sheet

Please Login or Register  to view this content.

Count on REF2 sheet

Please Login or Register  to view this content.

[FDibbins]

take a look at the attached and see if this is what you want?

i had to restrict the number or rows on sheet 2 and 3 to 100, but you should just be able to copy down as far as you need, if this works for you

[JosephP]

this has been posted on other forums too so you should provide links to those posts in accordance with the forum rules on cross-posting

[emirmansouri]

Look guys im looking for a solution, so I posted on one other forum the exact same post (so please do not go over the top when you say forums!). I did not link it to this post. All I did was link the file from this site.
I am in need of urgent help thats all. If I have offended anyone im sorry, if I have broken any rules, im honestly sorry, but for a valued forum contributor I hope this is not a contribution, have you read my query or did you even understand my request. I would rather see ppl contribute their efforts in helping ppl rather than worrying about the forum rules. I will obey by them as I get more and more familiar with the forum and ppl that roam around here, but I thought there was a nicer way to send a message to someone. Maybe a PM would have been more appropriate?, im just saying...

Anyway I would like to thank you for your message I will look at the rules and make the required changes. How many days do you generally allow? is it 2?

[emirmansouri]

FDibbins thanks for your effort, I see that it works well on REF2 METC but on REF1 METC its not including REF2 units 18 or 19. Is this possible.

[FDibbins]

when you select REF1 as a criteria, why would you want it to include anything that has REF2?

i tested it with REF1/METC, and on your data sheet, you only have 3 entries that match that criteria. you will see in the file that i uploaded, i added filters to your MASTER sheet, use them to test the answers

when you decide to EXCLUDE something (in this case 18 & 19) i assume that you want to exclude it from whichever MAINT PLANT is selected? if that is the case, we could just enter "18" and "19" in the "excude" cells, and then just use those as a reference for the helper column on your MATER sheet, it wiould give you more flexibility than hardcoding 18 and 19 into the formulas

[Cutter]

Hello emirmansouri, and welcome to the forum.

Unfortunately you have inadvertently broken two of the forum rules. Please read the following and make the necessary change. Thanks.

Firstly, I'm afraid your post does not comply with Rule 8 of our Forum RULES. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser)to the cross-post. Expect cross-posts without a link to be closed a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

Secondly, because you have not done what was asked of you in post #13 (post link to post in other forum):
Your post does not comply with Rule 7 of our Forum RULES. Please do not ignore Moderators' or Administrators' requests - note that this includes requests by senior members as well, if you are unclear about their request or instruction then send a private message to them asking for help. Do not post a reply to a thread where a moderator has requested an action that has not been complied with e.g Title change or Code tags...etc