Forum Discussion
Keep the row numbers in pivot table when you sort on field
I have made a pivot table and added a column at the end which is called comments. The purpose for that column is off course to make comments to the separate row in the pivot table. This works fine when i look at all the rows in the table, but when i use filter you get a shorter list, and the comments doesn’t follow the rows. You get a renumbering of the rows always starting with 1 and counting 1,2,3,4 and so on, instead that the row numbers stay constant as for the table without filter. Is it possible to keep the row number constant in a pivot table?
kennethsorsdalbirno right. This way it is never going to work. Excel doesn't "know" the data you put next to a pivot table needs to stay with a certain row of the pivot table. You need to create a separate table for the comments which has all the unique items which are your current pivot table rows. Then use a lookup formula to fetch the information from that table next to your pivot table.
- Please share:
- How the row numbers are generated
- How the comments you mention are fetched along the correct row numbersThe first picture you see the table when i have not used the filter. You can see the comments at each row.
In this picture i have used the filter 100. You can see that the comments do not follow the row they where written to
kennethsorsdalbirno right. This way it is never going to work. Excel doesn't "know" the data you put next to a pivot table needs to stay with a certain row of the pivot table. You need to create a separate table for the comments which has all the unique items which are your current pivot table rows. Then use a lookup formula to fetch the information from that table next to your pivot table.
