Client record numbers shift when resorted in Excel Sheet View

Question

Client record numbers shift when resorted in Excel Sheet View

Gail Nampel 0

We use Excel to store client data for our Food Pantry. When a client calls, we enter the date and pickup time so our packers can have their food ready when the client arrives. Each client has a record number that is used for the sole purpose of printing a label to place on the box.

The packers use sheet view and hide columns that are not necessary for them to fill the order. Their view is filtered by date and includes: Client number, name, number of adults and children in household, ages of the kids, pickup time, and specific client notes-like dietary restrictions.

They sort by pickup time to help prioritize their work. However, when they sort, the assigned record number doesn’t always follow the client. If caller one picks up at 9:00, caller 2 at 10:00, and caller 3 at 9:30, it seems to go wrong when inserting a time BETWEEN times already displayed

I recently converted all the data in the sheet to a table hoping that would eliminate the problem, but no such luck. I should mention that I use ROW(**) to auto assign record numbers

Any help would be appreciated.

0 comments

1 answer

Your answer

Answer 1

Danish Gul Khattak 335

It sounds like the issue might be related to the sorting behavior in Excel tables. When you insert a new row between existing rows, the formulas might not automatically update as expected.

One way to handle this is by using the SORT function directly on the data, rather than relying on sorting through the Excel interface. This way, the entire row, including the assigned record number, should stay together.

Assuming your data is in a table named "YourTable," you can use a formula like this to create a sorted view:=SORT(YourTable, 6, 1)

Here, "6" represents the column number where the pickup time is located, and "1" indicates sorting in ascending order. Adjust the column number accordingly.

This formula creates a dynamic sorted view of your table that should update automatically when new data is added. It preserves the relationships between the record number and the corresponding client data during sorting.

Danish Gul Khattak 335 Reputation points

2023-12-16T08:56:53.1433333+00:00
Issue related to the use of the ROW() function for assigning record numbers, as it relies on the current row order. To address this, consider using a formula that creates unique record numbers based on client information.

For example, you could concatenate relevant data fields (e.g., date, pickup time, client number) and use a function like HASH() or a combination of other functions to generate a unique identifier. This way, even if you insert a time between existing ones, the record number remains associated with the specific client.

Here's a simplified example:

Create a new column for unique record numbers.

Use a formula like =HASH(CONCATENATE(A2, B2, C2)) or =CONCATENATE(A2, B2, C2) & ROW().

This approach ensures that each client gets a unique record number that won't change during sorting. Adjust the formula based on your specific data structure.

After making these changes, when you sort the data, the record numbers should stay linked to the correct client information.
Gail Nampel 0 Reputation points

2023-12-16T15:45:03.0666667+00:00

I will certainly give this a try, but the odd thing is, it only happens in Sheet View. The default view, used by another user looks fine.

Thank you for the idea!
Gail Nampel 0 Reputation points

2023-12-16T15:45:55.57+00:00

Thank you. I’ll give it a shot!

Share via

Client record numbers shift when resorted in Excel Sheet View

1 answer

Your answer