Calgarypuck Forums - The Unofficial Calgary Flames Fan Community
Old 10-21-2010, 09:50 AM   #1
Boblobla
Franchise Player
 
Boblobla's Avatar
 
Join Date: Apr 2008
Location: Calgary
Exp:
Default 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.
Boblobla is offline   Reply With Quote
Old 10-21-2010, 11:40 AM   #2
Icon
Franchise Player
 
Icon's Avatar
 
Join Date: Jul 2009
Location: Calgary
Exp:
Default

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.
Icon is offline   Reply With Quote
The Following User Says Thank You to Icon For This Useful Post:
Old 10-21-2010, 11:56 AM   #3
Prototype
 
Prototype's Avatar
 
Join Date: May 2004
Location: @robdashjamieson
Exp:
Default

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.
Prototype is offline   Reply With Quote
The Following User Says Thank You to Prototype For This Useful Post:
Old 10-21-2010, 01:19 PM   #4
Icon
Franchise Player
 
Icon's Avatar
 
Join Date: Jul 2009
Location: Calgary
Exp:
Default

Quote:
Originally Posted by fotze View Post
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.
Icon is offline   Reply With Quote
Old 10-21-2010, 01:19 PM   #5
Boblobla
Franchise Player
 
Boblobla's Avatar
 
Join Date: Apr 2008
Location: Calgary
Exp:
Default

Quote:
Originally Posted by Prototype View Post
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.
Boblobla is offline   Reply With Quote
Old 10-21-2010, 01:29 PM   #6
Northendzone
Franchise Player
 
Northendzone's Avatar
 
Join Date: Aug 2009
Exp:
Default

Quote:
Originally Posted by Prototype View Post
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.
Northendzone is offline   Reply With Quote
Old 10-21-2010, 01:34 PM   #7
Prototype
 
Prototype's Avatar
 
Join Date: May 2004
Location: @robdashjamieson
Exp:
Default

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.
__________________
Prototype is offline   Reply With Quote
Old 10-21-2010, 01:37 PM   #8
Boblobla
Franchise Player
 
Boblobla's Avatar
 
Join Date: Apr 2008
Location: Calgary
Exp:
Default

Quote:
Originally Posted by fotze View Post
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.
Boblobla is offline   Reply With Quote
Old 10-21-2010, 04:12 PM   #9
yads
Powerplay Quarterback
 
Join Date: Apr 2008
Exp:
Default

If you're comfortable, write a function to do this in VBA. Should be pretty easy.
yads is offline   Reply With Quote
Old 10-21-2010, 08:10 PM   #10
Regorium
First Line Centre
 
Join Date: Apr 2006
Location: Calgary
Exp:
Default

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.
Regorium is offline   Reply With Quote
Old 10-21-2010, 08:35 PM   #11
Regorium
First Line Centre
 
Join Date: Apr 2006
Location: Calgary
Exp:
Default

Quote:
Originally Posted by Boblobla View Post
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.
Regorium is offline   Reply With Quote
Old 10-21-2010, 09:06 PM   #12
Bend it like Bourgeois
Franchise Player
 
Join Date: Oct 2001
Exp:
Default

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
Bend it like Bourgeois is offline   Reply With Quote
Old 10-22-2010, 11:12 AM   #13
Boblobla
Franchise Player
 
Boblobla's Avatar
 
Join Date: Apr 2008
Location: Calgary
Exp:
Default

Thanks for the help everyone, I will play around with a few of these.
Boblobla is offline   Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT -6. The time now is 03:19 AM.

Calgary Flames
2023-24




Powered by vBulletin® Version 3.8.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright Calgarypuck 2021