LinkBack URL
About LinkBacks
111.png
My goal is to find all matching "Job Number" from one column (A) and "BO" and create one, consolidated list with the job numbers and their related values - Help please!
Need VLOOKUP to Find Matching Entries
111.png
My goal is to find all matching "Job Number" from one column (A) and "BO" and create one, consolidated list with the job numbers and their related values - Help please!
Welcome to the forum!
You have Power Query in Office 365 - if you attach a workbook, I'll show you how to use it to create your summery table.
Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Thanks - see attached - The actual data will take values that are obviously different and find matches - these are just copies of the same columns but the formula should work I suppose - Thanks
Well, it won't work on a different layout - how different are we talking about?
This sample won't do - the two lists are identical. Please supply something more realistic. Thanks.
Never mind - have a look at the attached and see if it will suit your purposes. If so, I'll talk you through it.
Realistically - My goal is to take 2 lists, both with different job numbers and A. Find all matching occurrences of the job numbers from the 2 lists and return a list of those matches with the corresponding PO Total Value
I noticed, on the "blue" section, Job 7033055 has a PO value of 250; in the 3rd section, it appears again at 250. But in the middle column/section, I see that job number with a PO value of 750.
I am obviously missing something?
Unless your results found every instance of that job number (it may appear more than 1X) and added the totals from the 2 columns?
Yes! It did add them! Awesome. Tell you what, when I get the full and REAL 2nd list, I'll attach it here for your magicThanks!!!
7033055 appears three times in the source table, each with 250 - this is summarised by one entry of 750, which is a sum of those three 250s.
So you will have two tables side-by-side that will need summarising - is this the case?
OK - I will wait for the second file - be as quick as you can, as I want to sign off for the evening soon!
Actually 3
The first sheet will be a list of everything
The second will be a list of job numbers and costs
The third a list of Job numbers and costs...
The end goal is to compile a "list" of...
1. All matching job numbers from the 3 lists with values in the "site survey", Site audit", "design brand book" columns/cells and the resulting total PO amount
2. All matching job numbers from the 3 lists with values in the "site survey", Site audit", "design brand book" and "sent" columns/cells and the resulting total PO amount
Hope this isn't too confusing:
Tier C = Site Survey, Site Audit, Design Brand Book
Tier D = Site Survey, Site Audit, Design Brand Book, Sent
I’m interested in learning the actual direct costs associated with the tiers C and D of the above. Use a list of all the job numbers in these categories, and reconcile the 2 data sheets and the job number list to tally these costs. Summarize your findings by stating “Total Direct Costs for non-invoiced SAL project work”.
Once I receive all data, I can attach as one simple sheet
Mmm. Sounds doable, but to be sure, I do need to see a realistic sample sheet.
ASAP I promise - Thanks SO much
So, from the list (peach fill) - Can you find all the job numbers that it contains that are also contained in the gray and blue sections in the aforementioned triers?
So, all jobs that are in the peach section with values in columns J,K,L that are also in the gray and blue sections
and...
all jobs in the peach section with values in the J,K,L,M columns that are also in the gray and blue sections
I’ll have a look in the morning. Just about to turn in fir the night.
Perfect - Thanks much
So - let's just clarify what you want. You want a list of all job numbers from the peach area that have data in J, K, L or M that also appear in tghe blue and grey areas and then what? How do you want the resulting table to look? Just summary values for those job numbers?
Last edited by AliGW; 04-16-2019 at 02:30 AM.
OK - so I've made a stab at it. Have a look at the workbook attached and let me know. The SAB tab is your Tier C data and the SABS tab is the Tier D data. You can add totals lines to the tables on those tabs if you wish, of course.
If this is what you want, I can talk you through how I did it.
Just as an observation when thinking about thread titles in future: yours for this thread didn't even begin to convey what you were trying to achieve. In retrospect, it's far too simplistic and actually misleading. Something like this might have been better: "Creating Summary Data Table with Matching Criteria from Multiple Tables".
Last edited by AliGW; 04-16-2019 at 02:53 AM.
wow - Looks awesome and thanks! Can you please let me know the formulas used to achieve?
There are no formulae. It's all done with PowerQuery. It isn't complicated, but it will require careful explanation. Is it OK if I write up the instructions and post them tomorrow morning?
You're the best - Just to clarify...
Any job numbers from the 1st, blue area were matched against the job Numbers from the 2nd area, "gray" to come up with 2 lists that identified...
1. SAB Values = Job numbers that appear in both gray and blue areas, with corresponding PO values, with entries in the Site Audit, Survey, and Brand Book columns/cells
2. SABS Values = Job numbers that appear in both the gray and blue areas, with corresponding PO values, with entries in the site audit, survey, brand book, and Quote sent columns/cells
Yes - in my terms:
1. The blue and grey areas were loaded into PQ and summarised (unique numbers - summed values).
2. The summary table was then merged with each of the filtered lists from the peach area (Tier C and Tier D) to provide a list of just those numbers that matched the peach lists.
I suggest you check a few that you have manually calculated to ensure that the results are what you are expecting.
Are you happy to get the instructions tomorrow? Time zones are getting in the way again!
Perfectly happy and thanks again! I owe ya a pint!!!!
OK - I'll post the instructions first thing tomorrow my time.
One quick thing...I showed it to a colleague and he suggested that...
I don’t think the tier breakdown is working right. There shouldn’t be any overlap between tiers, so tier C is for locations where all three (audit, survey, and drawings) happened, and Tier D is for locations where all 4 tasks were completed. Maybe an If/Then formula could help.
Suggestions?
How can there not be an overlap? Easily fixed if you can specify with an example or two of what you actually mean.
Sure - If my colleague is incorrect that's cool too - Believe me, it's likely my misunderstanding but that's what he just sent.
Really, all I am looking for is: Tier C (all jobs that meet the 3 criteria with their corresponding PO values and Tier D - Jobs that meet the 4 criteria. Each job must meet all criteria in each tier, so, anything in tier D, that meets all 4 should not be in tier C, that only meets 3
Tier C = survey, site audit, brand book (all conditions met)
Tier D = survey, site audit, brand book, and quote sent (all conditions met)
No, sorry - I asked for specific examples.
I fail to see how the two can be mutually exclusive - Tier D must be a subset of Tier C. You are going to need to be more specific in your explanation.
OK - I have just seen the preceding post. I can work round that.
Tier C = ONLY jobs that meet the 3 criteria (Site Survey, Site Audit, Brand Book)
Tier D = ONLY jobs that meet all the above + Cost analysis/quote sent
Although a job, obviously, falls into the C tier if it is in the D tier by default, can we exclude these?
So, if Job 12345 meets SSB
and job 11111 meets SSBS
12345 is ONLY in tier C and not D
I think I get it - I’ll sort this out before sending instructions. Basically the two tiers need to be mutually exclusive.
yes. please
Tier C is ONLY Jobs that meet the 3 criteria and Tier D ONLY jobs that meet all 4 along with corresponding dollar values - there should be no duplicates between the 2 tiers
That’s clear now. Easy to tweak. I’ll post instructions in the morning.
Awesome - and attached revised sheet? Pretty please?
Of course!
As promised, all details attached. The instructions are too long to post directly to the thread, so I have put them into a Word document. I hope it all makes sense!
Absolutely awesome and thanks again!
Ah - our time zones coincide again!!! LOL!
I am really glad to have helped and I hope it all makes sense. Just shout if not. It's always a pleasure to open this particular Pandora's Box for someone else, as I was like a kid in a sweet shop (candy store) when I first started realising what PowerQuery can do. Enjoy!
Just 1 more item that could be included....
I need a quantity by Tier (4 tiers now) - No associated costs and broken out by region as well. Can you please point to that outcome in your report? The end result would look something like:
Pivot table - Choose Tier, Choose Region (Midwest, Southeast, Northeast, West); please exclude job numbers 707221 and 7027224
Tier
A. Site Audit only
B. Site Audit & Drawings
C. Site Audit, Site Survey, & Drawings
D. Site Audit, Site Survey, Drawings, & Cost Analysis (price quote)
You do understand that this isn't a service, don't you?
I have given you very clear instructions about how to use PowerQuery to achieve the results you asked for. The idea now is that you go away and have a go at adding extra functionality yourself. Please do so. If you get unstuck, I'll be happy to advise and point you in the right direction, but I am afraid it's over to you now to have a go by yourself. You won't learn anything unless you do.
I trust you did work through the tutorial I prepared for you? I hope so, as it took almost an hour of my free time this morning to make it detailed enough for you to be able to follow and hopefully adapt to your own purposes.
By the way, I don't use pivot tables, so I can't help you with that, sorry.
Last edited by AliGW; 04-17-2019 at 01:54 PM.
OK. Thanks
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks