Item order within its area

[leramone]

pan.jpg

Hello everybody! o/

I'm trying to figure out a way to create a column with the value of the item considering its "area" (the image can explain what this 'area' means for), from a pivot table, in this format:


The column; What it REALLY means (the order)
1.1.1 - 1st area; 1st state of 1st area; 1st unitsSold of 1st state of 1st area
1.1.2 - 1st area; 1st state of 1st area; 2nd unitsSold of 1st state of 1st area
1.2.1 - 1st area; 2nd state of 1st area; 1st unitsSold of 2nd state of 1st area
1.3.1 - 1st area; 3rd state of 1st area; 1st unitsSold of 3rd state of 1st area
1.3.2 - 1st area; 3rd state of 1st area; 2nd unitsSold of 3rd state of 1st area
1.3.3 - 1st area; 3rd state of 1st area; 3rd unitsSold of 3rd state of 1st area
1.3.4 - 1st area; 3rd state of 1st area; 4th unitsSold of 3rd state of 1st area
2.1.1 - 2nd area; 1st state of 2nd area; 1st unitsSold of 1st state of 2nd area
2.2.1 - 2nd area; 2nd state of 2nd area; 1st unitsSold of 2nd state of 2nd area
2.2.2 - 2nd area; 2nd state of 2nd area; 2nd unitsSold of 2nd state of 2nd area
3.1.1 - 3rd area; 1st state of 3rd area; 1st unitsSold of 1st state of 3rd area
3.1.2 - 3rd area; 1st state of 3rd area; 2nd unitsSold of 1st state of 3rd area
3.2.1 - 3rd area; 2nd state of 3rd area; 1st unitsSold of 2nd state of 3rd area


Some of you have already stuck with something like this?

I have already tried a lot o COUNTIF like, but none of them solved; some got really close!


Tks!

[leramone]

example.xlsx


Here is the spreadsheet with the example I need.

I think its more clear now...

[shg]

If you first duplicate the information in columns J and K, it's straightforward:

	J	K	L	M	N	O	P	Q
3	Region	State	Units		Region	State	Units
4	North	Kansas	264		1	1	1	N4: =IF(J4=J3, N3, SUM(N3,1))
5	North	Kansas	182		1	1	2	O4: =IF(J4<>J3, 1, IF(K4=K3, O3, SUM(O3,1)))
6	North	Kansas	192		1	1	3	P4: =IF(K4<>K3, 1, SUM(P3,1))
7	North	Kansas	317		1	1	4
8	North	Kansas	79		1	1	5
9	North	Maine	207		1	2	1
10	North	Maine	159		1	2	2
11	North	Maine	265		1	2	3
12	North	Maine	148		1	2	4
13	West	Arizona	94		2	1	1
14	West	Arizona	194		2	1	2
15	West	Arizona	234		2	1	3
16	West	Arizona	316		2	1	4
17	West	Nevada	26		2	2	1
18	West	Nevada	52		2	2	2
19	West	Nevada	212		2	2	3
20	West	Colorado	34		2	3	1
21	West	Colorado	51		2	3	2

[leramone]

OMGGGG!!!!!!!


IT WORKED!!!!!!!

\o/


I'm so grateful shg !

i'd like to reward you someway!

[shg]

You're welcome.

Send me a pound of 20's.

[leramone]

I will!

I'll creat an PayPal account and transfer it to you, or oder way you prefer!

anyway, my skype is "leandro.1eandro" we can keep in touch there!

Again, THANKS A LOT!

=D