Identifying US and International purchases?

[brotherwo]

I am suppose to make a pivot table of GP by product line and by US or International Countries. What is the easiest way to do this? I was thinking of creating a column and doing some kind of IF statement asking if the delivery state is two letters than it is US, if not it is international.

What do yuo guys think ?
Thanks!

[brotherwo]

bump.. anyone have an idea?
Thanks

[JBeaucaire]

You will definitely have to create a column to flag each row in some way as US or Intl, so your idea is as good as any other if it works for you.

[brotherwo]

You will definitely have to create a column to flag each row in some way as US or Intl, so your idea is as good as any other if it works for you.

what function do you use to identify a variety of US state codes? Is there one you can use just to identify a 2 character word?

[MarvinP]

Hi brotherwo,
A sample of your data would be good, but,

If in Column F are values like AZ, CA, Eng, WA, AUS...
A formula to capture only 2 letter countries would be =If(Len("F2") =2,"USA","NotUSA")

I hope that helps.

[brotherwo]

Hi brotherwo,
A sample of your data would be good, but,

If in Column F are values like AZ, CA, Eng, WA, AUS...
A formula to capture only 2 letter countries would be =If(Len("F2") =2,"USA","NotUSA")

I hope that helps.

Thats the idea! but it's not working Here is my workbook example

[JBeaucaire]

If you were to list all the "codes" that stand for the US in a column of their own, perhaps column T, then this formula would work dynamically...as you edit/change the values in column T the formula would change all the flags for all rows.

In B2 to test the code in A2 against column T:

=IF(ISNUMBER(MATCH($A2, $T:$T, 0)), "US", "INTL")

[brotherwo]

If you were to list all the "codes" that stand for the US in a column of their own, perhaps column T, then this formula would work dynamically...as you edit/change the values in column T the formula would change all the flags for all rows.

In B2 to test the code in A2 against column T:

=IF(ISNUMBER(MATCH($A2, $T:$T, 0)), "US", "INTL")

Thanks, that worked! Im learning a lot from this forum so far and promise to contribute more as my skills develop.

[JBeaucaire]

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.