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

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.
forwiw2dayAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Yes - it would be best if you could give us some sample data please.
0
ReneD100Commented:
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
Glenn RayExcel VBA DeveloperCommented:
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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Rob HensonFinance AnalystCommented:
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
forwiw2dayAuthor Commented:
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
forwiw2dayAuthor Commented:
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
Rob HensonFinance AnalystCommented:
Did you try the filter and copy as I suggested?
0
Glenn RayExcel VBA DeveloperCommented:
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
forwiw2dayAuthor Commented:
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
Rob HensonFinance AnalystCommented:
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
Glenn RayExcel VBA DeveloperCommented:
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
forwiw2dayAuthor Commented:
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
Glenn RayExcel VBA DeveloperCommented:
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
forwiw2dayAuthor Commented:
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
Glenn RayExcel VBA DeveloperCommented:
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
forwiw2dayAuthor Commented:
Glenn,

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

Thanks so Very Much!
test-auto-copy.xls
0
forwiw2dayAuthor Commented:
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
Glenn RayExcel VBA DeveloperCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
forwiw2dayAuthor Commented:
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
forwiw2dayAuthor Commented:
Glenn is Truly and Expert in all ways!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.