# Help: match date values between start/finish dates, then pull data from non-adjacent cells

1. ## Help: match date values between start/finish dates, then pull data from non-adjacent cells

I have included an example spreadsheet.
In it I have a list color orders in column A, and the dates those orders are due in column B.
There are multiple orders for the same color.
I want a way to count and list all the color orders, not including duplicate color matches, that are due in the next 7 days, and in the next 30 days.

I came up with a formula to count just the dates that fall between a start and finish date, but it's not smart enough to know of duplicate color entries.
Like in my example file, there should only be 15 unique colors with orders due in the next 7 days, but because of multiple same color orders, it lists 18. Likewise, there should only be 6 unique colors with orders in the next 30 days, but it lists 7.

I was thinking it would be better to just pull the unique colors with in the time period to a list, then have a separate formula count the resulting list.
But this is what I can not figure out how to do. Help me Gurus of the Excel formulas

P.S. The dates in column B are not in an Excel recognized date format, so I have a second column (column C) with formulas to convert the text into Excel recognized data values.

2. ## Re: Help: match date values between start/finish dates, then pull data from non-adjacent c

You could put this formula in D2:

=IF(AND(C2>=\$E\$2,C2<=\$F\$2,COUNTIFS(A\$2:A2,A2)=1),1,"")

and copy it down. when you sum the resulting 1's you will get 15. If you change the \$F\$2 in the middle to \$G\$2 and copy that down (maybe in a different column), then you get a result of 21.

Hope this helps.

Pete

3. ## Re: Help: match date values between start/finish dates, then pull data from non-adjacent c

Hi JohnSkinner. Welcome to the forum.

If you would still like a list of unique colors in addition to the unique counts insert a new column E. Then try a variation on Pete's formula in D2 and fill across column E. You will be able to count as well as reference the numbers for the formula to list the colors.

Formula:
`Please Login or Register  to view this content.`
Then in D1:E1 count those row numbers
Formula:
`Please Login or Register  to view this content.`

Then in the new G7 enter this, fill down and across column H until you get blanks.
Formula:
`Please Login or Register  to view this content.`

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1