imagine that I have two spreadsheets, one with tenants that lived in my properties and one with the repairs that were done in the properties after the tenant left. Here is how they look. Tenancies:
想象一下,我有两个电子表格,一个是住在我的房产里的租户,另一个是租户离开后在房产中完成的修理。这是他们的样子。租赁:
Property ID | Tenant ID | Start of Tenancy | End of Tenancy | Start of next tenancy
1 | 1 | 01/01/2001 | 30/01/2001 | 15/02/2001
1 | 2 | 15/02/2001 | 28/02/2001 | 15/03/2001
1 | 3 | 15/03/2001 | 30/03/2001 | 15/04/2001
2 | 4 | 15/01/2001 | 30/01/2001 | 31/01/2001
2 | 4 | 31/01/2001 | 30/04/2001 | 05/05/2001
2 | 5 | 05/05/2001 | 05/06/2001 | 20/06/2001
3 | 6 | 03/05/2001 | 15/08/2001 | 30/08/2001
3 | 7 | 30/08/2001 | 15/10/2001 | 15/11/2001
4 | 1 | 31/01/2001 | 30/04/2001 | 15/05/2001
4 | 8 | 15/05/2001 | 30/08/2001 | 30/09/2001
Repairs:
Property ID | Repair Issued Date |
1 | 08/03/2001 |
1 | 10/04/2001 |
2 | 03/05/2001 |
2 | 15/06/2001 |
3 | 18/08/2001 |
4 | 10/05/2001 |
4 | 15/09/2001 |
I need to find out who was the tenant before the repair was done. Thus, I need to match a 'Property ID' and ,if 'Repair Issued Date' falls in a range between the 'End of Tenancy' And 'Start of next tenancy', assign the 'Tenant ID'. Simple example, the first repair in a property '1' was on 08/03/2001 which falls between the range of void for Tenant '2', so assign the tenant ID to next column.
我需要在修理完成之前找出谁是租客。因此,我需要匹配“物业ID”,如果“维修发布日期”落在“租期结束”和“下一租约开始”之间的范围内,则指定“租户ID”。简单的例子,属性'1'中的第一次修复是在08/03/2001,它落在租户'2'的无效范围之间,因此将租户ID分配给下一列。
Normally, if there would be only one tenant per property, I could do a simple lookup, but this complicates my situation.
通常,如果每个属性只有一个租户,我可以进行简单的查找,但这使我的情况变得复杂。
I tried to play around with vlookup and dates functions, but could not figure out the way to do it.
我尝试使用vlookup和日期功能,但无法弄清楚如何做到这一点。
Thanks for help
感谢帮助
2 个解决方案
#1
2
You can do this with the SUMIFS function. Adding up all Tenant ID that match, provided there is only ever one, will result in the Tenant ID:
您可以使用SUMIFS功能执行此操作。添加匹配的所有租户ID(前提是只有一个)将导致租户ID:
=SUMIFS(B:B,A:A,G2,D:D,"<" & H4,E:E,">" & H2)
As suggested by @Tom Sharpe, you could test for multiple matches and 'error' those out:
正如@Tom Sharpe所建议的那样,你可以测试多个匹配项,并测试出错'错误':
=IF(COUNTIFS(A:A,G2,C:C,"<" & H2,E:E,">" & H2)>1,"#MULTIPLE FOUND",SUMIFS(B:B,A:A,G2,C:C,"<" & H2,E:E,">" & H2))
#2
1
You seem to be looking for an INDEX with multiple criteria for the matching row number. The row number can be returned with AGGREGATE by forcing everything that doesn't match into an error state while discarding errors.
您似乎正在寻找具有匹配行号的多个条件的INDEX。通过在丢弃错误时强制不匹配的所有内容进入错误状态,可以使用AGGREGATE返回行号。
=INDEX(B:B, AGGREGATE(15, 6, ROW($1:$11)/((A$1:A$11=G2)*(D$1:D$11<=H2)*(E$1:E$11>=H2)), 1))
#1
2
You can do this with the SUMIFS function. Adding up all Tenant ID that match, provided there is only ever one, will result in the Tenant ID:
您可以使用SUMIFS功能执行此操作。添加匹配的所有租户ID(前提是只有一个)将导致租户ID:
=SUMIFS(B:B,A:A,G2,D:D,"<" & H4,E:E,">" & H2)
As suggested by @Tom Sharpe, you could test for multiple matches and 'error' those out:
正如@Tom Sharpe所建议的那样,你可以测试多个匹配项,并测试出错'错误':
=IF(COUNTIFS(A:A,G2,C:C,"<" & H2,E:E,">" & H2)>1,"#MULTIPLE FOUND",SUMIFS(B:B,A:A,G2,C:C,"<" & H2,E:E,">" & H2))
#2
1
You seem to be looking for an INDEX with multiple criteria for the matching row number. The row number can be returned with AGGREGATE by forcing everything that doesn't match into an error state while discarding errors.
您似乎正在寻找具有匹配行号的多个条件的INDEX。通过在丢弃错误时强制不匹配的所有内容进入错误状态,可以使用AGGREGATE返回行号。
=INDEX(B:B, AGGREGATE(15, 6, ROW($1:$11)/((A$1:A$11=G2)*(D$1:D$11<=H2)*(E$1:E$11>=H2)), 1))