I have the following data in table July24_Details_1
| Case Number | Retro Start Date | Retro End Date | NMBR of Months | Retro Type | Invoice Amount | Contract Amount | Tenant Share | Company Share | Retro Units | December |
|---|---|---|---|---|---|---|---|---|---|---|
| 1793125 | 12/1/2024 | 12/31/2024 | 1 | Move_In | 1525 | 1525 | 0 | 1525 | 1 | 1.00 |
| 1793125 | 12/1/2024 | 12/31/2024 | 1 | Move_In | 2000 | 2000 | 0 | 2000 | 1 | 1.00 |
| 1845972 | 12/1/2024 | 12/31/2024 | 1 | 4662.9 | 4662.9 | 0 | 4662.9 | 0 | ||
| 1908825 | 12/1/2024 | 12/31/2024 | 1 | Move_In | 2246 | 2246 | 0 | 2246 | 0 |
and I have the months in a dynamic range in cell a2#.
I have the following formula to try to determine which months have duplicate case numbers that cross a retro threshold. In the case of my july data, the case number that meets this criteria is 1793125; it has a non-zero number in column Retro Units and December.
The following formula is an attempt to pull the July24_Details data to isolate just the case numbers that meet the criteria for each of the month in range a2#
<code>=MAP(A2#, LAMBDA(a, TRANSPOSE(CHOOSECOLS(FILTER(July24_Details_1, July24_Details_1[Retro Units]*CHOOSECOLS(July24_Details_1, XMATCH(a, July24_Details_1[#Headers]))), XMATCH(July24_Details_1[[#Headers],[Case Number]],July24_Details_1[#Headers])))))
</code>
<code>=MAP(A2#, LAMBDA(a, TRANSPOSE(CHOOSECOLS(FILTER(July24_Details_1, July24_Details_1[Retro Units]*CHOOSECOLS(July24_Details_1, XMATCH(a, July24_Details_1[#Headers]))), XMATCH(July24_Details_1[[#Headers],[Case Number]],July24_Details_1[#Headers])))))
</code>
=MAP(A2#, LAMBDA(a, TRANSPOSE(CHOOSECOLS(FILTER(July24_Details_1, July24_Details_1[Retro Units]*CHOOSECOLS(July24_Details_1, XMATCH(a, July24_Details_1[#Headers]))), XMATCH(July24_Details_1[[#Headers],[Case Number]],July24_Details_1[#Headers])))))
However, excel gives me an error stating that nested arrays are not supported.
I am essentially looking for
December – list of case numbers that meet the criteria for december
November – list of case numbers that meet the criteria for november
And so on.