Calendar array to return unique results based on date and PO number

jdefont

New member
Joined
Oct 7, 2016
Messages
5
Reaction score
0
Points
0
I am trying to modify the below array to determine if a date and PO number have already been returned as a result for a date then skip to the next PO. I'm using named ranges (CalList, PO_Number) when referencing the table holding all the data.

=IFERROR(INDEX(CalList,SMALL(IF(Date=A$8,ROW(Date)),ROW(1:1))

In the screen capture "Garb" is showing up 3 times and "Stance" 5 times, and I'm looking for the array formula to determine that PO and Date has already been used in the calendar so skip to the next matching date and PO.

website: i.stack.imgur.com/Y2CsV.jpg

I'm getting an error message but this formula seems closer to what I am trying to accomplish
=IFERROR(INDEX(CalList,SMALL(IF(Date=A$8,IF(MATCH(A$9:A$18&A$8, Date&A$8,0)=ROW(Date),ROW(1:1)))
 
Attach an Excel file; a single sheet is probably all that's necessary, you can't expect helpers to spend time reproducing your sheet from a picture and guessing (probably wrongly) what's in it. We have no idea what the named ranges CalList and PO_Number really are.
 
Cross posted without links:
http://stackoverflow.com/questions/...rn-unique-results-based-on-date-and-po-number
http://answers.microsoft.com/en-us/...n/1ba116c0-4565-4102-b1de-6df56d5723f7?auth=1
http://www.mrexcel.com/forum/excel-...turn-unique-results-based-date-po-number.html


jdefont, for your information, you should always provide links to your cross posts.
This is a requirement, not just a request.
If you have cross posted at other places, please add links to them too.
Why? Have a read of http://www.excelguru.ca/content.php?184
 
Last edited:
Unfortunately this file has cells which refer to a file and path and sheet such as C:\jdefont\Documents\Financials\[2016 Inventory Plan Template - Golf Shop 10_6.xlsm]POs'!F127 which doesn't exist on my machine. Copy the original file by saving as something different then delete the sheets you don't want to make public (rather than copying the sheets to a new workbook).
I won't be in front of a machine myself with the appropriate version of Excel until Monday or Tuesday.
 
OK here is the document with all other tabs removed so you wont get the linking issue to the original document. I had to consolidate the number of rows to fit within the sites size restriction. Its a substantially bigger document. Thanks for looking into my problem.

View attachment calendar 2.xlsm
 
The formula required to do this would be complex - very.
In the attached I have:
1. added a column Z at Z122 of sheet POs with a header which is the concatenated data you want to appear in the calendar.
2. added some more rows of data to the same table with a different date for testing.
3. added a VERY SIMPLE pivot table at cell J2 of sheet Calendar List (this aims to replace your existing table on that sheet)
4. added a line of code in the sheet POs's code-module to refresh the pivot table on sheet Calendar List when that sheet (POs) is deactivated (moved away from).
4. changed the formulae in cells A9:B18 of sheet Calendar.

Note that the formulae currently refer to ranges which may be too small to accommodate your real ranges. Adjust.
Likewise, the Pivot table source data may be too small for your real data. Adjust.
 

Attachments

  • excelguru6822calendar 2.xlsm
    71 KB · Views: 36
I'm traveling today but will look at it 1st thing tomorrow. The concept sounds correct to solve the problem. The PO tab has several other features I needed to remove to upload to you. I suspect I can retrofit from this point to the master document. Appreciate your work and I'll report back tomorrow.
 
The pivot table with refresh macro's did the trick. I modified the range to fit my document and made a few minor tweaks. Appreciate your help, extremely grateful!
 
Back
Top