Solved

Excel Auto increment one column based on another

Posted on 2014-04-25
7
5,438 Views
Last Modified: 2014-05-01
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
0
Comment
Question by:xllvr
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 26

Accepted Solution

by:
Shaun Kline earned 500 total points
ID: 40023476
You can use the COUNTIF function to do this. You would anchor the range to the first cell with an order number.
It would be similar to this: (This is assuming A1 has a title for your column. If not adjust accordingly.)
=COUNTIF($A$2:A2, A2)

If you put this in B2 and drag/fill down to the row with your last order number, it will count the number of occurrences of the order number that appear in the range.
0
 
LVL 1

Author Closing Comment

by:xllvr
ID: 40023495
I was so in the weeds I did not even consider such a simple solution.  Thank you!!!!  It's perfect.
0
 
LVL 1

Author Comment

by:xllvr
ID: 40023749
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
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 26

Expert Comment

by:Shaun Kline
ID: 40023982
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.
0
 
LVL 1

Author Comment

by:xllvr
ID: 40023993
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!
0
 
LVL 26

Expert Comment

by:Shaun Kline
ID: 40035570
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.
0
 
LVL 1

Author Comment

by:xllvr
ID: 40035659
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!
0

Featured Post

Get HTML5 Certified

Want to be a web developer? You'll need to know HTML. Prepare for HTML5 certification by enrolling in July's Course of the Month! It's free for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question