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

Podcaster

New Member
Joined
Jun 18, 2018
Messages
4
Hi Everyone -
I have three sets of data.
One is organized like this:
Table1
Product IDStart DateCode
1214/05/2018X
1314/05/2018V
1214/20/2018

<tbody>
</tbody>


Another like this:
Table2
Product IDBuy Date
1214/06/2018
1314/06/2018
1214/07/2018
1214/22/2018

<tbody>
</tbody>


And a third like this:
Table3
Product IDStart DateBuy Date 1Buy Date 2 (if applicable)Buy date 3 (if applicable)
1214/05/20184/6/20184/7/2018
1214/20/20184/22/2018nullnull
1314/05/20184/06/2018nullnull

<tbody>
</tbody>


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 :)
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
55,853
Office Version
  1. 365
Platform
  1. Windows
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.
 
Last edited:

Podcaster

New Member
Joined
Jun 18, 2018
Messages
4
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
55,853
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jun 18, 2018
Messages
4
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
55,853
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jun 18, 2018
Messages
4
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
55,853
Office Version
  1. 365
Platform
  1. Windows
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
ABCDE
1Table1
2Product IDStart DateCode
31215/04/2018X
41315/04/2018V
512120/04/2018
6
7Table2
8Product IDBuy Date
91216/04/2018
101316/04/2018
111217/04/2018
1212122/04/2018
13
14Table3
15Product IDStart DateBuy Date 1Buy Date 2Buy date 3
161215/04/20186/04/20187/04/2018
1712120/04/201822/04/2018
181315/04/20186/04/2018
19
Join Tables



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

Forum statistics

Threads
1,172,238
Messages
5,879,866
Members
433,461
Latest member
Confusedexcelhelp

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top