10-21-2010, 09:50 AM
|
#1
|
Franchise Player
Join Date: Apr 2008
Location: Calgary
|
Excel Formula Help
Alright, here is what I need to do. I am not sure if Excel can do this but I need to do the following:
I have a column of purchase order numbers. Most of the numbers are unique but some are duplicates. Associated with these PO numbers is the status of an assignment. I need to determine how many unique PO numbers are associated with open assignments. I do not want excel to count the duplicates. Example below:
PO Number Status
A100 Open
A101 Closed
A101 Open
A200 Closed
A201 Open
A201 Open
A300 Closed
A400 Open
I need excel to report to me that Total Open POs = 4.
I don't know if there is a way to tally the total number of unique POs (i.e. not count the duplication of A101 and A201, tally each as one) and combine this with the use of the =countif() function.
Let me know if I need to provide any more info.
|
|
|
10-21-2010, 11:40 AM
|
#2
|
Franchise Player
Join Date: Jul 2009
Location: Calgary
|
At first glance I think you need to set it up with lookup tables... but I'd have to play with it to figure it out past that.
|
|
|
The Following User Says Thank You to Icon For This Useful Post:
|
|
10-21-2010, 11:56 AM
|
#3
|
Join Date: May 2004
Location: @robdashjamieson
|
If you can, have the A#'s in Column A, and the Open/Close in Column B.
In column C, insert this formula: =IF(A3=A2, 0, IF(B2="Open", 1, 0)), then go fill down to the cells below that you need a result.
Then do a =Sum(C2:C9) in C10.
Result should be 4.
PM me if you need me to email it to you.
__________________
Last edited by Prototype; 10-21-2010 at 01:07 PM.
|
|
|
The Following User Says Thank You to Prototype For This Useful Post:
|
|
10-21-2010, 01:19 PM
|
#4
|
Franchise Player
Join Date: Jul 2009
Location: Calgary
|
Quote:
Originally Posted by fotze
Pivottable will do that in seconds.
Highlight the data, including the Column Names
insert pivottable,
use that selection,
Click OK.
Drag the PO Field to the ROW LABELS Box
Drag the OpenClosed Field to the Column labels box
Drag the OpenClosed Field to the Values box.
Done.
|
Thats what I was getting at but haven't made one in ages... and yeah, not lookuptable, pivottable.
|
|
|
10-21-2010, 01:19 PM
|
#5
|
Franchise Player
Join Date: Apr 2008
Location: Calgary
|
Quote:
Originally Posted by Prototype
If you can, have the A#'s in Column A, and the Open/Close in Column B.
In column C, insert this formula: =IF(A3=A2, 0, IF(B2="Open", 1, 0)), then go fill down to the cells below that you need a result.
Then do a =Sum(C2:C9) in C10.
Result should be 4.
PM me if you need me to email it to you.
|
So that will count the first occurrence of a value but none of the following if they are the same as long as the same values are one after the other?
Last edited by Boblobla; 10-21-2010 at 01:32 PM.
|
|
|
10-21-2010, 01:29 PM
|
#6
|
Franchise Player
|
Quote:
Originally Posted by Prototype
If you can, have the A#'s in Column A, and the Open/Close in Column B.
In column C, insert this formula: =IF(A3=A2, 0, IF(B2="Open", 1, 0)), then go fill down to the cells below that you need a result.
Then do a =Sum(C2:C9) in C10.
Result should be 4.
PM me if you need me to email it to you.
|
i think in the limited example shown above, this formula works fine - however i think if you had 3000 entries that were not in any type of order this formula may not yield the result the op is looking for.
to me a pivot table is the way to go - although they can be cumbersome.....my initial thought was to insert a blank column and then concatenate clolumn A & B and then do something off of that - but perhaps the PTable is easier.
|
|
|
10-21-2010, 01:34 PM
|
#7
|
Join Date: May 2004
Location: @robdashjamieson
|
I've never used pivot table, I'm more familiar with formula.
And yes, it will work in a limited batch, much like you've shown. But if you've got records in the hundreds of thousands... stay away from formulas.
Going to have to play around with this pivot table thing.
__________________
|
|
|
10-21-2010, 01:37 PM
|
#8
|
Franchise Player
Join Date: Apr 2008
Location: Calgary
|
Quote:
Originally Posted by fotze
Pivottable will do that in seconds.
Highlight the data, including the Column Names
insert pivottable,
use that selection,
Click OK.
Drag the PO Field to the ROW LABELS Box
Drag the OpenClosed Field to the Column labels box
Drag the OpenClosed Field to the Values box.
Done.
|
This doesn't remove the duplication, unless I am doing something wrong.
If I have 2 opens under the same PO number, it puts a 2 in the pivottable, not a 1. I need to eliminate any doubles.
I need to report on how many assignments are open, which I can do using countif(), and how many POs have open assignments. In the past I have just counted the lines on the spreadsheet but that is a big PITA.
|
|
|
10-21-2010, 04:12 PM
|
#9
|
Powerplay Quarterback
|
If you're comfortable, write a function to do this in VBA. Should be pretty easy.
|
|
|
10-21-2010, 08:10 PM
|
#10
|
First Line Centre
Join Date: Apr 2006
Location: Calgary
|
Okay, I think I've figured this out. However, it's not super robust.
I have A1:Ax being your PO numbers, B1:Bx being status, and a third column which will count.
In column C, I have this formula for row #3:
=(IF(COUNTIF(A3:$A$x,A3)=1,1,0))*(IF(B3="Open",1,0 ))
Drag it down, and sum the result.
The concept is that the first part will check the current row's value against anything below it. If it finds a duplicate, it will set it to zero. If it isn't a duplicate it'll set to 1. This means that only the very last non-unique value will get counted. The second part checks for whether it's open or closed. So even a unique PO number that is closed should return a zero.
My main issue is that you need to set the last row before hand. I guess you could set it to A65000 or something, but that makes countif really really slow.
Edit: Crap. One problem with this is that if the last non-unique value is "Closed", while it was "open" above it, then it'll miss it. I'll think on this further.
Last edited by Regorium; 10-21-2010 at 08:16 PM.
|
|
|
10-21-2010, 08:35 PM
|
#11
|
First Line Centre
Join Date: Apr 2006
Location: Calgary
|
Quote:
Originally Posted by Boblobla
So that will count the first occurrence of a value but none of the following if they are the same as long as the same values are one after the other?
|
That formula will count the last value if all the values are together. It runs into the same problem as the solution I posted - if your data set looks like this:
A101 Open
A101 Open
A101 Closed
A201 Open
It'll miss A101, since the if statement will be completed when the PO# is closed.
Last edited by Regorium; 10-21-2010 at 08:37 PM.
|
|
|
10-21-2010, 09:06 PM
|
#12
|
Franchise Player
|
Maybe still a pita but try the pivot table to give you the opens and closed by invoice number, and then use count in the column instead of summing them up.
I've found this forum handy in the past if you are still stumped.
http://www.mrexcel.com/forum/index.php
|
|
|
10-22-2010, 11:12 AM
|
#13
|
Franchise Player
Join Date: Apr 2008
Location: Calgary
|
Thanks for the help everyone, I will play around with a few of these.
|
|
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
All times are GMT -6. The time now is 03:16 PM.
|
|