Fill down automatically (problem) 3rd posting

[Lorrie]

Let me try this again.

-A- -B- -C- -D-
Account No. Check No. Name Address
123-0001 1234 John Doe 1234 Anywhere USA
1256 Mary Smith 1520 There Street
(approx 15 pgs) 1298 Harry Soso 1001 Post Street
1690 Henry Howe 345 This street
123-0065 1299 Jack Sprat 480 Queen Avenue
(approx 3 pgs) 1243 Jill JOnes 512 Harrold Way
1620 Jenny Jones 778 That Street

Here is the problem: I have approximately 800 accounts,
each has between 3 and 15 pages of entries. I need to
fill down between the accounts - BUT there are over 800
accounts of data and I do not want to spend the three
days that it would take to go page by page, find the cell
in which the account number changes, highlight them and
then use the F5, blanks, etc., etc. procedure. I must
not be explaining this problem correctly - I'm sure that
if I were; people would understand that it is an
impossible task to do each number change individually.
And, this must be accomplish every month...surely there
must be an easy way for doing it.

Thank you, in advance for any help - or letting me know
that it is impossible and I need to forget it and hire
someone to help.

 

[drabbacs]

I haven't seen your earlier posts so if I'm
misunderstanding, please forgive me.

When you say 800 accounts with 3 - 15 pages of data, if
these are all on *one sheet* then this is a 5 second
solution. If you mean 800 sheets then this won't really
help.

Using your example, I recommend splitting your display
(window-> split). In the bottom window, put the cursor in
column B and hit ctrl-downarrow. This should find the last
line of your data. In the top window click on A2. In the
bottom window shift-click on A??? where ??? = the last
line of your data.

Now hit f5 and select 'special'. Select 'blanks' and 'ok'.
Then type = and the uparrow and then hit *Ctrl-enter*.


The ctrl-enter is necessary to make the formula behave as
an array. It will automatically adjust the formula
throughout the selection (the blanks). Meaning, you don't
have to 'find' the first entry at all. Excel does that for
you.

See http://www.contextures.com/xlDataEntry02.html for more
info.

Good Luck
Drabbacs

 

[Jeff]

Based on your post, I believe you are trying to fill in the A,B,C,D colum with the value above whenever the value is blank. Try going out to columns E,F,G, and H and putting an IF formula which evaluates the A-D columns and returns the cell value or the value above it

Example: For Cell E
=IF(A2>0,A2,E1

Copy the formula down and across. Cells E2 through E??? should populate with 123-0001 until a new value appears in the A column and so on

Hope this helps

Jeff Bedenbaugh, CPA, MB
Quincy, FL

 

[Dave Smith]

Lorrie,

If I understand correctly, you want:

A B C D
1 Account No. Check No Name Address
2 101 301 John Somewhere
3 403 Mary Here
4 405 Harry There
5 102 343 Henry Everywhere
6 347 Jack Not Here
7 754 Jill Out there
8 103 435 Tom Main st
9 744 Dick Elm St
10 456 Harry High St


to become:

A B C D
1 Account No. Check No Name Address
2 101 301 John Somewhere
3 101 403 Mary Here
4 101 405 Harry There
5 102 343 Henry Everywhere
6 102 347 Jack Not Here
7 102 754 Jill Out there
8 103 435 Tom Main st
9 103 744 Dick Elm St
10 103 456 Harry High St


A macro like this should do the trick:

Sub AccountFiller()
Dim AccountNo

Range("A2").Select
While Not IsEmpty(selection.Offset(0, 1).Value)
If Not IsEmpty(selection.Value) Then
AccountNo = selection.Value
Else
selection.Value = AccountNo
End If
selection.Offset(1).Select
Wend
End Sub

To explain briefly: this moves down column A starting from A2 and stopping
when it finds that the adjacent cell in column B is empty (this serving as a
flag of the final entry; you may need to use another flag depending on your
actual data). If the Cell in A is _not_ empty, it's value is saved; if it
is empty, the previously saved value is written to the cell.

HTH.

-Dave Smith

 

[Guest]

Actaully I figured it out! phew!

I did a "LOOKUP" for the cell above, copied that formula,
F5, Special, blanks, pasted the formula into the bar then
hit Ctrl/enter and every empty cell was filled with the
acct no. from above. Thanks for you time.

Lorrie

 

[Dave Smith]

This is an excellent solution and is essentially what others were shooting
for I believe. One thing with this is that it will put formulas into the
cells which could cause issues if you really need values (e.g. if you delete
a row). You can fix this by copy, paste special, values.

Dave