Sorting every 3rd row 
Author Message
 Sorting every 3rd row

Is it possible to sort information based on what begins every 3rd row of a
worksheet?

Thanks,
Tom



Wed, 18 Jun 1902 08:00:00 GMT  
 Sorting every 3rd row
Yes.

But be more specific.  By "begins every 3rd row" do you mean what is in column
A?  What is your sort criterion? And which version of Excel are you using?  You
may be able to do this with adept use of the built-in spreadsheet functions,
depending on the answers to the avove.  If not, are you comfortable with using
VBA code (which, of course, is infeasible if you run Excel 4.0 or earlier)?

Dave Braden

Quote:

> Is it possible to sort information based on what begins every 3rd row of a
> worksheet?

> Thanks,
> Tom



Wed, 18 Jun 1902 08:00:00 GMT  
 Sorting every 3rd row
I'm using version 7.0A and I have a very extensive password list (about 1100
passwords with usernames) that extend down the "A" column.  Every 3rd row is
the username.  So the format is - username - password - blank row -
username - password - blank row, and so forth.  I'm trying to alphabetize it
by username but can't figure out how.  The usernames are on every 3rd row of
column A.

Thanks!
Tom



Quote:
> Yes.

> But be more specific.  By "begins every 3rd row" do you mean what is in
column
> A?  What is your sort criterion? And which version of Excel are you using?
You
> may be able to do this with adept use of the built-in spreadsheet
functions,
> depending on the answers to the avove.  If not, are you comfortable with
using
> VBA code (which, of course, is infeasible if you run Excel 4.0 or
earlier)?

> Dave Braden


> > Is it possible to sort information based on what begins every 3rd row of
a
> > worksheet?

> > Thanks,
> > Tom



Wed, 18 Jun 1902 08:00:00 GMT  
 Sorting every 3rd row
Tom

Perhaps not the perfect solution, but the structure you have is not good for
very much at all, as the data should be in columns, not rows.  E.g. User
name in A1, Password in B1.

To ease this transition, paste the code below into the module sheet of a new
book, copy and paste your data into column A on sheet one of this book and
run the code.  It will populate Sheet2, (make sure you have that too), of
this new book, with your data, but will have 'turned' it.  This will make it
far easier to sort, mail merge, or do just about anything with.  All told, a
better database structure

(Code written for XL2000 and un-tested in 95, but should be OK, hence the
copying to a new workbook before)

Sub TransData()
Dim topRow As Long, botRow As Long, newPos As Long
Application.ScreenUpdating = False
topRow = 1
botRow = 3
newPos = 1
Do While Sheets("Sheet1").Range("A" & topRow).Value <> ""
Sheets("Sheet1").Range("A" & topRow & ":A" & botRow).Copy
Sheets("Sheet2").Range("A" & newPos).PasteSpecial Transpose:=True
topRow = topRow + 3
botRow = botRow + 3
newPos = newPos + 1
Loop
Application.ScreenUpdating = True
End Sub

Let me know how you get on.

--
HTH
Nick Hodge
Southampton, England


Quote:
> I'm using version 7.0A and I have a very extensive password list (about
1100
> passwords with usernames) that extend down the "A" column.  Every 3rd row
is
> the username.  So the format is - username - password - blank row -
> username - password - blank row, and so forth.  I'm trying to alphabetize
it
> by username but can't figure out how.  The usernames are on every 3rd row
of
> column A.

> Thanks!
> Tom



> > Yes.

> > But be more specific.  By "begins every 3rd row" do you mean what is in
> column
> > A?  What is your sort criterion? And which version of Excel are you
using?
> You
> > may be able to do this with adept use of the built-in spreadsheet
> functions,
> > depending on the answers to the avove.  If not, are you comfortable with
> using
> > VBA code (which, of course, is infeasible if you run Excel 4.0 or
> earlier)?

> > Dave Braden


> > > Is it possible to sort information based on what begins every 3rd row
of
> a
> > > worksheet?

> > > Thanks,
> > > Tom



Wed, 18 Jun 1902 08:00:00 GMT  
 Sorting every 3rd row
Tom;

This little routine will move each password up next to the name, and delete
the other two rows. It's not smart, so if your list does not maintain the
three cell pattern it'll mess up. Have an extra copy of your list!

Sub RearrangePasswords()
  Do While Len(Selection) > 0
    Selection.Offset(1, 0).Select ' move down to next pwd
    Selection.Cut Destination:=Selection.Offset(-1, 1) ' move the pwd
    Selection.Range("A1:A2").EntireRow.Delete ' delete pwd and blank row
    Loop
  End Sub

Put the cell pointer in the first name cell before you run it. After it
runs, sort the list. If you've never used the sort command before, be
careful!!!

I think you'll find that having each person in one row easier to work with
anyway.

Regards,

Earl Kiosterud

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

Quote:

>I'm using version 7.0A and I have a very extensive password list (about
1100
>passwords with usernames) that extend down the "A" column.  Every 3rd row
is
>the username.  So the format is - username - password - blank row -
>username - password - blank row, and so forth.  I'm trying to alphabetize
it
>by username but can't figure out how.  The usernames are on every 3rd row
of
>column A.

>Thanks!
>Tom



>> Yes.

>> But be more specific.  By "begins every 3rd row" do you mean what is in
>column
>> A?  What is your sort criterion? And which version of Excel are you
using?
>You
>> may be able to do this with adept use of the built-in spreadsheet
>functions,
>> depending on the answers to the avove.  If not, are you comfortable with
>using
>> VBA code (which, of course, is infeasible if you run Excel 4.0 or
>earlier)?

>> Dave Braden


>> > Is it possible to sort information based on what begins every 3rd row
of
>a
>> > worksheet?

>> > Thanks,
>> > Tom



Wed, 18 Jun 1902 08:00:00 GMT  
 Sorting every 3rd row
Well, it's sort of brute force, but if your data is in A1:A1101 and columns B,
C, and D are empty, the following procedure will reorder your data; be sure to
make a copy first, because the old data will be gone:

Sub test1()
Set rngA = Sheets(1).[a1]
Set rngB = Sheets(1).[b1:d1].Rows
For i = 1 To 1101 Step 3
rngB(i) = Application.Transpose(rngA(i).Resize(3))
Next
Range("B1:D1101").Select
Selection.Sort Key1:=Range("B1"), Order1:=xlAscending
For i = 1 To 1101 / 3
rngA(3 * i - 2).Resize(3) = Application.Transpose(rngB(i))
Next
Sheets(1).Range(rngB(1), rngB(1101)).ClearContents
rngA.Select
End Sub


Quote:

> I'm using version 7.0A and I have a very extensive password list (about 1100
> passwords with usernames) that extend down the "A" column.  Every 3rd row is
> the username.  So the format is - username - password - blank row -
> username - password - blank row, and so forth.  I'm trying to alphabetize it
> by username but can't figure out how.  The usernames are on every 3rd row of
> column A.

> Thanks!
> Tom



> > Yes.

> > But be more specific.  By "begins every 3rd row" do you mean what is in
> column
> > A?  What is your sort criterion? And which version of Excel are you using?
> You
> > may be able to do this with adept use of the built-in spreadsheet
> functions,
> > depending on the answers to the avove.  If not, are you comfortable with
> using
> > VBA code (which, of course, is infeasible if you run Excel 4.0 or
> earlier)?

> > Dave Braden


> > > Is it possible to sort information based on what begins every 3rd row of
> a
> > > worksheet?

> > > Thanks,
> > > Tom



Wed, 18 Jun 1902 08:00:00 GMT  
 Sorting every 3rd row
I would do this in Winword using a little trick that often comes in handy
for importing addresses into spreadsheets.
I would shove the file into Winword and do a few Search and Replaces so as
to get the "list data" into lines.
Then shove it back into Excel for the sort.

Supposing the file is
--------------------------
Harry
Harry's Password

Julie
Julie's Password

etc

There is an toggle button in Word where you can view or not view paragrapgh
marks.
If you toggle this to ON the logic of the following should be easier to
understand.

You should now see the file something like this ...

Harry^p
Harry's Password^p
^p
Julie^p
Julie's Password^p
^p

etc

where ^p reperesents the paragraph break.

The idea is to create one line of data for each user.
So we have to get rid of all the unnecessary paragraph breaks but keep a
paragraph break a the end of each user's line. The fact that there is an
empty line after each user means that each user's data is actaully
terminated by a double-paragraph-break. One break from the end of the
password line and one break from the end of the "empty" line.
You can see this this in the file if you have switched the toggle on.

The single-paragraph-break has to be converted in a data separator character
(e.g. a semi colon) and the double-paragraph-break has to turned into a
single-paragraph-break .

Here's how it's done.

Replace all the double-paragrah-break (^p^p)  with some set of dummy
characters that doe not exist in the file.
Say , %$ for instance.

To specify a paragraph break in WinWord you must know that the paragraph
break is a special character that you will find in a Special dropdown menu
in the Search and Replace dialogue box.
Do it from the special menu as there is a pssibility of error if you enter
it from the keyboard.
Make a safe copy of the file before you start this.

So,
Search for ^p^p
Replace with %$
Hit the Replace All button.

Now the file should looked messed up but don't worry.

Now do a search and replace of all instances of a single paragraph break.
i.e. Search for ^p
Replace with a semi colon. ";"
Hit the Replace All button.
Now the file should look even more messed up.

Now go back and replace the dummy set of characters with a single paragraph
break.
Search for %$
Replace with ^p.

Now you should have
Harry;Harry's Password
Julie;Julie's Password.

Save the file as text and name it with a csv extension something like
"passwords.csv".
You can now import it into XL using the Import Text Wizard.

Or you could attempt a copy and paste from Word directly into Excel.
If all the pasted data stays in column A, no hassle,  you can then separate
it into two columns with the Data, Convert or (Data, Redistribute ?) from
the Data menu.

Then do your Data ,Sort.

HTH.
Seamus



Wed, 18 Jun 1902 08:00:00 GMT  
 Sorting every 3rd row
Tom,
Whew, you got three great approaches before I could get back to this.  Neat!

Dave Braden

Quote:

> Yes.

> But be more specific.  By "begins every 3rd row" do you mean what is in column
> A?  What is your sort criterion? And which version of Excel are you using?  You
> may be able to do this with adept use of the built-in spreadsheet functions,
> depending on the answers to the avove.  If not, are you comfortable with using
> VBA code (which, of course, is infeasible if you run Excel 4.0 or earlier)?

> Dave Braden


> > Is it possible to sort information based on what begins every 3rd row of a
> > worksheet?

> > Thanks,
> > Tom



Wed, 18 Jun 1902 08:00:00 GMT  
 Sorting every 3rd row
Tom,

A non-macro way.

Assuming your data starts in A2.  Insert a row to the right then enter the
same formula: =A2&Row() in B2,B3 and B4.  Then select Column B from B2
through the end of your data.  From the menu select
Edit>Fill>Series>AutoFill(Under Type).  While the column is still
highlighted, convert the formulas to values by doing a Copy and then a
Edit>PasteSpecial>Values.

Using Row() in the formulas handles duplicate names.

If the others have convinced you to convert to a database style format, you
can use a variation of the above method.  Use the formulas ="X"&Row()
="Y"&Row()  and ="Z"&Row() in B2, B3 and B4 respectively and follow the
instructions above.  Then do a sort on column B and Cut & Paste column A of
the Y's to B2.

As always, try these methods on a copy of the data first.

HTH
Denny Campbell
Grand Rapids, Michigan

Quote:

>I'm using version 7.0A and I have a very extensive password list (about
1100
>passwords with usernames) that extend down the "A" column.  Every 3rd row
is
>the username.  So the format is - username - password - blank row -
>username - password - blank row, and so forth.  I'm trying to alphabetize
it
>by username but can't figure out how.  The usernames are on every 3rd row
of
>column A.

>Thanks!
>Tom



Wed, 18 Jun 1902 08:00:00 GMT  
 Sorting every 3rd row
Tom,

Here is one more way of doing it. This approach has the advantage, that it isn't
necessary to create a help range, the sorting is carried out in the original column,
taking advantage of hidden rows.
For Excel 97/2000 it's possible to speed up the execution time. The code is
at the buttom.
Provide your own values for WholeRange, FirstSortCell and StepValue, and you're ready
to go.

Sub SortEveryNth()

'Excel 7.0
Dim WholeRange As Object
Dim WholeRangePlus As Object
Dim VisibleRange As Object
Dim FirstSortCell As Object
Dim Area As Object
Dim Cl As Object
Dim WholeRangeColumn As Long
Dim StepValue As Long
Dim LastRow As Long
Dim Counter As Long

    Set WholeRange = Range("B2:b12")
    Set FirstSortCell = Range("b2")
    StepValue = 3

    LastRow = WholeRange.Rows.Count + WholeRange.Row - 1
    WholeRangeColumn = WholeRange.Column
    Set VisibleRange = FirstSortCell
    Set WholeRangePlus = Range(Cells(1, WholeRangeColumn), Cells(LastRow, WholeRangeColumn)).EntireRow
    For Counter = FirstSortCell.Row + StepValue To LastRow Step StepValue
        Set VisibleRange = Union(VisibleRange, Cells(Counter, WholeRangeColumn))
    Next Counter
    WholeRangePlus.Hidden = True
    For Each Area In VisibleRange.Areas
        For Each Cl In Area.Cells
            Cl.EntireRow.Hidden = False
        Next Cl
    Next Area
    WholeRange.Sort key1:=Cells(1, WholeRangeColumn)
    WholeRangePlus.Hidden = False
    WholeRange(1).Activate

Set WholeRange = Nothing
Set WholeRangePlus = Nothing
Set VisibleRange = Nothing
Set FirstSortCell = Nothing
Set Area = Nothing
Set Cl = Nothing
End Sub

Sub SortEveryNth97()

'Excel 97/2000
Dim WholeRange As Object
Dim WholeRangePlus As Object
Dim VisibleRange As Object
Dim FirstSortCell As Object
Dim WholeRangeColumn As Long
Dim StepValue As Long
Dim LastRow As Long
Dim Counter As Long

    Set WholeRange = Range("B2:b12")
    Set FirstSortCell = Range("b2")
    StepValue = 3

    LastRow = WholeRange.Rows.Count + WholeRange.Row - 1
    WholeRangeColumn = WholeRange.Column
    Set VisibleRange = FirstSortCell
    Set WholeRangePlus = Range(Cells(1, WholeRangeColumn), Cells(LastRow, WholeRangeColumn)).EntireRow
    For Counter = FirstSortCell.Row + StepValue To LastRow Step StepValue
        Set VisibleRange = Union(VisibleRange, Cells(Counter, WholeRangeColumn))
    Next Counter
    WholeRangePlus.Hidden = True
    VisibleRange.Rows.Hidden = False
    WholeRange.Sort key1:=Cells(1, WholeRangeColumn)
    WholeRangePlus.Hidden = False
    WholeRange(1).Activate

Set WholeRange = Nothing
Set WholeRangePlus = Nothing
Set VisibleRange = Nothing
Set FirstSortCell = Nothing
End Sub

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


Quote:
>Is it possible to sort information based on what begins every 3rd row of a
>worksheet?

>Thanks,
>Tom



Wed, 18 Jun 1902 08:00:00 GMT  
 
 [ 12 post ] 

 Relevant Pages 

1. Delete every 2nd and 3rd row- VBA

2. Deleting every 2nd and 3rd row

3. Selecting every 3rd row for copying

4. Sort every 8th Row?

5. Getting #deleted message in every field of every row when linking to a SQL Server DB

6. Copy every 3rd cell in a column?

7. Change Format Of Every 3rd Cell?

8. Determining every 3rd tuesday of the month

9. Selecting every 3rd record

10. Every 3rd cell


 
Powered by phpBB® Forum Software © phpBB Group