So I want to be able to output a table that whenever an item changes its version or type, the output will list the date and information about the item.
For Example:
In this table, the Item has lines that have versions and/or types changing on a day.
<tbody>
</tbody>
THE DATA IS NOT STATIC, IT IS CONSTANTLY SORTED.
Using the data from this table, I want to create a static table that will output the following:
<tbody>
</tbody>
Pretty much I want to be output the information when one of or both of these two column changes. The first instance of an item will always output a Version and Type since it is the first "change".
My data comes from a pivot table so I'm doing short hand with the names of data:
I am not sure how to output neatly. I started with:
{=(SMALL(IF((Item-001),VALUE(Version),1))}
The problem is there can be multiple Versions so it doesn't pick the first one.
Then I tried:
{=INDEX(CompletedDate,MATCH(Item-001&SMALL(IF((Item-001),FieldingData[CompletedDate]),1),Item-001&CompletedDate),0)}
But it didn't give me the lowest for every one.
For Example:
In this table, the Item has lines that have versions and/or types changing on a day.
Item | CompletedDate | Version | Type |
Item-001 | 1/1/11 | 10 | 1 |
Item-001 | 1/1/12 | 11 | 1 |
Item-001 | 1/1/13 | 11 | 1 |
Item-002 | 1/1/11 | 10 | 1 |
Item-002 | 2/1/12 | 10 | 2 |
Item-002 | 2/1/13 | 11 | 1 |
<tbody>
</tbody>
THE DATA IS NOT STATIC, IT IS CONSTANTLY SORTED.
Using the data from this table, I want to create a static table that will output the following:
Item | CompletedDate | Version | Type |
Item-001 | 1/1/11 | 10 | 1 |
Item-001 | 1/1/12 | 11 | |
Item-002 | 1/1/11 | 10 | 1 |
Item-002 | 2/1/12 | 2 | |
Item-002 | 2/1/13 | 11 | 1 |
<tbody>
</tbody>
Pretty much I want to be output the information when one of or both of these two column changes. The first instance of an item will always output a Version and Type since it is the first "change".
My data comes from a pivot table so I'm doing short hand with the names of data:
I am not sure how to output neatly. I started with:
{=(SMALL(IF((Item-001),VALUE(Version),1))}
The problem is there can be multiple Versions so it doesn't pick the first one.
Then I tried:
{=INDEX(CompletedDate,MATCH(Item-001&SMALL(IF((Item-001),FieldingData[CompletedDate]),1),Item-001&CompletedDate),0)}
But it didn't give me the lowest for every one.
Last edited: