Join tables based on a non-unique ID and a formula

[Podcaster]

Hi Everyone -
I have three sets of data.
One is organized like this:
Table1
[TABLE="width: 500"]
<tbody>[TR]
[TD]Product ID[/TD]
[TD]Start Date[/TD]
[TD]Code[/TD]
[/TR]
[TR]
[TD]121[/TD]
[TD]4/05/2018[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]131[/TD]
[TD]4/05/2018[/TD]
[TD]V[/TD]
[/TR]
[TR]
[TD]121[/TD]
[TD]4/20/2018[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Another like this:
Table2
[TABLE="width: 500"]
<tbody>[TR]
[TD]Product ID[/TD]
[TD]Buy Date[/TD]
[/TR]
[TR]
[TD]121[/TD]
[TD]4/06/2018[/TD]
[/TR]
[TR]
[TD]131[/TD]
[TD]4/06/2018[/TD]
[/TR]
[TR]
[TD]121[/TD]
[TD]4/07/2018[/TD]
[/TR]
[TR]
[TD]121[/TD]
[TD]4/22/2018[/TD]
[/TR]
</tbody>[/TABLE]


And a third like this:
Table3
[TABLE="width: 500"]
<tbody>[TR]
[TD]Product ID[/TD]
[TD]Start Date[/TD]
[TD]Buy Date 1[/TD]
[TD]Buy Date 2 (if applicable)[/TD]
[TD]Buy date 3 (if applicable)[/TD]
[/TR]
[TR]
[TD]121[/TD]
[TD]4/05/2018[/TD]
[TD]4/6/2018[/TD]
[TD]4/7/2018[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]121[/TD]
[TD]4/20/2018[/TD]
[TD]4/22/2018[/TD]
[TD]null[/TD]
[TD]null[/TD]
[/TR]
[TR]
[TD]131[/TD]
[TD]4/05/2018[/TD]
[TD]4/06/2018[/TD]
[TD]null[/TD]
[TD]null[/TD]
[/TR]
</tbody>[/TABLE]


I need to take the values in Table 1 and just pull in the "bought dates" in Table2 that come BEFORE the next "start date" in table2. They also need to be associated with the product ID.
In other words, I would pull the bought date into the row with the product id and start date IF the product id matches AND start date is BEFORE the next start date.

Then, I need to compare that table with a table that is already set up like that (from external source) to check which dates we do not have recorded. Please help, other notes: I really don't want to vlookup everything or index/match so I'm asking for something other than that level of solution please

 

[Peter_SSs]

Welcome to the MrExcel board!

Your question is not clear to me. For example, you refer to "next start date" a couple of times. Which table and which column would we be looking in for that date since none are labelled "next start date"? Also "next" after what date?

Does your sample data include the expected results?
- If so, which table and which column?
- If not, can you post the same sample data but include & identify the expected result(s)?

I really don't want to vlookup everything or index/match so I'm asking for something other than that level of solution please

I don't know what method might be best until I understand the problem, but why are you ruling anything out? Wouldn't you be looking for a good solution no matter what method/functions were used?


What version of Excel are you using?

BTW, you can show sample data even better (row/column labels etc) if you follow the relevant link in my signature block below.

 

[Podcaster]

There are multiple start dates per each product id. So when I refer to next start date I'm referring to the next chronological "start date" column.

I mention that I know how to solve this using vlookup but am looking for a more elegant/ faster solution so that forum users understand what solutions I've considered.

 

[Peter_SSs]

Well, good luck. Your thread had had 40 viewers and I'm the only responder and I still do not understand and I suspect that is the issue with the other 39 viewers.

So when I refer to next start date I'm referring to the next chronological "start date" column.

But we still do not know what is the 'base date' that we are looking for the "next" start date after.

I mention that I know how to solve this using vlookup but am looking for a more elegant/ faster solution so that forum users understand what solutions I've considered.

Perhaps then you could show/explain your VLOOKUP solution so that we could consider improvements?

 

[Podcaster]

Basically there is multiple start dates for each product id. There are also multiple bought dates. I did not organize the database. Every start date needs a new row. It is associated with a particular productid. All bought dates that correspond to that productid and fall before the next start date and after the current rows start date should also be in that column. Do you have any other questions

 

[Peter_SSs]

Do you have any other questions

No, just the same ones really ..

Which table and which column would we be looking in for that date ....

Does your sample data include the expected results?
- If so, which table and which column?
- If not, can you post the same sample data but include & identify the expected result(s)?

What version of Excel are you using?

Perhaps then you could show/explain your VLOOKUP solution so that we could consider improvements?

 

[Podcaster]

Great, thank you for you questions. Here's some responses:
1. Which table and column will we be looking in for start date?
table1 column 2

2. does your sample data include expected results? Yes expected results are in table3, columns 1-5

3. What version? I'm using Excel 2016.

4. Perhaps you could show vlookup solution? I'm on a mac right now that doesn't have Excel but I can reply to this thread when I'm on the PC I use excel with. This is maybe another question actually, other than purchasing Excel for mac in addition the Windows Office I already have on the PC, any thoughts on the best way to use a desktop version/ not 365 with a Mac?

 

[Peter_SSs]

Thanks for the clarifications. Try this.
Formulas in A16 and B16 are copied down.
Formula in C16 is copied across and down.
(Note that my dates are in d/m/y format)

Excel Workbook
	A	B	C	D	E
1	Table1
2	Product ID	Start Date	Code
3	121	5/04/2018	X
4	131	5/04/2018	V
5	121	20/04/2018
6
7	Table2
8	Product ID	Buy Date
9	121	6/04/2018
10	131	6/04/2018
11	121	7/04/2018
12	121	22/04/2018
13
14	Table3
15	Product ID	Start Date	Buy Date 1	Buy Date 2	Buy date 3
16	121	5/04/2018	6/04/2018	7/04/2018
17	121	20/04/2018	22/04/2018
18	131	5/04/2018	6/04/2018
19
Join Tables

I only have Excel with a PC, so cannot offer any advice about Mac issues.