Grouping Logic Baed on Qty

[jarett]

I have posted this in another forum and have not received an answer so trying in here.
Grouping Logic Based on Qty

I have a set a data that is sorted to have a C- item code to be the first line of the PurchaseOrder# then all the items that go with the C- item right below it. I need to figure out how I can make the C- item line and the items that go with it have a "group 1 number", this would be another column/field. Any items not falling under this would be "group 2". In the example attached "PurchaseOrder"=0002151 would only have 1 group because the C- item is a qty of 1 and there is only 1 item below it. On "PurchaseOrderNo"=0002172 would have 2 groups, C-ACTLIN01 and the 24 items below it would be group 2 and the 3 lines above the C-ACTLIN01 would be group 1. Any ideas on best way to do this?

 

[Eric W]

I'm still a little hazy on the requirements, but try:

Book1
	B	C	D	E
1	PurchaseOrder	Quantity	ItemCode	GroupNo
2	2140		P-INDM10	Group 1
3	2141		x	Group 1
4	2144		C-ACTTAL03
5	2144		y	Group 1
6	2151		C-ACTDAN03
7	2151		z	Group 1
8	2153		C-ACTDAN04
9	2153		a	Group 1
10	2157		C-ACTDAN02
11	2157		b	Group 1
12	2157		C-ACTDAN04
13	2157		c	Group 2
14	2172		d	Group 1
15	2172		e	Group 1
16	2172		f	Group 1
17	2172		C-ACTLIN01
18	2172		g	Group 2
19	2172		h	Group 2
20	2172		i	Group 2
21	2172		j	Group 2
22	2172		k	Group 2
23	2172		l	Group 2
24	2172		m	Group 2
25	2172		n	Group 2
26	2172		o	Group 2
27	2172		p	Group 2
28	2172		q	Group 2
Sheet14

Cell Formulas
Range	Formula
E2:E28	E2	=IF(LEFT(D2,2)="C-","",IF(COUNTIFS(B$2:B2,B2,D$2:D2,"C-*")=0,"Group 1",IF(COUNTIFS(B$1:B1,B2,E$1:E1,"Group 1")=0,"Group 1","Group 2")))

 

[jarett]

I'm still a little hazy on the requirements, but try:

Book1
	B	C	D	E
1	PurchaseOrder	Quantity	ItemCode	GroupNo
2	2140		P-INDM10	Group 1
3	2141		x	Group 1
4	2144		C-ACTTAL03
5	2144		y	Group 1
6	2151		C-ACTDAN03
7	2151		z	Group 1
8	2153		C-ACTDAN04
9	2153		a	Group 1
10	2157		C-ACTDAN02
11	2157		b	Group 1
12	2157		C-ACTDAN04
13	2157		c	Group 2
14	2172		d	Group 1
15	2172		e	Group 1
16	2172		f	Group 1
17	2172		C-ACTLIN01
18	2172		g	Group 2
19	2172		h	Group 2
20	2172		i	Group 2
21	2172		j	Group 2
22	2172		k	Group 2
23	2172		l	Group 2
24	2172		m	Group 2
25	2172		n	Group 2
26	2172		o	Group 2
27	2172		p	Group 2
28	2172		q	Group 2
Sheet14

Cell Formulas
Range	Formula
E2:E28	E2	=IF(LEFT(D2,2)="C-","",IF(COUNTIFS(B$2:B2,B2,D$2:D2,"C-*")=0,"Group 1",IF(COUNTIFS(B$1:B1,B2,E$1:E1,"Group 1")=0,"Group 1","Group 2")))

I’ll try this in access tomorrow when I get into the office but looking at it on my phone is this in excel?

 

[Joe4]

I think Eric may have missed that this was posted in the Access forum, and posted an Excel solution.

 

[Eric W]

Yikes! Terribly sorry.