Need a function help please

[Pingger]

From a table of part numbers, I need a function to find matches in column B and D  and select the latest date from column A enter in F and column B data in G. Function may find 5 instances of matches in B and D, but it needs to find the latest date in column A use it for F and select the data in B put in G.

[#2]

If you'd upload the actual spreadsheet rather than just an image, we'd not need to recreate the spreadsheet first...I have a database query function in mind, but I'm also pretty confident that a Power Query expert could do it. If you're interested in trying to solve it yourself, research the DGETand DMAX functions.

[#3]

@mathetes 

Thanks. Here it is.

Pingger

[#4]

@Pingger    I haven't been able to wrap my head around this totally yet. Here's a partial solution, but I'm pretty sure it's not what you want. It's not clear to me how you'd be using this....what I've created WILL find the latest date associated with any given combination in columns B and D, and I see that as a start.

What I've not been able to do--and Power Query might, but I've not had a need for that in the past, so someone else will have to provide that--is find the latest date for each of the combos; I could, but with a very elaborate set of criteria ranges that just paralleled your raw data, and I don't think that's what you want--

Another thing that's not clear: will a given Part # in column B always be associated with the same number in Column D? i.e., are they really two different ways to refer to the same thing? Or will there be instances where 5600314108 can be associated with 830-00199, but another time with 346-6610-501. If they're always the same pairing, just that some of them appear (as you've highlighted) multiple times, which is why you want the latest date for THOSE...that becomes a simpler matter. In fact, I've also created a query that works from a concatenated data element, made up of the two parts you specified.

Again, this is at most a start. If what you're looking for is a single function that will produce a list of all of the combinations, but only one instance of any that repeat, and the latest dates associated with each of those......then I think you need Power Query or some function I haven't mastered yet.

[#5]

Your first assumption is right. Let me look at your solution suggestion. It is as simple as that. .

[#6]

@Pingger 

If with Power Query that is like

- query named range

- select Part and Other columns, Keep Duplicates (records without duplication will be removed)

- sort Date in descending order

- select Part and Other columns, Remove Duplicates (only first of each duplicate will be kept)

- load result back into Excel sheet

For reliability I'd fix sort result in memory by Table.Buffer() or by adding Index column (remove it at the end).

Above is for all duplicates. If we consider only records with 5 or more duplicates few more steps shall be added. Like Group by Part and Other counting combinations, remove records with less than 5 duplicates, merge source table with grouping result.

Result is second sheet attached.

[#7]

I clearly need to get educated on Power Query (or, in my Excel for Mac "Get & Transform"); as I suspected, it has the ability to give Pingger the results he was seeking, but I have to say that your brief descripton of the steps involved--even looking at the results--leaves me scratching my head. I have purchased a recent manual that contains all these new features Excel has added post my retirement (2002)... so I'm eagerly but carefully making my way through and applying as much as I can.
Thanks for all you do here on these boards!

[#8]

@Sergei Baklan 

Thank you for the response, Sergei. Sorting the data makes easier to see what data I needed from this shipment report. Nuts and bolts are shipped in different qty and different dates. I need to sort the bolts and nuts by part number, select the last date they were shipped and the total qty in all instances shipped.

So the function will summarize by part number, select the last date there was a shipment and total the qty shipped so far.
Thanks Sergei.

[#9]

@Sergei Baklan

Sergei, there are no duplicates as per your assumption. They are different shipments with different qty on different dates. They may be on the same dates but different qty, just different time of day but we don’t have time stamped so records show same dates.

[#10]

@Pingger , thank you for the clarification.

So, your logic is bit more complex than simple duplicates removing. Summing also could be added to Power Query logic if the quantity is in the source data.

[#11]

@Sergei Baklan 

you’re right, Sergei. I’m writing a VBA for it now to sort, select the latest date of a PN instance and sum the qty. 

[#12]

@mathetes 

On Windows it's also Get & Transform starting from Excel 2016, but everyone uses "Power Query", even Microsoft if outside of official documentation.

Power Query is not the rocket science and you could start doing real things as soon as understand some basics. From Power Query philosophy point of view I think it is more close to people who came from SQL rather than to Excel ones. You shall shift your thinking from ranges and cells to data sets, lists and records. The rest will go easier.

And yes, technology  doesn't stay on one place. When Power Query appeared, many things which were done within Power Pivot people start doing with Power Query. Now Dynamic Arrays are coming, after general availability (I guess in an year or so) most probably stress will be moving from Power Query on DA.

Anyway, Power Query is a great tool, good luck with it!

[#13]

@Pingger 

Yes, if you are comfortable with VBA that's most reasonable approach. I guess the same could be done with formulas, but heavy ones. With Power Query it shall be relatively easy, but only if you are more or less familiar with it.

[#14]

@Sergei BaklanWell, hearing that you think "think it is more close to people who came from SQL rather than to Excel ones" is good. I used SQL a lot, writing the queries myself, drawing data from a mainframe IBM DB2 database system, joining multiple tables in order to make a coherent report. So if that's the kind of logic that's behind Get & Transform, I should be at home quite quickly.  (Although I have to admit: I just realized my use of SQL was TWENTY years ago; I was very active at the time with it, but it was 20 years, two decades!!)