Create self updating data table

[jb]

Hi experts,
I want to create self updating data table.
I have a worksheet with 4 columns with title - serial number, name, code concatenated with name, and sorted list.

Sorted list is generated using formula and it is self updating when code attached with name in column C changes.

I want to generate a table with 6 columns with title 0 to 5. This table must be self updated according to sorted list.

I am attaching sample sheet.

Thanks all in advance.

 

[azumi]

Perhaps something like this? using array formula (you need topress CTRL-SHIFT-ENTER button together)

Regards

 

[jb]

Thanks for reply. It worked.
But I want to know meaning of ROWS($A1:$A$1) used at end of formula.

I have insert some rows before this calculation. Instead of of 5 to 49 rows now it is from 95 to 139.

I have inserted some column between B and C columns. Now C column went to Q column.

I tried to change formula as per new locations but it is not working. I have pressed ctrl+shift+enter in cell after making changes to make a array formula.

 

[azumi]

ROWS($A1:$A$1) return 1 and when you dragged down 1 row it change ROWS($A$1:$A2) results is 2, so it returns row number 1 and row number 2, combine with SMALL function and IF function, it qill return the results first occurence and second occurence and so on, and INDEX does the rest.

Maybe you have your sample workbook with actual table? with fake data its okay...

Regards