• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

find all material for each job

J

json

Member
im not sure if i can explain this well and i cant upload a sample due to work uploading restrictions.

but i have a list of lets say 10 jobs (the real list is actually much bigger but under 100) (for now)... each job has required materials. the number of materials vary; 1 job may have 2, another may have 4, etc.

what i need to do is find a way to identify which jobs have the identical list of materials. my data is organized by listing each job number and 1 of the materials. so if this job has 4 materials, the job # is listed 4 times, with each row containing a diff Cat#
JobA...>Cat1
JobA...>Cat2
JobA...>Cat3
JobA...>Cat4
...and so on

so jobB may have 4 cat#'s but the could be all different or just 1. JobE could have the exact same number and exact same materials. thats what i need to identify.... which jobs have the same exact materials list.

i feel like the solution is right under my nose, but i just got back from a conference where i was out for 3 days, working on 4.5hrs of sleep (flight was delayed).

can you help!?
 
Not thought this all the way through but something like this...
Create a unique list of all Categories and assign them 1 to n....
Then against each entry in your main list do a Vlookup on your list of categories to return the 1 to n bit.
So your table columns now looks like this
A B C
JOB A CAT1 n (where n is Vlookup(A1,{yourVlookupTblRef},2,FALSE) )
Then in the next column add up all the n's for matching JOBs
So your table now looks like this
A B C D
JOB A CAT1 n {total} (where your total is =SUMIF(A:A,A1,C:C) )
Now sort your columns by column D and all the jobs that match are together
Then remove the duplicates and you have your list of jobs with exactly matching categories
I think ?!! :)
 
thank you for the replies!! i think i figured something out although its too 'dirty' to explain on here lol.

Jabba: thank you for your help!
Nara: Thats a very cool solution! need to study what you did!!
 
You must log in or register to reply here.
Back
Top