Variable Calc. In Excel 2

[M8tt]

I am working with a spreadsheet that looks similar to the following: -

Col.1 Col.2
Item 1 Part A
Item 2 Part B
Part C
Item 3 Part D
Part E
Part F
Item 4 Part G

And I am aiming to get this data into a format similar to: -

Col.1 Col.2
Item 1 Part A
Item 2 Part B
Item 2 Part C
Item 3 Part D
Item 3 Part E
Item 3 Part F
Item 4 Part G

So that each row of data has a reference in Column 1 where none exists at the moment. The reference being the column 1 entry directly above it.

I cannot find a way to manipulate my formulas to do this so am presuming that I need to use variables within a macro? Can anyone suggest how this may be achieved, I have not bothered to include my existing vb syntax as I suspect it is pretty wide of the mark.

Many Thanks in advance.

 

[wmichael]

If you could explain the nature of the data in column 1, that may help. I know that IF statements in formulas can be pretty powerful, but narrowing down the criteria to check can be a trick.

If you need/want to do it in VBA, then try a "case" statement.

CASE (col 1.value)

CASE <potential value 1>
col 1 + 1.value = <approriate value>
CASE <potential value 2>
etc....

The above is not great syntax, but the CASE statement may be the way to go.

If you want to provide more information, I can probably paint a more accurate picture.

Good luck!

~wmichael

&quot;small change can often be found under seat cushions&quot;

 

[SkipVought]

M8tt,

I do ALOT in VBA. However, this is one that I usually do on the sheet UNLESS it is a recurring thing.

Worksheet:
In a new column (my example is in col D) starting in row 2
=if(A1=A2,D1,A2)

Copy down
Copy this column
Select Col A and paste special - values

VBA:
Select anywhere in the column

    With Selection
        c = .Column
        With .CurrentRegion
            r1 = .Row
            r2 = .Rows.Count + r1 - 1
            c1 = .Column
            c2 = .Columns.Count + c1 - 1
        End With
        prev = ""
        For r = r1 To r2
            With ActiveSheet.Cells(r, c)
                If .Value = "" Then
                    .Value = prev
                Else
                    prev = .Value
                End If
            End With
        Next
    End With

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

Skip,

http://www.TheOfficeExperts.com

 

[KenWright]

You'll like this one I hope

Select all the data in Col 1 and do Edit / Go To Special / Blanks. Now type = and then with the cursor click on the FIRST entry above the FIRST blank cell (Which will currently be your activecell. Now just hit CTRL+ENTER at the same time and job done.

Select the whole column and do Edit / Copy, then Edit / paste Special / Values to get rid of the formulas.

Regards
Ken..............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[dc20]

Very tricky, Ken - I like it !

I've used shift-control-enter for brackets (matrices, etc), but never knew about control-enter. Kinda like a fill down, adding similar/same content to the whole range.

 

[KenWright]

I use it a lot with Pivot Tables. It bugs me when I have to change the 'default' function from Count to Sum as 99% of the time I actually want SUM. It only defaults to Count though because ther are usually blank cells in the source table. Select the whole table, do Edit / Go To / Special / Blanks, type 0 and then hit CTRL+ENTER and create your Pivot Table and no more 'Count'

Regards
Ken.............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[M8tt]

Thank you all for your help. Eventually went with Ken's idea as this seemed quickest - took a bit of playing to get it to work with my data (as it turned out the blanks weren't actually empty) but its now fine.

Thanks again.

 

[xlbo]

Ken - have you thought about 'faqqing' your fill empty cells technique ??

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[SkipVought]

M8tt,

Since Ken was so helpful, it is customary and appropriate to thank him for the help by activating the

Thank KenWright for this valuable post! hyperlink.

This also identifies a helpful post for other Tek-Tip members who will be searching for GOOD TIPS!



Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

Skip,

http://www.TheOfficeExperts.com

 

[KenWright]

LOL - That FAQing thing always seemed so hard

Hadn't thought about it to be honest, but I guess I should. Would start me off in the right direction with these FAQ thingies I guess, so I'll do that at the weekend. Cheers Geoff.

Regards
Ken.............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------