Selectivly copy data in Excel

I need to be able to copy data to a cell if it is empty, based on the closest cell with data in. For example, if Cell A1, A2 contains no data, cell A3 has the value 100, and cell A4 and A5 contain no data, and cell A6 has the value 200, then cell A2 and A4 should display 100, and cell A5 should display 200.

Does that make sense?

Many thanks,

Ben
BenwycliffeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ImageryGrlCommented:
Does it need to be in the same sheet?  This could be accomplished quite easily if you use a separate spreadsheet or worksheet tab.  See spreadsheet attached for the "low tech" version.  The Base tab has the original numbers, the "Filled In" tab shows the formulas.  After the formulas are done, you could use copy paste values to put them back "filled in" into the original sheet.
test.xls
0
RaahaugenCommented:
You will have to specify a little more, but you can always show data in one cell depending on data in others.

One example is if you need A2 to be 100 if any data is put in A1, and otherwise empty:

A2: =IF(ISBLANK(A1);"";100)

Another example as above, but A2 now depends on both A1 and A3:

A2: =IF(AND(ISBLANK(A1);ISBLANK(A3));"";100)


But give us some more details on what you want?
0
BenwycliffeAuthor Commented:
Hi, Thanks for getting back. The data needs to be on the same sheet, as once the values are filled in, you can then filter them.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

RaahaugenCommented:
so the solution ImageryGrl came up with is what you want, just on one sheet? this is quiet easy, but will include some vba.

I will do it for you, but I am rather confused about what the point is :)

So same thing, but in one sheet only?

0
BenwycliffeAuthor Commented:
Hi,

Perhaps I should explain a bit. Basically, the spreadsheet comes from an accounts package that pumps out it's data in a rather messy fashion. In the example, the column I am interested in is G. The codes going down G need to apply to the row above and below the row/s that have a code.

G3 and G5 need to display 810000. G18 and G24 would need to display 101001. So where there is no code, I want to apply the code that corresponds to the invoice.
example.xlsx
0
Saurabh Singh TeotiaCommented:
There you go, Use the following code it will do what you are looking for..
Enclosed is your workbook post i have run macro over it.
Saurabh...

Sub fillvalues()
    Dim srow As Long, srow1 As Long
    Dim val As Variant
    srow = 3
    Do Until srow > Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
        If Cells(srow, "G").Value = "" And Cells(srow, "F").Value <> "" Then
            srow1 = srow
            Do Until Cells(srow1, "g").Value <> ""
                srow1 = srow1 + 1
            Loop
            val = Cells(srow + 1, "g").Value
            Cells(srow, "G").Value = val
        ElseIf Cells(srow, "g").Value = "" Then
            Cells(srow, "G").Value = val
        End If
        srow = srow + 1
    Loop



End Sub

Open in new window

example-1-.xls
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RaahaugenCommented:
Ok so a very simple solution without using vba will be to use the row next to G, as the code-row, since no formulas will then be overwritten whenever new data is pulled.

Starting from the cell I3: =IF(G2="";IF(G3="";IF(G4="";"";G4);G3);G2)

then simple drag the cell as long as you want down the I column.


another method is using vba as mentioned above.
0
BenwycliffeAuthor Commented:
Fantastic! That has done the job. I can now sort on the G column, and have all the codes in order.

Thanks for your help everyone.

Ben
0
BenwycliffeAuthor Commented:
Very quick and to the point. Huge happiness.
0
RaahaugenCommented:
You're welcome.

That was my first answer on experts exchange :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.