I think I need to create a macro?

[Guest]

Hi,

I have a large amount of unformatted data that I need to format in excel and
I don't know how! Currently the information is in the format

Username
Name
Title
Drive
Group
Group
Group
empty row
Username
Name
Title
Drive
Group
Group
empty row
Username etc The number of groups listed for each user varies.

I need to reformat so that on the one row the first column is just the
Username, the second is the Name, 3rd is Title, 4th is Drive, 5th is Group.
If there is more than one Group (and there are often multiple) the next Group
then needs to be in the same column as the first group (i.e. Column E) the
next row down, and the information in Rows A,B,C and D is repeated for each
extra row listing another group. There is then an empty row and then the next
User's information begins and I need the same reformatting to happen for
them. Each user has a random number of rows, depending on how many groups
that have listed for them, but you can tell when each user comes to an end
because of the blank row before the next user. Am I correct in thinking I
need a macro to reformat this? Can anyone help!? Thanks!

 

[Bob Phillips]

What identifies the end of the groups, or the start of the next username?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Hi Bob,

Currently it is all in just one column, so each group is on its own line -
i.e. a new row identifies a new group - and the start of the next username is
always preceded by a blank row.

Hope you can help!
Many thanks,
Emily

 

[maxsgroi]

Emily van Schaik ha scritto:

Hi Bob,

Currently it is all in just one column, so each group is on its own line -
i.e. a new row identifies a new group - and the start of the next username is
always preceded by a blank row.

Hope you can help!
Many thanks,
Emily

For the formulas below to work you need 2 empty rows before the first
username.
I supposed the first emptyrow below to be row1

Col1 (A) Col2 (B) Col3 (C)
emptyrow
emptyrow =if(A2="";0;B1+1)
=if(B2>4;indirect(address(row(B2)-B2+1;1));"")
Username =if(A3="";0;B2+1)
=if(B3>4;indirect(address(row(B3)-B3+1;1));"")
Name =if(A4="";0;B3+1)
=if(B4>4;indirect(address(row(B4)-B4+1;1));"")
Title etc.
Drive
Group
Group
Group
empty row
Username

Col4, col5, col6 as col3 (=if(B2>4;indirect(address(row(B2)-B2+X;
1));"") where X value is 2 for col4, 3 for col5, 4 for col6

Col1 (A) Col7 (G)
emptyrow
emptyrow =if(B2>4;A2;"")
Username =if(B3>4;A3;"")
Name =if(B4>4;A4;"")
Title etc.
Drive

I hope this helps

 

[Dave Peterson]

Are all the entries in that column values (not formulas)? And are those empty
rows really empty--not formulas that evaluate(d) to ""?

If yes to both, then with my data was in A1:Axxx on Sheet1, this worked ok for
me:

Option Explicit
Sub testme01()

Dim CurWks As Worksheet
Dim NewWks As Worksheet
Dim myRng As Range
Dim myArea As Range

Dim DestCell As Range
Dim HowManyGroups As Long
Dim NumberOfHeaders As Long

Set CurWks = Worksheets("sheet1")
Set NewWks = Worksheets.Add
Set DestCell = NewWks.Range("a1")

NumberOfHeaders = 4

With CurWks
Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp)) _
.Cells.SpecialCells(xlCellTypeConstants)
End With

For Each myArea In myRng.Areas
HowManyGroups = myArea.Rows.Count - NumberOfHeaders
If HowManyGroups > 0 Then
DestCell.Resize(HowManyGroups, NumberOfHeaders).Value _
= Application.Transpose(myArea.Resize(NumberOfHeaders, 1))
DestCell.Offset(0, NumberOfHeaders).Resize(HowManyGroups, 1).Value _
= myArea.Resize(HowManyGroups, 1) _
.Offset(NumberOfHeaders, 0).Value
Set DestCell = DestCell.Offset(HowManyGroups, 0)
End If
Next myArea

End Sub

 

[wdk]

Are all the entries in that column values (not formulas)? And are those empty
rows really empty--not formulas that evaluate(d) to ""?

If yes to both, then with my data was in A1:Axxx on Sheet1, this worked ok for
me:

Option Explicit
Sub testme01()

Dim CurWks As Worksheet
Dim NewWks As Worksheet
Dim myRng As Range
Dim myArea As Range

Dim DestCell As Range
Dim HowManyGroups As Long
Dim NumberOfHeaders As Long

Set CurWks = Worksheets("sheet1")
Set NewWks = Worksheets.Add
Set DestCell = NewWks.Range("a1")

NumberOfHeaders = 4

With CurWks
Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp)) _
.Cells.SpecialCells(xlCellTypeConstants)
End With

For Each myArea In myRng.Areas
HowManyGroups = myArea.Rows.Count - NumberOfHeaders
If HowManyGroups > 0 Then
DestCell.Resize(HowManyGroups, NumberOfHeaders).Value _
= Application.Transpose(myArea.Resize(NumberOfHeaders, 1))
DestCell.Offset(0, NumberOfHeaders).Resize(HowManyGroups, 1).Value _
= myArea.Resize(HowManyGroups, 1) _
.Offset(NumberOfHeaders, 0).Value
Set DestCell = DestCell.Offset(HowManyGroups, 0)
End If
Next myArea

End Sub









--

Dave Peterson- Hide quoted text -

- Show quoted text -

I'm trying to follow this as I need something similar. I see the macro
that has been written. My question (I'm new to macros) is how do you
use this macro. I mean, if I copied and pasted something similar,
where do I copy it to/how do use it. I've tried keystroke macros, but
copying and pasting this over a test recorded macro isn't working. I'm
sure that is not how it's supposed to be run anyway.

 

[Dave Peterson]

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Short course:
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel to test it out.

Tools|Macro|macros|select that macro and click Run

======
This line of code specifies where to find the data:
Set CurWks = Worksheets("sheet1")

if you want it to work against the activesheet, change it to:
Set CurWks = ActiveSheet

 

[wdk]

If you're new to macros, you may want to read David McRitchie's intro at:http://www.mvps.org/dmcritchie/excel/getstarted.htm

Short course:
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel to test it out.

Tools|Macro|macros|select that macro and click Run

======
This line of code specifies where to find the data:
Set CurWks = Worksheets("sheet1")

if you want it to work against the activesheet, change it to:
Set CurWks = ActiveSheet



wdk wrote:

Thank you!!!!

 

[Guest]

HI Dave,

Apologies - I was getting a Java script error at home so wasn't able to
respond to your advice. However it worked perfectly for me - many thanks!! I
really appreciate your efforts to help.

 

[Guest]

Hi,

I did actually use these formulas - thanks - and it worked well. In
particular it has really helped me get my head around the IF statements and
how I can use them, especially also in conjunction with setting up numbering
per user to help - so many thanks!!