Extracting pattern-matched text pieces from 1 cell, 1 piece into 1 destination cell

N

neonblaze

New Member
Joined
Nov 30, 2012
Messages
3
Hello all.

I have these 3 example cells (someone's pasted the query into Excel, so there are whitespaces too):
A1-> SELECT TBLFIRM.Firm_Name, TBLCLAIM.Claim_ID, TBLCLAIM.Claim_Unit_Number, TBLCLAIM.Case_Name, TBLCLAIM.Claim_Date, TBLTRANSMITTAL.Status,
A2-> TBLCLAIM.Coverage_Defence, TBLCLAIM.Litigation_Type, TBLCLAIM.Claim_LOB, TBLCLAIM_OFFICE.Name, TBLUSER.First_Name,
A3-> TBLUSER.Last_Name, TBLFIRM_LOCATION.State_ID, TBLCLAIM.Venue_State

This is just a Select SQL statement from a query. My aim is to extract all table names and their respective column names alongside or below.
Sample output:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]TBLFIRM[/TD]
[TD]TBLCLAIM[/TD]
[TD]TBLTRANSMITTAL[/TD]
[TD]TBLCLAIM_OFFICE[/TD]
[TD]TBLUSER[/TD]
[TD]TBLFIRM_LOCATION[/TD]
[/TR]
[TR]
[TD]Firm_Name[/TD]
[TD]Claims_ID[/TD]
[TD]Status[/TD]
[TD]Name[/TD]
[TD]First_Name[/TD]
[TD]State_ID[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Claim_Unit_Number[/TD]
[TD][/TD]
[TD][/TD]
[TD]Last_Name[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Case_Name[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Claim_Date[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Coverage_Defence[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Litigation_Type[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Claim_LOB[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Venue_State[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Thanks a lot in advance. :)

N

Details:
Excel 2007 on Windows 7.
I am comfortable with Macros and an inept beginner at VBA.
 
Sort by date Sort by votes
Quick and dirty fix here:

Select Text to Columns Delimited on spaces and commas

Cut and paste newly create columns back into A (so all data is in column A)

Use Text to columns - this time on periods.

this will give you your data in two columns one with table names and one with column names. Then it just a case of formatting the data you are going to use. (I would suggest a Pivot table would work well).

Cheers, :)
 
Upvote 0
Thanks much Shawnhet. Your suggestion certainly helps in the short term. I however have tons of data to sift through and am still on the lookout for a more automated solution.
I have meanwhile realized that a much simpler form of output will work better for me. Example below:
[TABLE="width: 258"]
<tbody>[TR]
[TD]TBLFIRM[/TD]
[TD]Firm_Name[/TD]
[/TR]
[TR]
[TD]TBLCLAIM[/TD]
[TD]Claim_ID[/TD]
[/TR]
[TR]
[TD]TBLCLAIM[/TD]
[TD]Claim_Unit_Number[/TD]
[/TR]
[TR]
[TD]TBLCLAIM[/TD]
[TD]Case_Name[/TD]
[/TR]
[TR]
[TD]TBLCLAIM[/TD]
[TD]Claim_Date[/TD]
[/TR]
[TR]
[TD]TBLTRANSMITTAL[/TD]
[TD]Status[/TD]
[/TR]
[TR]
[TD]TBLCLAIM[/TD]
[TD]Coverage_Defence[/TD]
[/TR]
[TR]
[TD]TBLCLAIM[/TD]
[TD]Litigation_Type[/TD]
[/TR]
[TR]
[TD]TBLCLAIM[/TD]
[TD]Claim_LOB[/TD]
[/TR]
[TR]
[TD]TBLCLAIM_OFFICE[/TD]
[TD]Name[/TD]
[/TR]
[TR]
[TD]TBLUSER[/TD]
[TD]First_Name[/TD]
[/TR]
[TR]
[TD]TBLUSER[/TD]
[TD]Last_Name[/TD]
[/TR]
[TR]
[TD]TBLFIRM_LOCATION[/TD]
[TD]State_ID[/TD]
[/TR]
[TR]
[TD]TBLCLAIM[/TD]
[TD]Venue_State[/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
Upvote 0
So you can get this data directly from your reporting system, right? I'm not quite sure what you would like your final output to look like.


As I mentioned above, I would try and see how a Pivot table solution will work for you. It will give you a variety of options for formatting the output that might be able to get you what you need. Here is a reference that will get you started with pivot tables.

Excel Pivot Tables Tutorial : What is a Pivot Table and How to Make one | Chandoo.org - Learn Microsoft Excel Online

If you are looking for a different approach to automating your report creation, why don't you post what you are looking for in terms of output? Do you need it in the format you show above? If one of the pivot table formats won't work a macro is always an option.

Cheers, :)
 
Upvote 0
Shawnhet, thanks much. I wrote some macros to solve my problem. Pasted is an example that I would like to share with all readers. To start off, navigate to the tab called SQL and press Ctrl-Shift-Q.
This will create a sandbox sheet called SQL(2). The macros will keep guiding the user right in the middle of the sheet. Try it. I am happy I managed to do it, especially since I can now share it with my colleagues and save a lot of person-hours. :)

Paste this on Sheet1:
[TABLE="width: 1310"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col span="2"><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Firm[/TD]
[TD]Case Name[/TD]
[TD]Insd[/TD]
[TD]Firm File Number[/TD]
[TD]Claim Number[/TD]
[TD]CR Last Name[/TD]
[TD]CR First Name[/TD]
[TD]Inv Number[/TD]
[TD]Invoice Submitted Date[/TD]
[TD]Total Billed[/TD]
[TD]Share %[/TD]
[TD]Billed Fees[/TD]
[TD]Paid Fees[/TD]
[TD]Billed Exp[/TD]
[TD]Paid Exp[/TD]
[TD]CVSC Amt[/TD]
[TD]Payment to Firm[/TD]
[TD]Release Date[/TD]
[TD]APC[/TD]
[TD]Inv ID[/TD]
[/TR]
[TR]
[TD]FirmName[/TD]
[TD]CaseName[/TD]
[TD]ClientName[/TD]
[TD]1234[/TD]
[TD="align: right"]5678[/TD]
[TD]LastName[/TD]
[TD]FirstName[/TD]
[TD="align: right"]9012[/TD]
[TD="align: right"]11/20/2012[/TD]
[TD="align: right"]$100.00[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]$50.00[/TD]
[TD="align: right"]$30.00[/TD]
[TD="align: right"]$10.00[/TD]
[TD="align: right"]$1.00[/TD]
[TD="align: right"]$2.00[/TD]
[TD="align: right"]$33.00[/TD]
[TD="align: right"]11/28/2012[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]12345678[/TD]
[/TR]
</tbody>[/TABLE]

Paste this on Sheet2 and rename it as SQL:
[TABLE="width: 1068"]
<colgroup><col span="12"></colgroup><tbody>[TR]
[TD="colspan: 10"]SELECT TBLFIRM.Firm_Name, TBLCLAIM.Case_Name, TBLCLAIM.Insured_Name, TBLCLAIM.Firm_File_Number, TBLCLAIM.Claim_Unit_Number, [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 11"] TBLUSER.Last_Name, TBLUSER.First_Name, TBLINVOICE.Firm_Invoice_Code, TBLINVOICE.Submitted_Date, TBLINVOICE.Total_Amount, [/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 11"] TBLINVOICE.Share_of_Firm_Fees, TBLINVOICE.Matter_total_detail_fees, TBLPAYMENT_REQUEST.Fees, TBLINVOICE.Matter_total_detail_exp, [/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 12"] TBLPAYMENT_REQUEST.Expense, ROUND(TBLPAYMENTMADE.Amount, 2) AS 'ROUNDED Payment to Firm', TBLPAYMENT_REQUEST.Created_Date, [/TD]
[/TR]
[TR]
[TD="colspan: 7"] TBLPAYMENT_REQUEST.Adjustment_Phase_Count, TBLINVOICE.Invoice_ID[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 3"]FROM TBLCLAIM INNER JOIN[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 7"] TBLINVOICE ON TBLCLAIM.Claim_ID = TBLINVOICE.Claim_ID INNER JOIN[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 9"] TBLPAYMENT_REQUEST ON TBLINVOICE.Invoice_ID = TBLPAYMENT_REQUEST.Invoice_ID INNER JOIN[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 7"] TBLPAYMENTMADE ON TBLINVOICE.Invoice_ID = TBLPAYMENTMADE.Invoice_ID AND [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 9"] TBLPAYMENT_REQUEST.Payment_Request_ID = TBLPAYMENTMADE.Payment_Request_ID INNER JOIN[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 6"] TBLLOB ON TBLCLAIM.Claim_LOB = TBLLOB.LOB_ID INNER JOIN[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 6"] TBLFIRM ON TBLCLAIM.Firm_ID = TBLFIRM.Firm_ID INNER JOIN[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 5"] TBLUSER ON TBLCLAIM.CR_ID = TBLUSER.User_ID[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 4"]WHERE (TBLLOB.LOB_Name = 'Asbestos') AND [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 8"] (TBLPAYMENT_REQUEST.Created_Date > CONVERT(DATETIME, '2012-11-14 05:00:00', 102)) AND [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 11"] (TBLPAYMENT_REQUEST.Created_Date < CONVERT(DATETIME, '2012-12-2 05:00:00', 102)) AND (TBLPAYMENTMADE.Amount <> 0) AND [/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 7"] (TBLFIRM.Firm_Name = 'Dogan&Wilkinson') AND (TBLPAYMENTMADE.Fees <> 0) OR[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 4"] (TBLLOB.LOB_Name = 'Asbestos') AND [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 8"] (TBLPAYMENT_REQUEST.Created_Date > CONVERT(DATETIME, '2012-11-14 05:00:00', 102)) AND [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 11"] (TBLPAYMENT_REQUEST.Created_Date < CONVERT(DATETIME, '2012-12-2 05:00:00', 102)) AND (TBLPAYMENTMADE.Amount <> 0) AND [/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 7"] (TBLFIRM.Firm_Name = 'Dogan&Wilkinson') AND (TBLPAYMENTMADE.Expense <> 0)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 3"]ORDER BY TBLINVOICE.Firm_Invoice_Code[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

SQL(2)(Sheet3) This is generated by pressing Ctrl-Shift-Q after navigating to sheet2 aka SQL:
[TABLE="width: 1072"]
<colgroup><col span="2"><col><col><col><col><col></colgroup><tbody>[TR]
[TD="colspan: 2"]FROM TBLCLAIM INNER JOIN[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 4"] TBLINVOICE ON TBLCLAIM.Claim_ID = TBLINVOICE.Claim_ID INNER JOIN[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 5"] TBLPAYMENT_REQUEST ON TBLINVOICE.Invoice_ID = TBLPAYMENT_REQUEST.Invoice_ID INNER JOIN[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 4"] TBLPAYMENTMADE ON TBLINVOICE.Invoice_ID = TBLPAYMENTMADE.Invoice_ID AND [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 5"] TBLPAYMENT_REQUEST.Payment_Request_ID = TBLPAYMENTMADE.Payment_Request_ID INNER JOIN[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 3"] TBLLOB ON TBLCLAIM.Claim_LOB = TBLLOB.LOB_ID INNER JOIN[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 3"] TBLFIRM ON TBLCLAIM.Firm_ID = TBLFIRM.Firm_ID INNER JOIN[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 3"] TBLUSER ON TBLCLAIM.CR_ID = TBLUSER.User_ID[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]WHERE (TBLLOB.LOB_Name = 'Asbestos') AND [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 5"] (TBLPAYMENT_REQUEST.Created_Date > CONVERT(DATETIME, '2012-11-14 05:00:00', 102)) AND [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 6"] (TBLPAYMENT_REQUEST.Created_Date < CONVERT(DATETIME, '2012-12-2 05:00:00', 102)) AND (TBLPAYMENTMADE.Amount <> 0) AND [/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 4"] (TBLFIRM.Firm_Name = 'Dogan&Wilkinson') AND (TBLPAYMENTMADE.Fees <> 0) OR[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"] (TBLLOB.LOB_Name = 'Asbestos') AND [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 5"] (TBLPAYMENT_REQUEST.Created_Date > CONVERT(DATETIME, '2012-11-14 05:00:00', 102)) AND [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 6"]SELECT TBLFIRM.Firm_Name, TBLCLAIM.Case_Name, TBLCLAIM.Insured_Name, TBLCLAIM.Firm_File_Number, TBLCLAIM.Claim_Unit_Number, [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="colspan: 6"] TBLUSER.Last_Name, TBLUSER.First_Name, TBLINVOICE.Firm_Invoice_Code, TBLINVOICE.Submitted_Date, TBLINVOICE.Total_Amount, [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="colspan: 6"] TBLINVOICE.Share_of_Firm_Fees, TBLINVOICE.Matter_total_detail_fees, TBLPAYMENT_REQUEST.Fees, TBLINVOICE.Matter_total_detail_exp, [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="colspan: 5"] TBLPAYMENT_REQUEST.Expense, TBLPAYMENTMADE.Amount, TBLPAYMENT_REQUEST.Created_Date, [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="colspan: 4"] TBLPAYMENT_REQUEST.Adjustment_Phase_Count, TBLINVOICE.Invoice_ID[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Alphabetically arranged[/TD]
[TD="colspan: 2"]Compare against SQL stmt above[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TBLCLAIM[/TD]
[TD]Case_Name[/TD]
[TD]TBLFIRM[/TD]
[TD]Firm_Name[/TD]
[TD]Firm[/TD]
[TD]FirmName[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TBLCLAIM[/TD]
[TD]Claim_Unit_Number[/TD]
[TD]TBLCLAIM[/TD]
[TD]Case_Name[/TD]
[TD]Case Name[/TD]
[TD]CaseName[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TBLCLAIM[/TD]
[TD]Firm_File_Number[/TD]
[TD]TBLCLAIM[/TD]
[TD]Insured_Name[/TD]
[TD]Insd[/TD]
[TD]ClientName[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TBLCLAIM[/TD]
[TD]Insured_Name[/TD]
[TD]TBLCLAIM[/TD]
[TD]Firm_File_Number[/TD]
[TD]Firm File Number[/TD]
[TD]1234[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TBLFIRM[/TD]
[TD]Firm_Name[/TD]
[TD]TBLCLAIM[/TD]
[TD]Claim_Unit_Number[/TD]
[TD]Claim Number[/TD]
[TD]5678[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TBLINVOICE[/TD]
[TD]Firm_Invoice_Code[/TD]
[TD]TBLUSER[/TD]
[TD]Last_Name[/TD]
[TD]CR Last Name[/TD]
[TD]LastName[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TBLINVOICE[/TD]
[TD]Invoice_ID[/TD]
[TD]TBLUSER[/TD]
[TD]First_Name[/TD]
[TD]CR First Name[/TD]
[TD]FirstName[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TBLINVOICE[/TD]
[TD]Matter_total_detail_exp[/TD]
[TD]TBLINVOICE[/TD]
[TD]Firm_Invoice_Code[/TD]
[TD]Inv Number[/TD]
[TD]9012[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TBLINVOICE[/TD]
[TD]Matter_total_detail_fees[/TD]
[TD]TBLINVOICE[/TD]
[TD]Submitted_Date[/TD]
[TD]Invoice Submitted Date[/TD]
[TD]11/20/2012[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TBLINVOICE[/TD]
[TD]Share_of_Firm_Fees[/TD]
[TD]TBLINVOICE[/TD]
[TD]Total_Amount[/TD]
[TD]Total Billed[/TD]
[TD]$100.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TBLINVOICE[/TD]
[TD]Submitted_Date[/TD]
[TD]TBLINVOICE[/TD]
[TD]Share_of_Firm_Fees[/TD]
[TD]Share %[/TD]
[TD]12[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TBLINVOICE[/TD]
[TD]Total_Amount[/TD]
[TD]TBLINVOICE[/TD]
[TD]Matter_total_detail_fees[/TD]
[TD]Billed Fees[/TD]
[TD]$50.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TBLPAYMENT_REQUEST[/TD]
[TD]Adjustment_Phase_Count[/TD]
[TD]TBLPAYMENT_REQUEST[/TD]
[TD]Fees[/TD]
[TD]Paid Fees[/TD]
[TD]$30.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TBLPAYMENT_REQUEST[/TD]
[TD]Created_Date[/TD]
[TD]TBLINVOICE[/TD]
[TD]Matter_total_detail_exp[/TD]
[TD]Billed Exp[/TD]
[TD]$10.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TBLPAYMENT_REQUEST[/TD]
[TD]Expense[/TD]
[TD]TBLPAYMENT_REQUEST[/TD]
[TD]Expense[/TD]
[TD]Paid Exp[/TD]
[TD]$1.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TBLPAYMENT_REQUEST[/TD]
[TD]Fees[/TD]
[TD]TBLPAYMENTMADE[/TD]
[TD]Amount[/TD]
[TD]CVSC Amt[/TD]
[TD]$2.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Missing in SQL[/TD]
[TD]Missing in SQL[/TD]
[TD]Missing in SQL[/TD]
[TD]Missing in SQL[/TD]
[TD]Payment to Firm[/TD]
[TD]$33.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TBLPAYMENTMADE[/TD]
[TD]Amount[/TD]
[TD]TBLPAYMENT_REQUEST[/TD]
[TD]Created_Date[/TD]
[TD]Release Date[/TD]
[TD]11/28/2012[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TBLUSER[/TD]
[TD]First_Name[/TD]
[TD]TBLPAYMENT_REQUEST[/TD]
[TD]Adjustment_Phase_Count[/TD]
[TD]APC[/TD]
[TD]9[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TBLUSER[/TD]
[TD]Last_Name[/TD]
[TD]TBLINVOICE[/TD]
[TD]Invoice_ID[/TD]
[TD]Inv ID[/TD]
[TD]12345678[/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]===============================================================================[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Macros:
First macro:
Sub CopyActiveSheetAndMoveToEnd()
'
' CopyActiveSheetAndMoveToEnd Macro
'
' Keyboard Shortcut: Ctrl+Shift+Q
'
Sheets("SQL").Select
Sheets("SQL").Copy After:=Sheets(2)

'Notification of next step
Range("A27").Select
ActiveCell.FormulaR1C1 = "Next: Ctrl-m"
Range("A27").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 13434879
.TintAndShade = 0
.PatternTintAndShade = 0
End With
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
With Selection.Font
.Color = -16776961
.TintAndShade = 0
End With
With Selection.Font
.ThemeColor = xlThemeColorLight2
.TintAndShade = 0.399975585192419
End With
With Selection.Font
.Color = -16776961
.TintAndShade = 0
End With
Selection.Font.Bold = True
'Notification of next step

End Sub

Second Macro:

[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sub ExtractTableNamesAndColumnNamesFromSQLinTopRowThenDeleteTopRow()'
' ExtractTableNamesAndColumnNamesFromSQLinTopRowThenDeleteTopRow Macro
' Caution: This macro will IRRETRIEVABLY DELETE the top row of the active worksheet after each use,
' since the logic here works only on the top row. Run this for extracting Table & Col Names.
' Example: TableName11.ColName4, TableName2ColName6 ...
' Keyboard Shortcut: Ctrl+m
'
Range("A30:A39").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A30:A39").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A1").Select
Selection.TextToColumns Destination:=Range("L1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
Semicolon:=False, Comma:=True, Space:=True, Other:=False, OtherChar:= _
".", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), _
Array(6, 1)), TrailingMinusNumbers:=True
Range("L1").Select
Selection.ClearContents
Range("M1:W1").Select
Selection.Copy
Range("L1").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("M1:W1").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("L1:L10").Select
Selection.TextToColumns Destination:=Range("L1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:=".", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
Range("L1:M10").Select
Selection.Cut
Range("A30").Select
ActiveSheet.Paste
Range("A20").Select
Selection.EntireRow.Insert
Rows("1:1").Select
Selection.Delete Shift:=xlUp
Columns("B:P").EntireColumn.AutoFit
Columns("A:A").Select
Selection.ColumnWidth = 23

'Notification of next step
Range("A27:D27").Select
ActiveCell.FormulaR1C1 = "Keep repeating Ctrl-m till entire Select stmt is done. Next: Ctrl-l as in Lima"
Range("A27:D27").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 13434879
.TintAndShade = 0
.PatternTintAndShade = 0
End With
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
With Selection.Font
.Color = -16776961
.TintAndShade = 0
End With
With Selection.Font
.ThemeColor = xlThemeColorLight2
.TintAndShade = 0.399975585192419
End With
With Selection.Font
.Color = -16776961
.TintAndShade = 0
End With
Selection.Font.Bold = True
'Notification of next step

Range("A1").Select

End Sub

Third Macro:
Sub CopyA1ThruA5FromSQLMasterAndPasteAtA20InSQLSlave()'
' CopyA1ThruA5FromSQLMasterAndPasteAtA20InSQLSlave Macro
'
' Keyboard Shortcut: Ctrl+l
'
Sheets("SQL").Select
Range("A1:A5").Select
Selection.Copy
Sheets("SQL (2)").Select
Range("A20").Select
ActiveSheet.Paste

'Notification of next step
Range("A27").Select
ActiveCell.FormulaR1C1 = "Next: Ctrl-p"
Range("A27").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 13434879
.TintAndShade = 0
.PatternTintAndShade = 0
End With
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
With Selection.Font
.Color = -16776961
.TintAndShade = 0
End With
With Selection.Font
.ThemeColor = xlThemeColorLight2
.TintAndShade = 0.399975585192419
End With
With Selection.Font
.Color = -16776961
.TintAndShade = 0
End With
Selection.Font.Bold = True
'Notification of next step

Range("A27").Select

End Sub

Fourth and last macro: [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sub ConsolidateAndSortTableNameColumnNamePairsInA30B30AndBelow()
'
' ConsolidateAndSortTableNameColumnNamePairsInA30B30AndBelow
'
' Keyboard Shortcut: Ctrl+p
'
Range("I30:J39").Select
Application.CutCopyMode = False
Selection.Cut
Range("K40").Select
ActiveSheet.Paste

Range("G30:H39").Select
Selection.Cut
Range("K50").Select
ActiveSheet.Paste


Range("E30:F39").Select
Selection.Cut
Range("K60").Select
ActiveSheet.Paste


Range("C30:D39").Select
Selection.Cut
Range("K70").Select
ActiveSheet.Paste


Range("A30:B39").Select
Selection.Cut
Range("K80").Select
ActiveSheet.Paste

Range("K30:L89").Select
Selection.Cut
Range("A30").Select
ActiveSheet.Paste

'Deleting empty rows with rows 30 and 89
On Error Resume Next
With Range("A30:B89")
.Value = .Value
.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End With
'Deletion of empty rows complete

'Creating an unsorted copy in columns C and D
Range("A30:B89").Select
Selection.Copy
Range("C30").Select
ActiveSheet.Paste
Range("A30").Select
'Creation of unsorted copy in columns C and D complete

'Sort harvested Table Names and Column Names in A and B
Range("A30:B89").Select
Application.CutCopyMode = False
ActiveWorkbook.Worksheets("SQL (2)").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("SQL (2)").Sort.SortFields.Add Key:=Range("A30:A89" _
), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("SQL (2)").Sort.SortFields.Add Key:=Range("B30:B89" _
), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("SQL (2)").Sort
.SetRange Range("A30:B89")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
'Sorting complete

'Drawing inside-outside borders on A30:D60 range
Range("A30:D60").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
Range("A27").Select
'Drawing inside-outside borders on A30:D60 range

'Draw border around SQL query and create header boxes over extracted Table Names and Column Names
Range("C29:D29").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
ActiveCell.FormulaR1C1 = "Compare against SQL stmt above"
With ActiveCell.Characters(Start:=1, Length:=30).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
Range("A29:B29").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
ActiveCell.FormulaR1C1 = "Alphabetically arranged"
With ActiveCell.Characters(Start:=1, Length:=23).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
Range("A29:D29").Select
Range("C29").Activate
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
Selection.Font.Bold = True
Range("A20:G24").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
'Paste here
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("F31").Select
'Draw border around SQL query and create header boxes over extracted Table Names and Column Names

'Highlight the extracted table and column info
Range("C29:D60").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent1
.TintAndShade = 0.599993896298105
.PatternTintAndShade = 0
End With
Selection.Font.Bold = False
Selection.Font.Bold = True
Selection.Font.Bold = False
Range("C29:D29").Select
Selection.Font.Bold = True
With Selection.Font
.Color = -16776961
.TintAndShade = 0
End With
Range("A29:B60").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent3
.TintAndShade = 0.799981688894314
.PatternTintAndShade = 0
End With
Range("A29:B29").Select
With Selection.Font
.ThemeColor = xlThemeColorAccent1
.TintAndShade = -0.249977111117893
End With
Range("A20:G24").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent6
.TintAndShade = 0.799981688894314
.PatternTintAndShade = 0
End With
Range("A27").Select
'Highlight the extracted table and column info

'Erase notification for next step
Range("A26:D28").Select
Range("A26:D28").Activate
Selection.Delete Shift:=xlUp
'Erase notification for next step

Columns("B:L").EntireColumn.AutoFit
Columns("A:A").Select
Selection.ColumnWidth = 23
Range("G35").Select
End Sub
[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
You must log in or register to reply here.

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top