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.

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.

mathetes Thanks. Here it is. Pingger

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.

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

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.

Need a function help please | Microsoft Community Hub

13 Replies

mathetes
Gold Contributor
Nov 20, 2019
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.
- Pingger
  Copper Contributor
  Nov 20, 2019
  mathetes
  
  Thanks. Here it is.
  
  Pingger
  Sample workbook1.xlsx13 KB
  - mathetes
    Gold Contributor
    Nov 21, 2019
    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.
    
    Sample workbook1b.xlsx15 KB

Forum Discussion

Need a function help please

13 Replies

Resources