Output a Table that shows when a column changes

toongal12

Board Regular
Joined
Dec 1, 2016
Messages
150
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.


ItemCompletedDateVersionType
Item-0011/1/11101
Item-0011/1/12111
Item-0011/1/13111
Item-0021/1/11101
Item-0022/1/12102
Item-0022/1/13111

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

ItemCompletedDateVersionType
Item-0011/1/11101
Item-0011/1/1211
Item-0021/1/11101
Item-0022/1/122
Item-0022/1/13111

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

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,543
Latest member
MartinLarkin

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
Back
Top