Microsoft Office Forums Extract data based on pattern with respect to specific column

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 10-28-2015, 04:35 AM
PRA007's Avatar
PRA007 PRA007 is offline Extract data based on pattern with respect to specific column Windows 7 32bit Extract data based on pattern with respect to specific column Office 2010 32bit
Competent Performer
Extract data based on pattern with respect to specific column
 
Join Date: Dec 2014
Location: Ahmedabad, Gujrat, India
Posts: 145
PRA007 is on a distinguished road
Default Extract data based on pattern with respect to specific column

I have source excel(main.xlsx) file containing three column



Code:
4913864	20020403	EXP.
4913864	20011023	REM.
4913864	19970512	M184
4913864	19930816	ASPN
4913864	19930715	M183
4984887	19950115	EXP.
4984887	19940823	REM.
4984887	19910506	ASPN
4998743	19950312	EXP.
4998743	19941018	REM.
5076714	20031231	EXP.
5076714	20030716	REM.
5076714	19990621	M184
5076714	19990223	RMPN

I have another excel (database2.xlsx) file containing two column for example

Code:
ASPN	Payor Number Assigned.
EXP.	Patent Expired for Failure to Pay Maintenance Fees.
EXPX	Patent Reinstated After Maintenance Fee Payment Confirmed.
F160	Maintenance Fee Has Already Been Paid. Refund is scheduled.
F161	Maintenance Fee Tendered too Early. Refund is scheduled.
F162	Maint Fee Payment Does Not Properly Identify the Patent.
F163	M.F.Payment/Certificate of Mailing Untimely, 37 CFR 1.8.
F164	M.F.Payment/Express Mail Procedures Untimely, 37 CFR 1.10.
F165	M.F.Payment Sent With Fees for Other Purposes - Will Refund.
F166	M.F. Payment not Accepted/Not Immed. Negotiable/Returned.
F167	Check/Deposit Account Authorization is Unsigned - Returned.
F168	Payment Received - Balance in Deposit Account Insufficient.
F169	Payment is in Excess of Amount Required. Refund Scheduled.

Now in source file I would like to replace column C with what found for the same from database file column B

Both files are relatively small and can be converted to any file formate if requred (XML, HTML etc).

Files can be found at:
https://sites.google.com/site/rtsk2015/excelforum

I can not upload files because of cyberoam at my office
Reply With Quote
  #2  
Old 10-28-2015, 11:45 AM
charlesdh charlesdh is offline Extract data based on pattern with respect to specific column Windows 7 32bit Extract data based on pattern with respect to specific column Office 2010 32bit
Expert
 
Join Date: Apr 2014
Location: Mississippi
Posts: 382
charlesdh is on a distinguished road
Default

Hi,

Do you open both file at the same time? Which file do you need the code in?
Are you wanting a separate workbook to hold the code?
Reply With Quote
  #3  
Old 10-28-2015, 09:37 PM
PRA007's Avatar
PRA007 PRA007 is offline Extract data based on pattern with respect to specific column Windows 7 32bit Extract data based on pattern with respect to specific column Office 2010 32bit
Competent Performer
Extract data based on pattern with respect to specific column
 
Join Date: Dec 2014
Location: Ahmedabad, Gujrat, India
Posts: 145
PRA007 is on a distinguished road
Default

Sorry for not making it clear

I want data to be replaced in main.xlsx to make it final.xlsx, can be found at:

Final.xlsx should look like this.

Code:
4984887	19950115	Patent Expired for Failure to Pay Maintenance Fees.
4984887	19940823	Maintenance Fee Reminder Mailed.
4984887	19910506	Payor Number Assigned.
4998743	19950312	Patent Expired for Failure to Pay Maintenance Fees.
trimmed final file can be found at:
https://sites.google.com/site/rtsk2015/excelforum
file name. Final.xlsx
Reply With Quote
  #4  
Old 10-28-2015, 10:44 PM
macropod's Avatar
macropod macropod is offline Extract data based on pattern with respect to specific column Windows 7 64bit Extract data based on pattern with respect to specific column Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,673
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

Try:
Code:
Sub Demo()
Application.ScreenUpdating = False
Dim rSrc As Long, rTgt As Long, StrTmp As String
Dim xlShtTgt As Worksheet, xlShtSrc As Worksheet, xlRng As Range
On Error Resume Next
Set xlShtTgt = Workbooks("Main.xlsx").Sheets(1)
Set xlShtSrc = Workbooks("Database2.xlsx").Sheets(1)
With xlShtTgt
  Set xlRng = .Range("A1:A" & .UsedRange.Cells.SpecialCells(xlCellTypeLastCell).Row)
End With
With xlShtTgt
  For rTgt = 1 To .UsedRange.Cells.SpecialCells(xlCellTypeLastCell).Row
    StrTmp = .Cells(rTgt, 3).Value
    With xlShtSrc
      rSrc = .Range("A1:A" & .UsedRange.Cells.SpecialCells(11).Row).Find( _
      What:=StrTmp, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
      MatchCase:=True, SearchFormat:=False).Row
    End With
    .Cells(rTgt, 3).Value = xlShtSrc.Cells(rSrc, 2).Value
  Next
  With .Columns(3)
    .WrapText = False
    .AutoFit
  End With
End With
Application.ScreenUpdating = True
End Sub
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #5  
Old 10-29-2015, 12:24 AM
PRA007's Avatar
PRA007 PRA007 is offline Extract data based on pattern with respect to specific column Windows 7 32bit Extract data based on pattern with respect to specific column Office 2010 32bit
Competent Performer
Extract data based on pattern with respect to specific column
 
Join Date: Dec 2014
Location: Ahmedabad, Gujrat, India
Posts: 145
PRA007 is on a distinguished road
Default

Sorry to bother but this is my first encounter with excel macro.

I tried running the macro from main.xlsm but its not working, nor showing any error.
I think problem is with Path to database file. How do I Run if both of my files are on desktop i.e. C:\Users\rahulkumar.patel\Desktop.
From wchich document I should run macro.
I tried changing like this.
Code:
xlShtTgt = Workbooks("C:\Users\rahulkumar.patel\Desktop\Main.xlsm").Sheets(1)
Set xlShtSrc = Workbooks("C:\Users\rahulkumar.patel\Desktop\Database2.xlsx").Sheets(1)
Can I direcly run this excel macro from CMD by saving it as .vbs?
__________________
Thanks,
Rahul Patel
Reply With Quote
  #6  
Old 10-29-2015, 12:32 AM
macropod's Avatar
macropod macropod is offline Extract data based on pattern with respect to specific column Windows 7 64bit Extract data based on pattern with respect to specific column Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,673
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

The macro is designed to be run with both files open. As is obvious from the code I posted, the doesn't make any attempt to open either file. After all, you haven't provided any indication of whether either file is stored in relation to the other. The macro can be run from any open Excel file.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #7  
Old 10-29-2015, 01:02 AM
PRA007's Avatar
PRA007 PRA007 is offline Extract data based on pattern with respect to specific column Windows 7 32bit Extract data based on pattern with respect to specific column Office 2010 32bit
Competent Performer
Extract data based on pattern with respect to specific column
 
Join Date: Dec 2014
Location: Ahmedabad, Gujrat, India
Posts: 145
PRA007 is on a distinguished road
Default First Excel Macro!!!!!!!!!!

Can we assign path If I dont want to open any of the file and want to run macro from third macro enabled doccument?
Yes:

I got the solution using simple excel tutorial
Code:
Sub Demo()
Application.ScreenUpdating = False
Dim rSrc As Long, rTgt As Long, StrTmp As String
Dim xlShtTgt As Worksheet, xlShtSrc As Worksheet, xlRng As Range
On Error Resume Next
Set wkb = Workbooks.Open("C:\Users\rahulkumar.patel\Desktop\Sheet1.xlsx")
Set wkb1 = Workbooks.Open("C:\Users\rahulkumar.patel\Desktop\FE.xlsx")
Set xlShtTgt = Workbooks("Sheet1.xlsx").Sheets(1)
Set xlShtSrc = Workbooks("FE.xlsx").Sheets(1)
With xlShtTgt
  Set xlRng = .Range("A1:A" & .UsedRange.Cells.SpecialCells(xlCellTypeLastCell).Row)
End With
With xlShtTgt
  For rTgt = 1 To .UsedRange.Cells.SpecialCells(xlCellTypeLastCell).Row
    StrTmp = .Cells(rTgt, 3).Value
    With xlShtSrc
      rSrc = .Range("A1:A" & .UsedRange.Cells.SpecialCells(11).Row).Find( _
      What:=StrTmp, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
      MatchCase:=True, SearchFormat:=False).Row
    End With
    .Cells(rTgt, 3).Value = xlShtSrc.Cells(rSrc, 2).Value
  Next
  With .Columns(3)
    .WrapText = False
    .AutoFit
  End With
End With
Application.ScreenUpdating = False
wkb.Close SaveChanges:=True
wkb1.Close SaveChanges:=True
End Sub
__________________
Thanks,
Rahul Patel
Reply With Quote
  #8  
Old 10-29-2015, 01:23 AM
macropod's Avatar
macropod macropod is offline Extract data based on pattern with respect to specific column Windows 7 64bit Extract data based on pattern with respect to specific column Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,673
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

Since the files must be open for the macro to work, they would have to be opened anyway, whether by the user or by the macro. I've already provided you with code in the Word forum showing what's involved in testing whether the file is open and, if not, opening it.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #9  
Old 10-29-2015, 01:25 AM
PRA007's Avatar
PRA007 PRA007 is offline Extract data based on pattern with respect to specific column Windows 7 32bit Extract data based on pattern with respect to specific column Office 2010 32bit
Competent Performer
Extract data based on pattern with respect to specific column
 
Join Date: Dec 2014
Location: Ahmedabad, Gujrat, India
Posts: 145
PRA007 is on a distinguished road
Default

I will try and use guidance provided by you.
__________________
Thanks,
Rahul Patel
Reply With Quote
  #10  
Old 10-29-2015, 04:02 AM
PRA007's Avatar
PRA007 PRA007 is offline Extract data based on pattern with respect to specific column Windows 7 32bit Extract data based on pattern with respect to specific column Office 2010 32bit
Competent Performer
Extract data based on pattern with respect to specific column
 
Join Date: Dec 2014
Location: Ahmedabad, Gujrat, India
Posts: 145
PRA007 is on a distinguished road
Default

I found solution to run my script from CMD

I made following vbscript to run macro saved in macro enabled doc.

Code:
Option Explicit

On Error Resume Next

ExcelMacroExample

Sub ExcelMacroExample() 

  Dim xlApp 
  Dim xlBook 

  Set xlApp = CreateObject("Excel.Application") 
  Set xlBook = xlApp.Workbooks.Open("C:\Users\rahulkumar.patel\Desktop\Fees.xlsm", 0, True) 
  xlApp.Run "OF"
  xlApp.Quit 

  Set xlBook = Nothing 
  Set xlApp = Nothing 

End Sub
__________________
Thanks,
Rahul Patel
Reply With Quote
  #11  
Old 10-29-2015, 04:17 AM
macropod's Avatar
macropod macropod is offline Extract data based on pattern with respect to specific column Windows 7 64bit Extract data based on pattern with respect to specific column Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,673
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

I can't see what that code has to do with anything discussed in this thread. Furthermore, if you want to run Excel from Word, I've already shown you the proper way to do so, here: https://www.msofficeforums.com/word-...html#post90291
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #12  
Old 10-29-2015, 04:26 AM
PRA007's Avatar
PRA007 PRA007 is offline Extract data based on pattern with respect to specific column Windows 7 32bit Extract data based on pattern with respect to specific column Office 2010 32bit
Competent Performer
Extract data based on pattern with respect to specific column
 
Join Date: Dec 2014
Location: Ahmedabad, Gujrat, India
Posts: 145
PRA007 is on a distinguished road
Default

Sorry for posting extended solution.
Actually I wanted to automate few things using vbscript.

So in this case I saved above vbscript as Fees.vbs and ran using

Code:
cscript Fees.vbs
This code further runs macro solution given by you above.
I Thout this might be helpful to someone in case.
__________________
Thanks,
Rahul Patel
Reply With Quote
  #13  
Old 10-29-2015, 12:43 PM
macropod's Avatar
macropod macropod is offline Extract data based on pattern with respect to specific column Windows 7 64bit Extract data based on pattern with respect to specific column Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,673
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

Quote:
Originally Posted by PRA007 View Post
Actually I wanted to automate few things using vbscript.
...
This code further runs macro solution given by you above.
You hadn't previously mentioned wanting to automate in this way. Even so, that code won't run the macro I posted, since it tries to use a workbook named 'Fees.xlsm' and your attachment workbooks were named 'Main.xlsx' and 'Database2.xlsx'.

Furthermore, in post #7 you have:
Code:
Set wkb = Workbooks.Open("C:\Users\rahulkumar.patel\Desktop\Sheet1.xlsx")
Set wkb1 = Workbooks.Open("C:\Users\rahulkumar.patel\Desktop\FE.xlsx")
Set xlShtTgt = Workbooks("Sheet1.xlsx").Sheets(1)
Set xlShtSrc = Workbooks("FE.xlsx").Sheets(1)
Aside from the fact that could could be reduced to:
Code:
Set wkb = Workbooks.Open("C:\Users\rahulkumar.patel\Desktop\Sheet1.xlsx")
Set wkb1 = Workbooks.Open("C:\Users\rahulkumar.patel\Desktop\FE.xlsx")
Set xlShtTgt = wkb .Sheets(1)
Set xlShtSrc = wkb 1.Sheets(1)
this code doesn't do any of the checking to see if either workbook is already open. As I said in post #8, I've already given you code showing how to do that. If you don't do such checks, you risk having your macro crash.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #14  
Old 10-29-2015, 01:39 PM
charlesdh charlesdh is offline Extract data based on pattern with respect to specific column Windows 7 32bit Extract data based on pattern with respect to specific column Office 2010 32bit
Expert
 
Join Date: Apr 2014
Location: Mississippi
Posts: 382
charlesdh is on a distinguished road
Default

Hi,

I believe if the OP answered my questions there may have been less confusion.
Reply With Quote
  #15  
Old 12-04-2015, 04:32 AM
PRA007's Avatar
PRA007 PRA007 is offline Extract data based on pattern with respect to specific column Windows 7 64bit Extract data based on pattern with respect to specific column Office 2010 32bit
Competent Performer
Extract data based on pattern with respect to specific column
 
Join Date: Dec 2014
Location: Ahmedabad, Gujrat, India
Posts: 145
PRA007 is on a distinguished road
Default

As, this has solved my problems, can be marked as solved.
__________________
Thanks,
Rahul Patel
Reply With Quote
Reply

Tags
excel vba, excel vba xml import loop

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Extract data based on pattern with respect to specific column Extract Data From Text file based on Pattern PRA007 Word VBA 13 11-01-2015 11:20 PM
Powerpoint show specific data based on selection Christov PowerPoint 1 05-14-2015 09:35 PM
Extract data based on pattern with respect to specific column How to Extract Data from table based on pattern. PRA007 Word Tables 4 03-17-2015 11:05 PM
Extract data based on pattern with respect to specific column Excel VBA Macro - Deleting Specific Data based on criteria MD011 Excel Programming 3 12-10-2014 02:15 AM
Mouseover cell to indicate mouse pointer location based on Specific Row/Column values bolandk Excel 1 05-15-2014 08:22 AM


All times are GMT -7. The time now is 10:31 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2019, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2019 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft