Creating Macro using Ctrl+Shift+Up

[KO69]

Dear Prospective Helper,

I am currently in a situation where I am required to reformat a large spreadsheet. My current format is as follows:

Mr Smith
Ice Cream	Londis
Ice Cream	Londis
Mr Lewis
Crisps	Tescos
Crisps	Tescos
Ice Cream	Londis
Crisps	Tescos
Ice Cream	Tescos
Ice Cream	Londis
Mrs Jones
Chocolates	Co-op
Ice Cream	Londis
Chocolates	Co-op
Chocolates	Co-op
Chocolates	Co-op

<colgroup><col><col></colgroup><tbody>
</tbody>

AND I need to get it into the following format, where the subheading (name) is in a column to the left of the relevant columns:

	Mr Smith
Mr Smith	Ice Cream	Londis
Mr Smith	Ice Cream	Londis
	Mr Lewis
Mr Lewis	Crisps	Tescos
Mr Lewis	Crisps	Tescos
Mr Lewis	Ice Cream	Londis
Mr Lewis	Crisps	Tescos
Mr Lewis	Ice Cream	Tescos
Mr Lewis	Ice Cream	Londis
	Mrs Jone
Mrs Jone	Chocolates	Co-op
Mrs Jone	Ice Cream	Londis
Mrs Jone	Chocolates	Co-op
Mrs Jone	Chocolates	Co-op
Mrs Jone	Chocolates	Co-op

<colgroup><col><col><col></colgroup><tbody>
</tbody>

I have around 30,000 rows, and about 7,000 names to do this for, and am unable to 'record' a macro to automate this process (given my limited knowledge of excel.)

Please let me know if it is possible to automate this process using macros.

Many Thanks,

KO

 

[RoryA]

Assuming that data is in columns B and C and starts in row 1, you could enter in A2:
=IF(C2="","",IF(C1="",B1,A1))
and copy down

 

[DeBeuz]

Hi,

Insert the new column before the list (My original used colums A and B).
Open VBA and put in a formula with this code:

Option Explicit

Sub Copy_name()

    Dim R As Long
    Dim NameRng
    
    R = 1
    While Cells(R, 2).Value <> ""
        'Last name cell
        If Cells(R, 3).Value = "" Then
            NameRng = Cells(R, 2).Address
        Else
            Range(NameRng).Copy Cells(R, 1)
        End If
        R = R + 1
    Wend
End Sub

From the sheet run the macro (Alt-F8).

Is this what you were looking for?

Paul

 

[MickG]

Try this:-
This on the basis that "Mr Smith start in "A1"

[COLOR="Navy"]Sub[/COLOR] MG12Sep48
[COLOR="Navy"]Dim[/COLOR] Rng     [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dn      [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Temp    [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
application.ScreenUpdating = False
Columns("A:A").Insert
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("B1"), Range("B" & Rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Dn.Offset(, 1) = vbNullString [COLOR="Navy"]Then[/COLOR]
        Temp = Dn
    [COLOR="Navy"]Else[/COLOR]
        Dn.Offset(, -1) = Temp
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
application.ScreenUpdating = Tru
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[KO69]

Dear RoryA,

You are an absolute star!

You have saved me countless hours or trying to create a macro, and now my database will be set up in at least 1/10 of the time.

Many Thanks,

KO69

 

[KO69]

Dear RoryA, MickG and DeBeuz,


Thank you all for the help!

A quick question as to whether it would be most time efficient to use RoryA's, MickG's or DeBeuz's suggestions, given that this code will be used on a 'source' spreadsheet, which will feed into about 7 spreadsheets filled with 'if' functions.

Many Thanks,

KO69

 

[RoryA]

All you have to do for mine is type that formula once, double-click the fill handle then copy and paste special - values if required. I suspect it will be faster than looping VBA but you can always test it.