Auto populate cells

**cruzesr** · 04-23-2008, 05:15 PM

Good Afternoon,
I'm trying to track down numbers on a running race. The participants will run 5 laps around a 1/2 mile course and I want to track their numbers as they complete their laps.
I want to enter ALL numbers for ALL laps in columb A, starting at A1 thru A.... Now some racers will probably be lapped, therefore I want to automatically populate column B for runners who complete their second lap and automatically populate column C for the ones that complete their third lap and so on...until the fifth lap. Remember that I always want to enter numbers on column A. Therefore I will have repeated numbers in column A, that's OK. Here is an example of all this.

COLUMN A COLUMN B COLUMN C
2 --------- ---- 5 -------------- 5
3 --------- ---- 10
5 --------- ---- 21
21 --------- --- 1
10
1
8
4
5
10
25
99
5

So, as numbers repeat in column A, they should get populated in the other columns. As you see number 5 completed three laps and should populate column C.
If you could help with the formula to use I would really appreciate it. Please try to avoid macros. I don't know how to use them yet. Thank you.

**mrice** · 04-26-2008, 03:59 PM

Paste the following into the appropriate sheet tab (probably Sheet1) in the VBA editor. You will then get what you want.

Private Sub Worksheet_Change(ByVal Target As Range)
For Each Cell In Target
    If Cell.Column = 1 Then
        Count = Application.CountIf(Columns(1), Target)
        If Count > 1 Then
            Columns(1).Find(Target, Cells(1, 1), xlValues, xlWhole).Offset(0, Count - 1) = Target
        End If
    End If
Next Cell
End Sub

Some problems are best solved by macro.

**shg** · 04-26-2008, 06:34 PM

Here's a formula-based solution if you prefer. An advantage is that if you make a mistake (e.g., incorrectly enter a jersey number), you can fix it and the result will change appropriately.

A VBA solution could be written to do the same thing, of course.

Auto populate cells

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Auto populate cells

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions