formula or macro to filter by 3 criterias

[jw01]

hello

im struggling to filter by 3 criterias
1) user name = cell D9 (missed account sheet)
2) date = D11 (month + billing cycle) ....billing cycle is 1st, 7th, 15th, 22nd of each month
3) accounts missed = anything that is "-" in the respective column in master sheet ("-" = missed)


my "master" sheet contains the raw data.
a) column G = billing cycle (1st, 7th, 15th, 22nd)
b) column C = user name
c) row 7 = month listed (jan-dec) i.e. January = column O
d) cell O7 down = billed revenue for January. I want to only show "-"


in my "missed account" sheet, i want to show show by the 3 criterias listed above. so show only the accounts that are by user name, by month + billing cycle and by "-".

for example, if i select "test1" as USER; January 22nd as DATE
- i want to show the account name, "-" aka missed revenue for Jan 22nd cycle, and simply show the values from Feb-March for the accounts

im trying to get a list of all the missed billed revenue by month.

i have attached a sample , if someone can pls help. ive been struggling with this for 3 days now!

thx u

[jw01]

any thoughts on this anyone?

[arlu1201]

If Jan 22nd is selected, why should Feb-march data be shown?

Also, it would have been easier if your monthly data was one below the other instead of side by side. It will make filtering easier. Is it possible for you to reformat the sheet?

[jw01]

hello

good question and sorry for confusion.

basically, if jan 22nd is selected

it shows:
company XZY; Jan 22nd (jan column on missed account sheet) revenue = "-", but they might have revenue for feb-dec, so it will show the amounts shown for that account for the other months.

does that make sense?

[arlu1201]

Sure.

Also, it would have been easier if your monthly data was one below the other instead of side by side. It will make filtering easier. Is it possible for you to reformat the sheet?

[jw01]

hello

so you mean,
Company Name January
XYZ "-"
Febuary
$200?

Is that the format? pls let me know.

i would prefer the data per my sheet as it makes it easier for your eyes to follow any patterns, but pls let me know if the otherway is easier?

[Richard Buttrey]

Criteria cells need to have their values underneath their labels. As Arlette has suggested you need to change your criteria - and output layout

[jw01]

sure thing....Arlette u can change the layout if that makes more sense. pls advise. thank you.

[arlu1201]

It will make it simpler though you may feel data being repeated specially columns C to G.

The format i am suggesting is -
Columns C to K remain where they are.
Below that you put data for Feb, then for March, etc.

If you format the 2 date columns with the required date formats (and not general as it is now), we can easily derive the month out of it. You do not need to have a separate column stating the month then.

[jw01]

i would like to change it but that is how i get the data in reporting format....so i guess i will have to create some dummy columns that concatenate billing cycle with month i.e. January&" "G9 etc

what do you think?

[arlu1201]

Ok, maybe you can change the format of the date columns to a date format?

If its too manual, we can get that automated first and then move on to the rest of the sheet.

[jw01]

hello arlette

that is relatively manual and this file has taken me longer than i thought/my boss.

any other way as ive been trying to achieve it to work for past 5 hours now.

i tried this formula in cell "master sheet" cell D15 down and copied it across
{=IFERROR(INDEX(INDIRECT("Master!"&D$6&":"&D$6),SMALL(IF(((INDEX(_Data,0,1))='Missed Accounts'!$D$9)*((INDEX(_Data,0,9))=" - ")*((INDEX(_Data,0,5))=RIGHT($D$11,LEN($D$11)-SEARCH(" ",$D$11))),ROW((INDEX(_Data,0,9))),""),ROW()-14)),"")}

it works but the issue is...it does not refresh the data based on the month + billing cycle selected. for example, if i select may 22nd as my date, i want the may column to show all "-", all other columns to show the data pertaining to the "-" in may

any thoughts or is there an easier way? thx u sir.

[jw01]

any input on this one?

[Richard Buttrey]

Hi,

As suggested you need to change your layout. There was a typo in my original. I should have said you need to change your Master tab layout. Until you do that you'll never be able to easily analyse your data

Change so that you have the following column headers. Your data will of course be deeper since the main month dates have a column of their own and are not spread across the columns with the sub headers repeated.

BA Name
Sold To No.
NA Code
XX
Billing Day
Excel date numbers. (i.e. January 1, February 1st, etc. as in row 7)
Final Pre Audit Date
Date submitted to Client
800 No.
Invoice Amount