How to find the closest date with the specific number

Help Activities
, ,
1

Hello guys,

I currently have the following problem. I have two lists available. In list A, column A contains various material numbers. In another list, these material numbers are also included but with different dates. I would like to find the closest date and then write this row in a new Excel. Do you have any ideas?

Thanks for your help.

2

Hi @NicoB24

Try this syntax in the meanwhile can you please share the sample input:

result = (From rowA In dtListA.AsEnumerable()
              Join rowB In dtListB.AsEnumerable()
              On rowA("MaterialNumber") Equals rowB("MaterialNumber") Into Group
              Let closestDateRow = Group.OrderBy(Function(r) Math.Abs((CDate(r("DateColumnB")) - CDate(rowA("DateColumnA"))).TotalDays)).First()
              Select rowA("MaterialNumber"), ClosestDate = closestDateRow("DateColumnB")).CopyToDataTable()

Regards

3

Hi, thanks for your anwser. Here is a picture of my two excel lists.
For each material number, I need all the customers who ordered last. As shown in the picture. I need all customers for the material

image
image839×367 8.65 KB

4

Hi @NicoB24

Can you share these excels and expected output too

Regards

5

Excel_input_list_Material.xlsx (8.8 KB)
Excel_with_Customer.xlsx (9.2 KB)
Excel_final_output.xlsx (9.1 KB)

Hi,

here i have an input list and a customer list when each customer always ordered and i need a new list with the data from the input list and when each customer last ordered.

1 Like
6

HI @NicoB24

Please check the below attached file.

BlankProcess30.zip (60.8 KB)

Output.xlsx is the output sheet.

Regards

7

Thanks!! That works for me

1 Like
8

You’re Welcome @NicoB24

Happy Automation!!

1 Like
9

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.