Extracting patient control number and patient names

look · Mar 1, 2017

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

L. Howard · Mar 1, 2017

Give this a try, Copy to a standard module.

Assumes that the data shown in your post is all in column A and each line is in a row, I show row 409 as the last entry on my test sheet. (A2 to A409)

The blank rows do not matter. The list can be longer.

Run the code from the sheet with the data, where you will have columns B, C & D free to list the results. You can have headers on those three columns if you want, otherwise the list starts in row 1.

Howard

Code:

Option Explicit

Sub Data_Find()
  Dim lRowCount&
 
  lRowCount = Cells(Rows.Count, "A").End(xlUp).Row ''Note is column A for row count
  
  With Range("B2").Resize(lRowCount)
    .Formula = "=IFERROR(MID(A2,FIND(""ICN #"",A2)+7,FIND(""CH Claim"",A2)-33),"""")": .Value = .Value
  End With
  
    With Range("C2").Resize(lRowCount)
    .Formula = "=IFERROR(MID(A2,FIND(""Control Number:"",A2)+17,6),"""")": .Value = .Value
  End With
  
    With Range("D2").Resize(lRowCount)
    .Formula = "=IFERROR(MID(A2,14,FIND(""Patient Id:"",A2)-14),"""")": .Value = .Value
  End With
  
    Columns("B:D").Select
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Selection.Delete Shift:=xlUp
    Range("B1").Select
    
End Sub

look · Mar 2, 2017

Thank you L. Howard.
The data is from column a to column e.
When I copied it to board it displays only in column a.
Is there a way to show data as it appears. What happened to HTLM maker?
I tried the macro and it failed.

Thank you for your time and attention.

look · Mar 2, 2017

A truer representation of what worksheet looks like. Thanks. 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

L. Howard · Mar 2, 2017

I copied the truer version to a worksheet and it seems quite jumbled, not sure if I am looking at a true representation of the data layout.

How about posting a link to your test workbook that has the true layout (but dummy data of course).

You can use one of the link utilities to that. I use drop box but there are others. No attachments here, and a screen shot would not be very helpful in this case.

If you would highlight the first few patients names and the numbers you are seeking to extract, that would be very helpful.

Howard

look · Mar 2, 2017

Can not link.

Thanks for your help Howard.

L. Howard · Mar 2, 2017

Okay, try this. There are five columns that contain the data, A, B, C, D, E. I presume all the names to be extracted will be in column is A. I see the red highlighted names.

For the other two number extractions, do this. Since they will be in a single cell of a certain column, post a copy of a few of those cells and tell me what column they are in.

Medicare ICN #: 17040C123177 post the entire cell contents (a few of them) for this number and what is the column.

Patient Control Number: 186613 do the same for this number, along with which column.

I am thinking my macro will still work if I can direct the formulas to the correct column for the extractions.

Also, what columns are free for the macro to extract the names & numbers into? I will need three unused column.

Further, try posting one single patients info/data for each of the five columns. You can use the Cloud icon in the tool bar (left of the # icon) for each column of the one patient, of course, noting which column each is in.

I would look something like this:

The name is in column A

PATIENT - SUBSCRIBER INFORMATION
Patient Name: Patient 13
Corrected Patient/Subscriber Name:
Subscriber Name:
Other Subscr. Name:
REMITTANCE PROCESSING INFORMATION - SERVICE LINE DETAIL
Svc Line #

<tbody>
</tbody><colgroup><col></colgroup>

The Control number is in column C

Patient Control Number: 186537
Group/Policy Id:
Group/Policy Id:
Proc Code - Units

<tbody>
</tbody><colgroup><col></colgroup>

I will continue to parse the data you have already posted for a solution.

Howard

L. Howard · Mar 2, 2017

Before doing all the stuff in my Post #7, try this in a standard module, run from the sheet with the data on it and where the sheet has columns G, H & I free to return the found data.

Howard

Code:

Option Explicit

Sub Data_Find_2()
  Dim lRowCount&
 
  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
  
    Columns("G:I").Select
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Selection.Delete Shift:=xlUp
    Range("G1").Select
    
End Sub

look · Mar 3, 2017

Hi Howard,

Ran the last macro and got #Name? in columns G, H & I

Some consistencies:
The Payer Claim#/Medicare ICN # is always in column A.
The patient name is always in column A, 6 rows down from the Payer Claim#/Medicare ICN # if claim was denied, 5 rows down if the claim was paid
If the claim was paid the patient ID has an actual number like below

Patient Id: A00162406
If the claim was denied the patient ID is shown as
Patient Id: 00000
The patient control number is always in column C on the same row as the patient name and two columns to the right of the patient name.

Thanks for your help.

L. Howard · Mar 3, 2017

When I run my code on your Post #4 data example, I get this info compiled in columns G, H, & I.

Excel 2012

G

H

I

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

Sheet2

Cell Formulas
Range	Formula
G1	Name
G2	Patient 1
G3	Patient 2
G4	Patient 3
G5	Patient 4
G6	Patient 5
G7	Patient 6
G8	Patient 7
G9	Patient 8
G10	Patient 9
G11	Patient 10
G12	Patient 11
G13	Patient 12
G14	Patient 13
G15	Patient 14
G16	Patient 15
H1	Control
H2	186613
H3	186614
H4	186458
H5	186459
H6	186314
H7	186788
H8	186713
H9	186407
H10	186720
H11	186709
H12	186310
H13	186309
H14	186537
H15	186545
H16	186396
I1	INC #
I2	17040C123177
I3	17040C123273
I4	17035C099954
I5	17035C100098
I6	17034C128311
I7	17040C123281
I8	17040C123311
I9	17035C100089
I10	17040C123245
I11	17040C123223
I12	17034C128322
I13	17034C128330
I14	17035C100044
I15	17035C100047
I16	17035C100084

Is this a correct extraction for that data?

Howard

Extracting patient control number and patient names

look

Board Regular

Excel Facts

L. Howard

Well-known Member

look

Board Regular

look

Board Regular

L. Howard

Well-known Member

look

Board Regular

L. Howard

Well-known Member

L. Howard

Well-known Member

look

Board Regular

L. Howard

Well-known Member

Similar threads

Forum statistics

Share this page

Extracting patient control number and patient names

Board Regular

Excel Facts

Well-known Member

Board Regular

Board Regular

Well-known Member

Board Regular

Well-known Member

Well-known Member

Board Regular

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock