Extracting patient control number and patient names

L

look

Board Regular
Joined
May 22, 2008
Messages
99
Hi All,

Below is a sample report on payments made.
Ideally on another sheet I would list all unique "Payer Claim # / Medicare ICN #".
Then I would need to extract the associated "Patient Control Number" and patient names.
This report is copied from a PDF document into Excel.
There is no consistency in the number of rows or where the patient control number or names appear.
Thank you in advance.

Sample below:

Change Healthcare EFT/Check #: 170410000001501 EFT/Check Date: 02/13/2017 EFT/Check Amount: $ 0.00 Payment Type: NON
ERA Check 1 of 2 RBHA 1 CH Payer Id: 33628 CH Process Date: 02/15/2017
Provider Name: Tax Id: 860480412 NPI: 1053362368 Other Payee Id:
Address: 123 Street Addl. Payee Id: 1053362368 POS:
CLAIM PROCESSING INFORMATION - CLAIM [1 OF 10]
Service Dates: 02/01/2017 Processing Status: 4 - Denied
Payer Claim # / Medicare ICN #: 17040C123177 CH Claim Trace Id: 039034999659656 Place Of Service: Total Adjustment Amount: $ 0.00
Charge: $ 176.61 Paid: $ 0.00 Patient Responsibility: $ 176.61 Deductible: $ -
Co-Insurance: $ - Co-Pay: $ - Other/Crossover Insurance:
Remark Codes: N52 Patient not enrolled in the billing provider's managed care plan on the date of service. Start: 01/01/2000
N650 This policy was not in effect for this date of loss. No coverage is available. Start: 07/15/2013
PATIENT - SUBSCRIBER INFORMATION
Patient Name: Patient 1 Patient Id: 00000 Patient Control Number: 186613
Corrected Patient/Subscriber Name:
Subscriber Name: Subscriber Id: Group/Policy Id:
Other Subscr. Name: Other Subscriber Id: Group/Policy Id:
REMITTANCE PROCESSING INFORMATION - SERVICE LINE DETAIL
Svc Line # Service Date Proc Code - Units Charge $ Allowed $
Modifiers
1 2/1/2017 H2014 - 7 176.61 0
SUPPLEMENTAL INFORMATION/ADJUSTMENT INFORMATION - SERVICE LINES

Svc Line # Supp/Adj Group Code Description Supp/Adj Reason Code Description
1 CO Contractual Obligations 26 Expenses incurred prior to coverage. Start: 01/01/1995
Claim 1 of 10 Page 1 of 15
Change Healthcare EFT/Check #: 170410000001501 EFT/Check Date: 02/13/2017 EFT/Check Amount: $ 0.00 Payment Type: NON
ERA Check 1 of 2 RBHA 1 CH Payer Id: 33628 CH Process Date: 02/15/2017
Provider Name: Tax Id: 860480412 NPI: 1053362368 Other Payee Id:
Address: 123 Street Addl. Payee Id: 1053362368 POS:
CLAIM PROCESSING INFORMATION - CLAIM [2 OF 10]
Service Dates: 01/31/2017 Processing Status: 4 - Denied
Payer Claim # / Medicare ICN #: 17040C123273 CH Claim Trace Id: 039034999660656 Place Of Service: Total Adjustment Amount: $ 0.00
Charge: $ 14.03 Paid: $ 0.00 Patient Responsibility: $ 14.03 Deductible: $ -
Co-Insurance: $ - Co-Pay: $ - Other/Crossover Insurance:
Remark Codes: N52 Patient not enrolled in the billing provider's managed care plan on the date of service. Start: 01/01/2000
N650 This policy was not in effect for this date of loss. No coverage is available. Start: 07/15/2013
PATIENT - SUBSCRIBER INFORMATION
Patient Name: Patient 2 Patient Id: 00000 Patient Control Number: 186614
Corrected Patient/Subscriber Name:
Subscriber Name: Subscriber Id: Group/Policy Id:
Other Subscr. Name: Other Subscriber Id: Group/Policy Id:
REMITTANCE PROCESSING INFORMATION - SERVICE LINE DETAIL
Svc Line # Service Date Proc Code - Units Charge $ Allowed $
Modifiers
1 1/31/2017 T1016 - 1 14.03 0
HN
SUPPLEMENTAL INFORMATION/ADJUSTMENT INFORMATION - SERVICE LINES

Svc Line # Supp/Adj Group Code Description Supp/Adj Reason Code Description
1 CO Contractual Obligations 26 Expenses incurred prior to coverage. Start: 01/01/1995
Claim 2 of 10 Page 2 of 15
Change Healthcare EFT/Check #: 170410000001501 EFT/Check Date: 02/13/2017 EFT/Check Amount: $ 0.00 Payment Type: NON
ERA Check 1 of 2 RBHA 1 CH Payer Id: 33628 CH Process Date: 02/15/2017
Provider Name: Tax Id: 860480412 NPI: 1053362368 Other Payee Id:
Address: 123 Street Addl. Payee Id: 1053362368 POS:
CLAIM PROCESSING INFORMATION - CLAIM [3 OF 10]
Service Dates: 01/27/2017 Processing Status: 4 - Denied
Payer Claim # / Medicare ICN #: 17035C099954 CH Claim Trace Id: 034032702671656 Place Of Service: Total Adjustment Amount: $ 0.00
Charge: $ 80.48 Paid: $ 0.00 Patient Responsibility: $ 80.48 Deductible: $ -
Co-Insurance: $ - Co-Pay: $ - Other/Crossover Insurance:
Remark Codes: N52 Patient not enrolled in the billing provider's managed care plan on the date of service. Start: 01/01/2000
N650 This policy was not in effect for this date of loss. No coverage is available. Start: 07/15/2013
PATIENT - SUBSCRIBER INFORMATION
Patient Name: Patient 3 Patient Id: 00000 Patient Control Number: 186458
Corrected Patient/Subscriber Name:
Subscriber Name: Subscriber Id: Group/Policy Id:
Other Subscr. Name: Other Subscriber Id: Group/Policy Id:
REMITTANCE PROCESSING INFORMATION - SERVICE LINE DETAIL
Svc Line # Service Date Proc Code - Units Charge $ Allowed $
Modifiers
1 1/27/2017 H0004 - 4 80.48 0
SUPPLEMENTAL INFORMATION/ADJUSTMENT INFORMATION - SERVICE LINES

Svc Line # Supp/Adj Group Code Description Supp/Adj Reason Code Description
1 CO Contractual Obligations 26 Expenses incurred prior to coverage. Start: 01/01/1995
Claim 3 of 10 Page 3 of 15
Change Healthcare EFT/Check #: 170410000001501 EFT/Check Date: 02/13/2017 EFT/Check Amount: $ 0.00 Payment Type: NON
ERA Check 1 of 2 RBHA 1 CH Payer Id: 33628 CH Process Date: 02/15/2017
Provider Name: Tax Id: 860480412 NPI: 1053362368 Other Payee Id:
Address: 123 Street Addl. Payee Id: 1053362368 POS:
CLAIM PROCESSING INFORMATION - CLAIM [4 OF 10]
Service Dates: 01/26/2017 Processing Status: 4 - Denied
Payer Claim # / Medicare ICN #: 17035C100098 CH Claim Trace Id: 034032699421659 Place Of Service: Total Adjustment Amount: $ 0.00
Charge: $ 92.75 Paid: $ 0.00 Patient Responsibility: $ 92.75 Deductible: $ -
Co-Insurance: $ - Co-Pay: $ - Other/Crossover Insurance:
Remark Codes: N52 Patient not enrolled in the billing provider's managed care plan on the date of service. Start: 01/01/2000
N650 This policy was not in effect for this date of loss. No coverage is available. Start: 07/15/2013
PATIENT - SUBSCRIBER INFORMATION
Patient Name: Patient 4 Patient Id: 00000 Patient Control Number: 186459
Corrected Patient/Subscriber Name:
Subscriber Name: Subscriber Id: Group/Policy Id:
Other Subscr. Name: Other Subscriber Id: Group/Policy Id:
REMITTANCE PROCESSING INFORMATION - SERVICE LINE DETAIL
Svc Line # Service Date Proc Code - Units Charge $ Allowed $
Modifiers
1 1/26/2017 T1016 - 5 92.75 0
HO
SUPPLEMENTAL INFORMATION/ADJUSTMENT INFORMATION - SERVICE LINES

Svc Line # Supp/Adj Group Code Description Supp/Adj Reason Code Description
1 CO Contractual Obligations 26 Expenses incurred prior to coverage. Start: 01/01/1995
Claim 4 of 10 Page 4 of 15
Change Healthcare EFT/Check #: 170410000001501 EFT/Check Date: 02/13/2017 EFT/Check Amount: $ 0.00 Payment Type: NON
ERA Check 1 of 2 RBHA 1 CH Payer Id: 33628 CH Process Date: 02/15/2017
Provider Name: Tax Id: 860480412 NPI: 1053362368 Other Payee Id:
Address: 123 Street Addl. Payee Id: 1053362368 POS:
CLAIM PROCESSING INFORMATION - CLAIM [5 OF 10]
Service Dates: 01/24/2017 Processing Status: 4 - Denied
Payer Claim # / Medicare ICN #: 17034C128311 CH Claim Trace Id: 033031633801657 Place Of Service: Total Adjustment Amount: $ 0.00
Charge: $ 30.40 Paid: $ 0.00 Patient Responsibility: $ 30.40 Deductible: $ -
Co-Insurance: $ - Co-Pay: $ - Other/Crossover Insurance:
Remark Codes: N52 Patient not enrolled in the billing provider's managed care plan on the date of service. Start: 01/01/2000
N650 This policy was not in effect for this date of loss. No coverage is available. Start: 07/15/2013
PATIENT - SUBSCRIBER INFORMATION
Patient Name: Patient 5 Patient Id: 00000 Patient Control Number: 186314
Corrected Patient/Subscriber Name:
Subscriber Name: Subscriber Id: Group/Policy Id:
Other Subscr. Name: Other Subscriber Id: Group/Policy Id:
REMITTANCE PROCESSING INFORMATION - SERVICE LINE DETAIL
Svc Line # Service Date Proc Code - Units Charge $ Allowed $
Modifiers
1 1/24/2017 T1016 - 1 30.4 0
HO
SUPPLEMENTAL INFORMATION/ADJUSTMENT INFORMATION - SERVICE LINES

Svc Line # Supp/Adj Group Code Description Supp/Adj Reason Code Description
1 CO Contractual Obligations 26 Expenses incurred prior to coverage. Start: 01/01/1995
Claim 5 of 10 Page 5 of 15
Change Healthcare EFT/Check #: 170410000001501 EFT/Check Date: 02/13/2017 EFT/Check Amount: $ 0.00 Payment Type: NON
ERA Check 1 of 2 RBHA 1 CH Payer Id: 33628 CH Process Date: 02/15/2017
Provider Name: Tax Id: 860480412 NPI: 1053362368 Other Payee Id:
Address: 123 Street Addl. Payee Id: 1053362368 POS:
CLAIM PROCESSING INFORMATION - CLAIM [6 OF 10]
Service Dates: 02/06/2017 Processing Status: 4 - Denied
Payer Claim # / Medicare ICN #: 17040C123281 CH Claim Trace Id: 039034999273657 Place Of Service: Total Adjustment Amount: $ 0.00
Charge: $ 307.20 Paid: $ 0.00 Patient Responsibility: $ 307.20 Deductible: $ -
Co-Insurance: $ - Co-Pay: $ - Other/Crossover Insurance:
Remark Codes: N52 Patient not enrolled in the billing provider's managed care plan on the date of service. Start: 01/01/2000
N650 This policy was not in effect for this date of loss. No coverage is available. Start: 07/15/2013
PATIENT - SUBSCRIBER INFORMATION
Patient Name: Patient 6 Patient Id: 00000 Patient Control Number: 186788
Corrected Patient/Subscriber Name:
Subscriber Name: Subscriber Id: Group/Policy Id:
Other Subscr. Name: Other Subscriber Id: Group/Policy Id:
REMITTANCE PROCESSING INFORMATION - SERVICE LINE DETAIL
Svc Line # Service Date Proc Code - Units Charge $ Allowed $
Modifiers
1 2/6/2017 H0004 - 8 307.2 0
HR
SUPPLEMENTAL INFORMATION/ADJUSTMENT INFORMATION - SERVICE LINES

Svc Line # Supp/Adj Group Code Description Supp/Adj Reason Code Description
1 CO Contractual Obligations 26 Expenses incurred prior to coverage. Start: 01/01/1995
Claim 6 of 10 Page 6 of 15
Change Healthcare EFT/Check #: 170410000001501 EFT/Check Date: 02/13/2017 EFT/Check Amount: $ 0.00 Payment Type: NON
ERA Check 1 of 2 RBHA 1 CH Payer Id: 33628 CH Process Date: 02/15/2017
Provider Name: Tax Id: 860480412 NPI: 1053362368 Other Payee Id:
Address: 123 Street Addl. Payee Id: 1053362368 POS:
CLAIM PROCESSING INFORMATION - CLAIM [7 OF 10]
Service Dates: 02/01/2017 Processing Status: 4 - Denied
Payer Claim # / Medicare ICN #: 17040C123311 CH Claim Trace Id: 039034999591656 Place Of Service: Total Adjustment Amount: $ 0.00
Charge: $ 14.03 Paid: $ 0.00 Patient Responsibility: $ 14.03 Deductible: $ -
Co-Insurance: $ - Co-Pay: $ - Other/Crossover Insurance:
Remark Codes: N52 Patient not enrolled in the billing provider's managed care plan on the date of service. Start: 01/01/2000
N650 This policy was not in effect for this date of loss. No coverage is available. Start: 07/15/2013
PATIENT - SUBSCRIBER INFORMATION
Patient Name: Patient 7 Patient Id: 00000 Patient Control Number: 186713
Corrected Patient/Subscriber Name:
Subscriber Name: Subscriber Id: Group/Policy Id:
Other Subscr. Name: Other Subscriber Id: Group/Policy Id:
REMITTANCE PROCESSING INFORMATION - SERVICE LINE DETAIL
Svc Line # Service Date Proc Code - Units Charge $ Allowed $
Modifiers
1 2/1/2017 T1016 - 1 14.03 0
HN
SUPPLEMENTAL INFORMATION/ADJUSTMENT INFORMATION - SERVICE LINES

Svc Line # Supp/Adj Group Code Description Supp/Adj Reason Code Description
1 CO Contractual Obligations 26 Expenses incurred prior to coverage. Start: 01/01/1995
Claim 7 of 10 Page 7 of 15
Change Healthcare EFT/Check #: 170410000001501 EFT/Check Date: 02/13/2017 EFT/Check Amount: $ 0.00 Payment Type: NON
ERA Check 1 of 2 RBHA 1 CH Payer Id: 33628 CH Process Date: 02/15/2017
Provider Name: Tax Id: 860480412 NPI: 1053362368 Other Payee Id:
Address: 123 Street Addl. Payee Id: 1053362368 POS:
CLAIM PROCESSING INFORMATION - CLAIM [8 OF 10]
Service Dates: 01/19/2017 Processing Status: 4 - Denied
Payer Claim # / Medicare ICN #: 17035C100089 CH Claim Trace Id: 034032703019657 Place Of Service: Total Adjustment Amount: $ 0.00
Charge: $ 112.24 Paid: $ 0.00 Patient Responsibility: $ 112.24 Deductible: $ -
Co-Insurance: $ - Co-Pay: $ - Other/Crossover Insurance:
Remark Codes: N52 Patient not enrolled in the billing provider's managed care plan on the date of service. Start: 01/01/2000
N650 This policy was not in effect for this date of loss. No coverage is available. Start: 07/15/2013
PATIENT - SUBSCRIBER INFORMATION
Patient Name: Patient 8 Patient Id: 00000 Patient Control Number: 186407
Corrected Patient/Subscriber Name:
Subscriber Name: Subscriber Id: Group/Policy Id:
Other Subscr. Name: Other Subscriber Id: Group/Policy Id:
REMITTANCE PROCESSING INFORMATION - SERVICE LINE DETAIL
Svc Line # Service Date Proc Code - Units Charge $ Allowed $
Modifiers
1 1/19/2017 T1016 - 8 112.24 0
HN
SUPPLEMENTAL INFORMATION/ADJUSTMENT INFORMATION - SERVICE LINES

Svc Line # Supp/Adj Group Code Description Supp/Adj Reason Code Description
1 CO Contractual Obligations 26 Expenses incurred prior to coverage. Start: 01/01/1995
Claim 8 of 10 Page 8 of 15
Change Healthcare EFT/Check #: 170410000001501 EFT/Check Date: 02/13/2017 EFT/Check Amount: $ 0.00 Payment Type: NON
ERA Check 1 of 2 RBHA 1 CH Payer Id: 33628 CH Process Date: 02/15/2017
Provider Name: Tax Id: 860480412 NPI: 1053362368 Other Payee Id:
Address: 123 Street Addl. Payee Id: 1053362368 POS:
CLAIM PROCESSING INFORMATION - CLAIM [9 OF 10]
Service Dates: 02/01/2017 Processing Status: 4 - Denied
Payer Claim # / Medicare ICN #: 17040C123245 CH Claim Trace Id: 039034999592656 Place Of Service: Total Adjustment Amount: $ 0.00
Charge: $ 14.03 Paid: $ 0.00 Patient Responsibility: $ 14.03 Deductible: $ -
Co-Insurance: $ - Co-Pay: $ - Other/Crossover Insurance:
Remark Codes: N52 Patient not enrolled in the billing provider's managed care plan on the date of service. Start: 01/01/2000
N650 This policy was not in effect for this date of loss. No coverage is available. Start: 07/15/2013
PATIENT - SUBSCRIBER INFORMATION
Patient Name: Patient 9 Patient Id: 00000 Patient Control Number: 186720
Corrected Patient/Subscriber Name:
Subscriber Name: Subscriber Id: Group/Policy Id:
Other Subscr. Name: Other Subscriber Id: Group/Policy Id:
REMITTANCE PROCESSING INFORMATION - SERVICE LINE DETAIL
Svc Line # Service Date Proc Code - Units Charge $ Allowed $
Modifiers
1 2/1/2017 T1016 - 1 14.03 0
HN
SUPPLEMENTAL INFORMATION/ADJUSTMENT INFORMATION - SERVICE LINES

Svc Line # Supp/Adj Group Code Description Supp/Adj Reason Code Description
1 CO Contractual Obligations 26 Expenses incurred prior to coverage. Start: 01/01/1995
Claim 9 of 10 Page 9 of 15
Change Healthcare EFT/Check #: 170410000001501 EFT/Check Date: 02/13/2017 EFT/Check Amount: $ 0.00 Payment Type: NON
ERA Check 1 of 2 RBHA 1 CH Payer Id: 33628 CH Process Date: 02/15/2017
Provider Name: Tax Id: 860480412 NPI: 1053362368 Other Payee Id:
Address: 123 Street Addl. Payee Id: 1053362368 POS:
CLAIM PROCESSING INFORMATION - CLAIM [10 OF 10]
Service Dates: 02/01/2017 Processing Status: 4 - Denied
Payer Claim # / Medicare ICN #: 17040C123223 CH Claim Trace Id: 039034999228657 Place Of Service: Total Adjustment Amount: $ 0.00
Charge: $ 70.15 Paid: $ 0.00 Patient Responsibility: $ 70.15 Deductible: $ -
Co-Insurance: $ - Co-Pay: $ - Other/Crossover Insurance:
Remark Codes: N52 Patient not enrolled in the billing provider's managed care plan on the date of service. Start: 01/01/2000
N650 This policy was not in effect for this date of loss. No coverage is available. Start: 07/15/2013
PATIENT - SUBSCRIBER INFORMATION
Patient Name: Patient 10 Patient Id: 00000 Patient Control Number: 186709
Corrected Patient/Subscriber Name:
Subscriber Name: Subscriber Id: Group/Policy Id:
Other Subscr. Name: Other Subscriber Id: Group/Policy Id:
REMITTANCE PROCESSING INFORMATION - SERVICE LINE DETAIL
Svc Line # Service Date Proc Code - Units Charge $ Allowed $
Modifiers
1 2/1/2017 T1016 - 5 70.15 0
HN
SUPPLEMENTAL INFORMATION/ADJUSTMENT INFORMATION - SERVICE LINES

Svc Line # Supp/Adj Group Code Description Supp/Adj Reason Code Description
1 CO Contractual Obligations 26 Expenses incurred prior to coverage. Start: 01/01/1995
Claim 10 of 10 Page 10 of 15


Change Healthcare EFT/Check #: 170410000005725 EFT/Check Date: 02/13/2017 EFT/Check Amount: $ 0.00 Payment Type: NON
ERA Check 2 of 2 RBHA 1 CH Payer Id: 33628 CH Process Date: 02/15/2017
Provider Name: Tax Id: 860480412 NPI: 1053362368 Other Payee Id:
Address: 123 Street Addl. Payee Id: 1053362368 POS:
CLAIM PROCESSING INFORMATION - CLAIM [1 OF 5]
Service Dates: 01/27/2017 Processing Status: 1 - Processed as Primary
Payer Claim # / Medicare ICN #: 17034C128322 CH Claim Trace Id: 033031633798657 Place Of Service: Total Adjustment Amount: $ 0.00
Charge: $ 131.04 Paid: $ 0.00 Patient Responsibility: $ - Deductible: $ -
Co-Insurance: $ - Co-Pay: $ - Other/Crossover Insurance:

PATIENT - SUBSCRIBER INFORMATION
Patient Name: Patient 11 Patient Id: A00162406 Patient Control Number: 186310
Corrected Patient/Subscriber Name:
Subscriber Name: Subscriber Id: Group/Policy Id:
Other Subscr. Name: Other Subscriber Id: Group/Policy Id:
REMITTANCE PROCESSING INFORMATION - SERVICE LINE DETAIL
Svc Line # Service Date Proc Code - Units Charge $ Allowed $
Modifiers
1 1/27/2017 H0004 - 4 131.04 131.04
SUPPLEMENTAL INFORMATION/ADJUSTMENT - ADJUDICATION INFORMATION - CLAIM

Supp/Adj Group Code Description Supp/Adj Reason Code Description Amount $
AMT Supplemental Information AU Coverage Amount 131.04
SUPPLEMENTAL INFORMATION/ADJUSTMENT INFORMATION - SERVICE LINES

Svc Line # Supp/Adj Group Code Description Supp/Adj Reason Code Description
1 CO Contractual Obligations 24 Charges are covered under a capitation agreement/managed care plan. Start: 01/01/1995 | Last Modified: 09/30/2007
AMT Supplemental Info B6 Allowed - Actual
Claim 1 of 5 Page 11 of 15
Change Healthcare EFT/Check #: 170410000005725 EFT/Check Date: 02/13/2017 EFT/Check Amount: $ 0.00 Payment Type: NON
ERA Check 2 of 2 RBHA 1 CH Payer Id: 33628 CH Process Date: 02/15/2017
Provider Name: Tax Id: 860480412 NPI: 1053362368 Other Payee Id:
Address: 123 Street Addl. Payee Id: 1053362368 POS:
CLAIM PROCESSING INFORMATION - CLAIM [2 OF 5]
Service Dates: 01/27/2017 Processing Status: 1 - Processed as Primary
Payer Claim # / Medicare ICN #: 17034C128330 CH Claim Trace Id: 033031633797657 Place Of Service: Total Adjustment Amount: $ 0.00
Charge: $ 30.40 Paid: $ 0.00 Patient Responsibility: $ - Deductible: $ -
Co-Insurance: $ - Co-Pay: $ - Other/Crossover Insurance:

PATIENT - SUBSCRIBER INFORMATION
Patient Name: Patient 12 Patient Id: A00162406 Patient Control Number: 186309
Corrected Patient/Subscriber Name:
Subscriber Name: Subscriber Id: Group/Policy Id:
Other Subscr. Name: Other Subscriber Id: Group/Policy Id:
REMITTANCE PROCESSING INFORMATION - SERVICE LINE DETAIL
Svc Line # Service Date Proc Code - Units Charge $ Allowed $
Modifiers
1 1/27/2017 T1016 - 1 30.4 30.4
HO
SUPPLEMENTAL INFORMATION/ADJUSTMENT - ADJUDICATION INFORMATION - CLAIM

Supp/Adj Group Code Description Supp/Adj Reason Code Description Amount $
AMT Supplemental Information AU Coverage Amount 30.4
SUPPLEMENTAL INFORMATION/ADJUSTMENT INFORMATION - SERVICE LINES

Svc Line # Supp/Adj Group Code Description Supp/Adj Reason Code Description
1 CO Contractual Obligations 24 Charges are covered under a capitation agreement/managed care plan. Start: 01/01/1995 | Last Modified: 09/30/2007
AMT Supplemental Info B6 Allowed - Actual
Claim 2 of 5 Page 12 of 15
Change Healthcare EFT/Check #: 170410000005725 EFT/Check Date: 02/13/2017 EFT/Check Amount: $ 0.00 Payment Type: NON
ERA Check 2 of 2 RBHA 1 CH Payer Id: 33628 CH Process Date: 02/15/2017
Provider Name: Tax Id: 860480412 NPI: 1053362368 Other Payee Id:
Address: 123 Street Addl. Payee Id: 1053362368 POS:
CLAIM PROCESSING INFORMATION - CLAIM [3 OF 5]
Service Dates: 01/30/2017 Processing Status: 1 - Processed as Primary
Payer Claim # / Medicare ICN #: 17035C100044 CH Claim Trace Id: 034032699458659 Place Of Service: Total Adjustment Amount: $ 0.00
Charge: $ 12.53 Paid: $ 0.00 Patient Responsibility: $ - Deductible: $ -
Co-Insurance: $ - Co-Pay: $ - Other/Crossover Insurance:

PATIENT - SUBSCRIBER INFORMATION
Patient Name: Patient 13 Patient Id: A00162406 Patient Control Number: 186537
Corrected Patient/Subscriber Name:
Subscriber Name: Subscriber Id: Group/Policy Id:
Other Subscr. Name: Other Subscriber Id: Group/Policy Id:
REMITTANCE PROCESSING INFORMATION - SERVICE LINE DETAIL
Svc Line # Service Date Proc Code - Units Charge $ Allowed $
Modifiers
1 1/30/2017 T1016 - 1 12.53 12.53
HN
SUPPLEMENTAL INFORMATION/ADJUSTMENT - ADJUDICATION INFORMATION - CLAIM

Supp/Adj Group Code Description Supp/Adj Reason Code Description Amount $
AMT Supplemental Information AU Coverage Amount 12.53
SUPPLEMENTAL INFORMATION/ADJUSTMENT INFORMATION - SERVICE LINES

Svc Line # Supp/Adj Group Code Description Supp/Adj Reason Code Description
1 CO Contractual Obligations 24 Charges are covered under a capitation agreement/managed care plan. Start: 01/01/1995 | Last Modified: 09/30/2007
AMT Supplemental Info B6 Allowed - Actual
Claim 3 of 5 Page 13 of 15
Change Healthcare EFT/Check #: 170410000005725 EFT/Check Date: 02/13/2017 EFT/Check Amount: $ 0.00 Payment Type: NON
ERA Check 2 of 2 RBHA 1 CH Payer Id: 33628 CH Process Date: 02/15/2017
Provider Name: Tax Id: 860480412 NPI: 1053362368 Other Payee Id:
Address: 123 Street Addl. Payee Id: 1053362368 POS:
CLAIM PROCESSING INFORMATION - CLAIM [4 OF 5]
Service Dates: 01/31/2017 Processing Status: 1 - Processed as Primary
Payer Claim # / Medicare ICN #: 17035C100047 CH Claim Trace Id: 034032699466659 Place Of Service: Total Adjustment Amount: $ 0.00
Charge: $ 25.06 Paid: $ 0.00 Patient Responsibility: $ - Deductible: $ -
Co-Insurance: $ - Co-Pay: $ - Other/Crossover Insurance:

PATIENT - SUBSCRIBER INFORMATION
Patient Name: Patient 14 Patient Id: A00162406 Patient Control Number: 186545
Corrected Patient/Subscriber Name:
Subscriber Name: Subscriber Id: Group/Policy Id:
Other Subscr. Name: Other Subscriber Id: Group/Policy Id:
REMITTANCE PROCESSING INFORMATION - SERVICE LINE DETAIL
Svc Line # Service Date Proc Code - Units Charge $ Allowed $
Modifiers
1 1/31/2017 T1016 - 2 25.06 25.06
HN
SUPPLEMENTAL INFORMATION/ADJUSTMENT - ADJUDICATION INFORMATION - CLAIM

Supp/Adj Group Code Description Supp/Adj Reason Code Description Amount $
AMT Supplemental Information AU Coverage Amount 25.06
SUPPLEMENTAL INFORMATION/ADJUSTMENT INFORMATION - SERVICE LINES

Svc Line # Supp/Adj Group Code Description Supp/Adj Reason Code Description
1 CO Contractual Obligations 24 Charges are covered under a capitation agreement/managed care plan. Start: 01/01/1995 | Last Modified: 09/30/2007
AMT Supplemental Info B6 Allowed - Actual
Claim 4 of 5 Page 14 of 15
Change Healthcare EFT/Check #: 170410000005725 EFT/Check Date: 02/13/2017 EFT/Check Amount: $ 0.00 Payment Type: NON
ERA Check 2 of 2 RBHA 1 CH Payer Id: 33628 CH Process Date: 02/15/2017
Provider Name: Tax Id: 860480412 NPI: 1053362368 Other Payee Id:
Address: 123 Street Addl. Payee Id: 1053362368 POS:
CLAIM PROCESSING INFORMATION - CLAIM [5 OF 5]
Service Dates: 01/30/2017 Processing Status: 1 - Processed as Primary
Payer Claim # / Medicare ICN #: 17035C100084 CH Claim Trace Id: 034032699366659 Place Of Service: Total Adjustment Amount: $ 0.00
Charge: $ 30.40 Paid: $ 0.00 Patient Responsibility: $ - Deductible: $ -
Co-Insurance: $ - Co-Pay: $ - Other/Crossover Insurance:

PATIENT - SUBSCRIBER INFORMATION
Patient Name: Patient 15 Patient Id: A00162406 Patient Control Number: 186396
Corrected Patient/Subscriber Name:
Subscriber Name: Subscriber Id: Group/Policy Id:
Other Subscr. Name: Other Subscriber Id: Group/Policy Id:
REMITTANCE PROCESSING INFORMATION - SERVICE LINE DETAIL
Svc Line # Service Date Proc Code - Units Charge $ Allowed $
Modifiers
1 1/30/2017 T1016 - 1 30.4 30.4
HO
SUPPLEMENTAL INFORMATION/ADJUSTMENT - ADJUDICATION INFORMATION - CLAIM

Supp/Adj Group Code Description Supp/Adj Reason Code Description Amount $
AMT Supplemental Information AU Coverage Amount 30.4
SUPPLEMENTAL INFORMATION/ADJUSTMENT INFORMATION - SERVICE LINES

Svc Line # Supp/Adj Group Code Description Supp/Adj Reason Code Description
1 CO Contractual Obligations 24 Charges are covered under a capitation agreement/managed care plan. Start: 01/01/1995 | Last Modified: 09/30/2007
AMT Supplemental Info B6 Allowed - Actual
 
Sort by date Sort by votes
For Post #10, refer to the upper graphic only as to what appears on my test sheet. Ignore the lower one.

Howard
 
Upvote 0

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Thanks Howard.
It is a correct extraction of data. The macro works.
Does it matter that I am using Excel 2003. When I run it still gives me three columns with [TABLE="width: 144"]
<colgroup><col width="64" style="width: 48pt;" span="3"> <tbody>[TR]
[TD="width: 64, bgcolor: transparent, align: center"]#NAME?[/TD]
[TD="width: 64, bgcolor: transparent, align: center"]#NAME?[/TD]
[TD="width: 64, bgcolor: transparent, align: center"]
#NAME?
[/TD]
[/TR]
</tbody>[/TABLE]
Thanks
 
Upvote 0
Hi Howard.

The macro works!! It is a correct extraction of data.
It does not work on Excel 2003 but when I did it on Excel 2013 it works.
I will try it on a big file with thousands of claims and will check the results.
Any reason it would not work on Excel 2003.
No matter the person who is going to use it works with 2013.

Thanks again.
 
Upvote 0
Hi Howard.

The macro works!! It does not work on Excel 2003 but it works on Excel 2013.
Any reason?
Does not matter the person who will use it works on Excel 2013.

Thanks again!!
 
Upvote 0
look said:
Thanks Howard.
It is a correct extraction of data. The macro works.
Does it matter that I am using Excel 2003. When I run it still gives me three columns with [TABLE="width: 144"]
<tbody>[TR]
[TD="width: 64, bgcolor: transparent, align: center"]#NAME?[/TD]
[TD="width: 64, bgcolor: transparent, align: center"]#NAME?[/TD]
[TD="width: 64, bgcolor: transparent, align: center"]
#NAME?
[/TD]
[/TR]
</tbody>[/TABLE]
Thanks
Click to expand...
Yes. it matters. Howard is using the IFERROR worksheet function in his code. That function wasn't available in Excel 2003. Howard can modify his code to adapt it for Excel 2003.
 
Upvote 0
JoeMo said:
Yes. it matters. Howard is using the IFERROR worksheet function in his code. That function wasn't available in Excel 2003. Howard can modify his code to adapt it for Excel 2003.
Click to expand...


Thanks Joe, I would not have remembered that 2003 did not have IFERROR.

Howard
 
Upvote 0
Howard thanks again.
Salaina also says thanks. It will save her a lot of manual work.
 
Upvote 0
look said:
Hi Howard.

The macro works!! It does not work on Excel 2003 but it works on Excel 2013.
Any reason?
Does not matter the person who will use it works on Excel 2013.

Thanks again!!
Click to expand...

You're welcome. And it you do not need a 2003 workable version?

Howard

Just read your last post. You are good to go! Great.

Howard
 
Last edited:
Upvote 0
Thanks Howard.
Do not need 2003 version.
Would it be easy to add "Processing Status" to the extracted info?
Thanks
 
Upvote 0
look said:
Thanks Howard.

Would it be easy to add "Processing Status" to the extracted info?

Thanks
Click to expand...

Try this, pretty much the same as the others and includes column J in the compiling columns.

Howard


Code: 
Option Explicit

Sub Data_Find_22()

  Dim lRowCount&
  
Application.ScreenUpdating = False

  lRowCount = Cells(Rows.Count, "A").End(xlUp).Row ''Note is column A for row count
  
  With Range("G2").Resize(lRowCount)
    .Formula = "=IFERROR(TRIM(MID(A2,FIND(""Patient Name:"",A2)+13,99)),"""")": .Value = .Value
  End With
  
    With Range("H2").Resize(lRowCount)
    .Formula = "=IFERROR(TRIM(MID(C2,FIND(""Patient Control Number:"",C2)+23,99)),"""")": .Value = .Value
  End With
  
    With Range("I2").Resize(lRowCount)
    .Formula = "=IFERROR(TRIM(MID(A2,FIND(""Medicare ICN #:"",A2)+15,99)),"""")": .Value = .Value
  End With
  
  With Range("J2").Resize(lRowCount)
    .Formula = "=IFERROR(TRIM(MID(C2,FIND(""Processing Status:"",C2)+18,99)),"""")": .Value = .Value
  End With
  
    Columns("G:J").Select
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Selection.Delete Shift:=xlUp
    Range("G1").Select
    
   Application.ScreenUpdating = True
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Solved
Stumped on counting patients unique visits to a clinic
Replies
7
Views
526
Fluff
Fluff
G
  • Question Question
Fuzzy Match code
Replies
1
Views
2K
gelu
G
A
  • Solved
Clubbing 2 if conditions into one
Replies
9
Views
930
ansvk1
A
Sufiyan97
  • Question Question
Partial Text Lookup
2
Replies
18
Views
2K
Sufiyan97
Sufiyan97
cmschmitz24
  • Question Question
Macro Help - Move/Delete based on cell criteria
2
Replies
13
Views
2K
DanteAmor
DanteAmor

Forum statistics

Threads
1,223,884
Messages
6,175,174
Members
452,615
Latest member
bogeys2birdies

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