-1

At the bottom of every hour I receive an excel spreadsheet from an AS400 that shows the various counts of orders and customer info. What I would Like to do is take just the Customer Purchase Order Number and the Customer Number and list/graph which ones appear > 1x. What kind of formula can I use to make this happen? Your input will be greatly appreciated. Thanks in advance!!

Greg
  • 21
  • 4

1 Answers1

2

You could use a formula like this and filter for "Y" and "N" or even create a pivot chart for "Y" and "N":

enter image description here

Which gives these results:

enter image description here

Macness
  • 1,226
  • 2
  • 13
  • 24
  • 1
    Thanks for the quick response! Why do you concatenate the Customer PO# and Customer #? Also, The Customer PO # isn't always a number, but some group of characters that represent the customer..an example is PCO123456 – Greg Apr 04 '14 at 16:28
  • I concat the columns because of the formula used in the last column. You could make an array function that would do this for you, but that would slow down your worksheet a lot if you have a lot of rows. You can always hide that extra column. It would still work if you customer po# is mix of numbers/characters. – Macness Apr 04 '14 at 17:15
  • Is there a way to break out customer PO # and customer # into different columns? – Greg Apr 04 '14 at 20:38
  • Not sure what you mean @Greg. They are broken out in my example: the first two columns. – Macness Apr 04 '14 at 23:03
  • So basically we want to see which customer PO and customer #'s are appearing more than once. I created a pivot table for what you gave me above, but I was wondering if there was a way to show just the two columns that have "N" rather than the entire list? – Greg Apr 05 '14 at 17:30
  • If you use a filter you can filter for just N and sort your columns. If you move them into a pivot table you can even just get the unique values by moving the data in columns A and B into a row section (in the pivot table) and then dragging the last column (Y and N) into the data section to get a count for each of those. Heck you could just drag the concatenated column into pivot table to get a complete accurate count of each. – Macness Apr 05 '14 at 21:34