Macro to search, find, extract specific data to another sheet

[rhondajoy59]

Hello

I have a remaining balance report which consists of 36,000 contracts. I receive this report daily. We own approximately 100 of these contracts. Data changes frequently. How can I write a macro that will fine the specific contract, extract the row of information and place it in a new sheet? I tried finding duplicates but there are too many with the same name. Currently I am using the dropdown menu and typing in the first 6 digits of the contract number then I have to choose which contract to copy. Can this be done?

 

[offthelip]

The way I would do that is to keep a workbook which has the list of the 100 contracts that you own , In this workbook I would have macro that would search your daily balance report for all the lines that match any of the 100 contracts and copy those into a new workbook. The only slight issue is how the macro would know which workbook to search through, this could be by workbook name if the workbook has a consistent naming convention. Ot it could be by going through all the workbooks whichare open in turn and asking the user to select the right one.

 

[rhondajoy59]

Thank you for reply. How would I write the macro in the 100 contract report to search for the specific contracts in the larger remaining balance report? Currently I am using column B to search for specific contract number then I choose which (sometimes there are several with version numbers) contract I want then copy it over for the most current data. There has to be an easier way. Yes? It takes me a very long time to search for each contract. Please see the snip it. As you can see there are multiple line items for each contract as they keep the expired along with the active. I did a duplicate search and my whole page turned red. I can eliminate nearly half but sorting out our business unit but this still leave over 16000 contracts. Is this even possible?

 

[offthelip]

To be able to write any code to help you have to give more details, to help try answering these questions:
How do you know which numbers in Column B you want to copy, do you have list of them somewhere , or do they alwyas start with a certain pattern?
When selecting which rows to copy do you look at any other columns to decide ie. current? or expired? if so which columns and what are the details of how you tell the difference, and which ones do you want to keep and which ones do you reject.
How many columns wide is the data that you want to copy
do you want all the columns copied
What is the name of the workbook containing the complete balance report,
What is the name of the worksheet containing the complete balance report
Do these names change from month to month, if so how do they change
Do you want the results to be stored in a new workbok every month or in the workbook which the macro is run from on a new worksheet

All of this is systems analysis which has very little to do with writing VBA , but is an essential part of building a good reliable system

 

[rhondajoy59]

Hi again... thank you for reply. My answers are in red with a comment at the end.

• How do you know which numbers in Column B you want to copy, do you have list of them somewhere, Yes, in another workbook I created for the staff based on the 100 contracts in the office, or do they always start with a certain pattern, basically same pattern, just different contract numbers, ie 192365000 or 217895001 or 204781000A. This may break the deal right off with the variance of contract numbers.
• When selecting which rows to copy do you look at any other columns to decide ie. current? No other columns, just PO_ID column but I do choose which version, if applicable in the same column. or expired I do not choose the expired. if so which columns and what are the details of how you tell the difference and which ones do you want to keep and which ones do you reject. Some companies have many lines with same base contract number but last digit is the version number. Ex: 205623009, 9 being the version I need from contract 205623.
• How many columns wide is the data that you want to copy
  do you want all the columns copied. 13 columns wide will include all the data needed.
• What is the name of the workbook containing the complete balance report, Remaining Balance Report 03-11-2021 date 03-11-2021 changes daily.
• What is the name of the worksheet containing the complete balance report, fdx_ENCORE_REM_BAL_CERTS_202103. 202103 is year/month which changes monthly.
• Do these names change from month to month, if so how do they change, the date changes daily for the workbook, monthly for the worksheet
• Do you want the results to be stored in a new workbook every month or in the workbook which the macro is run from on a new worksheet, what would be easier? Preferably in the book containing the macro. I create a report weekly from the Friday workbook that is emailed. I can copy and past the sheet to email.

I thought maybe a macro could be written/built which produced a query box asking me to 1. list specific contract numbers, 2. which excel file the data is contained, 3. list which column to look. Once I pasted the contract numbers, the file name, and the column, I could hit the run button and the macro would go into the file and extract only those rows of contracts I listed in the query box. The data can be left in the same sheet, but the macro would narrow the data from 16000 to 100. I could manipulate the data from there. As it is, I narrow down the initial report by filtering our business unit which takes the report from 36000 rows down to 16000 rows of data. I then filter the PO_ID column & search for each contract number, highlight the row needed then search for the next one, highlight then search again. I do this until I find all the lines then I filter the rows of highlighted data, copy, and paste into a new book/file. Very time consuming. Again, thank you for your time and effort here.

 

[offthelip]

when you say you have a list of the contract in another workbook, does that include that include the version number??
If the software doesn't need to detect which version number it becomces very simple, so it does depend a bit on whether the list you are providing to the code includes the version number or not.
Assuming the list from the other workbook IS the list of number that you are looking for, I think it would be much better to just copy and paste that information a "control" worksheet which can then be used for the list. Thus I wouild avoid a Userform and query box, and just use a "control" worksheet, this worksheet would hold the list of numbers the name of the complete balance workbook .

 

[rhondajoy59]

Hello again

Yes to answer your question simply. The workbook that contains the100 contracts specific to my team is one that I created by filtering, searching, highlighting, searching, highlighting 100 times to find each contract specific to us then I copied and pasted to a new book and titled it Master. It has all the contract numbers with version numbers IF the contract has a version. Most do not have version numbers.
When I was in a different department, we used various macros that were wrote specifically for us to use on an application that is no longer being used. It is from my experience with those macros that I thought maybe a simple macro could be wrote to extract specific data from the workbook I receive daily with 35000 rows of contract information. I can see this is no simple task based on your questioning as there are too many variables in this workbook to write a simple macro. Thank you for being so gracious about helping me and trying to understand what it is I requested. I see it simple in my mind's eye but it surely is not.

 

[offthelip]

Actually it is a simple problem and I have written the code. I have made some assumptions;
I assumed the list of contracts you are looking for is in column A of "Sheet1" in the workbook that contains this macro. The macor will search through all open workbooks to find a workbooks with a name that starts with "Remaining Balance Report". It will then select the worksheet which starts with "fdx_ENCORE_REM_BAL_CERTS_" followed by the current year number and current month number. If it can't find the workbook or the worksheet the code exits with a message and does nothing
If the worksheet is found a message pops up so the user can confirm this is the correct sheet. if you answer NO then the code exits and does nothing
Finally having got to the correct place it is less than 25 lines of code to do the copying. The output it writtein into a new worksheet in the workbook which the code resides in.
So try this:

Sub test()
currentwb = ActiveWorkbook.Name ' save the curent workbook name so that we can get back here

Dim wb As Workbook
Dim ws As Worksheet
' loop through all the workbooks to find one ith a name starting with "Remaining Balance Report"
wbnotfound = True ' set a flag to check if the workbook is found
For Each wb In Application.Workbooks
  If Left(wb.Name, 24) = "Remaining Balance Report" Then
   wb.Activate    ' select  the workbook if found
   wbnotfound = False ' set the flag to show we foudn the workbook
   Exit For
  End If
Next wb
If wbnotfound Then GoTo notfound  ' if workbook not found exit sub with message
yrno = Year(Now()) ' save this year
mn = Month(Now())  ' save this month
If Len(mn) = 1 Then ' check if month number is single digit
zz = "0" ' if so add a zero on the front
Else
zz = ""  ' else leave as blank
End If
wsname = "fdx_ENCORE_REM_BAL_CERTS_" & yrno & zz & mn  ' create string for worksheet name for current date using this month an year
On Error GoTo notfound  ' set error capture to local exit
Worksheets(wsname).Select ' select the workseet if this creates an error it will goto notfound
On Error GoTo 0 ' reset error handling to outside this procedure again
qq = MsgBox("is this the correct worksheet", vbYesNo) ' ask user whether we have gtoto the correct sheet
If qq <> vbYes Then ' if user asnwer no
 MsgBox ("exit") ' raise a message
 Exit Sub ' aad exit the sub having done nothing
End If
' we are in the correct sheet so not load all the data into a variant array
' find lastrow in column B
lastrow = Cells(Rows.Count, "B").End(xlUp).Row
inarr = Range(Cells(1, 1), Cells(lastrow, 13))  ' pick up all the data

' swap back to original workbook
Workbooks(currentwb).Activate
With Worksheets("Sheet1") 'select the workhseet with the list of contract in column A
 lasta = .Cells(Rows.Count, "A").End(xlUp).Row  ' find last row
POID = .Range(.Cells(1, 1), .Cells(lasta, 1)) ' load all the data in column A into a variant array
End With
Sheets.Add   '  add a workhseet to put the results
outarr = Range(Cells(1, 1), Cells(lasta * 10, 13))   ' define an output variant array
 
indi = 2           ' set out put start row

 For i = 2 To lasta   ' loop through all the contracts
  For j = 2 To lastrow  ' loop through all the data in the remaining balance report
   If POID(i, 1) = inarr(j, 2) Then '  check if this line has dat afor the contract we are looking at
     For k = 1 To 13            ' if so copy 13 columns to output
      outarr(indi, k) = inarr(j, k)
     Next k
     indi = indi + 1            ' increment the output row
    End If
  Next j
Next i

Range(Cells(1, 1), Cells(lasta * 10, 13)) = outarr  ' output the array to the worksheet
    

Exit Sub
' error handler for workbook or worksheet not found
notfound:
MsgBox "Worksheet or workbook not found"


End Sub

 

[rhondajoy59]

Not ignoring you... received your message and will return with feedback. Thank you very much!!!! So excited to try this tomorrow.