Double Column Matching and fill value with criteria

[iceburger96]

Hi all, first of all, thank you in advance if you could help me solve the below excel puzzle.

needs matching and filling of data.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Data Set 1						Data Set 2
2	PO	Part No.	commit date	quantity	Y/N		PO	Part No.	07-21-2021	07-22-2021	07-23-2021
3	PO0001	A001					PO0005	A002		50
4	PO0002	A001					PO0008	A005	40	30
5	PO0003	A001					PO0001	A001	50
6	PO0004	A002					PO0004	A002	100		50
7	PO0005	A002					PO0003	A001
8	PO0006	A003					PO0006	A003	100
9	PO0007	A004					PO0007	A004	100	100	200
10	PO0008	A005					PO0002	A001		40
11	PO0009	A006					PO0009	A006		100	50
12	PO0010	A008					PO0010	A009
Sheet1

We have 2 sets of data here, PO and part no. both must MATCH EXACTLY before we can input value into data set 1.

Let's look at data set 2.

As you can see, we have 3 dates with data set 2. We only need to input the number with the latest date into data set 1's quantity (which is column D).

For example, PO0006 (G8) we take the 100 to fill into D8. PO0005 (G3) we take the 50 to fill into D7. PO0007 (G9) we take the 100 to fill into D9.

Second, we need to fill the date that we took the number from into data set 1 at column C.

For example, PO0006 (G8) the 100 corresponds to 07-21-2021 then this date needs to fill into C8. PO0005 (G3) the 50 corresponds to 07-22-2021 then this date needs to fill into C7. You get the idea.

Third, wherever there is value in both column C and D, we will need to put a 'Y' in column E.

Please let me know if you have any solutions to this puzzle.

Thank you again.

Regards

 

[steve the fish]

Firstly that data isnt set out well. Design is everything if you then want to query it. Secondly if you want to return the latest date quantity why is PO0007 100? Surely its 200 if following your rules?

 

[iceburger96]

Hi Steve the fish, thank you for your reply. I assume that you refer to the PO sequence of the data set 2 that I didn't set out well?

For your second question, the number that we want is the number under the nearest date to today. For PO0007 in data set 2, we will take 100 (I9), as 7-21-2021 is nearest to today's date. For PO0002, we then take 40 (J10), as I10 has no value.

Thank you.

 

[steve the fish]

Yes your 2nd set of data would be much easier to work with if it was one row for each piece of data, PO, Part, Date. You didnt actually say closest to today anywhere as far as i could see. You said latest.