Excel Auto increment one column based on another
Hi Experts!
I have a column (Column A) of work order numbers that vary in the number of times they repeat. Is there a formula I can use in another column to assign a number (1, 2, 3, etc.) based on the number of times a number is repeated in Column A?
Please see attached. I'd prefer a formula vs. VBA if possible. Thanks for your help!
Count-Test.xlsx
I have a column (Column A) of work order numbers that vary in the number of times they repeat. Is there a formula I can use in another column to assign a number (1, 2, 3, etc.) based on the number of times a number is repeated in Column A?
Please see attached. I'd prefer a formula vs. VBA if possible. Thanks for your help!
Count-Test.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Shaun,
I've pounded on this a bit further and see it's failing within the List/Table structure. It wasn't apparent at first, but if I add to the bottom of the table, the formula copies down but grabs more data below than it should. The attached showed clearly what's happening.
So sorry I didn't catch this initially. It seemed to work so beautifully until now. I have to believe there's a simple fix. If I need to re-open and assign more points, I'd be happy to do that. Thank you!
Count-Test.xlsx
I've pounded on this a bit further and see it's failing within the List/Table structure. It wasn't apparent at first, but if I add to the bottom of the table, the formula copies down but grabs more data below than it should. The attached showed clearly what's happening.
So sorry I didn't catch this initially. It seemed to work so beautifully until now. I have to believe there's a simple fix. If I need to re-open and assign more points, I'd be happy to do that. Thank you!
Count-Test.xlsx
The issue is the formula did not copy down correctly. For the two cells with issues, the formula should have been countif($A$4:A18,A18) and countif($A$4:A19,A19) respectively.
ASKER
Hey Shaun. I understand how it works except the formula is auto populating and not in my control. It's happening when I add a new entry underneath the table. The formula jumps rows to include a row further down in the table. It's a bit odd. I use tables all the time and have not had this issue before. If you try adding a new entry below the table, it will likely happen to you as well. I've gone in and fixed it and then added a new row underneath with the same problem.
If you've got any suggestions at all, I'd be super grateful! Thanks again for your help on this!
If you've got any suggestions at all, I'd be super grateful! Thanks again for your help on this!
I believe the issue you are seeing is related to the autocompletion setting. If you insert rows in the middle of your table, the auto complete is incorrectly filling in the adjacent cells. If you turn this off (Microsoft Office button -> Excel Options -> Advanced) you should see different results.
ASKER
Aha! I will have to give that a try. Thank you so much for following up! I came up with a workaround in another column using ADDRESS and then adding INDIRECT to the formula you and I were working with. It worked beautifully. Thanks again, Shaun!
ASKER