MrPokemans
New Member
- Joined
- Dec 3, 2013
- Messages
- 10
Hi,
I'm breaking my head on this one.
The data set contains:
on Worksheet 1, a table with thousands of projects:
column A: company name (not unique for each project)
column B: start date of project
column C: end date of project
...with every row being a single project
on Worksheet 2, a table with thousands of invoices:
column A: company name (not unique for each invoice)
column B: starting date of invoiced period
column C: end date of invoiced period
...with every row being a single invoice
Each project is unique and doesnt overlap with other projects of the same company. A single project can have multiple invoices corresponding to it, but each invoice applies only to a single project.
The company name must be similar to that of the invoice to be applicable for that project. Additionally, the invoiced dates must be within the period of that project.
The question is:
How to add a value to each invoice showing the row number of the project that it corresponds to.
For example:
The first row is a project from company HAPPY and has a starting date of 1-1-16 and an end date of 12-12-16.
The second is a project from company HAPPY has a starting date of 1-1-17 and an end date of 2-2-17.
The third is a project from company FAKE has a starting date of 5-5-16 and an end date of 9-9-16.
The first invoice is from company HAPPY, for the period 2-2-16 untill 4-4-16
The second invoice is from company HAPPY, for the period 5-5-16 untill 6-6-16
The third invoice is from company FAKE, for 10-10-16 untill 11-11-16.
The result should be that the first invoice corresponds to the first project (row# 1)
The second invoice also corresponds to the first project (row# 1)
The third invoice corresponds to none of the three projects (row# error)
My progress so far:
Ive tried INDEX MATCH functions in array format, but it fails to deal with multiple MATCH conditions for both starting and ending dates at the same time.
One interesting idea was to convert the company names in column A to numbers. Then I made an extra column D adding the company number to the starting date (=A1&B1) for both the projects table and the invoice table. Next I added another column E in the Invoice table to MATCH them with the projects column D. This way I can play with the MATCH criteria number of -1, 0 or 1 to match the invoice starting date as 'at least' as high as the starting date of the project. But I couldnt make the MATCH it with the end date at the same time and came to a dead end.
Unfortunately I can't share the file with you because of a lot of privacy related data. I could build a new file but that would take some time.
Can anyone help me???
Regards, Joris
I'm breaking my head on this one.
The data set contains:
on Worksheet 1, a table with thousands of projects:
column A: company name (not unique for each project)
column B: start date of project
column C: end date of project
...with every row being a single project
on Worksheet 2, a table with thousands of invoices:
column A: company name (not unique for each invoice)
column B: starting date of invoiced period
column C: end date of invoiced period
...with every row being a single invoice
Each project is unique and doesnt overlap with other projects of the same company. A single project can have multiple invoices corresponding to it, but each invoice applies only to a single project.
The company name must be similar to that of the invoice to be applicable for that project. Additionally, the invoiced dates must be within the period of that project.
The question is:
How to add a value to each invoice showing the row number of the project that it corresponds to.
For example:
The first row is a project from company HAPPY and has a starting date of 1-1-16 and an end date of 12-12-16.
The second is a project from company HAPPY has a starting date of 1-1-17 and an end date of 2-2-17.
The third is a project from company FAKE has a starting date of 5-5-16 and an end date of 9-9-16.
The first invoice is from company HAPPY, for the period 2-2-16 untill 4-4-16
The second invoice is from company HAPPY, for the period 5-5-16 untill 6-6-16
The third invoice is from company FAKE, for 10-10-16 untill 11-11-16.
The result should be that the first invoice corresponds to the first project (row# 1)
The second invoice also corresponds to the first project (row# 1)
The third invoice corresponds to none of the three projects (row# error)
My progress so far:
Ive tried INDEX MATCH functions in array format, but it fails to deal with multiple MATCH conditions for both starting and ending dates at the same time.
One interesting idea was to convert the company names in column A to numbers. Then I made an extra column D adding the company number to the starting date (=A1&B1) for both the projects table and the invoice table. Next I added another column E in the Invoice table to MATCH them with the projects column D. This way I can play with the MATCH criteria number of -1, 0 or 1 to match the invoice starting date as 'at least' as high as the starting date of the project. But I couldnt make the MATCH it with the end date at the same time and came to a dead end.
Unfortunately I can't share the file with you because of a lot of privacy related data. I could build a new file but that would take some time.
Can anyone help me???
Regards, Joris
Last edited:
