Filtering by unique identifiers while perfoming function

T

txdude311

New Member
Joined
Jul 26, 2016
Messages
5
Hello I need some help:[FONT=&quot][/FONT]

I have a spread sheet with dozens of thousands of unique identifies (Subscriber IDs). Each one represents a person.

Some people were billed for a coverage periods and some people's billing for a coverage period was skipped.

Now I have to figure out which people need to be billed for the because previous coverage periods were not billed, according to the current months record (July).

EXAMPLE:


NOTE:
Red shows the discrepancy that a person needs to be billed for
Green represents the journal entry and billing coverage period accounting for each other and not bill needs to be issued.

ADRI's coverage period from 2/1 - 2/29 was billed in June.

June's bill that covered 2/1 - 2/29 coverage period has a July journal entry for said coverage period.

However, the coverage period from 1/1 - 1/31, has a July journal entry, but no 1/1 - 1/31 billing month.

Therefore, ADRI, needs to be billed for coverage period, the 1/1-131.

HERE ARE THE VALUES:
[TABLE="class: x_MsoNormalTable, width: 920"]
<tbody>[TR]
[TD="width: 64"][FONT=&quot]Group ID[/FONT]
[/TD]
[TD="width: 99"][FONT=&quot]Subscriber ID[/FONT]
[/TD]
[TD="width: 75"][FONT=&quot]Last Name[/FONT]
[/TD]
[TD="width: 64"][FONT=&quot]First Name[/FONT]
[/TD]
[TD="width: 79"][FONT=&quot]Coverage Period Start[/FONT]
[/TD]
[TD="width: 85"][FONT=&quot]Coverage Period End[/FONT]
[/TD]
[TD="width: 64"][FONT=&quot]Bill[/FONT]
[/TD]
[TD="width: 64"][FONT=&quot]Bill[/FONT]
[/TD]
[TD="width: 64"][FONT=&quot]Journal Entry[/FONT]
[/TD]
[TD="width: 64"][FONT=&quot] [/FONT]
[/TD]
[TD="width: 64"][FONT=&quot] [/FONT]
[/TD]
[TD="width: 135"][FONT=&quot]Charge or No Charge[/FONT]
[/TD]
[/TR]
[TR]
[TD][FONT=&quot]POD-11-KP\[/FONT]
[/TD]
[TD]
[FONT=&quot]2225836554​
[/FONT]​
[/TD]
[TD][FONT=&quot]SHUNS[/FONT]
[/TD]
[TD][FONT=&quot]ANGI[/FONT]
[/TD]
[TD]
[FONT=&quot]1/1/2016​
[/FONT]​
[/TD]
[TD]
[FONT=&quot]1/31/2016​
[/FONT]​
[/TD]
[TD][FONT=&quot]May[/FONT]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][FONT=&quot]$ 221.91[/FONT]
[/TD]
[TD][/TD]
[TD][FONT=&quot]No Charge[/FONT]
[/TD]
[/TR]
[TR]
[TD][FONT=&quot]POD-11-KP\[/FONT]
[/TD]
[TD]
[FONT=&quot]2225836554​
[/FONT]​
[/TD]
[TD][FONT=&quot]SHUNS[/FONT]
[/TD]
[TD][FONT=&quot]ANGI[/FONT]
[/TD]
[TD]
[FONT=&quot]2/1/2016​
[/FONT]​
[/TD]
[TD]
[FONT=&quot]2/29/2016​
[/FONT]​
[/TD]
[TD][FONT=&quot] [/FONT]
[/TD]
[TD][FONT=&quot]June[/FONT]
[/TD]
[TD][/TD]
[TD][FONT=&quot]$ 776.53[/FONT]
[/TD]
[TD][/TD]
[TD][FONT=&quot]No Charge[/FONT]
[/TD]
[/TR]
[TR]
[TD][FONT=&quot]POD-11-KP\[/FONT]
[/TD]
[TD]
[FONT=&quot]2225836554​
[/FONT]​
[/TD]
[TD][FONT=&quot]SHUNS[/FONT]
[/TD]
[TD][FONT=&quot]ANGI[/FONT]
[/TD]
[TD]
[FONT=&quot]1/1/2016​
[/FONT]​
[/TD]
[TD]
[FONT=&quot]1/31/2016​
[/FONT]​
[/TD]
[TD][FONT=&quot] [/FONT]
[/TD]
[TD][FONT=&quot] [/FONT]
[/TD]
[TD][FONT=&quot]July[/FONT]
[/TD]
[TD][FONT=&quot]$ 758.37[/FONT]
[/TD]
[TD][/TD]
[TD][FONT=&quot]No Charge[/FONT]
[/TD]
[/TR]
[TR]
[TD][FONT=&quot]POD-11-KP\[/FONT]
[/TD]
[TD]
[FONT=&quot]2225836554​
[/FONT]​
[/TD]
[TD][FONT=&quot]SHUNS[/FONT]
[/TD]
[TD][FONT=&quot]ANGI[/FONT]
[/TD]
[TD]
[FONT=&quot]2/1/2016​
[/FONT]​
[/TD]
[TD]
[FONT=&quot]2/29/2016​
[/FONT]​
[/TD]
[TD][FONT=&quot] [/FONT]
[/TD]
[TD][FONT=&quot] [/FONT]
[/TD]
[TD][FONT=&quot]July[/FONT]
[/TD]
[TD][FONT=&quot]$ 245.70[/FONT]
[/TD]
[TD][FONT=&quot] [/FONT]
[/TD]
[TD][FONT=&quot]No Charge[/FONT]
[/TD]
[/TR]
[TR]
[TD][FONT=&quot]POD-11-KP\[/FONT]
[/TD]
[TD]
[FONT=&quot]3522559283​
[/FONT]​
[/TD]
[TD][FONT=&quot]TOTT[/FONT]
[/TD]
[TD][FONT=&quot]TORI[/FONT]
[/TD]
[TD]
[FONT=&quot]2/1/2016​
[/FONT]​
[/TD]
[TD]
[FONT=&quot]2/29/2016​
[/FONT]​
[/TD]
[TD][/TD]
[TD][FONT=&quot]June[/FONT]
[/TD]
[TD][FONT=&quot] [/FONT]
[/TD]
[TD][FONT=&quot]$ 336.84[/FONT]
[/TD]
[TD][/TD]
[TD][FONT=&quot]No Charge[/FONT]
[/TD]
[/TR]
[TR]
[TD][FONT=&quot]POD-11-KP\[/FONT]
[/TD]
[TD]
[FONT=&quot]3522559283​
[/FONT]​
[/TD]
[TD][FONT=&quot]TOTT[/FONT]
[/TD]
[TD][FONT=&quot]TORI[/FONT]
[/TD]
[TD]
[FONT=&quot]2/1/2016​
[/FONT]​
[/TD]
[TD]
[FONT=&quot]2/29/2016​
[/FONT]​
[/TD]
[TD][FONT=&quot] [/FONT]
[/TD]
[TD][FONT=&quot] [/FONT]
[/TD]
[TD][FONT=&quot]July[/FONT]
[/TD]
[TD][FONT=&quot]$ 306.74[/FONT]
[/TD]
[TD][FONT=&quot] [/FONT]
[/TD]
[TD][FONT=&quot]No Charge[/FONT]
[/TD]
[/TR]
[TR]
[TD][FONT=&quot]POD-11-KP\[/FONT]
[/TD]
[TD]
[FONT=&quot]6833582290​
[/FONT]​
[/TD]
[TD][FONT=&quot]SANCH[/FONT]
[/TD]
[TD][FONT=&quot]ADRI[/FONT]
[/TD]
[TD]
[FONT=&quot]2/1/2016​
[/FONT]​
[/TD]
[TD]
[FONT=&quot]2/29/2016​
[/FONT]​
[/TD]
[TD][FONT=&quot] [/FONT]
[/TD]
[TD][FONT=&quot]June[/FONT]
[/TD]
[TD][/TD]
[TD][FONT=&quot]$ 269.71[/FONT]
[/TD]
[TD][/TD]
[TD][FONT=&quot]No Charge[/FONT]
[/TD]
[/TR]
[TR]
[TD][FONT=&quot]POD-11-KP\[/FONT]
[/TD]
[TD]
[FONT=&quot]6833582290​
[/FONT]​
[/TD]
[TD][FONT=&quot]SANCH[/FONT]
[/TD]
[TD][FONT=&quot]ADRI[/FONT]
[/TD]
[TD]
[FONT=&quot]1/1/2016​
[/FONT]​
[/TD]
[TD]
[FONT=&quot]1/31/2016​
[/FONT]​
[/TD]
[TD][FONT=&quot] [/FONT]
[/TD]
[TD][FONT=&quot] [/FONT]
[/TD]
[TD][FONT=&quot]July[/FONT]
[/TD]
[TD][FONT=&quot]$ 770.69[/FONT]
[/TD]
[TD][/TD]
[TD][FONT=&quot]No Charge[/FONT]
[/TD]
[/TR]
[TR]
[TD][FONT=&quot]POD-11-KP\[/FONT]
[/TD]
[TD]
[FONT=&quot]6833582290​
[/FONT]​
[/TD]
[TD][FONT=&quot]SANCH[/FONT]
[/TD]
[TD][FONT=&quot]ADRI[/FONT]
[/TD]
[TD]
[FONT=&quot]2/1/2016​
[/FONT]​
[/TD]
[TD]
[FONT=&quot]2/29/2016​
[/FONT]​
[/TD]
[TD][FONT=&quot] [/FONT]
[/TD]
[TD][FONT=&quot] [/FONT]
[/TD]
[TD][FONT=&quot]July[/FONT]
[/TD]
[TD][FONT=&quot]$ 950.93[/FONT]
[/TD]
[TD][FONT=&quot] [/FONT]
[/TD]
[TD][FONT=&quot]Charge [/FONT]
[/TD]
[/TR]
[TR]
[TD][FONT=&quot]POD-11-KP\[/FONT]
[/TD]
[TD]
[FONT=&quot]6580150312​
[/FONT]​
[/TD]
[TD][FONT=&quot]VIKOOD[/FONT]
[/TD]
[TD][FONT=&quot]SKLER[/FONT]
[/TD]
[TD]
[FONT=&quot]1/1/2016​
[/FONT]​
[/TD]
[TD]
[FONT=&quot]1/31/2016​
[/FONT]​
[/TD]
[TD][FONT=&quot]May[/FONT]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][FONT=&quot]$ 209.49[/FONT]
[/TD]
[TD][/TD]
[TD][FONT=&quot]No Charge[/FONT]
[/TD]
[/TR]
[TR]
[TD][FONT=&quot]POD-11-KP\[/FONT]
[/TD]
[TD]
[FONT=&quot]6580150312​
[/FONT]​
[/TD]
[TD][FONT=&quot]VIKOOD[/FONT]
[/TD]
[TD][FONT=&quot]SKLER[/FONT]
[/TD]
[TD]
[FONT=&quot]2/1/2016​
[/FONT]​
[/TD]
[TD]
[FONT=&quot]2/29/2016​
[/FONT]​
[/TD]
[TD][FONT=&quot] [/FONT]
[/TD]
[TD][FONT=&quot]June[/FONT]
[/TD]
[TD][/TD]
[TD][FONT=&quot]$ 518.66[/FONT]
[/TD]
[TD][/TD]
[TD][FONT=&quot]No Charge[/FONT]
[/TD]
[/TR]
[TR]
[TD][FONT=&quot]POD-11-KP\[/FONT]
[/TD]
[TD]
[FONT=&quot]6580150312​
[/FONT]​
[/TD]
[TD][FONT=&quot]VIKOOD[/FONT]
[/TD]
[TD][FONT=&quot]SKLER[/FONT]
[/TD]
[TD]
[FONT=&quot]1/1/2016​
[/FONT]​
[/TD]
[TD]
[FONT=&quot]1/31/2016​
[/FONT]​
[/TD]
[TD][FONT=&quot] [/FONT]
[/TD]
[TD][FONT=&quot] [/FONT]
[/TD]
[TD][FONT=&quot]July[/FONT]
[/TD]
[TD][FONT=&quot]$ 744.44[/FONT]
[/TD]
[TD][/TD]
[TD][FONT=&quot]No Charge[/FONT]
[/TD]
[/TR]
[TR]
[TD][FONT=&quot]POD-11-KP\[/FONT]
[/TD]
[TD]
[FONT=&quot]6580150312​
[/FONT]​
[/TD]
[TD][FONT=&quot]VIKOOD[/FONT]
[/TD]
[TD][FONT=&quot]SKLER[/FONT]
[/TD]
[TD]
[FONT=&quot]2/1/2016​
[/FONT]​
[/TD]
[TD]
[FONT=&quot]2/29/2016​
[/FONT]​
[/TD]
[TD][/TD]
[TD][FONT=&quot] [/FONT]
[/TD]
[TD][FONT=&quot]July[/FONT]
[/TD]
[TD][FONT=&quot]$ 209.49[/FONT]
[/TD]
[TD][/TD]
[TD][FONT=&quot]No Charge[/FONT]
[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
You must log in or register to reply here.

Similar threads

T
  • Question Question
Using a dynamic formula to calculate value of reinvested dividends over changing time periods
Replies
2
Views
3K
Tuckbox
T
B
  • Question Question
vba code / macro required to merge and add up data
Replies
0
Views
326
bryanrobson
B
D
  • Question Question
Tracking Project Grand Total Impact
Replies
1
Views
415
pbornemeier
pbornemeier
D
  • Question Question
Find the matching details from 2 workbooks compile it in a sheet and then summarize it and put it in different sheet.
Replies
0
Views
381
deba2020
D
W
  • Solved
Need help. I think the OFFSET is the correct function but I don’t have the correct imbedded formulas.
Replies
3
Views
398
Fluff
Fluff

Forum statistics

Threads
1,223,231
Messages
6,170,885
Members
452,364
Latest member
springate

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