Limit entries for the same number and color code the rows..

[thelgeson10]

Hello Excel gurus! its been a while but ive run into a problem that i cant figure out. Ive attached a sample book for your viewing and ive pointed out the areas in need of help in that book. Basically im in need of some guidance on how to limit entries in a cell if the row before it has a code number in it that are the same, and id like to be able to color code the rows that have all the same code numbers, have a look and we'll see if i explained it well enough in the book. there is a bit of VBA that ive done for color coding the rows but it didnt work as i expected so it is commented out. if it can be tweaked to work, that is great.

this same question is cross posted at: Limit entries for the same number and color code the rows.. - OzGrid Free Excel/VBA Help Forum

with no answers... is this even possible to do in excel?

See the minisheet below?? ive never used that so i hope i did it right.

example WorkSheet.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	addin	Label Code	Last Name	City	Street Address	PU Time	Listed Miles	PU Code	Multiple Orders?	Order Number	Estimated Pay
2	8/1/2023	S64673	Ehlo	La Crosse	2527 Prospect	ASAP	4.5	2426	3	200011105479026	$25.00		How it looks now
3	8/1/2023	S94678	Brown	La Crosse	1400 Washington	ASAP	4.5	2426	3	200011105053475	$25.00
4	8/1/2023	G45691	Green	La Crosse	2374 First St.	ASAP	4.5	2426	3	200011105320774	$25.00
5	8/1/2023	T44576	White	Onalaska	4126 West Ave	ASAP	7.2	2624	2	200011105474431	$19.50
6	8/1/2023	M44392	Smith	Holmen	2329 Losey Blvd	ASAP	7.2	2624	2	200011105472435	$19.50
7
8
9
10	Date	Label Code	Last Name	City	Street Address	PU Time	Listed Miles	PU Code	Multiple Orders?	Order Number	Estimated Pay		How I want it to look
11	8/1/2023	S64673	Ehlo	La Crosse	2527 Prospect	ASAP	4.5	2426	3	200011105479026	$25.00		I would like this to be random colors, By Group.**
12	8/1/2023	S94678	Brown	La Crosse	1400 Washington	ASAP		2426		200011105053475			**each color is a "group"
13	8/1/2023	G45691	Green	La Crosse	2374 First St.	ASAP		2426		200011105320774
14	8/1/2023	T44576	White	Onalaska	4126 West Ave	ASAP	7.2	2624	0	200011105474431	$19.50
15	8/1/2023	M44392	Smith	Holmen	2329 Losey Blvd	ASAP		2624	0	200011105472435
16
17								↑
18								EVERYTHING is based on this number. If this number changes, that is a new "Group".
19								If this number DOES NOT CHANGE then I want the "Multiple Orders"to only show
20								on the top line of the "Group". It doesn’t matter if there are 2 entries or 10+. If there are 10 entries then
21								Multiple Orders onlys shows a single entry on the first line of that "Group"
22								HOWEVER, if for some reason the "PU Code" cells get seperated,(sorting or delayed entry) id still want "Multiple Orders" to include ALL numbers that are the same,
23								for example, 2 entries of 2426, then 5 entries of 2624, then 3 more entries of 2426… I still want the first instance of 2426 to show "5" and all 5 be the same color.
24								When a new group starts, same senario, first line shows how many orders in the group for multiple orders and they all get the same color fill.
25
26								Listed Miles and Estimated pay are direct entry cells. But I would like to put some sort of "procedure" in place to make sure that only one entry per group is
27								entered in the "Listed Miles" and "Estimated Pay" per group.
28								Eventually these cells will be used for calculation and reporting for these fields per group.
DATA

Cell Formulas
Range	Formula
I11,I2:I6	I2	=COUNTIF([PU Code],[@[PU Code]])
I14:I15	I14	=COUNTIF(Table1[PU Code],Table1[@[PU Code]])

 

[Akuini]

@thelgeson10, welcome to MrExcel.
How many groups are there approximately? I mean, if it's more than 20, you would require over 20 distinct colors, and some of these colors might appear quite similar.

You cannot attach a file on this forum.
Could you please upload a sample workbook (without sensitive data) to a file-sharing site like Dropbox.com or Google Drive, and then share the link here? Also, ensure that the link is accessible to anyone.

 

[thelgeson10]

Hello Akuini, thanks for the reply. What you see in the minisheet IS the sample work book. there isnt much more to it than that. The only thing missing is a small bit of VBA that I tried for changing color that didnt work.

**YOUR QUESTION**
Yes, there "Could" be over 20 different entries, the colors could be close in hue and that would be ok but im going to say i doubt that there would be more than 20 PER DAY.... maybe the color distintion can repeat each day? or after 24 hours? or after midnight local time? not sure how that could be set up but this is more a "PER DAY" need.

I plan to set this up with a userform for data entry but havent got that far yet. The minisheet was set up to show what i wanted to happen and all done manually so the sheet is nothing more than a representitive of what im looking for and not a working sheet. The big thing is the counts, and numbers that get repeated. for example in the first bank of info you see where the "listed miles", "multiple orders", and "estimated pay" are repeated. that info needs to be allowed ONLY in the first occurance (row) of the that unique "PU Code". The cells effected include ALL entries of the Unique PU Code. So as it is now, and as i understand it, the total miles for "2426" would total 13.5 miles and its supposed to be 4.5 for all and this cannot be "split between the entries", Multiple orders shows 9 and should be 3, this is just a count of how many orders in the batch. And estimated pay would total $75.00 and should be $25.00 and again cannot be "split". If there is a better way of doing this, by all means, let me hear your suggestions. And by the way.... that PU Code is and always will be unique. To my knowledge it should never repeat.

 

[Akuini]

HOWEVER, if for some reason the "PU Code" cells get seperated,(sorting or delayed entry) id still want "Multiple Orders" to include ALL numbers that are the same,
for example, 2 entries of 2426, then 5 entries of 2624, then 3 more entries of 2426… I still want the first instance of 2426 to show "5" and all 5 be the same color.

I don't understand this part: the first instance of 2426 only has 2 entries but you want to show 5 entries?
Your example in post #1 doesn't show multiple "PU Code" that are separated.
Could you please provide an example with sufficient data to test all of the criteria you mentioned above, along with the expected result?