Assign Number to Duplicate Values

Bethany Garcia

I have a sheet that tracks the phone number of our company cell phones and the allocation % breakdown for each phone for the appropriate budget codes that pay for that phone.

For example, line 123-456-7890 has one row with 100% allocation. Line 098-765-4321 has three rows with allocation of 50%, 25% and 25%.

I'm trying to create a report off that sheet that will display each line one time, regardless of how many times it appears on the primary sheet. My idea for how to do that is to use a helper column to assign a number to each row based on how many rows contain that value.

So for example, line 123-456-7890 would show a 1 in the helper column, and line 098-765-4321 would have a 1 in the helper column for the first instance of the row, a 2 in the helper column for the second instance, and a 3 for the third. Then, when I build my report I can filter it to show just the values of 1 from the helper column.

Any idea how to accomplish this, or a better way to create a report that lists every number one time regardless of how many rows it has on the source sheet?

Carson Penticuff

Here is my take on it:

Setup the phone numbers as a hierarchy. Set your phone number as the parent and each "budget code" or however you differentiate as the children. Then use a formula to add the childrens' percentages together for the actual phone number. (If this information is needed) Add a checkbox helper column to distinguish the phone numbers. (Needed for the report)

For the report, add a filter to only display rows with the [Phone?] checkbox checked.

I'm sure there are other options, but this seems like a starting point.