Need to sort range of values so it only selects one date to the range

[sparksy1981]

Hi in need of same desperate help, have spent the last 4 to five hours trying to find a solution and hoping someone out there can make some magic for me.

as per attached spreedsheet to reference to.

Sheet 1 (2) is my data range in column A are a list of the POs i have outstanding, as you can see the same number appears more than once. In column J is the required delivery date.

Sheet Graph - i have created a graph to capture the % that are outstanding by how many days, however it captures multiple lines for the same PO# mutliple times when the delivery date in column j is the same, it only needs to capture the number once. It is distorting the actual figures.

Sheet Graph Data - is how im going about getting the data to the graph, basically i need to know how i can get it to only count one of the multiple PO# in sheet 1 (2) and the due date instead at the moment it counts the same po # multiple times instead of just once. As you can see in sheet 1 (2) column T i only have 141 purchase orders in total, so the graph needs to show the same and the depending on the due date for the 141 po's

note - Sheet 1(2) is constantly changing from day to day in regards to the POnum etc etc and it is imported from maximo so cant chnge much on sheet1(2)

i hope someone can help me with this it would be greatly appreciated

[FDibbins]

Hi and welcome to the forum

Try this little trick. In AD7 enter this and copy it down...
=IF(COUNTIF($A$7:A7,A7)=1,J7,"")
Then on your GRAPH DATA sheet, reference AD instead of J

[sparksy1981]

thanks for your reply fdibbins, trying now god i hope you have nailed this one for me

[FDibbins]

I am adding a helper column in AD. Copy that formula to AD7, and then copy it down as far as you need it

[sparksy1981]

it worked a treat your a super star, quick question though think im doing something wrong in another area if you look at sheet1 (2) - T1 i get 141 in total purchase orders and in sheet graph data J11 i get 142 in total . i cant believe how quick you did that, if only i had this knowledge

[FDibbins]

Now that you have that helper column (which you can hide if you want by the way) it makes you count much easier....

T1 =COUNT(AD7:AD373)
for LOCAL...=COUNTIFS(AD7:AD373,">"&0,D7:D373,"LOCAL")
for HOUSTON...=COUNTIFS(AD7:AD373,">"&0,D7:D373,"HOUSTON")

[sparksy1981]

thanks you must have been reading my mind, were do i gain skills like yours? quick question how hard would it be to make separate graphs like sheet graph that showed the percentage of houston to promised date and the percentage of local to promised date?

[FDibbins]

see if the attached is what you want? I just copied the existing chart and changed the range, adjust as needed
I changed your sumproduct() to countifS()

Also, I would suggest that you modify your profile to indicate which version of excel you are using - "latest excel" may be fine for now, but it could change over time?

[sparksy1981]

perfect mate, thanks very much for your help with this greatly appreciated.

as requested updated mate

thanks again works a treat

[FDibbins]

Happy to help.

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.