Display unqiue text entries in a list based on date criteria

[lookingforhelp1]

Hi there

Please would someone be able to help me with an issue I'm having with my spreadsheet.

My set up is as follows:

a) Data sheet pulled out of independant software into Excel.
b) Front end sheets with a break down of each customer per calender month (start and end date of the month is listed on each front end sheet).

In my front end, I have managed to pull through any unique customer account numbers from the data source with the following array:

Please Login or Register  to view this content.

However, an issue I seem to have stumbled accross is that we may not necessarily provide a service to the exact same customer base each month. For example, we could go 4 months and not provide a service to customer A, but it would still show up on each months report.

So, rather than list each unique customer from the data source, is there a way to only show if the customer received our services if it falls between a start and end date (the date of service is listed in my data source).

I'd rather stay away from pivot tables as there are several tables on the report where I would have to change the date range to show the correct month (On the back of this, I've also used VBA historically to update pivot table ranges but I don't feel spreadsheet run as smoothly from an operational point of view by doing this - Perhaps this is just the spec of my computer).

Thanks in advance!

[Aardigspook]

Without seeing an example of the way your file is structured, it's difficult to be sure, but I think what you need is to combine your existing lookup with one which looks up the date of service, using something like MONTH(servicedate)=MONTH(reportmonthdate) within a COUNTIF. Normally you could combine such functions using AND(condition1,condition2) but AND doesn't work in array formulae - instead you need to use (condition1)*(condition2).

I've never tried combining two COUNTIFs and MONTH in an array, so the following formula might be completely wrong - but hopefully it's a starting point:
{=INDEX(table_name[Job_Account],MATCH(0,(COUNTIF($A$34:A34,table_name[Job_Account])*COUNTIF((MONTH(columnwithdates)=MONTH(cellcontainingreportdate))),0))}

If I'm completely off with this, hopefully someone here more knowledgeable with arrays will correct it for you!

Regards,
Aardigspook

If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark Thread as Solved'.
You don't need to give me rep if I helped, but thanks are appreciated.

[lookingforhelp1]

Thank you for having a go with this. It seems to be missing part of the syntax but I couldn't figure out where though.

After posting this yesterday I managed to half get the result I require, this formula being:

- K1 being the start of the list I want to populate (although this is just on a test sheet right now), and B1 being the 1st August 2015 - Something I liked in your suggestion was using the MONTH function so I tried to incorporate that into the formula in place of $B$1 but still couldn't get this to work.

Please Login or Register  to view this content.

The above lists every customer on the 1st August 2015, but if I could get the MONTH function to work, this may just hit the nail on the head and work!

Without seeing an example of the way your file is structured, it's difficult to be sure, but I think what you need is to combine your existing lookup with one which looks up the date of service, using something like MONTH(servicedate)=MONTH(reportmonthdate) within a COUNTIF. Normally you could combine such functions using AND(condition1,condition2) but AND doesn't work in array formulae - instead you need to use (condition1)*(condition2).

I've never tried combining two COUNTIFs and MONTH in an array, so the following formula might be completely wrong - but hopefully it's a starting point:
{=INDEX(table_name[Job_Account],MATCH(0,(COUNTIF($A$34:A34,table_name[Job_Account])*COUNTIF((MONTH(columnwithdates)=MONTH(cellcontainingreportdate))),0))}

If I'm completely off with this, hopefully someone here more knowledgeable with arrays will correct it for you!

Regards,
Aardigspook

If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark Thread as Solved'.
You don't need to give me rep if I helped, but thanks are appreciated.

[Glenn Kennedy]

Please attach a sample workbook. Make sure there is enough data to demonstrate your need. Make sure your desired results are shown, mock them up manually if necessary. Remember to remove ALL confidential information first!!!

Click on GO ADVANCED and use the paperclip icon to open the upload window.

The paperclip icon

[lookingforhelp1]

I've attached basic sample data attached to my first post.

As customer1 in the core data didnt received any service in August, I don't want this to appear in the list.

Customer 3 also appears twice in my core data, and I only want this to appear once in the populated list on the front end.

Please attach a sample workbook. Make sure there is enough data to demonstrate your need. Make sure your desired results are shown, mock them up manually if necessary. Remember to remove ALL confidential information first!!!

Click on GO ADVANCED and use the paperclip icon to open the upload window.

The paperclip icon

[Glenn Kennedy]

Paste this into B2, drag across and down:

Please Login or Register  to view this content.

[lookingforhelp1]

Hi, thanks but it's the customer list I want to populate. Sorry if this wasn't too clear in the sample workbook.



Edit - I solved this by using a workaround.

In my core data, I added an extra column to pull the month number of the date.

I then used my original formula (the one I used in which the customer base displayed for the 1st August only) and changed this to look at the Month Number column instead and then match the criteria to the current month number.

Works perfect for now, although probably not as elegant as it could be.

Thanks for everyones input.

[Aardigspook]

Glad you got it working and that I was able to help - even if only a little.

Regards,
Aardigspook

If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark Thread as Solved'.
You don't need to give me rep if I helped, but thanks are appreciated.