Solved

Excel 2000: Periodic sampling of text data

Posted on 2002-04-24
11
539 Views
Last Modified: 2010-05-18
Hello,

I'm trying to help an associate develop what I believe will be a (fairly) simple Excel macro, but I have ~0 experience with Excel macro programming (surprise, bet you don't get that often). Anyway, assuming the data occupies about 15 columns, we want to extract say columns 1-3,10, and 13, from every 5th row. Tried doing this with Analysis Toolkit's Sampling method, but it only works with numeric data. Data size is ~2000 rows. Note nothing fancy is required here, just selecting the information and copying it so it can be pasted into another sheet.

Thanks in advance for any input.

-dog*
0
Comment
Question by:Kyle Schroeder
  • 5
  • 4
  • 2
11 Comments
 
LVL 44

Expert Comment

by:bruintje
ID: 6967307
Hi dogztar,

the data has to be copied to a second sheet?

:O)Bruintje
0
 
LVL 44

Accepted Solution

by:
bruintje earned 250 total points
ID: 6967336
Hi dogztar,

You can try this as a start

-this code assumes some things
  |--workbook with two or more sheets
  |--first sheet contains data
  |--second sheet contains no data but data will be copied here
  |--the data will be pasted in columns 1-5 instead of 1-3,10,13


-first in your workbook
-open the VB Editor with ALT+F11
-then choose insert new module
-paste the code

Option Explicit

Public Sub CopySpecificRanges()
Dim i As Integer
Dim j As Integer
  i = 5
  j = 2
  While i <= 15
    With Worksheets(1)
      .Activate
      .Range("A" & i & ",B" & i & ",C" & i & ",J" & i & ",M" & i).Select
      Selection.Copy
    End With
    With Worksheets(2)
      .Activate
      .Range("A" & j).Select
      .Paste
    End With
    j = j + 1
    i = i + 5
  Wend

End Sub

-then save
-close the editor
-in your workbook choose ALT+F8
-run the macro "CopySpecificRanges"

if you need anything else or some additions just leave a comment

HTH:O)Bruintje
0
 
LVL 16

Author Comment

by:Kyle Schroeder
ID: 6967702
Bruintje:

That looks likely...I figured it would need some sort of FOR loop or a WHILE like that (I do know programming, just not VBA).  I poked around in the VB editor but couldn't figure out how to concatenate a counter with the cell reference.  I will try that tomorrow at work and see if it does what he needs it to do.

Thanks!

-dog*
0
 
LVL 6

Expert Comment

by:bkpchs237
ID: 6969312
dogztar,

Here's an algebraic method to perform this activity. My method assumes you have a header row and your data starts in row 2 and continues down until its eventual end ~2000 rows below.

In a new column (I used CS) place the header "check" in cell CS1.  In cell CS2 place the following formula:
=IF((ROW()-1)/5=INT((ROW()-1)/5),1,"")
This will place a 1 in every fifth row and a blank elsewhere.

Now hide all the columns you don't want to copy.  Then go to Data, Filter, AutoFilter.  Select the arrow on column CS and choose 1.  This will filter your list displaying only every 5th row.  Highlight/select all the cells in view.  Click on Edit, GoTo, Special, Visible Cells Only, OK.  Then Edit, Copy, go to your desired location and click on Edit, Paste.

Hope this helps.
0
 
LVL 16

Author Comment

by:Kyle Schroeder
ID: 6969764
bkpchs237:

An interesting approach as well, though a bit more complicated then bruintje's.  I don't want to remove any data though, just extract a 20% sample (correct me if I'm wrong, but it looks like your formula will replace any data with 1's and blanks).  I don't quite understand your instructions either, right now I have CS1 with text "check" (no quotes) and the formula below it.

-dog*
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 6

Expert Comment

by:bkpchs237
ID: 6969939
dogztar,

Ooops.  Left out a step or two.  After you place the formula in cell CS2 then copy that formula down the entire column as needed.  I stated column CS since my data went all the way over to column CR.  But if your data ends at say column Y then place this information in column Z (i.e. the next column to the right of your data) and copy the formula down the column.

After that the directions are straight-forward.  It doesn't delete anything, just filters the data for you to copy those visible cells from your sampling at the end to another location of your choosing.  

When you finish and have your items in place on the new worksheet, go back to your original and turn off the AutoFilter feature, unhide your columns and select a cell to deactivate the selection mode.

May be a little more cumbersome than a macro, but I prefer to use formulas, one: because I am just learning to use vba, and two: I believe if it can be done with formulas why use code.

Now, that being said, I am posting one of my initial entries into vba here for your use.  It's not so much for a try at points, but just because it works!

Again, I made an assumption that your data has a header row (row 1) and data starts in row 2 and proceeds downward.  Also it creates a new worksheet in the file called "FilterSheet" so don't name any of your other sheets with that name.  I'm not savvy enough to define the variables much more than that at this time.  


'***************************
Sub Macro2()
 '

'  To copy only data from columns A,B,C,K & N from one worksheet
'   to another worksheet (1st, 2nd, 3rd, 10th & 13th columns)

    Dim wks As Worksheet
    Dim wks2 As Worksheet
   
    Set wks = ActiveWorkbook.ActiveSheet
   
    ActiveWorkbook.Sheets.Add.Name = "FilterSheet"
    Set wks2 = ActiveWorkbook.Worksheets("FilterSheet")
   
    wks.Activate
   
    wks.Range("A1").Select
    Selection.EntireColumn.Insert
    ActiveCell.FormulaR1C1 = "check"
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "=IF((ROW()-1)/5=INT((ROW()-1)/5),1,"""")"
    Range("A2").Select
    Selection.AutoFill Destination:=Range("A2:A" & wks.Range("b1").End(xlDown).Row)
   
    Columns("E:J").EntireColumn.Hidden = True
    Columns("L:M").EntireColumn.Hidden = True
    Columns("O:IV").EntireColumn.Hidden = True
   
    Range("a1:n" & wks.Range("n1").End(xlDown).Row).Select
       
    Selection.AutoFilter
    Selection.AutoFilter Field:=1, Criteria1:="1"
    Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.Copy
   
    wks2.Activate
    ActiveSheet.Paste
       
    Range("A1").Select
    Application.CutCopyMode = False
    Selection.EntireColumn.Delete
   
    wks.Activate
    Range("A1").Select
    Selection.AutoFilter
    Cells.Select
    Selection.EntireColumn.Hidden = False
    Range("A1").Select
    Selection.EntireColumn.Delete
   
    wks2.Activate
    Range("A1").Select

End Sub
'*******************

Hope this helps.
0
 
LVL 16

Author Comment

by:Kyle Schroeder
ID: 6971370
Ahh, OK that makes more sense to copy the formula down...I didn't exactly understand what the formula was doing at the time, but looking at it again I see what's going on.  The problem is the sample data I have has ~2000 rows so it could get cumbersome to copy the formula that far (esp. since Excel has that nifty "scroll 5000 lines at a time when the mouse is clicked and dragged down" function.  Man that drives me nuts!)

I am waiting to hear back from the person to see if bruintje's macro worked for him.

One question...I did some more looking around and found out how to prompt for user input, but there is one other item.  Right now, my macro looks like this:

Option Explicit

Public Sub CopySpecificRanges()
Dim i As Integer 'Counts the Source worksheet rows
Dim j As Integer 'Counts the Target worksheet rows
Dim MAXROWS As Integer 'Total number of rows in the sheet
 MAXROWS = 1915
 j = 2
i = Application.InputBox("Type in number of every other Row to Copy", Default:=5, Type:=1)
If i = 0 Then GoTo UserCancelled
 
 While i <= MAXROWS
   With Worksheets(1)
     .Activate
     .Range("A" & i & ",B" & i & ",C" & i & ",J" & i & ",M" & i).Select
     Selection.Copy
   End With
   With Worksheets(3)
     .Activate
     .Range("A" & j).Select
     .Paste
   End With
   j = j + 1
   i = i + 5
 Wend
UserCancelled:
End Sub

You'll see my MAXROWS variable I added.  Is there a way to calculate the number of used rows (i.e. the last row with data in it)?

-dog*
0
 
LVL 44

Expert Comment

by:bruintje
ID: 6971465
you can add something like

A1 is just the first cell

Range("A1").Select
Selection.End(xlDown).Select

this last cell contains the address

activecell.address
or
activecell.row

:O)Bruintje
0
 
LVL 16

Author Comment

by:Kyle Schroeder
ID: 6972495
Thanks bruintje, your solution (combined with some other ideas I got at mrexcel.com) has my client happy.

Thanks again!
-dog*
0
 
LVL 44

Expert Comment

by:bruintje
ID: 6972823
glad i could help, thanks for the grade
0
 
LVL 16

Author Comment

by:Kyle Schroeder
ID: 6972894
Anytime you need some PC hardware help, come on by Hardware (General).  I'm the #6 T.E. there.  I was going to just leave a C grade and no comment...heh heh (don't know if you see that in this TA, but it happens all the time in H(G).)

-dog*
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Have you ever had the experience that you had to follow 10 steps over and over again every time when you need to nicely forward an important email to your manager? Fear no more! With the help of the Quick Steps feature in Outlook 2010, your old chor…
How many times recently have you prepared a presentation or emailed a document to a client and you have found that they have older versions of MS Office and they can not open the file you have prepared.  Although most visitors to this site are exper…
This video walks the viewer through the process of creating envelopes and labels, with multiple names and addresses. Navigate to the “Start Mail Merge” button in the Mailings tab: Follow the step-by-step process until asked to find the address doc…
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now