Solved

Excel 2013 Selected Data Criteria for Rows and Columns, How to Automate?

Posted on 2014-10-31
20
152 Views
Last Modified: 2014-12-10
Experts,

I have an Excel 2013 spreadsheet that contains several hundred rows, and columns from A thru H.  I manually select the rows and columns needed, but I would like to automate this process and put the data into another sheet.  Best method?

In The Excel Spreadsheet:
In Column A, I will select all rows that contain number 10055 AND in Column B Text Starting with Letters "CA".  All rows and columns are copied to a new sheet.

A second copy is done for Column A containing number 10055 AND Column B Text Starting with the Letters "PB".  Again, All rows and columns are copied into an empty row in the new sheet.

The Excel spreadsheet is converted from a .txt file and opened in Excel.
The data is NOT continuous.  Within the rows can be page heading information, where the report continued from page 1 to page 2, etc.

It's time consuming selecting the rows meeting the specific criteria.  

Any ideas how this can be automated in Excel?

Thanks Experts.
0
Comment
Question by:forwiw2day
  • 9
  • 6
  • 3
  • +2
20 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40415985
Yes - it would be best if you could give us some sample data please.
0
 
LVL 5

Expert Comment

by:ReneD100
ID: 40416053
You can easily do this with VBA on a line by line basis, but if you can sort the data on Column A and get it to be continuous it will speed up the process to copy drastically. So sample data will be helpful indeed.
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40416073
This code will copy the rows with your criteria to a new sheet called "Result" (it will replace an existing "Result" sheet if it exists).  The data will be added with the "CA*" items first, then the "PB*" items next.
Option Explicit
Sub Copy_Select_Data()
    Dim rng As Range
    Dim cl As Object
    Dim intNextRow As Integer
    Dim strSourceSheet As String
    
    On Error Resume Next
    strSourceSheet = ActiveSheet.Name
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Sheets("Result").Delete
    Sheets.Add after:=Sheets(strSourceSheet)
    ActiveSheet.Name = "Result"
    'header information may be added here, for example:
    'Range("A1").Value = "ID"
    'Range("B1").Value = "Category"
    intNextRow = 2
    Sheets("Main").Select
    
    Set rng = Range("A2:A" & Cells.SpecialCells(xlLastCell).Row)
    For Each cl In rng
        If cl.Value = 10055 And (Left(cl.Offset(0, 1).Value, 2) = "CA") Then
            cl.EntireRow.Copy
            Sheets("Result").Cells(intNextRow, 1).PasteSpecial
            Application.CutCopyMode = False
            intNextRow = intNextRow + 1
        End If
    Next cl
    For Each cl In rng
        If cl.Value = 10055 And (Left(cl.Offset(0, 1).Value, 2) = "PB") Then
            cl.EntireRow.Copy
            Sheets("Result").Cells(intNextRow, 1).PasteSpecial
            Application.CutCopyMode = False
            intNextRow = intNextRow + 1
        End If
    Next cl

    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    Sheets("Result").Select
    Range("A2").Select
End Sub

Open in new window


All used rows in the source sheet are considered; blank rows included.

See the attached workbook for an example.

Regards,
-Glenn
EE-Q28548483.xlsm
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 40416139
How about using the AutoFilter function.

Select Data and activate AutoFilter.

In column A use drop down to specify criteria, in column B use drop down to specify criteria.

Select visible data as a block, copy and paste to new sheet. Only visible rows will be copied and pasted.

if this copies surplus columns just delete columns once pasted onto the destination sheet.

Thanks
Rob H
0
 

Author Comment

by:forwiw2day
ID: 40424920
Experts,

Sorry for the delay, work has been Really Busy right now.  The data will be against company policy to publish, though I could change it before sending.  

Glenn Ray, Your response seems to fit what I'm looking for, but I don't know anything about the Code you provided, Where in Excel to enter this, how to run the code, nothing?  

A sample code would be:
Heading, Heading, Heading, Heading, Heading, Heading
10020  CA12345 data, data, data, data
10020  CA12355 data, data, data, data
10020  PB22222 data, data, data, data
10021  PB22223 data, data, data, data
Heading, Heading, Heading, Heading, Heading, Heading
10055  CA645785 data, data, data, data
10055  CA756458 data, data, data, data
10055  PB235480 data, data, data, data
Heading, Heading, Heading, Heading, Heading, Heading
10055  PB565248 data, data, data, data
10055  PB235824 data, data, data, data
21000  CA568753 data, data, data, data
21000  CA656854 data, data, data, data
Heading, Heading, Heading, Heading, Heading, Heading
so on and so forth...

Other options, can a macro run from another sheet in the workbook?
Other?

Thanks Experts!
0
 

Author Comment

by:forwiw2day
ID: 40424957
Experts,

Wanted to include, that once I select the rows that contain number 10055 AND in Column B Text Starting with Letters "CA".  All rows and columns are copied to a new sheet.  There are two columns with in this group of data that I create a SUM total for.  

Then I start the process over again for the second group 10055 AND Column B Text Starting with Letters "PB".  Again, I will create a SUM total for the same two columns, but this is a different group of data.

FYI
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 40425150
Did you try the filter and copy as I suggested?
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40426627
forwiw2day,

Yes, you can run the macro I supplied on another workbook.

1) Open my example file.
2) Open your data file.  Select the sheet with the data you want to process.
3) Press [Alt]+[F8] to call up the Macros dialog box.
You'll see a window like this:
macro dialog4) Click on the macro "Copy_Select_Data" and then click the "Run" button.

Regards,
-Glenn
0
 

Author Comment

by:forwiw2day
ID: 40437251
Experts,

Rob, yes.  The filter has manual methods... Looking for something that will get the data I need quicker, thanks.

Glenn, Thanks for your suggestion, please give me a few days to try this, as I have been Very Busy at work.  This is why I'm trying to automate this manual process.

Thanks to All!
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 40437267
Advanced Filter (rather than Auto Filter) can be used to copy results to another location and can be automated into a VBA routine if so required.

Thanks
Rob H
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40478751
Hi,

Did you have any additional questions about the solutions provided?  If so, let us know and we'll help resolve it.  Otherwise, if you found solution(s) that helped you, please properly close this question by clicking the "Accept as solution" link above the appropriate post(s) above.

Thanks,
-Glenn
0
 

Author Comment

by:forwiw2day
ID: 40479033
Glenn,

I super apologize for not replying back sooner, but I was filling in for another employee for a couple weeks.  I did try the macro steps you provided and the macro created a new sheet in the active workbook called Result and filled in two title columns in row 1 named:
Column A    Column B

That's a start, it did create a new sheet, but no data.
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40479038
You have to be viewing your data file when you run the macro.  Check my listed steps again.

If you indeed did this, then the data file may have a different construction than originally.
0
 

Author Comment

by:forwiw2day
ID: 40486763
Glenn,

I tried the steps you provided again.  I opened your spreadsheet, then I opened mine, pressed Alt & F8, selected the macro with copy selected data, from your excel file name EE..., then clicked on the Run button.  Same result as before.

So close.  Is there something you need from me to help finish this question?

Thanks so much!
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40487673
Can you provide either a screenshot or copy of your data sheet?  I tested this on an external workbook (i.e., one without the macro) and ran my macro on it and it worked fine.  I suspect there is an issue with the actual data - either structure or content.  In any case, I'll try and help you resolve this.

Regards,
-Glenn
0
 

Author Comment

by:forwiw2day
ID: 40489622
Glenn,

Here is a Text Excel Workbook, with Test sheet and Result sheet.

Thanks so Very Much!
test-auto-copy.xls
0
 

Author Comment

by:forwiw2day
ID: 40489711
Glenn,

PS.  The Excel spreadsheet I posted, only contains that data that is needed.  There would be hundreds of additional rows in the spreadsheet with different data in columns A & B.   FYI.

Thanks!
0
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 500 total points
ID: 40491752
My profuse apologies!  There was a mistake in line 19 of the original code:
Sheets("Main").Select
should be
Sheets(strSourceSheet).Select

I corrected that and have attached an updated copy of the macro workbook.  I tested it on your data and it appears to work as-requested.

Sorry for the inconvenience,
-Glenn
EE-Q28548483.xlsm
0
 

Author Comment

by:forwiw2day
ID: 40492500
Glenn the Expert,

Yes, it works perfectly!!! Exactly what I was looking for, BOY this is going to save a lot of manual work!!!  You did it, and I truly appreciate you sticking with me during my busy times at work!!!  Points are all yours.

Thank You, Thank You!
0
 

Author Closing Comment

by:forwiw2day
ID: 40492506
Glenn is Truly and Expert in all ways!
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

760 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

21 Experts available now in Live!

Get 1:1 Help Now